Description:
Wrapping COUNT(*) in CASE WHEN t1.k IS NULL THEN COUNT(*) ELSE COUNT(*) END changes the result of a query that uses a scalar subquery in its WHERE condition. The query with the direct COUNT(*) returns no rows, while the query with CASE returns one row.
Both branches of the CASE expression contain the same COUNT(*), so the two queries should return the same result. The CTE contains a TIMESTAMP range comparison with zero date components. Both queries complete with warnings, but produce different result sets.
How to repeat:
Run the following in an empty schema:
CREATE TABLE t1 (
i INT NOT NULL,
j INT,
k INT,
l INT,
PRIMARY KEY (i),
INDEX j (j, k DESC, l),
INDEX i (i, j, k, l)
) ENGINE=InnoDB;
CREATE TABLE t2 (
ts TIMESTAMP NOT NULL,
PRIMARY KEY (ts)
) ENGINE=InnoDB;
Actual result:
The first and third queries return:
i | j | k | l
4 | 1 | 2 | 3
The second query returns an empty result set with one warning. The third query returns one row with five warnings.
Expected result:
The second and third queries should return the same result set. Wrapping COUNT(*) in a CASE expression whose two branches both return COUNT(*) should not change the scalar subquery's value or the rows selected by the outer query.
INSERT INTO t1 VALUES (4, 1, 2, 3);
INSERT INTO t2 VALUES ('2008-01-01 00:00:00');
ANALYZE TABLE t1;
SELECT * FROM t1 WHERE i < 10 AND j >= 1 AND k >= 2 AND l <= 5;
WITH c AS (SELECT * FROM t2 WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY ts DESC LIMIT 2) SELECT * FROM t1 WHERE i < 10 AND j >= 1 AND k >= CAST((SELECT COUNT(*) FROM c) AS SIGNED) + 2 AND l <= 5;
WITH c AS (SELECT * FROM t2 WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY ts DESC LIMIT 2) SELECT * FROM t1 WHERE i < 10 AND j >= 1 AND k >= CAST((SELECT CASE WHEN t1.k IS NULL THEN COUNT(*) ELSE COUNT(*) END FROM c) AS SIGNED) + 2 AND l <= 5;
Description: Wrapping COUNT(*) in CASE WHEN t1.k IS NULL THEN COUNT(*) ELSE COUNT(*) END changes the result of a query that uses a scalar subquery in its WHERE condition. The query with the direct COUNT(*) returns no rows, while the query with CASE returns one row. Both branches of the CASE expression contain the same COUNT(*), so the two queries should return the same result. The CTE contains a TIMESTAMP range comparison with zero date components. Both queries complete with warnings, but produce different result sets. How to repeat: Run the following in an empty schema: CREATE TABLE t1 ( i INT NOT NULL, j INT, k INT, l INT, PRIMARY KEY (i), INDEX j (j, k DESC, l), INDEX i (i, j, k, l) ) ENGINE=InnoDB; CREATE TABLE t2 ( ts TIMESTAMP NOT NULL, PRIMARY KEY (ts) ) ENGINE=InnoDB; Actual result: The first and third queries return: i | j | k | l 4 | 1 | 2 | 3 The second query returns an empty result set with one warning. The third query returns one row with five warnings. Expected result: The second and third queries should return the same result set. Wrapping COUNT(*) in a CASE expression whose two branches both return COUNT(*) should not change the scalar subquery's value or the rows selected by the outer query. INSERT INTO t1 VALUES (4, 1, 2, 3); INSERT INTO t2 VALUES ('2008-01-01 00:00:00'); ANALYZE TABLE t1; SELECT * FROM t1 WHERE i < 10 AND j >= 1 AND k >= 2 AND l <= 5; WITH c AS (SELECT * FROM t2 WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY ts DESC LIMIT 2) SELECT * FROM t1 WHERE i < 10 AND j >= 1 AND k >= CAST((SELECT COUNT(*) FROM c) AS SIGNED) + 2 AND l <= 5; WITH c AS (SELECT * FROM t2 WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY ts DESC LIMIT 2) SELECT * FROM t1 WHERE i < 10 AND j >= 1 AND k >= CAST((SELECT CASE WHEN t1.k IS NULL THEN COUNT(*) ELSE COUNT(*) END FROM c) AS SIGNED) + 2 AND l <= 5;