Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5, 10.3(EOL), 10.4(EOL)
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