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;

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 ',')

c	f1	f2

2	8	2022-12-12

3	NULL	2023-12-12