Description:
I found a wrong result when sql_buffer_result is enabled. Wrapping an IN predicate in a CASE expression changes the result even though the THEN and ELSE branches contain the same predicate. The issue also reproduces on a freshly initialized MySQL instance using only the session setting and the two tables below.
How to repeat:
Run the following in an empty schema:
SET SESSION sql_buffer_result = 1;
CREATE TABLE t1 (a BIT(7)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (64), (65), (65), (NULL), (66);
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
INSERT INTO t2 VALUES (61), (64), (65);
SELECT * FROM t2
WHERE a IN (
SELECT CASE
WHEN t2.a IS NULL OR NOT (t2.a IS NULL)
OR (t2.a IS NULL) IS NULL
THEN a ELSE a
END FROM t1
);
SELECT * FROM t2
WHERE CASE
WHEN (t2.a IS NULL OR t2.a IS NULL)
OR NOT (t2.a IS NULL OR t2.a IS NULL)
OR (t2.a IS NULL OR t2.a IS NULL) IS NULL
THEN a IN (
SELECT CASE
WHEN t2.a IS NULL OR NOT (t2.a IS NULL)
OR (t2.a IS NULL) IS NULL
THEN a ELSE a
END FROM t1
)
ELSE a IN (
SELECT CASE
WHEN t2.a IS NULL OR NOT (t2.a IS NULL)
OR (t2.a IS NULL) IS NULL
THEN a ELSE a
END FROM t1
)
END;
Actual result:
The first query returns 64 and 65. The second returns 61, 64, and 65.
Expected result:
Both queries should return 64 and 65. The outer CASE selects between two identical predicates, so it should not cause the additional row 61 to match.
Setting sql_buffer_result back to its default value makes the second query return only 64 and 65 in my test.
Description: I found a wrong result when sql_buffer_result is enabled. Wrapping an IN predicate in a CASE expression changes the result even though the THEN and ELSE branches contain the same predicate. The issue also reproduces on a freshly initialized MySQL instance using only the session setting and the two tables below. How to repeat: Run the following in an empty schema: SET SESSION sql_buffer_result = 1; CREATE TABLE t1 (a BIT(7)) ENGINE=InnoDB; INSERT INTO t1 VALUES (64), (65), (65), (NULL), (66); CREATE TABLE t2 (a INT) ENGINE=InnoDB; INSERT INTO t2 VALUES (61), (64), (65); SELECT * FROM t2 WHERE a IN ( SELECT CASE WHEN t2.a IS NULL OR NOT (t2.a IS NULL) OR (t2.a IS NULL) IS NULL THEN a ELSE a END FROM t1 ); SELECT * FROM t2 WHERE CASE WHEN (t2.a IS NULL OR t2.a IS NULL) OR NOT (t2.a IS NULL OR t2.a IS NULL) OR (t2.a IS NULL OR t2.a IS NULL) IS NULL THEN a IN ( SELECT CASE WHEN t2.a IS NULL OR NOT (t2.a IS NULL) OR (t2.a IS NULL) IS NULL THEN a ELSE a END FROM t1 ) ELSE a IN ( SELECT CASE WHEN t2.a IS NULL OR NOT (t2.a IS NULL) OR (t2.a IS NULL) IS NULL THEN a ELSE a END FROM t1 ) END; Actual result: The first query returns 64 and 65. The second returns 61, 64, and 65. Expected result: Both queries should return 64 and 65. The outer CASE selects between two identical predicates, so it should not cause the additional row 61 to match. Setting sql_buffer_result back to its default value makes the second query return only 64 and 65 in my test.