[ https://issues.apache.org/jira/browse/KYLIN-2407?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
liyang updated KYLIN-2407: -------------------------- Summary: TPC-H query 20, routing bug in lookup query and cube query (was: TPC-H query 20, why this query returns no result?) > TPC-H query 20, routing bug in lookup query and cube query > ---------------------------------------------------------- > > Key: KYLIN-2407 > URL: https://issues.apache.org/jira/browse/KYLIN-2407 > Project: Kylin > Issue Type: Bug > Reporter: liyang > Assignee: Kaige Liu > Fix For: v2.0.0 > > Attachments: KYLIN-2407.patch > > > Below query returns no result. > {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 > ), > tmp5 as ( > select > ps_suppkey > from > v_partsupp inner join tmp3 on ps_partkey = l_partkey and ps_suppkey = > l_suppkey > where > ps_availqty > sum_quantity > ) > select > s_name, > s_address > from > supplier > where > s_suppkey IN (select ps_suppkey from tmp5) > order by s_name > {code} > While another similar query returns correct result. > {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} > Maybe something wrong with the "where ... IN ..." clause? -- This message was sent by Atlassian JIRA (v6.3.15#6346)