Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL), 10.5
Description
Under some circumstances a query with materialized subquery give an empty result.
Testcase to reproduce
It is likely a variant of MDEV-21614 , which is not fixed with MDEV-21328.
SET optimizer_switch="split_materialized=on"; |
|
drop table if EXISTS t1; |
drop table if exists t2 ; |
|
|
CREATE TABLE `t1` ( |
`id` TINYINT(4) NOT NULL, |
`sint1` TINYINT(4) NOT NULL, |
`a1` MEDIUMINT(9) NOT NULL, |
`d1` DATE NOT NULL, |
PRIMARY KEY (`id`, `sint1`, `a1`, `d1`) USING BTREE, |
INDEX `idx0` (`a1`, `d1`) USING BTREE, |
INDEX `idx1` (`sint1`, `a1`, `d1`) USING BTREE |
)
|
;
|
|
CREATE TABLE `t2` ( |
`a1` MEDIUMINT(9) NOT NULL, |
`sint1` SMALLINT(6) NOT NULL, |
INDEX `idx` (`sint1`, `a1`) USING BTREE |
)
|
;
|
|
|
INSERT INTO `t1` ( |
with recursive series as ( |
select 1 as id union all |
select id +1 as id from series |
where id < 5000) |
select 1,FLOOR(15 + (RAND() * 15)),id, subdate(NOW() ,INTERVAL (FLOOR(1 + (RAND() * 15)) ) DAY) from series); |
|
|
|
INSERT INTO `t2` ( |
with recursive series as ( |
select 1 as id union all |
select id +1 as id from series |
where id < 5000) |
select id,FLOOR(0 + (RAND() * 2)) from series); |
|
ANALYZE TABLE t1; #without it works |
|
DROP TEMPORARY TABLE IF EXISTS _Tmp; |
CREATE TEMPORARY TABLE _Tmp ( id_a1 INTEGER(8) ) ENGINE=MEMORY; |
INSERT INTO _Tmp (id_a1) |
VALUES (2500),(3000); |
SELECT * |
FROM
|
d_maestros.t1 cp
|
INNER JOIN |
(
|
SELECT
|
a1,
|
MAX(d1) d1 |
FROM
|
d_maestros.t1 cp2
|
GROUP BY |
a1) fe
|
ON
|
fe.a1 = cp.a1
|
INNER JOIN |
_Tmp AS tmp_0 |
ON
|
cp.a1 = tmp_0.id_a1
|
WHERE cp.a1 IN |
(
|
SELECT
|
a1
|
FROM
|
d_maestros.t2 dcp
|
|
);
|
It works fine with
"SET optimizer_switch="split_materialized=off";"
and/or
without
analyze table t1;
Attachments
Issue Links
- causes
-
MDEV-25714 Join using derived with aggregation returns incorrect results
-
- Closed
-
- is duplicated by
-
MDEV-24204 inconsistent query results using a LEFT JOIN
-
- Closed
-
- relates to
-
MDEV-21328 Different query result between 10.4.10 and 10.4.11 from materialized subquery with split_materialized=ON
-
- Closed
-
-
MDEV-21614 Wrong query results with optimizer_switch="split_materialized=on"
-
- Closed
-
Repeatable on 10.3-10.5, with Myisam/InnoDB (but to repeat with InnoDB there should be more data in t1, uncomment the second insert)
MariaDB [test]> set optimizer_switch="split_materialized=on";
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> select * from t1
-> join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1
-> join t3 on t1.a1 = t3.id
-> where t1.a1 in (select a1 from t2);
Empty set (0.005 sec)
MariaDB [test]> set optimizer_switch="split_materialized=off";
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> select * from t1
-> join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1
-> join t3 on t1.a1 = t3.id
-> where t1.a1 in (select a1 from t2);
+------+------+------+------+
| id | a1 | a1 | id |
+------+------+------+------+
| 17 | 1 | 1 | 1 |
| 21 | 2 | 2 | 2 |
+------+------+------+------+
2 rows in set (0.003 sec)
MariaDB [test]> explain extended select * from t1 join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1 join t3 on t1.a1 = t3.id where t1.a1 in (select a1 from t2);
+------+-----------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-----------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
| 1 | PRIMARY | t1 | ref | a1 | a1 | 5 | test.t3.id | 1 | 100.00 | |
| 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | test.t3.id | 2 | 100.00 | |
| 3 | MATERIALIZED | t2 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | |
| 2 | LATERAL DERIVED | cp2 | ref | a1 | a1 | 5 | test.t1.a1 | 1 | 100.00 | Using where; Using index |
+------+-----------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+
6 rows in set, 1 warning (0.002 sec)
Note (Code 1003): /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a1` AS `a1`,`dt`.`a1` AS `a1`,`test`.`t3`.`id` AS `id` from `test`.`t1` semi join (`test`.`t2`) join (/* select#2 */ select `test`.`cp2`.`a1` AS `a1` from `test`.`t1` `cp2` where `test`.`cp2`.`a1` = `test`.`t1`.`a1` and `test`.`cp2`.`a1` = `test`.`t2`.`a1` and `test`.`cp2`.`a1` = `test`.`t3`.`id` group by `test`.`cp2`.`a1`) `dt` join `test`.`t3` where `test`.`t1`.`a1` = `test`.`t3`.`id` and `dt`.`a1` = `test`.`t3`.`id`
MariaDB [test]> set optimizer_switch="split_materialized=off";
Query OK, 0 rows affected (0.001 sec)
MariaDB [test]> explain extended select * from t1 join (select a1 from t1 cp2 group by a1) dt on dt.a1 = t1.a1 join t3 on t1.a1 = t3.id where t1.a1 in (select a1 from t2);
+------+--------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
| 1 | PRIMARY | t1 | ref | a1 | a1 | 5 | test.t3.id | 1 | 100.00 | |
| 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | test.t3.id | 2 | 100.00 | |
| 3 | MATERIALIZED | t2 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | |
| 2 | DERIVED | cp2 | range | NULL | a1 | 5 | NULL | 8 | 100.00 | Using index for group-by |
+------+--------------+-------------+--------+---------------+--------------+---------+------------+------+----------+--------------------------+
6 rows in set, 1 warning (0.006 sec)
Note (Code 1003): /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a1` AS `a1`,`dt`.`a1` AS `a1`,`test`.`t3`.`id` AS `id` from `test`.`t1` semi join (`test`.`t2`) join (/* select#2 */ select `test`.`cp2`.`a1` AS `a1` from `test`.`t1` `cp2` group by `test`.`cp2`.`a1`) `dt` join `test`.`t3` where `test`.`t1`.`a1` = `test`.`t3`.`id` and `dt`.`a1` = `test`.`t3`.`id`