[teiid-issues] [JBoss JIRA] (TEIID-4988) Oracle - table alias is not used in whole query
Juraj Duráni (JIRA)
issues at jboss.org
Wed Jul 12 02:39:01 EDT 2017
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)
More information about the teiid-issues
mailing list