Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20633

Optimizer unexpectedly changes equi-JOIN predicates

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Not a Bug
    • Affects Version/s: 10.4.8
    • Fix Version/s: 10.4.8
    • Component/s: Optimizer
    • Labels:
      None

      Description

      Consider the following example and how optimizer rewrites equi-join predicates.

      DROP TABLE IF EXISTS t1;
      DROP TABLE IF EXISTS t2;
      DROP TABLE IF EXISTS t3;
      create table t1 (c1 bigint, c2 bigint);
      create table t2 (c1 bigint, c2 bigint);
      create table t3 (c1 bigint, c2 bigint);
      select * from t1,t2,t3 where t1.c1=t2.c1 AND t3.c1=t2.c1 AND t2.c2=t3.c2;
      

      Here is the dbug_print_item(conds) for the COND item in optimize_inner() before optimize_cond(), right after it and after optimize_stage2().

      before optimize_conds()
      dbug_print_item(conds) = 0x5600736ee9a0 <dbug_item_print_buf> "t1.c1 = t2.c1 and t3.c1 = t2.c1 and t2.c2 = t3.c2"
       
      after optimize_conds()
      dbug_print_item(conds) = 0x5600736ee9a0 <dbug_item_print_buf> "multiple equal(t1.c1, t2.c1, t3.c1) and multiple equal(t2.c2, t3.c2)"
       
      after optimize_stage2()
      dbug_print_item(conds) = 0x5600736ee9a0 <dbug_item_print_buf> "t2.c1 = t1.c1 and t3.c1 = t1.c1 and t3.c2 = t2.c2"
      

      However if one swaps t1 and t2 in FROM we got expected join predicates:

      before optimize_conds()
      1: dbug_print_item(conds) = 0x5600736ee9a0 <dbug_item_print_buf> "t1.c1 = t2.c1 and t3.c1 = t2.c1 and t2.c2 = t3.c2"
       
      after optimize_conds()
      $2 = 0x5582958f59a0 <dbug_item_print_buf> "multiple equal(t1.c1, t2.c1, t3.c1) and multiple equal(t2.c2, t3.c2)"
       
      after optimize_stage2()
      1: dbug_print_item(conds) = 0x5600736ee9a0 <dbug_item_print_buf> "t1.c1 = t2.c1 and t3.c1 = t2.c1 and t2.c2 = t3.c2"
      

      This unstable behavior breaks a number of regression tests in CS. How to avoid such replacements?

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                drrtuy Roman
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: