[teiid-issues] [JBoss JIRA] (TEIID-3673) Subquery field with limit giving a wrong JDBC SQL in Oracle

[ https://issues.jboss.org/browse/TEIID-3673?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-3673: --------------------------------------- The sql is valid. This is simply an oracle limitation - http://stackoverflow.com/questions/5314321/oracle-nested-correlated-subqu... and noted in https://issues.jboss.org/browse/TEIID-1008 The difference here is that the query was not directly entered with a second level reference, but was created by the effect of applying the limit. So this will need to have a fix provided. At worst we'll inhibit the subquery pushdown. > 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 > Reporter: Mark Tawk > Assignee: Steven Hawkins > > 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 > Note that the same case works fine with H2 and mysql -- This message was sent by Atlassian JIRA (v6.4.11#64026)