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

3 files changed, 42 insertions, 6 deletions

diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result
index 151dbc8d5d8..cda36932be0 100644
--- a/mysql-test/main/func_time.result
+++ b/mysql-test/main/func_time.result
@@ -3537,3 +3537,25 @@ SET @@session.slow_query_log= @sav_slow_query_log;
 DROP FUNCTION fn_sleep_before_now;
 DROP TRIGGER trg_insert_t_ts;
 DROP TABLE t_ts, t_trig;
+#
+# MDEV-16878 Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer
+#
+CREATE TABLE t1 (a TIME);
+INSERT INTO t1 VALUES ('00:00:10'),('00:00:20');
+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
+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';
+a
+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
+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';
+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
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where subtime(`test`.`t1`.`a`,TIME'00:00:01') = TIME'00:00:09' and addtime(`test`.`t1`.`a`,TIME'00:00:01') <= TIME'00:00:09'
+DROP TABLE t1;
diff --git a/mysql-test/main/func_time.test b/mysql-test/main/func_time.test
index ecbf23f8e36..cbce3dc26bc 100644
--- a/mysql-test/main/func_time.test
+++ b/mysql-test/main/func_time.test
@@ -2135,3 +2135,17 @@ DROP TABLE t_ts, t_trig;
 #
 # End of MDEV-13727
 ###################
+
+
+--echo #
+--echo # MDEV-16878 Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer
+--echo #
+
+CREATE TABLE t1 (a TIME);
+INSERT INTO t1 VALUES ('00:00:10'),('00:00:20');
+SELECT a, SUBTIME(a,TIME'00:00:01'), ADDTIME(a,TIME'00:00:01') FROM t1;
+SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09';
+SELECT * FROM t1 WHERE ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09'; 
+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'; 
+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'; 
+DROP TABLE t1;
diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result
index 3f2e6d39db8..2fb2d32f447 100644
--- a/mysql-test/main/gis.result
+++ b/mysql-test/main/gis.result
@@ -4128,17 +4128,17 @@ ERROR HY000: Illegal parameter data types geometry and interval for operation 'd
 SELECT INTERVAL 10 DAY + POINT(1,1);
 ERROR HY000: Illegal parameter data types geometry and interval for operation 'date_add_interval'
 SELECT ADDTIME(POINT(1,1), '10:10:10');
-ERROR HY000: Illegal parameter data types geometry and varchar for operation 'add_time'
+ERROR HY000: Illegal parameter data types geometry and varchar for operation 'addtime'
 SELECT ADDTIME('10:10:10', POINT(1,1));
-ERROR HY000: Illegal parameter data types varchar and geometry for operation 'add_time'
+ERROR HY000: Illegal parameter data types varchar and geometry for operation 'addtime'
 SELECT ADDTIME(POINT(1,1), TIME'10:10:10');
-ERROR HY000: Illegal parameter data types geometry and time for operation 'add_time'
+ERROR HY000: Illegal parameter data types geometry and time for operation 'addtime'
 SELECT ADDTIME(TIME'10:10:10', POINT(1,1));
-ERROR HY000: Illegal parameter data types time and geometry for operation 'add_time'
+ERROR HY000: Illegal parameter data types time and geometry for operation 'addtime'
 SELECT ADDTIME(POINT(1,1), TIMESTAMP'2001-01-01 10:10:10');
-ERROR HY000: Illegal parameter data types geometry and datetime for operation 'add_time'
+ERROR HY000: Illegal parameter data types geometry and datetime for operation 'addtime'
 SELECT ADDTIME(TIMESTAMP'2001-01-01 10:10:10', POINT(1,1));
-ERROR HY000: Illegal parameter data types datetime and geometry for operation 'add_time'
+ERROR HY000: Illegal parameter data types datetime and geometry for operation 'addtime'
 SELECT STR_TO_DATE(POINT(1,1),'%M %d,%Y');
 ERROR HY000: Illegal parameter data types geometry and varchar for operation 'str_to_date'
 SELECT STR_TO_DATE('2001-01-01', POINT(1,1));