Description:
Adding a PRIMARY KEY changes the result from non-empty to empty.
Further simplification of the CASE expressions or replacing the DOUBLE constant (0.1) with an INTEGER prevents the issue from reproducing.
How to repeat:
--------------------------
DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
INSERT INTO t0(c0) VALUES(1);
INSERT INTO t1(c0) VALUES(0);
SELECT * FROM t0, t1 WHERE (0 IN (CASE WHEN 0 THEN t1.c0 ELSE 2 END)) LIKE (CASE 0 WHEN t1.c0 IS NOT NULL THEN 0.1 ELSE 0 END) ;
result:
+------+------+
| c0 | c0 |
+------+------+
| 1 | 0 |
+------+------+
1 row in set (0.00 sec)
---------------------------
DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;
CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT PRIMARY KEY);
INSERT INTO t0(c0) VALUES(1);
INSERT INTO t1(c0) VALUES(0);
SELECT *FROM t0, t1 WHERE (0 IN (CASE WHEN 0 THEN t1.c0 ELSE 2 END)) LIKE (CASE 0 WHEN t1.c0 IS NOT NULL THEN 0.1 ELSE 0 END) ;
Empty set (0.01 sec)
