Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0, 10.1, 10.2, 10.3, 10.4
-
None
Description
WHERE optimization does not seem to distinguish between a simple CASE and a searched CASE with the same arguments.
I create and populate a table:
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (a INT, b INT, KEY(a)); |
INSERT INTO t1 VALUES (1,1),(2,2),(3,3); |
A query with a simple CASE:
SELECT * FROM t1 WHERE CASE a WHEN b THEN 1 END=1; |
+------+------+
|
| a | b |
|
+------+------+
|
| 1 | 1 |
|
| 2 | 2 |
|
| 3 | 3 |
|
+------+------+
|
returns 3 rows.
A query with a searched CASE:
SELECT * FROM t1 WHERE CASE WHEN a THEN b ELSE 1 END=3; |
+------+------+
|
| a | b |
|
+------+------+
|
| 3 | 3 |
|
+------+------+
|
returns one row.
Now I combine both conditions using AND:
SELECT * FROM t1 WHERE |
CASE a WHEN b THEN 1 END=1 |
AND
|
CASE WHEN a THEN b ELSE 1 END=3; |
Empty set (0.00 sec)
|
The expected result is to return one row, as in the second query.
The condition was erroneously optimized to WHERE 0;
EXPLAIN EXTENDED
|
SELECT * FROM t1 WHERE |
CASE a WHEN b THEN 1 END=1 |
AND
|
CASE WHEN a THEN b ELSE 1 END=3; |
SHOW WARNINGS;
|
+-------+------+-------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where 0 |
|
+-------+------+-------------------------------------------------------------------------------+
|
Attachments
Issue Links
- relates to
-
MDEV-16885 A method of CASE searched/simple detection
-
- Closed
-