MySQL Bugs: #70466: No results when filesorting with a correlation in subquery's HAVING clause

Bug #70466	No results when filesorting with a correlation in subquery's HAVING clause
Submitted:	30 Sep 2013 7:24	Modified:	30 Sep 2013 8:42
Reporter:	Alan Egerton	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.5,5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	correlated, filesort, having, regression, subquery

Description:
In certain obscure edge cases, MySQL server will erroneously return an empty resultset.

How to repeat:
CREATE TABLE t (x INT NULL);  -- table with nullable column
INSERT INTO  t VALUES (0);    -- but non null data

SELECT   a.x                  -- select our nullable column
FROM     t a, (SELECT NULL) b -- joining it with anything at all
    
WHERE    EXISTS (             -- but filter on a subquery
           SELECT *
           FROM   (SELECT NULL) c -- doesn't really matter what
           HAVING a.x IS NOT NULL -- provided there is some correlated condition
                                  -- on our nullable column in the HAVING clause
         )
    
ORDER BY RAND()               -- then perform a filesort on the outer query

Suggested fix:
Clearly, in this case, it is possible (indeed advisable!) to rewrite the query - either as a join, or else to correlate the subquery in its WHERE clause rather than in the HAVING clause.

Alternatively, one can try rewriting the query to avoid the table join in the outer query or to avoid selecting the nullable column.

Failing all of the above, altering the schema (either to make the column nullable or else to avoid a filesort) may be amongst the solutions to consider.

Same with 5.5.32, so it does not seem a new problem:

mysql> select * from t;
+------+
| x    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> SELECT   a.x
    -> FROM     t a, (SELECT NULL) b
    -> WHERE    EXISTS (
    ->            SELECT *
    ->            FROM   (SELECT NULL) c
    ->            HAVING a.x IS NOT NULL
    ->          )
    -> ;
+------+
| x    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> SELECT   a.x
    -> FROM     t a, (SELECT NULL) b
    -> WHERE    EXISTS (
    ->            SELECT *
    ->            FROM   (SELECT NULL) c
    ->            HAVING a.x IS NOT NULL
    ->          )
    -> ORDER BY RAND()
    -> ;
Empty set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.32    |
+-----------+
1 row in set (0.00 sec)

Hello Alan,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

Yes, definitely a bug, but not a serious nor urgent, due to several reasons. Usage of HAVING instead of WHERE, Cartesian product, usage of column from upper node in the inner node etc ...

Another testcase:

----
drop table if exists t;
create table t (x int)engine=innodb;
insert into  t values (0);
select * from t where exists(select 1 from t having t.x=0) order by rand();
select * from t where exists(select 1 from t having t.x=0);
----

It seems my testcase affects 4.1, 5.0, 5.1, 5.5, 5.6, 5.7.
But original testcase from reporter only affects 5.5+.
Not sure if there are two bugs. Let's let developers decide.