#237 open

paginate_by_sql fails when using Oracle and the jdbc driver.

Reported by squirrelbone | July 8th, 2008 @ 09:05 PM

Why not remove the following block of code altogether?

unless ['oracle', 'oci'].include?(self.connection.adapter_name.downcase)
  count_query << ' AS count_table'

When using JDBC connection.adapter_name shows up as jdbc regardless of the database. So this will fail for Oracle when using JDBC and when the inner select statement returns no rows.

count_query = "SELECT COUNT(*) FROM (#{count_query})"

Comments and changes to this ticket

  • Mislav

    Mislav August 4th, 2008 @ 05:32 PM

    • Tag changed from will_paginate to oracle, will_paginate

    Interesting. And how will removing those lines of code fix the problem with Oracle?

  • squirrelbone

    squirrelbone August 6th, 2008 @ 12:42 AM

    Example of what happens with Oracle. Since

    count_query = "SELECT COUNT(*) FROM (#{count_query})"
    unless ['oracle', 'oci'].include?(self.connection.adapter_name.downcase)
    count_query << ' AS count_table'

    count_query is a subquery, though in oracle when you try to name it using "AS count_table" it throws an error. So when using JDBC, despite connecting to an Oracle database (self.connection.adapter_name.downcase == "jdbc") it will runs that block of code which isn't necessary in the first place. Even though naming it count_table works on the other databases, it's used for nothing.

    So I'll show you in Oracle with a simple example

    SQL> create table pooper (id NUMBER);
    Table created.
    SQL> select count(*) from (select * from pooper);
    SQL> select count(*) from (select * from pooper) AS count_table;
    select count(*) from (select * from pooper) AS count_table
    ERROR at line 1:
    ORA-00933: SQL command not properly ended
  • Mislav

    Mislav August 6th, 2008 @ 03:14 PM

    • State changed from “new” to “open”

    I see now. I'll try to take a different approach of just adding the "AS ..." alias explicitly to databases which need it.

  • squirrelbone

    squirrelbone August 7th, 2008 @ 04:05 PM

    Why is that alias needed for any of the databases? I can't find anywhere that it's used. If I were you I'd just remove this block of code:

    @@@ruby unless ['oracle', 'oci'].include?(self.connection.adaptername.downcase) countquery << ' AS count_table' end

    Unless I'm missing something, which is certainly possible.
  • dave smylie

    dave smylie February 24th, 2009 @ 10:26 PM

    Not sure if this has been fixed in git, but bug still exists in the current gem version (2.3.7)

    I'm using the oracleenhanced adaptor, which falls into this unless block and causes invalid sql.

    I've changed it to:

    unless ['oracle', 'oci', 'oracleenhanced'].include? self.connection.adapter_name.downcase)

    count_query << '  AS count_table'


    which fixes it for me.

  • Mislav

    Mislav February 26th, 2009 @ 06:50 PM

    Not fixed yet. Looking into it

  • chobomuffin

    chobomuffin March 31st, 2009 @ 02:52 AM

    I am also experiencing this problem with the Oracle Enhanced Adapter. Adding 'oracleenhanced' to the following seemed to work; unless ['oracle', 'oci'].include?(self.connection.adapter_name.downcase)

    However do you even need the ' AS count_table' at all? Does MySQL or other database implementations require that syntax?

  • Mislav

    Mislav March 31st, 2009 @ 11:27 PM

    They probably don't. I will throw this out the window soon, so you guys don't have any more problems

  • nathanvda

    nathanvda April 23rd, 2009 @ 05:47 PM

    We switched to the oracle-enhanced and also bumped into this error. Squirrelbone gave a good explanation. I would just adjust the code as mentioned above too:

    unless ['oracle', 'oci', 'oracle_enhanced'].include?(self.connection.adapter_name.downcase)

    This works for me, and does not impact the other databases, where the extra alias might be needed.

  • Mislav

    Mislav May 21st, 2009 @ 11:36 PM

    • Tag changed from oracle, will_paginate to jdbc, oracle, will_paginate

    I improved oracle detection a bit. Still doesn't solve the JDBC issue, though.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Everyone's favorite Ruby library for pagination of practically anything!