You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@kylin.apache.org by "liyang (JIRA)" <ji...@apache.org> on 2017/01/18 07:51:26 UTC
[jira] [Created] (KYLIN-2407) TPC-H query 20, why this query
returns no result?
liyang created KYLIN-2407:
-----------------------------
Summary: TPC-H query 20, why this query returns no result?
Key: KYLIN-2407
URL: https://issues.apache.org/jira/browse/KYLIN-2407
Project: Kylin
Issue Type: Bug
Reporter: liyang
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.4#6332)