[jira] [Commented] (KYLIN-4061) Swap inner join's left side, right side table will get different result when query
[
https://issues.apache.org/jira/browse/KYLIN-4061?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16875789#comment-16875789
]
weibin0516 commented on KYLIN-4061:
---
[~Shaofengshi], thanks for explaining.
> 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)
[jira] [Commented] (KYLIN-4061) Swap inner join's left side, right side table will get different result when query
[
https://issues.apache.org/jira/browse/KYLIN-4061?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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)
