[db2] arel query building with offset/limit broken

[kares]

~ as on AR < 4.2 ... still aggregate generate something else (an invalid) :

Error: test: should handle pagination with ordering even when order column is not returned(DB2LimitOffsetTest)
  ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=B.INTERNAL$ROWNUM, DRIVER=4.15.134: SELECT B.* FROM (SELECT A.*, row_number() OVER () AS internal$rownum FROM (SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM names JOIN persons p on p.id = names.person_id) A ) B WHERE B.internal$rownum > 3 AND B.internal$rownum <= 5) subquery_for_count
arjdbc/jdbc/RubyJdbcConnection.java:878:in `execute_query'
/home/kares/workspace/oss/activerecord-jdbc-adapter/lib/arjdbc/jdbc/adapter.rb:452:in `exec_query'
/opt/local/rvm/gems/jruby-1.7.22@jdbc/gems/activerecord-4.2.4/lib/active_record/connection_adapters/abstract_adapter.rb:473:in `log'
/opt/local/rvm/gems/jruby-1.7.22@jdbc/gems/activesupport-4.2.4/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/opt/local/rvm/gems/jruby-1.7.22@jdbc/gems/activesupport-4.2.4/lib/active_support/notifications/instrumenter.rb:19:in `instrument'
/opt/local/rvm/gems/jruby-1.7.22@jdbc/gems/activerecord-4.2.4/lib/active_record/connection_adapters/abstract_adapter.rb:467:in `log'
/home/kares/workspace/oss/activerecord-jdbc-adapter/lib/arjdbc/jdbc/adapter.rb:452:in `exec_query'
/home/kares/workspace/oss/activerecord-jdbc-adapter/lib/arjdbc/db2/adapter.rb:515:in `select'
/opt/local/rvm/gems/jruby-1.7.22@jdbc/gems/activerecord-4.2.4/lib/active_record/connection_adapters/abstract/database_statements.rb:32:in `select_all'
/opt/local/rvm/gems/jruby-1.7.22@jdbc/gems/activerecord-4.2.4/lib/active_record/connection_adapters/abstract/query_cache.rb:70:in `select_all'
/opt/local/rvm/gems/jruby-1.7.22@jdbc/gems/activerecord-4.2.4/lib/active_record/relation/calculations.rb:264:in `execute_simple_calculation'
/opt/local/rvm/gems/jruby-1.7.22@jdbc/gems/activerecord-4.2.4/lib/active_record/relation/calculations.rb:221:in `perform_calculation'
/opt/local/rvm/gems/jruby-1.7.22@jdbc/gems/activerecord-4.2.4/lib/active_record/relation/calculations.rb:127:in `calculate'
/opt/local/rvm/gems/jruby-1.7.22@jdbc/gems/activerecord-4.2.4/lib/active_record/relation/calculations.rb:42:in `count'
/opt/local/rvm/gems/jruby-1.7.22@jdbc/gems/activerecord-4.2.4/lib/active_record/relation.rb:268:in `empty?'
/home/kares/workspace/oss/activerecord-jdbc-adapter/test/db/db2/simple_test.rb:204:in `test: should handle pagination with ordering even when order column is not returned'
     201: 
     202:   test "should handle pagination with ordering even when order column is not returned" do
     203:     # passes on 1.2.9, failed on <= 1.3.13
  => 204:     assert_empty arel_with_pagination(3).order("p.tax_code").all
     205:   end

the expected SQL (on 4.1) is: SELECT COUNT(count_column) FROM (SELECT B.* FROM (SELECT A.*, row_number() OVER () AS internal$rownum FROM (SELECT 1 AS count_column FROM names JOIN persons p on p.id = names.person_id) A ) B WHERE B.internal$rownum > 3 AND B.internal$rownum <= 5) subquery_for_count

generated on 4.2 is: SELECT B.* FROM (SELECT A.*, row_number() OVER () AS internal$rownum FROM (SELECT COUNT(count_column) FROM (SELECT 1 AS count_column FROM names JOIN persons p on p.id = names.person_id) A ) B WHERE B.internal$rownum > 3 AND B.internal$rownum <= 5) subquery_for_count

--- Want to back this issue? **[Post a bounty on it!](https://www.bountysource.com/issues/26385242-db2-arel-query-building-with-offset-limit-broken?utm_campaign=plugin&utm_content=tracker%2F136963&utm_medium=issues&utm_source=github)** We accept bounties via [Bountysource](https://www.bountysource.com/?utm_campaign=plugin&utm_content=tracker%2F136963&utm_medium=issues&utm_source=github).

[donv]

How do we test this?

[kares]

the obvious way is rake test_db2 ... so its currently only testable with a DB2 instance, but your q is valid - it should be reproducable without a real db as well (usually each test/db/xxx sub-folder has a unit_test.rb)

trying to finish the 4.2 review (for all adapters) and leaving the left-overs as separate issues, this one seems like it could really use a new perspective - recall there has been attempts to get orders working with the current limit/offset hacks but caused regressions ... current status: https://github.com/jruby/activerecord-jdbc-adapter/commit/7ab8161b3046c60c5f8ae990e95dc90dfbd4532b

[rsov]

Whats the reason for not using the provided LIMIT and OFFSET SQL commands?

Seems like a hack to get around it