Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-5543

Aggregated count on left join result is incorrect after switching join order

    XMLWordPrintable

Details

    Description

      In some cases the count throw wrong results , when join order was switched

       
      select count(t1.ID)
      from t1  -- 2
      left join t2  on t2.ID = t1.ID -- 104
      left join t3  on t3.ID = t1.ID -- 42
      left join t4  on t4.ID = t1.ID -- 149
      where t1.ID = 509583314; -- 71.565.312 != 2*104*42*149 = 1.301.664
       
      select count(t1.ID)
      from t1 -- 2
      left join t2 on t2.ID = t1.ID -- 104
      left join t4  on t4.ID = t1.ID -- 149          !!! switched t3 and t4 !!!
      left join t3  on t3.ID = t1.ID -- 42
      where t1.ID = 509583314; -- 1.301.664 = 2*104*149*42 = 1.301.664
      

      Maybe it is caused from wrong extent elimination according trace.

       
      select calSetTrace(1);
      SELECT count(*)
      from t1 -- 2
      left join t2 on t2.ID = t1.ID -- 104
      left join t3 on t3.ID = t1.ID -- 42
      left join t4 on t4.ID = t1.ID -- 149
      where t1.ID = 509583314; -- 71.565.312 != 2*104*42*149 = 1.301.664
       
      select calGetTrace();
       
      Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
      BPS PM t1 3311 (id) 0 3580 0 0.005 2
      BPS PM t2 3314 (id) 0 520 0 0.016 0
      HJS PM t2-t1 3314 - - - - ----- -
      BPS PM t3 3318 (id) 0 664 0 0.022 0
      HJS PM t3-t1 3318 - - - - ----- -
      BPS PM t4 3321 (id) 0 4128 0 1.508 6655
      HJS PM t4-t1 3321 - - - - ----- -
      TAS UM - - - - - - 1.399 1
      TNS UM - - - - - - 0.000 1
      |
       
      select calSetTrace(1);
      SELECT count(*)
      from t1 -- 2
      left join t2 on t2.ID = t1.ID -- 104
      left join t4 on t4.ID = t1.ID -- 149 !!! switched t3 and t4 !!!
      left join t3 on t3.ID = t1.ID -- 42
      where t1.ID = 509583314; -- 1.301.664 = 2*104*149*42 = 1.301.664
       
      select calGetTrace();
       
      Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
      BPS PM t2 3314 (id) 0 392 0 0.020 0
      HJS PM t2-t1 3314 - - - - ----- -
      BPS PM t4 3321 (id) 0 2000 0 0.114 0
      HJS PM t4-t1 3321 - - - - ----- -
      BPS PM t3 3318 (id) 0 4193 0 0.211 4291957
      BPS PM t1 3311 (id) 0 3580 0 0.018 2
      HJS UM t1-t3 3311 - - - - ----- -
      TAS UM - - - - - - 0.001 1
      TNS UM - - - - - - 0.000 1
      

      Setup with data to reproduce is attached.

      Attachments

        1. ebi-886.sql
          0.5 kB
        2. import_data.sql
          1 kB
        3. t2.zip
          2.37 MB
        4. t3.zip
          4.76 MB
        5. t4.zip
          3.58 MB
        6. t1.zip.002
          3.32 MB
        7. t1.zip.001
          10.00 MB

        Issue Links

          Activity

            People

              Unassigned Unassigned
              Richard Richard Stracke
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.