[jira] [Commented] (KYLIN-2407) TPC-H query 20, why this query returns no result?

[" Kaige Liu (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/KYLIN-2407?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15864864#comment-15864864 ] 

 Kaige Liu commented on KYLIN-2407:
-----------------------------------

IN clause is parsed to an inner join here.So that we got a "LOOKUP T" join "SUB QUERY" which should use *executeLookupTableQuery* to do the query, but was routed to *executeOLAPQuery*.

{code}
OLAPToEnumerableConverter
  EnumerableLimit(fetch=[50000])
    EnumerableSort(sort0=[$0], dir0=[ASC])
      EnumerableCalc(expr#0..7=[{inputs}], S_NAME=[$t1], S_ADDRESS=[$t2])
        EnumerableJoin(condition=[=($0, $7)], joinType=[inner])       <---------------------  IN clause here
          OLAPTableScan(table=[[TPCH_FLAT_ORC_2, SUPPLIER]], fields=[[0, 1, 2, 3, 4, 5, 6]])       <--------------- This is a lookup table
          EnumerableAggregate(group=[{0}])
            EnumerableCalc(expr#0..10=[{inputs}], expr#11=[>($t2, $t9)], PS_SUPPKEY=[$t1], $condition=[$t11])
              EnumerableJoin(condition=[AND(=($0, $8), =($1, $10))], joinType=[inner])
                OLAPTableScan(table=[[TPCH_FLAT_ORC_2, V_PARTSUPP]], fields=[[0, 1, 2, 3, 4, 5, 6, 7]])
                EnumerableCalc(expr#0..2=[{inputs}], expr#3=[0.5], expr#4=[*($t3, $t2)], L_PARTKEY=[$t0], SUM_QUANTITY=[$t4], L_SUPPKEY=[$t1])
                  EnumerableAggregate(group=[{0, 1}], agg#0=[SUM($2)])
                    EnumerableCalc(expr#0..36=[{inputs}], expr#37=['1992-01-01'], expr#38=[>=($t8, $t37)], expr#39=['1995-01-01'], expr#40=[<=($t8, $t39)], expr#41=['CANADA'], expr#42=[=($t28, $t41)], expr#43=['forest%'], expr#44=[LIKE($t31, $t43)], expr#45=[AND($t38, $t40, $t42, $t44)], L_PARTKEY=[$t1], L_SUPPKEY=[$t2], L_QUANTITY=[$t3], $condition=[$t45])
                      OLAPJoinRel(condition=[=($1, $30)], joinType=[inner])
                        OLAPJoinRel(condition=[=($23, $27)], joinType=[inner])
                          OLAPJoinRel(condition=[=($2, $20)], joinType=[inner])
                            OLAPTableScan(table=[[TPCH_FLAT_ORC_2, V_LINEITEM]], fields=[[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19]])
                            OLAPTableScan(table=[[TPCH_FLAT_ORC_2, SUPPLIER]], fields=[[0, 1, 2, 3, 4, 5, 6]])
                          OLAPTableScan(table=[[TPCH_FLAT_ORC_2, NATION]], fields=[[0, 1, 2]])
                        OLAPTableScan(table=[[TPCH_FLAT_ORC_2, PART]], fields=[[0, 1, 2, 3, 4, 5, 6]])
{code}

The logic to route query method below is not correct:
{code}
private String genExecFunc() {
        // if the table to scan is not the fact table of cube, then it's a lookup table
        if (context.hasJoin == false && context.realization.getModel().isLookupTable(tableName)) {
            return "executeLookupTableQuery";
        } else {
            return "executeOLAPQuery";
        }

    }
{code}

> 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
>            Assignee:  Kaige Liu
>
> 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)