[#MDEV-16878] Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer

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

[MDEV-16878] Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer Created: 2018-08-02 Updated: 2018-08-13 Resolved: 2018-08-02
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.1, 10.2, 10.3, 10.4
Fix Version/s:	10.3.9

[MDEV-16878] Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer Created: 2018-08-02 Updated: 2018-08-13 Resolved: 2018-08-02