[teiid-issues] [JBoss JIRA] Resolved: (TEIID-756) Oracle Connector is not producing the correct queries when using a LIMIT clause

Thu Aug 6 14:42:29 EDT 2009

     [ https://jira.jboss.org/jira/browse/TEIID-756?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-756.
----------------------------------

    Resolution: Rejected

I could not reproduce your result of returning no rows.

Just to make sure we're on the same page, our limit clause limit parameter refers to the absolute number of rows that can be returned after processing offset, thus when pushed to oracle it must make the inner criteria cumulative.  

Also the fix to TEIID-700 that any situation that would actually care about the projected symbols will have the outer select expanded to use the alias names.  Otherwise having an additional column in the results will simply be dropped during the retrieval of values.

> Oracle Connector is not producing the correct queries when using a LIMIT clause
> -------------------------------------------------------------------------------
>
>                 Key: TEIID-756
>                 URL: https://jira.jboss.org/jira/browse/TEIID-756
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 6.0.0, 6.1.0, 6.2.0
>            Reporter: Larry O'Leary
>            Assignee: Steven Hawkins
>
> When using a LIMIT clause in a query, the Oracle Connector rewrites it to something that Oracle will understand.  The problem is that the rewritten version of the query is not returning the expected results.
> For example:
> SELECT INTKEY FROM BQT1.MEDIUMA LIMIT 50, 100
> Should return 50 rows starting at row 100 but instead returns 0 rows when executed via the Oracle Connector.  Currently the above query is rewritten as:
> SELECT * FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT MediumA.IntKey FROM MediumA) VIEW_FOR_LIMIT WHERE ROWNUM <= 150) WHERE ROWNUM_ > 50
> Further more, the rewritten version of the query is also projecting a column named ROWNUM_ which would be invalid based on our query processing.

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