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

Wrong query result with LATERAL DERIVED optimization and join_cache_level=6

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.4(EOL)
    • 10.3.11
    • Optimizer
    • 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

            igor Igor Babaev added a comment - - edited

            The problem is reproducible in 10.3 as well.
            Setting join_cache_level to 4 does change the result set.
            Setting join_cache_level to 2 helps:

            MariaDB [test]> set join_cache_level=default;
            ariaDB [test]> 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    |   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 |
            +----+------+-----------+
            12 rows in set (0.001 sec)
            

            igor Igor Babaev added a comment - - edited The problem is reproducible in 10.3 as well. Setting join_cache_level to 4 does change the result set. Setting join_cache_level to 2 helps: MariaDB [test]> set join_cache_level=default; ariaDB [test]> 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 | 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 | +----+------+-----------+ 12 rows in set (0.001 sec)
            igor Igor Babaev added a comment -

            The following modification of the test case reproduces the problem without any additional patches:

            CREATE TABLE t1 (
              id int NOT NULL,
              amount decimal DEFAULT NULL,
            PRIMARY KEY (id)
            ) ENGINE=InnoDB;
             
            CREATE TABLE t2 (
              id int NOT NULL,
              name varchar(50) DEFAULT NULL,
            PRIMARY KEY (id)
            ) ENGINE=InnoDB;
             
            INSERT INTO t1 VALUES
            (1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000),
            (5, NULL), (6, NULL), (7, 70.0000), (8, 80.0000);
             
            INSERT INTO t2 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=4;
             
            SELECT t2.id,t2.name,t.total_amt 
              FROM  t2
                    LEFT JOIN
                    (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t
                    ON t2.id=t.id
              WHERE t2.id < 3;
            

            Here we see a wrong result set as well:

            MariaDB [test]> SELECT t2.id,t2.name,t.total_amt    FROM  t2         LEFT JOIN         (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t         ON t2.id=t.id   WHERE t2.id < 3;
            +----+------+-----------+
            | id | name | total_amt |
            +----+------+-----------+
            |  1 | A    |      NULL |
            |  2 | B    |      NULL |
            +----+------+-----------+
            

            EXPLAIN for this query 'explains' why it happens. The server tries to use simultaneously splitting technique
            and hash join to join the materialized table t. In fact only one of these two methods can be used.

            MariaDB [test]> explain SELECT t2.id,t2.name,t.total_amt    FROM  t2         LEFT JOIN         (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t         ON t2.id=t.id   WHERE t2.id < 3;
            +------+-----------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
            | id   | select_type     | table      | type     | possible_keys | key        | key_len | ref        | rows | Extra                               |
            +------+-----------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
            |    1 | PRIMARY         | t2         | range    | PRIMARY       | PRIMARY    | 4       | NULL       |    2 | Using where                         |
            |    1 | PRIMARY         | <derived2> | hash_ALL | key0          | #hash#key0 | 5       | test.t2.id |    8 | Using join buffer (flat, BNLH join) |
            |    2 | LATERAL DERIVED | t1         | eq_ref   | PRIMARY       | PRIMARY    | 4       | test.t2.id |    1 |                                     |
            +------+-----------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
            

            igor Igor Babaev added a comment - The following modification of the test case reproduces the problem without any additional patches: CREATE TABLE t1 ( id int NOT NULL , amount decimal DEFAULT NULL , PRIMARY KEY (id) ) ENGINE=InnoDB;   CREATE TABLE t2 ( id int NOT NULL , name varchar (50) DEFAULT NULL , PRIMARY KEY (id) ) ENGINE=InnoDB;   INSERT INTO t1 VALUES (1, 10.0000), (2, 20.0000), (3, 30.0000), (4, 40.0000), (5, NULL ), (6, NULL ), (7, 70.0000), (8, 80.0000);   INSERT INTO t2 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=4;   SELECT t2.id,t2. name ,t.total_amt FROM t2 LEFT JOIN ( SELECT id, sum (amount) total_amt FROM t1 GROUP BY id) AS t ON t2.id=t.id WHERE t2.id < 3; Here we see a wrong result set as well: MariaDB [test]> SELECT t2.id,t2.name,t.total_amt FROM t2 LEFT JOIN (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t ON t2.id=t.id WHERE t2.id < 3; +----+------+-----------+ | id | name | total_amt | +----+------+-----------+ | 1 | A | NULL | | 2 | B | NULL | +----+------+-----------+ EXPLAIN for this query 'explains' why it happens. The server tries to use simultaneously splitting technique and hash join to join the materialized table t. In fact only one of these two methods can be used. MariaDB [test]> explain SELECT t2.id,t2.name,t.total_amt FROM t2 LEFT JOIN (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t ON t2.id=t.id WHERE t2.id < 3; +------+-----------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-----------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+ | 1 | PRIMARY | t2 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where | | 1 | PRIMARY | <derived2> | hash_ALL | key0 | #hash#key0 | 5 | test.t2.id | 8 | Using join buffer (flat, BNLH join) | | 2 | LATERAL DERIVED | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.id | 1 | | +------+-----------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
            igor Igor Babaev added a comment -

            Another problem that with optimizer_switch='split_materialized=off' we have:

            MariaDB [test]> explain SELECT t2.id,t2.name,t.total_amt    FROM  t2         LEFT JOIN         (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t         ON t2.id=t.id   WHERE t2.id < 3;
            +------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
            | id   | select_type | table      | type     | possible_keys | key        | key_len | ref        | rows | Extra                               |
            +------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
            |    1 | PRIMARY     | t2         | range    | PRIMARY       | PRIMARY    | 4       | NULL       |    2 | Using where                         |
            |    1 | PRIMARY     | <derived2> | hash_ALL | key0          | #hash#key0 | 5       | test.t2.id |    8 | Using join buffer (flat, BNLH join) |
            |    2 | DERIVED     | t1         | index    | NULL          | PRIMARY    | 4       | NULL       |    8 |                                     |
            +------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+
            

            It does not make sense to use hash join by the key created for a derived table, because this key is already a hash key.

            igor Igor Babaev added a comment - Another problem that with optimizer_switch='split_materialized=off' we have: MariaDB [test]> explain SELECT t2.id,t2.name,t.total_amt FROM t2 LEFT JOIN (SELECT id, sum(amount) total_amt FROM t1 GROUP BY id) AS t ON t2.id=t.id WHERE t2.id < 3; +------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+ | 1 | PRIMARY | t2 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where | | 1 | PRIMARY | <derived2> | hash_ALL | key0 | #hash#key0 | 5 | test.t2.id | 8 | Using join buffer (flat, BNLH join) | | 2 | DERIVED | t1 | index | NULL | PRIMARY | 4 | NULL | 8 | | +------+-------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+ It does not make sense to use hash join by the key created for a derived table, because this key is already a hash key.
            igor Igor Babaev added a comment -

            I've created MDEV-17382 "Hash join algorithm should not be used for equi-join of materialized derived table or view". Resolution of this defect should fix MDEV-17381.

            igor Igor Babaev added a comment - I've created MDEV-17382 "Hash join algorithm should not be used for equi-join of materialized derived table or view". Resolution of this defect should fix MDEV-17381 .
            igor Igor Babaev added a comment -

            This problem was fixed by the patch for mdev-17382. The test case was pushed into 10.3

            igor Igor Babaev added a comment - This problem was fixed by the patch for mdev-17382. The test case was pushed into 10.3

            People

              igor Igor Babaev
              psergei Sergei Petrunia
              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.