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

optimizer doesn't convert outer join to inner on views with WHERE clause

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.11
    • Fix Version/s: 10.1.15
    • Component/s: Optimizer
    • Labels:
      None

      Description

      The optimizer can't reorder tables for OUTER JOINs, but when a later WHERE condition on a column of an outer table requires a non-NULL value it can convert the OUTER join into an INNER one, and then it can also make use of re-ordering.

      The same works when the OUTER JOIN is inside a view definition, and a WHERE condition on a column of the outer table is applied on the view ... but apparently only if the view definition doesn't have a WHERE clause.

      How to reproduce:

      DROP TABLE IF EXISTS t1;
       
      CREATE TABLE t1(i1 int primary key, v1 int, key(v1)) engine=myisam;
       
      INSERT INTO t1 VALUES (1, 1);
      INSERT INTO t1 VALUES (2, 2);
      INSERT INTO t1 VALUES (3, 3);
      INSERT INTO t1 VALUES (4, 4);
      INSERT INTO t1 VALUES (5, 3);
      INSERT INTO t1 VALUES (6, 6);
      INSERT INTO t1 VALUES (7, 7);
      INSERT INTO t1 VALUES (8, 8);
      INSERT INTO t1 VALUES (9, 9);
       
      DROP TABLE IF EXISTS t2;
       
      CREATE TABLE t2(i2 int primary key, v2 int, key(v2)) engine=myisam;
       
      INSERT INTO t2 VALUES (1, 1);
      INSERT INTO t2 VALUES (2, 2);
      INSERT INTO t2 VALUES (3, 3);
      INSERT INTO t2 VALUES (4, 4);
      INSERT INTO t2 VALUES (5, 3);
      INSERT INTO t2 VALUES (6, 6);
      INSERT INTO t2 VALUES (7, 7);
      INSERT INTO t2 VALUES (8, 8);
      INSERT INTO t2 VALUES (9, 9);
       
      DROP TABLE IF EXISTS t3;
       
      CREATE TABLE t3(i3 int primary key, v3 int, key(v3)) engine=myisam;
       
      INSERT INTO t3 VALUES (2, 2);
      INSERT INTO t3 VALUES (4, 4);
      INSERT INTO t3 VALUES (6, 6);
      INSERT INTO t3 VALUES (8, 8);
       
      # view definition without WHERE
       
      DROP VIEW IF EXISTS v1;
       
      CREATE ALGORITHM=MERGE VIEW v1 AS
                        SELECT t1.i1 as i1, t1.v1 as v1,
                               t2.i2 as i2, t2.v2 as v2,
                               t3.i3 as i3, t3.v3 as v3
                          FROM t1
                          JOIN t2 on t1.i1 = t2.i2
                     LEFT JOIN t3 on t2.i2 = t3.i3
                         ;
       
      # view definition with WHERE
       
      DROP VIEW IF EXISTS v2;
       
      CREATE ALGORITHM=MERGE VIEW v2 AS
                        SELECT t1.i1 as i1, t1.v1 as v1,
                               t2.i2 as i2, t2.v2 as v2,
                               t3.i3 as i3, t3.v3 as v3
                          FROM t1 JOIN t2 on t1.i1 = t2.i2
                     LEFT JOIN t3 on t2.i2 = t3.i3
                         WHERE t1.i1 = t2.i2
                           AND 1 = 1
                         ;
       
      # query plan of plain query
       
      EXPLAIN EXTENDED SELECT t1.i1 as i1, t1.v1 as v1,
                              t2.i2 as i2, t2.v2 as v2,
                              t3.i3 as i3, t3.v3 as v3
                         FROM t1
                         JOIN t2 on t1.i1 = t2.i2
                    LEFT JOIN t3 on t2.i2 = t3.i3
                        WHERE 1 = 1
                          AND t3.v3 = 4
                        ;
       
      # query plan for view without WHERE
       
      EXPLAIN EXTENDED SELECT * FROM v1 WHERE v3 = 4;
       
      # query plan for view with  WHERE
       
      EXPLAIN EXTENDED SELECT * FROM v2 WHERE v3 = 4;
      

      The plans for the plain query, and for the VIEW without a WHERE condition inside the definition, look like this:

      Plain query:

      id      select_type     table   type    possible_keys   key     key_len ref         rows    filtered    Extra
      1       SIMPLE          t3      ref     PRIMARY,v3      v3      5       const       1       100.00  
      1       SIMPLE          t1      eq_ref  PRIMARY         PRIMARY 4       test.t3.i3  1       100.00  
      1       SIMPLE          t2      eq_ref  PRIMARY         PRIMARY 4       test.t3.i3  1       100.00  
      

      View without WHERE:

      id      select_type     table   type    possible_keys   key     key_len ref         rows    filtered    Extra
      1       SIMPLE          t3      ref     PRIMARY,v3      v3      5       const       1       100.00  
      1       SIMPLE          t1      eq_ref  PRIMARY         PRIMARY 4       test.t3.i3  1       100.00  
      1       SIMPLE          t2      eq_ref  PRIMARY         PRIMARY 4       test.t3.i3  1       100.00  
      

      So these two are identical.

      For the VIEW with the extra WHERE 1=1 in its definition the plan looks like this though:

      id      select_type     table   type    possible_keys   key     key_len ref         rows    filtered    Extra
      1       SIMPLE          t1      ALL     PRIMARY         NULL    NULL    NULL        9       100.00  
      1       SIMPLE          t2      eq_ref  PRIMARY         PRIMARY 4       test.t1.i1  1       100.00  
      1       SIMPLE          t3      eq_ref  PRIMARY,v3      PRIMARY 4       test.t1.i1  1       100.00      Using where
      

      So here table reordering didn't happen, and a "Using where" full scan of the VIEW results has to be done ....

        Attachments

          Activity

            People

            Assignee:
            psergey Sergei Petrunia
            Reporter:
            hholzgra Hartmut Holzgraefe
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: