|
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.
|