Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5.40, 10.0.14, 10.1.1
-
None
Description
Condition propagation works for string and numeric data types:
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (c1 VARCHAR(10)); |
INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02'); |
EXPLAIN EXTENDED SELECT * FROM t1 WHERE CONCAT(c1)='2001-01-01' AND CONCAT(c1)>'2002-01-01'; |
The above script correctly detects impossible condition:
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
|
But in case of temporal data types, it does not work:
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 (c1 DATE); |
INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02'); |
EXPLAIN EXTENDED SELECT * FROM t1 WHERE TIMESTAMP(c1)=TIMESTAMP'2001-01-01 00:00:00' AND TIMESTAMP(c1)>TIMESTAMP'2002-01-01 00:00:00'; |
SHOW WARNINGS;
|
It does not detect that the condition is not possible:
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where ((cast(`test`.`t1`.`c1` as datetime) = TIMESTAMP'2001-01-01 00:00:00') and (cast(`test`.`t1`.`c1` as datetime) > TIMESTAMP'2002-01-01 00:00:00')) |
|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
It's going to scan the entire table.
The problem happens because clone_item() returns NULL for temporal literals. It should be rewritten to do true cloning.