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

[RH Bugzilla Integration (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4988?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13526850#comment-13526850 ] 

RH Bugzilla Integration commented on TEIID-4988:
------------------------------------------------

Maximilián Košiarčik <mkosiarc at redhat.com> changed the Status of [bug 1469946|https://bugzilla.redhat.com/show_bug.cgi?id=1469946] from ON_QA to VERIFIED

> 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.x-6.4
>            Reporter: Juraj Duráni
>            Assignee: Steven Hawkins
>             Fix For: 10.0, 8.12.x-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}



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)