Description:
I found this while testing ALL subqueries inside a CASE expression. The subquery reads an empty table, so v = ALL (SELECT v FROM t0) is true and the single row in ot should be returned. That is what happens with subquery_to_derived=off. When I turn subquery_to_derived on, the same query returns no rows, without any change to the tables or data. I get the empty result with semijoin both on and off. The CASE has the same predicate in its THEN and ELSE branches, so it should return the row in either case.
How to repeat:
DROP TABLE IF EXISTS ot, t0;
CREATE TABLE ot (v INT NOT NULL);
CREATE TABLE t0 (v INT NOT NULL);
INSERT INTO ot VALUES (1);
-- t0 is empty.
SET SESSION optimizer_switch = 'semijoin=off,subquery_to_derived=off';
-- Actual: 1 row (v = 1).
SELECT v FROM ot WHERE CASE WHEN TRUE
THEN v = ALL (SELECT v FROM t0)
ELSE v = ALL (SELECT v FROM t0)
END;
SET SESSION optimizer_switch = 'semijoin=off,subquery_to_derived=on';
-- Actual: 0 rows; expected: 1 row (v = 1).
SELECT v FROM ot WHERE CASE WHEN TRUE
THEN v = ALL (SELECT v FROM t0)
ELSE v = ALL (SELECT v FROM t0)
END;
SET SESSION optimizer_switch = 'semijoin=on,subquery_to_derived=on';
-- Actual: 0 rows; expected: 1 row (v = 1).
SELECT v FROM ot WHERE CASE WHEN TRUE
THEN v = ALL (SELECT v FROM t0)
ELSE v = ALL (SELECT v FROM t0)
END;
Expected result: All three queries return v = 1. MySQL specifies that an ALL comparison against an empty subquery is true; optimizer settings must preserve that result.
