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

Wrong query result with LATERAL DERIVED optimization and join_cache_level=6

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 10.4
    • Fix Version/s: 10.3.11
    • Component/s: Optimizer
    • Labels:
      None

      Description

      This bug is originally from 10.2-compatibility branch. It cannot be reproduced on 10.4, because 10.4 will not use LATERAL DERIVED optimization for this testcase. However, if one changes the cost numbers, one can get the query plan that produces wrong query results.

      First, apply this patch to 10.4:

      diff --git a/sql/opt_split.cc b/sql/opt_split.cc
      index fc3f084..3a12b89 100644
      --- a/sql/opt_split.cc
      +++ b/sql/opt_split.cc
      @@ -987,6 +987,7 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count,
           }
           if (spl_plan)
           {
      +      spl_opt_info->unsplit_cost=1000*1000*1000; // psergey
             if(record_count * spl_plan->cost < spl_opt_info->unsplit_cost)
             {
               /*
      

      Then, prepare the testcase:

       
      CREATE TABLE `test` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `AMOUNT` decimal(8,4) DEFAULT NULL,
      PRIMARY KEY (`ID`)
      ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
       
      CREATE TABLE `test1` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `NAME` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`ID`)
      ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
       
      insert into test values
      ( 1 , 10.0000),
      ( 2 , 20.0000),
      ( 3 , 30.0000),
      ( 4 , 40.0000),
      ( 5 , NULL   ),
      ( 6 , NULL   );
       
      insert into test1 values 
      ( 1  ,'A'   ),
      ( 2  ,'B'   ),
      ( 3  ,'C'   ),
      ( 4  ,'D'   ),
      ( 5  , NULL ),
      ( 6  , NULL ),
      ( 7  ,'E'   ),
      ( 8  ,'F'   ),
      ( 9  ,'G'   ),
      ( 10 ,'H'   ),
      ( 11 , NULL ),
      ( 12 , NULL );
      set join_cache_level=6;
      

      Then, run this query:

      select t1.id,t1.name,t.total_amt from test1 t1 left join
      (select id,sum(amount) total_amt from test group by id) t ON t1.ID=t.ID;
      

      The EXPLAIN:

      +------+-----------------+------------+----------+---------------+------------+---------+----------+------+-------------------------------------+
      | id   | select_type     | table      | type     | possible_keys | key        | key_len | ref      | rows | Extra                               |
      +------+-----------------+------------+----------+---------------+------------+---------+----------+------+-------------------------------------+
      |    1 | PRIMARY         | t1         | ALL      | NULL          | NULL       | NULL    | NULL     |   12 |                                     |
      |    1 | PRIMARY         | <derived2> | hash_ALL | key0          | #hash#key0 | 5       | j1.t1.ID |    6 | Using join buffer (flat, BNLH join) |
      |    2 | LATERAL DERIVED | test       | eq_ref   | PRIMARY       | PRIMARY    | 4       | j1.t1.ID |    1 |                                     |
      +------+-----------------+------------+----------+---------------+------------+---------+----------+------+-------------------------------------+
      

      Query output:

      MariaDB [j1]> select t1.id,t1.name,t.total_amt from test1 t1 left join (select id,sum(amount) total_amt from test group by id) t ON t1.ID=t.ID;+----+------+-----------+
      | id | name | total_amt |
      +----+------+-----------+
      |  1 | A    |      NULL |
      |  2 | B    |      NULL |
      |  3 | C    |      NULL |
      |  4 | D    |      NULL |
      |  5 | NULL |      NULL |
      |  6 | NULL |      NULL |
      |  7 | E    |      NULL |
      |  8 | F    |      NULL |
      |  9 | G    |      NULL |
      | 10 | H    |      NULL |
      | 11 | NULL |      NULL |
      | 12 | NULL |      NULL |
      +----+------+-----------+
      12 rows in set (0.00 sec)
      

      If one disables LATERAL DERIVED: set optimizer_switch='split_materialized=off';, then they get correct result which is:

      +----+------+-----------+
      | id | name | total_amt |
      +----+------+-----------+
      |  1 | A    |   10.0000 |
      |  2 | B    |   20.0000 |
      |  3 | C    |   30.0000 |
      |  4 | D    |   40.0000 |
      |  5 | NULL |      NULL |
      |  6 | NULL |      NULL |
      |  7 | E    |      NULL |
      |  8 | F    |      NULL |
      |  9 | G    |      NULL |
      | 10 | H    |      NULL |
      | 11 | NULL |      NULL |
      | 12 | NULL |      NULL |
      +----+------+-----------+
       

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              psergey Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: