[jira] [Commented] (KYLIN-4061) Swap inner join's left side, right side table will get different result when query

["Shaofeng SHI (JIRA)" <ji...@apache.org>]

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

Shaofeng SHI commented on KYLIN-4061:
-------------------------------------

It is known issue I think, with low priority. Because in OLAP scenarios, most queries are started from fact table. Of course, enhancement patches are always welcomed.

> Swap inner join's left side, right side table will get different result when query
> ----------------------------------------------------------------------------------
>
>                 Key: KYLIN-4061
>                 URL: https://issues.apache.org/jira/browse/KYLIN-4061
>             Project: Kylin
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: v2.5.2
>            Reporter: weibin0516
>            Priority: Major
>         Attachments: failed.png, succeed.png
>
>
> When the left side table of inner join is a fact table and the right side table is a lookup table, will query cube and get correct result. Sql is as follows.
> {code:java}
> SELECT KYLIN_SALES.TRANS_ID, SUM(KYLIN_SALES.PRICE), COUNT(KYLIN_ACCOUNT.ACCOUNT_ID)
>  FROM KYLIN_SALES
>  INNER JOIN KYLIN_ACCOUNT ON KYLIN_SALES.BUYER_ID = KYLIN_ACCOUNT.ACCOUNT_ID
>  WHERE KYLIN_SALES.LSTG_SITE_ID != 1000
>  GROUP BY KYLIN_SALES.TRANS_ID
>  ORDER BY TRANS_ID
>  LIMIT 10;{code}
>  
> However，when swap the left and right side tables of the inner join will failed due to no realization found. Sql is as follows.
> {code:java}
> SELECT KYLIN_SALES.TRANS_ID, SUM(KYLIN_SALES.PRICE), COUNT(KYLIN_ACCOUNT.ACCOUNT_ID)
>  FROM KYLIN_ACCOUNT
>  INNER JOIN KYLIN_SALES ON KYLIN_SALES.BUYER_ID = KYLIN_ACCOUNT.ACCOUNT_ID
>  WHERE KYLIN_SALES.LSTG_SITE_ID != 1000
>  GROUP BY KYLIN_SALES.TRANS_ID
>  ORDER BY TRANS_ID
>  LIMIT 10;{code}
> We know that the above two sql semantics are consistent and should return the same result. 
>  I looked at the source code, kylin will use context.firstTableScan(assigned in OLAPTableScan.implementOLAP) as the fact table, whether it is or not. The fact table will be the key evidence for choosing realization later. So, in the second sql Regard a lookup table as a fact table can not find corresponding realization.
> Is this a bug, do we need to fix it?



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)