Uploaded image for project: 'Kylin'
  1. Kylin
  2. KYLIN-4061

Swap inner join's left side, right side table will get different result when query

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: v2.5.2
    • Fix Version/s: None
    • Component/s: Query Engine
    • Labels:
      None

      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?

        Attachments

        1. succeed.png
          560 kB
          weibin0516
        2. failed.png
          1001 kB
          weibin0516

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              codingforfun weibin0516
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: