[MDEV-25128]  Wrong result from join with materialized semi-join and splittable derived Created: 2021-03-12  Updated: 2022-04-06  Resolved: 2021-03-25

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.3, 10.4, 10.5
Fix Version/s: 10.3.29, 10.4.19, 10.5.10

Type: Bug Priority: Major
Reporter: Richard Stracke Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: statistics

Issue Links:
Duplicate
is duplicated by MDEV-24204 inconsistent query results using a LE... Closed
Problem/Incident
causes MDEV-25714 Join using derived with aggregation r... Closed
Relates
relates to MDEV-21328 Different query result between 10.4.1... Closed
relates to MDEV-21614 Wrong query results with optimizer_sw... Closed

 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;

 Comments   
Comment by Alice Sherepa [ 2021-03-12 ]

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)

create table t1 (id int, a1 int, index (a1), index (id, a1));
 
insert into t1 values (17,1),(17,3010),(17,3013),(17,3053),(21,2446),(21,2467),(21,2);
 
#insert into t1 values (17,3000),(17,3010),(17,3013),(17,3053),(17,3071),(17,3087),(17,3089),(17,3093),(17,3121),(17,3136),(17,3150),(17,3160),(17,3182),(17,3200),(17,3202),(17,3215),(17,3216),(17,3222),(17,3224),(17,3268),(17,3291),(17,3373),(17,3413),(17,3415),(17,3418),(17,3448),(17,3474),(17,3477),(17,3479),(17,3493),(17,3507),(17,3518),(17,3527),(17,3528),(17,3550),(17,3566),(17,3600),(17,3613),(17,3615),(17,3624),(17,3631),(17,3638),(17,3653),(17,3672),(17,3678),(17,3699),(17,3703),(17,3740),(17,3748),(17,3753),(17,3768),(17,3779),(17,3787),(17,3816),(17,3817),(17,3836),(17,3840),(17,3859),(17,3894),(17,3897),(17,3961),(17,3962),(17,3963),(17,3970),(17,4048),(17,4073),(17,4084),(17,4089),(17,4091),(17,4149),(17,4177),(17,4180),(17,4206),(17,4218),(17,4222),(17,4239),(17,4244),(17,4247),(17,4268),(17,4277),(17,4310),(17,4326),(17,4354),(17,4377),(17,4386),(17,4392),(17,4433),(17,4465),(17,4472),(17,4500),(17,4536),(17,4540),(17,4543),(17,4576),(17,4582),(17,4588),(17,4596),(17,4628),(17,4640),(17,4653),(17,4693),(17,4702),(17,4703),(17,4712),(17,4724),(17,4725),(17,4732),(17,4761),(17,4765),(17,4792),(17,4800),(17,4816),(17,4827),(17,4833),(17,4844),(17,4849),(17,4864),(17,4870),(17,4871),(17,4886),(17,4892),(17,4895),(17,4898),(17,4918),(17,4928),(17,4958),(17,4990),(21,28),(21,47),(21,49),(21,74),(21,83),(21,99),(21,113),(21,115),(21,140),(21,142),(21,173),(21,183),(21,185),(21,197),(21,208),(21,212),(21,239),(21,273),(21,277),(21,296),(21,333),(21,373),(21,380),(21,410),(21,434),(21,452),(21,471),(21,500),(21,501),(21,509),(21,535),(21,548),(21,555),(21,559),(21,603),(21,606),(21,612),(21,624),(21,636),(21,652),(21,669),(21,670),(21,712),(21,717),(21,732),(21,736),(21,739),(21,741),(21,742),(21,761),(21,772),(21,809),(21,813),(21,814),(21,839),(21,883),(21,885),(21,894),(21,913),(21,924),(21,928),(21,935),(21,959),(21,995),(21,1000),(21,1012),(21,1020),(21,1041),(21,1052),(21,1059),(21,1060),(21,1063),(21,1081),(21,1082),(21,1108),(21,1110),(21,1120),(21,1143),(21,1152),(21,1153),(21,1154),(21,1161),(21,1175),(21,1187),(21,1190),(21,1200),(21,1209),(21,1210),(21,1215),(21,1221),(21,1227),(21,1237),(21,1245),(21,1252),(21,1268),(21,1276),(21,1278),(21,1302),(21,1319),(21,1326),(21,1329),(21,1347),(21,1369),(21,1372),(21,1384),(21,1399),(21,1432),(21,1435),(21,1443),(21,1464),(21,1497),(21,1538),(21,1541),(21,1571),(21,1587),(21,1599),(21,1611),(21,1613),(21,1676),(21,1681),(21,1685),(21,1688),(21,1737),(21,1752),(21,1753),(21,1787),(21,1810),(21,1823),(21,1833),(21,1840),(21,1846),(21,1870),(21,1942),(21,1948),(21,1960),(21,2030),(21,2040),(21,2053),(21,2083),(21,2122),(21,2126),(21,2127),(21,2154),(21,2180),(21,2255),(21,2273),(21,2292),(21,2311),(21,2329),(21,2332),(21,2348),(21,2380),(21,2388),(21,2401),(21,2411),(21,2414),(21,2416),(21,2428),(21,2446),(21,2467),(21,2500),(21,2506),(21,2508),(21,2534),(21,2576),(21,2587),(21,2593),(21,2611),(21,2625),(21,2631),(21,2653),(21,2658),(21,2661),(21,2667),(21,2706),(21,2717),(21,2721),(21,2732),(21,2736),(21,2737),(21,2761),(21,2766),(21,2772),(21,2775),(21,2777),(21,2787),(21,2796),(21,2821),(21,2848),(21,2865),(21,2874),(21,2884),(21,2889),(21,2936),(21,2950),(21,2958),(21,2959),(21,2985),(21,3040),(21,3059),(21,3081),(21,3082),(21,3101),(21,3110),(21,3114),(21,3129),(21,3139),(21,3149),(21,3155),(21,3158),(21,3159),(21,3173),(21,3188),(21,3209),(21,3236),(21,3254),(21,3263),(21,3275),(21,3283),(21,3387),(21,3388),(21,3389),(21,3396),(21,3407),(21,3410),(21,3411),(21,3417),(21,3420),(21,3451),(21,3455),(21,3478),(21,3483),(21,3487),(21,3529),(21,3530),(21,3531),(21,3556),(21,3558),(21,3571),(21,3579),(21,3585),(21,3619),(21,3621),(21,3634),(21,3642),(21,3645),(21,3648),(21,3665),(21,3674),(21,3710),(21,3714),(21,3736),(21,3741),(21,3745),(21,3758),(21,3761),(21,3774),(21,3781),(21,3783),(21,3792),(21,3811),(21,3842),(21,3847),(21,3865),(21,3875),(21,3949),(21,3971),(21,3974),(21,3985),(21,3986),(21,3993),(21,4010),(21,4012),(21,4020),(21,4032),(21,4043),(21,4051),(21,4055),(21,4080),(21,4090),(21,4099),(21,4107),(21,4165),(21,4172),(21,4205),(21,4207),(21,4209),(21,4221),(21,4231),(21,4240),(21,4253),(21,4255),(21,4266),(21,4274),(21,4309),(21,4314),(21,4364),(21,4417),(21,4421),(21,4443),(21,4447),(21,4454),(21,4467),(21,4481),(21,4491),(21,4507),(21,4522),(21,4548),(21,4573),(21,4583),(21,4599),(21,4605),(21,4613),(21,4614),(21,4620),(21,4624),(21,4632),(21,4636),(21,4646),(21,4651),(21,4652),(21,4656),(21,4684),(21,4695),(21,4700),(21,4709),(21,4743),(21,4760),(21,4774),(21,4809),(21,4867),(21,4891),(21,4900),(21,4936),(21,4944),(21,4988),(22,2),(22,24),(22,39),(22,40),(22,44),(22,61),(22,70),(22,82),(22,93),(22,95),(22,144),(22,145),(22,162),(22,165),(22,167),(22,184),(22,213),(22,227),(22,244),(22,260),(22,262),(22,314),(22,326),(22,342),(22,343),(22,374),(22,383),(22,385),(22,387),(22,400),(22,417),(22,433),(22,437),(22,445),(22,446),(22,451),(22,459),(22,468),(22,493),(22,536),(22,554),(22,569),(22,576),(22,579),(22,592),(22,593),(22,600),(22,649),(22,656),(22,657),(22,658),(22,671),(22,680),(22,729),(22,740),(22,762),(22,766),(22,769),(22,802),(22,808),(22,832),(22,834),(22,902),(22,905),(22,945),(22,946),(22,983),(22,986),(22,999),(22,1033),(22,1062),(22,1083),(22,1090),(22,1102),(22,1111),(22,1160),(22,1184),(22,1196),(22,1220),(22,1224),(22,1244),(22,1273),(22,1285),(22,1297),(22,1303);
 
 
 
analyze table t1; 
 
 
create table t2 (a1 int);
 
insert into t2 values (1),(2),(3);
 
create  table t3 (id int);
 
insert into t3 values (1),(2);
 
 
set optimizer_switch="split_materialized=on";
 
 
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);
 
 
 
set optimizer_switch="split_materialized=off";
 
 
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);
 
 
 
drop table t1,t2,t3;


 
 
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`

Comment by Igor Babaev [ 2021-03-25 ]

A fix for this bug was pushed into 10.3

Generated at Thu Feb 08 09:35:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.