Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
I create and populate a table like this:
DROP TABLE IF EXISTS t1; |
CREATE OR REPLACE TABLE t1 (a TIME); |
INSERT INTO t1 VALUES ('00:00:10'),('00:00:20'); |
Now I do some time arithmetic:
SELECT a, SUBTIME(a,TIME'00:00:01'), ADDTIME(a,TIME'00:00:01') FROM t1; |
+----------+---------------------------+---------------------------+
|
| a | SUBTIME(a,TIME'00:00:01') | ADDTIME(a,TIME'00:00:01') |
|
+----------+---------------------------+---------------------------+
|
| 00:00:10 | 00:00:09 | 00:00:11 |
|
| 00:00:20 | 00:00:19 | 00:00:21 |
|
+----------+---------------------------+---------------------------+
|
Now I put the same arithmetic expressions into WHERE:
SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09'; |
+----------+
|
| a |
|
+----------+
|
| 00:00:10 |
|
+----------+
|
SELECT * FROM t1 WHERE ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; |
Empty set (0.01 sec)
|
So far so good:
- The first query correctly returned one row
- The second query correctly returned empty set
Now I mix these two conditions using AND and expect to get empty set again:
SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; |
+----------+
|
| a |
|
+----------+
|
| 00:00:10 |
|
+----------+
|
However, the server returned one row. This is wrong!
The following EXPLAIN script demonstrates that the second condition part was erroneously eliminated by the optimizer:
EXPLAIN EXTENDED SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; |
SHOW WARNINGS;
|
...
|
+-------+------+---------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+---------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where subtime(`test`.`t1`.`a`,TIME'00:00:01') = TIME'00:00:09' |
|
+-------+------+---------------------------------------------------------------------------------------------------------------+
|
Notice, there is no the ADDTIME() condition in the optimzed query.
The problem happens because Item_func_add_time::func_name() erroneously always returns the same value, which makes the equal expression propagation code erroneously think ADDTIME() and SUBTIME() are equal to each other.
Item_func_add_time::func_name() should return three different names depending on the exact SQL function it handles:
- timestamp
- addtime
- subtime
Attachments
Issue Links
- relates to
-
-
- Closed
-