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

[Steven Hawkins (JIRA)]

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

Work on TEIID-4988 started by Steven Hawkins.
---------------------------------------------
> 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
>    Affects Versions: 8.12.x-6.4
>            Reporter: Juraj Duráni
>            Assignee: Steven Hawkins
>
> 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}



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)