# Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer

XMLWordPrintable

#### Details

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.2, 10.3, 10.4, 10.1
• Fix Version/s:
• Component/s:
• Labels:
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
• subtime

#### People

Assignee:
Alexander Barkov
Reporter:
Alexander Barkov