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

[Juraj Duráni (JIRA)]

Juraj Duráni created TEIID-4988:
-----------------------------------

             Summary: 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)