[MDEV-17381] Wrong query result with LATERAL DERIVED optimization and join_cache_level=6 Created: 2018-10-05  Updated: 2020-08-25  Resolved: 2018-10-09

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4
Fix Version/s: 10.3.11

Type: Bug Priority: Critical
Reporter: Sergei Petrunia Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-17382 Hash join algorithm should not be use... Closed

 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 |
 
+----+------+-----------+



 Comments   
Comment by Igor Babaev [ 2018-10-06 ]

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)

Comment by Igor Babaev [ 2018-10-06 ]

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 |                                     |
 
+------+-----------------+------------+----------+---------------+------------+---------+------------+------+-------------------------------------+

Comment by Igor Babaev [ 2018-10-06 ]

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.

Comment by Igor Babaev [ 2018-10-06 ]

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.

Comment by Igor Babaev [ 2018-10-09 ]

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

Generated at Thu Feb 08 08:36:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.