Problem with will_paginate

[TheSynthMaster]

I have this problem when using the gem will paginate, using jrails 9.1.5.0, rails 5.0.0.1 under windows.

I'm not 100% sure if this is a problem of will paginate or oracle enhanced, but I suspect it's a bug in oracle enhanced dealing with limits to the number of returned rows.

This work:
@vendors = Vendor.all

This don't work
@vendors = Vendor.all.paginate :per_page => 20 , :page => params[:page]

giving the following error message:

Java::JavaSql::SQLException: Missing IN or OUT parameter at index:: 3:
SELECT * FROM (
SELECT raw_sql_., rownum raw_rnum
FROM (SELECT "VENDORS"._ FROM "VENDORS" ORDER BY "VENDORS"."ID" ASC ) raw_sql_ WHERE rownum <= (:a1 + :a2) ) WHERE raw_rnum_ > :a1

The query in itself it's correct, eg:

SELECT * FROM (
SELECT raw_sql_., rownum raw_rnum
FROM (SELECT "VENDORS"._ FROM "VENDORS" ORDER BY "VENDORS"."ID" ASC ) raw_sql_ WHERE rownum <= (100) ) WHERE raw_rnum_ > 0

in toad just works... any ideas?

[yahonda]

This error is related with #848 and rails/arel#438. There is a fix made #450 and I've found it is for CRuby only.

Current possible workarounds are:

• Upgrade database to 12.1 which uses Oracle12 visitor
• Use CRuby

@roooodcastro Do you have some idea for this exception with JRuby?

[TheSynthMaster]

First of all, thanks for the answer... I was unsure if it was a problem of jruby, will paginate, oracle enhanced adapter or any of the other gem... so knowing the source of the problem is a start...

Changing db is obviously out of question, these are production machines and a db change is something very costly and long to do... also going back to cruby is something I don't want to do because the multithread of cruby is awful...

In your opinion what is the repository more suitable to open the issue?

[roooodcastro]

@yahonda No, sorry, I don't know much about JRuby and JDBC.

But looking at #144, I noticed that the error then was on some "index:: 1", which was probably the first and only bind param of the query being tested there:

TestPost.where(TestPost.arel_table[pk.name].eq(sub)).bind([pk, 1]).explain

In this case, there are 2 bind parameters, but :a1 is being called twice, the last one bing the third bind parameter call. I imagine this is why the error reads "at index:: 3":

WHERE rownum <= (:a1 + :a2) ) WHERE raw_rnum_ > :a1

However, I don't know how this can be fixed.

[roooodcastro]

And by the way I tried looking again at #848 and rails/arel#438, and found out that because of this commit:

rails/rails@574f255#diff-bf6dd6226db3aab589916f09236881c7R959,

the queries containing both limit and offset can't be simplified to use a single bind parameter or the old behaviour of doing the limit + offset sum directly, because Rails only substitutes the binds with the actual values after the visitor is executed, as seen here.

The only way to return to the old, ideal behaviour (for Oracle < 12) is to revert that Rails change, which I imagine won't happen.

[yahonda]

Thanks guys.

@PanDar1
Would you update the Gemfile and see if it still reproduces or not.

gem 'activerecord-oracle_enhanced-adapter', github: 'yahonda/oracle-enhanced', branch: 'diag_1030'

@roooodcastro Thanks for the reply. since this query binds :a1 twice then
I guess that bind variables are referenced by their order in Oracle enhanced adapter JDBC implementation otherwise referenced by name in Oracle enhanced adapter OCI implementation.

Once this fix is confirmed I'm going to open a pull request to master branch.

Thanks.

[TheSynthMaster]

Sure yahonda, tomorrow I will test it and let you know... In the meanwhile, thanks a lot!

[TheSynthMaster]

It works!!!

[yahonda]

Glad to know it has been fixed. closing it since #1033 has been merged to master.

Thanks.