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

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: v2.5.2
• Component/s: Query Engine
• Assignee: Unassigned
• Reporter: weibin0516
• Created: 29/Jun/19 13:34
• Updated: 30/Jun/19 13:45

Description

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.

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;

 

However，when swap the left and right side tables of the inner join will failed due to no realization found. Sql is as follows.

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;

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?