[teiid-issues] [JBoss JIRA] (TEIID-4988) Oracle - table alias is not used in whole query

Oracle - table alias is not used in whole query ----------------------------------------------- Key: TEIID-4988 URL: https://issues.jboss.org/browse/TEIID-4988 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 8.12.12.6_4 Reporter: Juraj Duráni Assignee: Steven Hawkins Fix For: 10.0, 8.12.12.6_4, 9.3.2 When I run query like \[1\] Teiid translates it to query which does not use one of table aliases in whole query. Oracle (11g 11.2.0.2.0) seems to be sensitive to aliasing table and then not using it. Query fails. Source specific query (problematic one) \[2\]. Sample query which fails if running directly against Oracle database \[3\] {code:sql|title=\[1\] Query} select s_name, s_address from tpch1.soaeds.supplier, tpch1.soaeds.nation where s_supplierkey in ( select ps_supplierkey from tpch2.soaeds.partsupp where ps_partkey in ( select p_partkey from tpch2.soaeds.part where p_name like 'powder%' ) and ps_availqty > ( select 0.5 * sum(l_quantity) from tpch2.soaeds.lineitem where l_partkey = ps_partkey and l_supplierkey = ps_supplierkey and l_shipdate >= '1994-01-01' and l_shipdate < TIMESTAMPADD(SQL_TSI_YEAR,'1', '1994-01-01') ) ) and s_nationkey = n_nationkey and n_name = 'BRAZIL' order by s_name; {code} {code:sql|title=\[2\] Source-specific query} SELECT g_0."PS_SUPPLIERKEY" AS c_0 FROM "SOAEDS"."PARTSUPP" g_0 WHERE g_0."PS_PARTKEY" IN (SELECT g_1."P_PARTKEY" FROM "SOAEDS"."PART" g_1 WHERE g_1."P_NAME" LIKE 'powder%') AND g_0."PS_AVAILQTY" > (SELECT (0.5 * SUM(g_2."L_QUANTITY")) FROM "SOAEDS"."LINEITEM" g_2 WHERE g_2."L_PARTKEY" = "SOAEDS"."PARTSUPP"."PS_PARTKEY" AND g_2."L_SUPPLIERKEY" = "SOAEDS"."PARTSUPP"."PS_SUPPLIERKEY" AND g_2."L_SHIPDATE" >= to_date('1994-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND g_2."L_SHIPDATE" < ?) ORDER BY c_0 {code} {code:sql|title=\[3\] Oracle query} SELECT smalla.intkey FROM smalla a {code}