[teiid-issues] [JBoss JIRA] (TEIID-3673) Subquery field with limit giving a wrong JDBC SQL in Oracle
Steven Hawkins (JIRA)
issues at jboss.org
Fri Sep 4 13:08:00 EDT 2015
[ https://issues.jboss.org/browse/TEIID-3673?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steven Hawkins resolved TEIID-3673.
-----------------------------------
Fix Version/s: 8.12
Resolution: Done
Added the capability supportsCorrelatedSubqueryLimit to capture the Oracle limitation with creating the limit view at the translator layer.
> Subquery field with limit giving a wrong JDBC SQL in Oracle
> ------------------------------------------------------------
>
> Key: TEIID-3673
> URL: https://issues.jboss.org/browse/TEIID-3673
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.0
> Reporter: Mark Tawk
> Assignee: Steven Hawkins
> Fix For: 8.12
>
>
> i'm executing the following query using Teiid 8.11.3 and Oracle translator:
> select "Trx_LjM_030915"."CID" as "Trx_LjM_030915_CID"
> ,"Trx_LjM_030915"."TransactionDate" as "Trx_LjM_030915_TransactionDa"
> ,(select "Trx_LjM_030915_sub"."TransactionDate" as "Trx_LjM_030915_TransactionDa"
> from "AUDIDATA_SCHEMANAME_MODEL"."AUDIDATA_SCHEMANAME"."Trx_LjM_030915" "Trx_LjM_030915_sub"
> where ("Trx_LjM_030915"."CID" = "Trx_LjM_030915_sub"."CID")
> AND ( ("Trx_LjM_030915_sub"."TransactionDate" < "Trx_LjM_030915"."TransactionDate") )
> order by "Trx_LjM_030915_sub"."TransactionDate" DESC
> LIMIT 0 , 1) as "SubQuery Trx"
> from "AUDIDATA_SCHEMANAME_MODEL"."AUDIDATA_SCHEMANAME"."Trx_LjM_030915" "Trx_LjM_030915" LIMIT 0 , 10
> And i'm getting this error:
> Error TEIID30504 AUDIDATA_SCHEMANAME_MODELVDB: 904 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT c_0, c_1, c_2 FROM (SELECT g_0."CID" AS c_0, g_0."TransactionDate" AS c_1, (SELECT c_0 FROM (SELECT g_1."TransactionDate" AS c_0 FROM "AUDIDATA_SCHEMANAME"."Trx_LjM_030915" g_1 WHERE g_1."CID" = g_0."CID" AND g_1."TransactionDate" < g_0."TransactionDate" ORDER BY c_0 DESC) WHERE ROWNUM <= 1) AS c_2 FROM "AUDIDATA_SCHEMANAME"."Trx_LjM_030915" g_0) WHERE ROWNUM <= 10
> You can notice g_0."CID" and g_0."TransactionDate" in the where condition are no longer valid in the Teiid generated JDBC SQL; Since they do not exist in the parent query.
> Note that the same case works fine with H2 and mysql
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
More information about the teiid-issues
mailing list