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.