[
https://issues.apache.org/jira/browse/KYLIN-2427?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kaige Liu updated KYLIN-2427:
-----------------------------
Description:
KYLIN-2406 reports an issue: The order of joins will affect the result of
query. For example, below query leads to "No model found"
Below query triggers NPE
{code}
with tmp3 as (
select l_partkey, 0.5 * sum(l_quantity) as sum_quantity, l_suppkey
from v_lineitem
inner join supplier on l_suppkey = s_suppkey
inner join nation on s_nationkey = n_nationkey
inner join part on l_partkey = p_partkey
where l_shipdate >= '1992-01-01' and l_shipdate <= '1995-01-01'
and n_name = 'CANADA'
and p_name like 'forest%'
group by l_partkey, l_suppkey
)
select
s_name,
s_address
from
v_partsupp
inner join tmp3 on ps_partkey = l_partkey and ps_suppkey = l_suppkey
inner join supplier on ps_suppkey = s_suppkey
where
ps_availqty > sum_quantity
group by
s_name, s_address
order by
s_name
{code}
While below query is OK. Only difference being the order of "inner join tmp3"
and "inner join supplier"
{code}
with tmp3 as (
select l_partkey, 0.5 * sum(l_quantity) as sum_quantity, l_suppkey
from v_lineitem
inner join supplier on l_suppkey = s_suppkey
inner join nation on s_nationkey = n_nationkey
inner join part on l_partkey = p_partkey
where l_shipdate >= '1992-01-01' and l_shipdate <= '1995-01-01'
and n_name = 'CANADA'
and p_name like 'forest%'
group by l_partkey, l_suppkey
)
select
s_name,
s_address
from
v_partsupp
inner join supplier on ps_suppkey = s_suppkey
inner join tmp3 on ps_partkey = l_partkey and ps_suppkey = l_suppkey
where
ps_availqty > sum_quantity
group by
s_name, s_address
order by
s_name
{code}
was:
KYLIN-2406 reports an issue: The order of joins will affect the result of
query. For example, below query leads to "No model found"
Below query triggers NPE
{code}
with tmp3 as (
select l_partkey, 0.5 * sum(l_quantity) as sum_quantity, l_suppkey
from v_lineitem
inner join supplier on l_suppkey = s_suppkey
inner join nation on s_nationkey = n_nationkey
inner join part on l_partkey = p_partkey
where l_shipdate >= '1992-01-01' and l_shipdate <= '1995-01-01'
and n_name = 'CANADA'
and p_name like 'forest%'
group by l_partkey, l_suppkey
)
select
s_name,
s_address
from
v_partsupp
inner join tmp3 on ps_partkey = l_partkey and ps_suppkey = l_suppkey
inner join supplier on ps_suppkey = s_suppkey
where
ps_availqty > sum_quantity
group by
s_name, s_address
order by
s_name
{code}
While below query is OK. Only difference being the order of "inner join tmp3"
and "inner join supplier"
{code}
with tmp3 as (
select l_partkey, 0.5 * sum(l_quantity) as sum_quantity, l_suppkey
from v_lineitem
inner join supplier on l_suppkey = s_suppkey
inner join nation on s_nationkey = n_nationkey
inner join part on l_partkey = p_partkey
where l_shipdate >= '1992-01-01' and l_shipdate <= '1995-01-01'
and n_name = 'CANADA'
and p_name like 'forest%'
group by l_partkey, l_suppkey
)
select
s_name,
s_address
from
v_partsupp
inner join supplier on ps_suppkey = s_suppkey
inner join tmp3 on ps_partkey = l_partkey and ps_suppkey = l_suppkey
where
ps_availqty > sum_quantity
group by
s_name, s_address
order by
s_name
{code}
But below query is OK.
{code}
with tmp3 as (
select l_partkey, 0.5 * sum(l_quantity) as sum_quantity, l_suppkey
from v_lineitem
inner join supplier on l_suppkey = s_suppkey
inner join nation on s_nationkey = n_nationkey
inner join part on l_partkey = p_partkey
where l_shipdate >= '1992-01-01' and l_shipdate <= '1995-01-01'
and n_name = 'CANADA'
and p_name like 'forest%'
group by l_partkey, l_suppkey
)
select
s_name,
s_address
from
v_partsupp
inner join supplier on ps_suppkey = s_suppkey
inner join tmp3 on ps_partkey = l_partkey and ps_suppkey = l_suppkey
where
ps_availqty > sum_quantity
group by
s_name, s_address
order by
s_name
{code}
> Auto adjust join order to make query executable
> -----------------------------------------------
>
> Key: KYLIN-2427
> URL: https://issues.apache.org/jira/browse/KYLIN-2427
> Project: Kylin
> Issue Type: Bug
> Reporter: Kaige Liu
>
> KYLIN-2406 reports an issue: The order of joins will affect the result of
> query. For example, below query leads to "No model found"
> Below query triggers NPE
> {code}
> with tmp3 as (
> select l_partkey, 0.5 * sum(l_quantity) as sum_quantity, l_suppkey
> from v_lineitem
> inner join supplier on l_suppkey = s_suppkey
> inner join nation on s_nationkey = n_nationkey
> inner join part on l_partkey = p_partkey
> where l_shipdate >= '1992-01-01' and l_shipdate <= '1995-01-01'
> and n_name = 'CANADA'
> and p_name like 'forest%'
> group by l_partkey, l_suppkey
> )
> select
> s_name,
> s_address
> from
> v_partsupp
> inner join tmp3 on ps_partkey = l_partkey and ps_suppkey = l_suppkey
> inner join supplier on ps_suppkey = s_suppkey
> where
> ps_availqty > sum_quantity
> group by
> s_name, s_address
> order by
> s_name
> {code}
> While below query is OK. Only difference being the order of "inner join tmp3"
> and "inner join supplier"
> {code}
> with tmp3 as (
> select l_partkey, 0.5 * sum(l_quantity) as sum_quantity, l_suppkey
> from v_lineitem
> inner join supplier on l_suppkey = s_suppkey
> inner join nation on s_nationkey = n_nationkey
> inner join part on l_partkey = p_partkey
> where l_shipdate >= '1992-01-01' and l_shipdate <= '1995-01-01'
> and n_name = 'CANADA'
> and p_name like 'forest%'
> group by l_partkey, l_suppkey
> )
> select
> s_name,
> s_address
> from
> v_partsupp
> inner join supplier on ps_suppkey = s_suppkey
> inner join tmp3 on ps_partkey = l_partkey and ps_suppkey = l_suppkey
> where
> ps_availqty > sum_quantity
> group by
> s_name, s_address
> order by
> s_name
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
