[KYLIN-2406] TPC-H query 20, prevent NPE and give error hint

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: v2.0.0
• Assignee: Kaige Liu
• Reporter: liyang
• Created: 18/Jan/17 07:47
• Updated: 30/Apr/17 12:21
• Resolved: 06/Feb/17 09:09

Description

Below query triggers NPE

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

While below query is OK. Only difference being the order of "inner join tmp3" and "inner join supplier"

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

Issue Links

• is related to: KYLIN-2467 TPC-H queries run on Kylin