Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.4(EOL)
-
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
- is blocked by
-
MDEV-17382 Hash join algorithm should not be used to join materialized derived table / view by equality
-
- Closed
-