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

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

weibin0516 created KYLIN-4061:
---------------------------------

             Summary: 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


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.

Is this a bug, do we need to fix it?



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