Kaige Liu created KYLIN-2427: --------------------------------- Summary: 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} 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} -- This message was sent by Atlassian JIRA (v6.3.15#6346)