Bug 1279460 - Oracle translator - OFFSET function does not work
Summary: Oracle translator - OFFSET function does not work
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: JBoss Data Virtualization 6
Classification: JBoss
Component: Teiid
Version: 6.2.0
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified
Target Milestone: CR1
: 6.3.0
Assignee: Van Halbert
QA Contact: Juraj Duráni
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2015-11-09 13:50 UTC by Juraj Duráni
Modified: 2016-08-24 11:43 UTC (History)
1 user (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2016-08-24 11:43:27 UTC
Type: Bug
Embargoed:

Attachments (Terms of Use)


Links
System ID Private Priority Status Summary Last Updated
Red Hat Issue Tracker TEIID-3815 0 Blocker Closed Oracle translator - OFFSET function does not work 2016-08-11 12:20:19 UTC

Description Juraj Duráni 2015-11-09 13:50:54 UTC 
Oracle translator translates OFFSET function as:
SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (***Query***) VIEW_FOR_LIMIT WHERE ROWNUM <= -2147483604) WHERE ROWNUM_ > 45

This always returns an empty result because of ROWNUM <= -2147483604.

Example:
Query:
SELECT INTKEY FROM bqt1.smalla ORDER BY INTKEY OFFSET 45 ROWS

Source-specific command:
SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT trunc(g_0."INTKEY") AS c_0 FROM "DV"."SMALLA" g_0 ORDER BY c_0) VIEW_FOR_LIMIT WHERE ROWNUM <= -2147483604) WHERE ROWNUM_ > 45
Comment 1 JBoss JIRA Server 2015-11-09 18:11:10 UTC 
Steven Hawkins <shawkins> updated the status of jira TEIID-3815 to Resolved
Comment 2 JBoss JIRA Server 2016-01-27 21:25:02 UTC 
Steven Hawkins <shawkins> updated the status of jira TEIID-3815 to Closed
Comment 3 Juraj Duráni 2016-06-09 13:21:28 UTC 
The Teiid still returns no row.

From the log:
15:19:50,124 DEBUG [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue78) VAmB/CPZtJOo.12.0.13 Processing NEW request: SELECT g_0.INTKEY AS c_0 FROM Source.SMALLA AS g_0 ORDER BY c_0 OFFSET 45 ROWS
15:19:50,125 DEBUG [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue78) VAmB/CPZtJOo.12.0.13 Obtained execution
15:19:50,125 DEBUG [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue78) Source-specific command: SELECT c_0 FROM (SELECT g_0."INTKEY" AS c_0 FROM "DV"."SMALLA" g_0 ORDER BY c_0) WHERE ROWNUM > 45
15:19:50,269 DEBUG [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue78) VAmB/CPZtJOo.12.0.13 Executed command
15:19:50,270 DEBUG [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue78) VAmB/CPZtJOo.12.0.13 Processing MORE request
15:19:50,270 DEBUG [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue78) VAmB/CPZtJOo.12.0.13 Getting results from connector
15:19:50,270 DEBUG [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue78) VAmB/CPZtJOo.12.0.13 Obtained last batch, total row count: 0
Comment 4 JBoss JIRA Server 2016-06-09 13:21:54 UTC 
Juraj Duráni <jdurani> updated the status of jira TEIID-3815 to Reopened
Comment 5 JBoss JIRA Server 2016-06-09 17:18:35 UTC 
Steven Hawkins <shawkins> updated the status of jira TEIID-3815 to Resolved
Comment 6 JBoss JIRA Server 2016-08-11 12:20:19 UTC 
Steven Hawkins <shawkins> updated the status of jira TEIID-3815 to Closed
Note You need to log in before you can comment on or make changes to this bug.