Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL)
Description
In this script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a ENUM('5','6') CHARACTER SET BINARY);
|
INSERT INTO t1 VALUES ('5'),('6');
|
SELECT * FROM t1 WHERE a='5';
|
SELECT * FROM t1 WHERE a=1;
|
SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END;
|
all SELECT queries return the same row:
+------+
|
| a |
|
+------+
|
| 5 |
|
+------+
|
Now if I join the first and the third conditions into the same condition:
SELECT * FROM t1 WHERE CASE a WHEN 1 THEN 1 ELSE 0 END AND a='5';
|
it returns empty set. The expected result is to return the same row.
Another example demonstrates that CASE does not propagate equal fields when it safely could:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a ENUM('a','b'));
|
INSERT INTO t1 VALUES ('a'),('b');
|
EXPLAIN EXTENDED SELECT * FROM t1 WHERE CASE a WHEN 'a' THEN 1 ELSE 0 END AND a='a';
|
SHOW WARNINGS;
|
The above script returns:
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 'a') and (case `test`.`t1`.`a` when 'a' then 1 else 0 end)) |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------+
|
It could safely be rewritten as:
SELECT * FROM t1 WHERE CASE 'a' WHEN 'a' THEN 1 ELSE 0 END AND a='a';
|
and then remove the constant part:
SELECT * FROM t1 WHERE a='a';
|
Attachments
Issue Links
- blocks
-
-
- Closed
-