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

Optimizer unexpectedly changes equi-JOIN predicates

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Not a Bug
    • 10.4.8
    • 10.4.8
    • Optimizer
    • 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

              igor Igor Babaev
              drrtuy Roman
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.