[JIRA] Commented: (ACTIVERECORD_JDBC-161) activerecord-jdbc-adapter breaks working with db2 (method replace_limit_offset seems to be the issue, if query has subqueries)

["farooqpervaiz (JIRA)" <jira-no-reply@kenai.com>]

    [ 
http://kenai.com/jira/browse/ACTIVERECORD_JDBC-161?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=58498#action_58498
 ] 

farooqpervaiz commented on ACTIVERECORD_JDBC-161:
-------------------------------------------------

Hi Nick,
          Yes, replacing gsub with sub has fixed the issue. This fix will be 
part of which activerecord-jdbc-adapter gem version?

> activerecord-jdbc-adapter breaks working with db2 (method 
> replace_limit_offset seems to be the issue, if query has subqueries)
> ------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: ACTIVERECORD_JDBC-161
>                 URL: http://kenai.com/jira/browse/ACTIVERECORD_JDBC-161
>             Project: activerecord-jdbc
>          Issue Type: Bug
>          Components: DB2
>    Affects Versions: 1.2.0
>         Environment: windows + jruby 1.6 + activerecord-jdbc-adapter (1.2.0)
>            Reporter: farooqpervaiz
>            Assignee: Nick Sieger
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: 
http://kenai.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira