Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Description
CREATE TABLE t1 (a INT); |
INSERT INTO t1 VALUES (8),(9); |
|
CREATE TABLE t2 (b INT); |
INSERT INTO t2 VALUES (1),(2); |
|
CREATE TABLE t3 (c INT, d DATE); |
INSERT INTO t3 VALUES (2,'2022-12-12'),(3,'2023-12-12'); |
|
SELECT c, (SELECT MIN(a) FROM t1 WHERE t3.c IN (SELECT b FROM t2)) AS f1, GROUP_CONCAT(d) AS f2 FROM t3 GROUP BY c, f1 ORDER BY f2; |
10.3 0ca3aaa7 |
SELECT c, (SELECT MIN(a) FROM t1 WHERE t3.c IN (SELECT b FROM t2)) AS f1, GROUP_CONCAT(d) AS f2 FROM t3 GROUP BY c, f1 ORDER BY f2; |
c f1 f2
|
2 NULL 2022-12-12 |
3 NULL 2023-12-12 |
EXPLAIN EXTENDED SELECT c, (SELECT MIN(a) FROM t1 WHERE t3.c IN (SELECT b FROM t2)) AS f1, GROUP_CONCAT(d) AS f2 FROM t3 GROUP BY c, f1 ORDER BY f2; |
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 temporary; Using filesort |
2 DEPENDENT SUBQUERY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00
|
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) |
3 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 |
Warnings:
|
Note 1276 Field or reference 'test.t3.c' of SELECT #2 was resolved in SELECT #1 |
Note 1003 /* select#1 */ select `test`.`t3`.`c` AS `c`,<expr_cache><`test`.`t3`.`c`>((/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1` semi join (`test`.`t2`) where `test`.`t3`.`c` = `test`.`t2`.`b`)) AS `f1`,group_concat(`test`.`t3`.`d` separator ',') AS `f2` from `test`.`t3` group by `test`.`t3`.`c`,<expr_cache><`test`.`t3`.`c`>((/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1` semi join (`test`.`t2`) where `test`.`t3`.`c` = `test`.`t2`.`b`)) order by group_concat(`test`.`t3`.`d` separator ',') |
Expected result is
c f1 f2
|
2 8 2022-12-12
|
3 NULL 2023-12-12 |
On 10.3 it can be achieved with materialization=off.
Reproducible with at least MyISAM and InnoDB.
Reproducible on 5.5 (and materialization=off doesn't seem to help there).
Not reproducible on MySQL (5.6, 5.7, 8.0).
On bb-11.0 (a.k.a improved selectivity tree MDEV-26974) the plan with default settings is different and the result is correct, but the wrong one can still be triggered with firstmatch=off.