I have discovered a potential bug in activerecord-jdbc-adapter while
working with db2. I am using paginate gem to paginate my sql results. My
query is like this:
self.paginate(:all,:page => 1,:per_page => 30,:order=> “name”],
:conditions => [" country_id in (select distinct country_id from
country) and group_id = 10 )"])
I had this query working fine when I was using ibm_db gem with my rails
application. After I have to converted to jruby and start using
activerecord-jdbc-adapter, the above statement fails because the query
generated from this is incorrect. I further investigated it to find the
root cause, and think found the problem. This will work fine if I remove
the sub query from this statement i.e removing (select distinct
country_id from country) will fix the issue.
Problem seems to be in method replace_limit_offset of
activerecord-jdbc-adapter-1.2.0\lib\arjdbc\db2\adapter.rb file. In this
method, it replaces ‘select’ with this ‘SELECT B.* FROM (SELECT A.*,
row_number() over () AS internal$rownum FROM (SELECT’ to introduce
paging. While this is correct, but because I have two selects in my
query (because of a subquery), both selects get replaced by this replace
string, thus making the query invalid.
Only first ‘select’ word in the string should have been replaced.
Following code does this replacement:
sql.gsub!(/SELECT/i, ‘SELECT B.* FROM (SELECT A.*, row_number() over ()
AS internal$rownum FROM (SELECT’)
sql << “) A ) B WHERE B.internal$rownum > #{offset} AND
B.internal$rownum <= #{limit + offset}”
Am I right? Isn’t it a bug? Has anyone else experienced the same issue?
I am using the 1.2.0 version of the activerecord-jdbc-adapter gem. Who
should I contact to fix this?
Regards,
Farooq