Bug #81259 Get unexpected `Empty Set` for correlated subquery with aggregate functions
Submitted: 1 May 2016 16:29 Modified: 2 May 2016 5:52
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.8, 5.5.49, 5.6.30, 5.7.12 OS:Any
Assigned to: CPU Architecture:Any

[1 May 2016 16:29] Su Dylan
Description:
Output:
=====
mysql> create table t1(c1 int, c2 int, c3 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
+------+------+------+
3 rows in set (0.00 sec)

mysql> create table t2(c1 int, c2 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(2,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+------+
| c1   | c2   |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.00 sec)

mysql> select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+c) from t2 tt));
Empty set (0.00 sec)

Problem:
=====
Since (select t.c1 as c from t2 ...) can only produce one value 2, the condition c1 > 2 should not produce `Empty Set` result.

How to repeat:

drop table if exists t1,t2;
create table t1(c1 int, c2 int, c3 int);
insert into t1 values(1,1,1),(2,2,2),(3,3,3);
select * from t1;
create table t2(c1 int, c2 int);
insert into t2 values(2,2);
select * from t2;
select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+c) from t2 tt));

Suggested fix:
Non empty set is returned for the query.
[2 May 2016 5:52] MySQL Verification Team
Hello Su Dylan,

Thank you for the report and test case.

Thanks,
Umesh