drop table if exists t1,t2;
create table t1 (a1 int, b1 int, c1 int);
create table t2 (a2 int, b2 int, c2 int);
insert into t1 values (1,11,111), (2,22,222), (2,22,222), (3,33,300), (3,33,301), (4,44,444);
insert into t2 values (1,11,111), (2,22,222), (2,22,222), (3,33,300), (3,33,301), (5,55,555);
SET @@sql_mode='ansi';
explain extended select a1, (select count(b1) from t2 limit 1) from t1 group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	
Warnings:
Note	1276	Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1
Note	1472	Aggregate function 'count(b1)' of SELECT #2 will be aggregated in SELECT #1
Note	1003	select "test"."t1"."a1" AS "a1",(select count("test"."t1"."b1") AS "count(b1)" from "test"."t2" limit 1) AS "(select count(b1) from t2 limit 1)" from "test"."t1" group by "test"."t1"."a1"
explain extended select a1 from t1 group by a1 having (select count(b1) from t2 limit 1) ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	
Warnings:
Note	1276	Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1
Note	1472	Aggregate function 'count(b1)' of SELECT #2 will be aggregated in SELECT #1
Note	1003	select "test"."t1"."a1" AS "a1" from "test"."t1" group by "test"."t1"."a1" having (select count("test"."t1"."b1") AS "count(b1)" from "test"."t2" limit 1)
explain extended select a1, (select 1 from t2 where count(b1) limit 1) from t1 group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
Warnings:
Note	1276	Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1
Note	1472	Aggregate function 'count("test"."t1"."b1")' of SELECT #2 will be aggregated in SELECT #1
Note	1003	select "test"."t1"."a1" AS "a1",(select 1 AS "1" from "test"."t2" where count("test"."t1"."b1") limit 1) AS "(select 1 from t2 where count(b1) limit 1)" from "test"."t1" group by "test"."t1"."a1"
explain extended select a1 from t1 group by a1 having (select 1 from t2 where count(b1) limit 1) ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
Warnings:
Note	1276	Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1
Note	1472	Aggregate function 'count("test"."t1"."b1")' of SELECT #2 will be aggregated in SELECT #1
Note	1003	select "test"."t1"."a1" AS "a1" from "test"."t1" group by "test"."t1"."a1" having (select 1 AS "1" from "test"."t2" where count("test"."t1"."b1") limit 1)
explain extended select (select group_concat(sum(b1)) from t2 limit 1) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	
Warnings:
Note	1276	Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1
Note	1472	Aggregate function 'sum("test"."t1"."b1")"' of SELECT #2 will be aggregated in SELECT #1
Note	1003	select (select group_concat(sum("test"."t1"."b1") separator ',') AS "group_concat(sum(b1))" from "test"."t2" limit 1) AS "(select group_concat(sum(b1)) from t2 limit 1)" from "test"."t1"
SET @@sql_mode=default;
explain extended select a1, (select count(b1) from t2 limit 1) from t1 group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	
Warnings:
Note	1276	Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1
Note	1472	Aggregate function 'count(b1)' of SELECT #2 will be aggregated in SELECT #1
Note	1003	select `test`.`t1`.`a1` AS `a1`,(select count(`test`.`t1`.`b1`) AS `count(b1)` from `test`.`t2` limit 1) AS `(select count(b1) from t2 limit 1)` from `test`.`t1` group by `test`.`t1`.`a1`
explain extended select a1 from t1 where (select count(b1) from t2 limit 1) ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	
Warnings:
Note	1276	Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1
Note	1003	select `test`.`t1`.`a1` AS `a1` from `test`.`t1` where (select count(`test`.`t1`.`b1`) AS `count(b1)` from `test`.`t2` limit 1)
explain extended select a1 from t1 group by a1 having (select count(b1) from t2 limit 1) ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	
Warnings:
Note	1276	Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1
Note	1472	Aggregate function 'count(b1)' of SELECT #2 will be aggregated in SELECT #1
Note	1003	select `test`.`t1`.`a1` AS `a1` from `test`.`t1` group by `test`.`t1`.`a1` having (select count(`test`.`t1`.`b1`) AS `count(b1)` from `test`.`t2` limit 1)
explain extended select a1, (select 1 from t2 where count(b1) limit 1) from t1 group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
Warnings:
Note	1276	Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1
Note	1472	Aggregate function 'count(`test`.`t1`.`b1`)' of SELECT #2 will be aggregated in SELECT #1
Note	1003	select `test`.`t1`.`a1` AS `a1`,(select 1 AS `1` from `test`.`t2` where count(`test`.`t1`.`b1`) limit 1) AS `(select 1 from t2 where count(b1) limit 1)` from `test`.`t1` group by `test`.`t1`.`a1`
explain extended select a1 from t1 group by a1 having (select 1 from t2 where count(b1) limit 1) ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
Warnings:
Note	1276	Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1
Note	1472	Aggregate function 'count(`test`.`t1`.`b1`)' of SELECT #2 will be aggregated in SELECT #1
Note	1003	select `test`.`t1`.`a1` AS `a1` from `test`.`t1` group by `test`.`t1`.`a1` having (select 1 AS `1` from `test`.`t2` where count(`test`.`t1`.`b1`) limit 1)
explain extended select (select group_concat(sum(b1)) from t2 limit 1) from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	
Warnings:
Note	1276	Field or reference 'test.t1.b1' of SELECT #2 was resolved in SELECT #1
Note	1472	Aggregate function 'sum(`test`.`t1`.`b1`)`' of SELECT #2 will be aggregated in SELECT #1
Note	1003	select (select group_concat(sum(`test`.`t1`.`b1`) separator ',') AS `group_concat(sum(b1))` from `test`.`t2` limit 1) AS `(select group_concat(sum(b1)) from t2 limit 1)` from `test`.`t1`
explain extended select a1, (select 1 from t2 where count(*) limit 1) from t1 group by a1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
Warnings:
Note	1472	Aggregate function 'count(0)t`.ys' of SELECT #2 will be aggregated in SELECT #1
Note	1003	select `test`.`t1`.`a1` AS `a1`,(select 1 AS `1` from `test`.`t2` where count(0) limit 1) AS `(select 1 from t2 where count(*) limit 1)` from `test`.`t1` group by `test`.`t1`.`a1`
explain extended select a1 from t1 group by a1 having (select 1 from t2 where count(*) limit 1) ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
Warnings:
Note	1472	Aggregate function 'count(0)t`.ys' of SELECT #2 will be aggregated in SELECT #1
Note	1003	select `test`.`t1`.`a1` AS `a1` from `test`.`t1` group by `test`.`t1`.`a1` having (select 1 AS `1` from `test`.`t2` where count(0) limit 1)