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:
Tags: correlated, filesort, having, regression, subquery

[30 Sep 2013 7:24] Alan Egerton
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.
[30 Sep 2013 7:44] Valerii Kravchuk
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)
[30 Sep 2013 8:42] Umesh Shastry
Hello Alan,

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

Thanks,
Umesh
[30 Sep 2013 17:13] Sinisa Milivojevic
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 ...
[30 Sep 2013 17:54] Shane Bester
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);
----
[2 Oct 2013 11:36] Shane Bester
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.