paginate_by_sql fails when using Oracle and the jdbc driver.

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'
end

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);
  COUNT(*)
----------
         0

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

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.

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'

end

which fixes it for me.

Not fixed yet. Looking into it

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?

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

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.