diff options
| -rw-r--r-- | mysql-test/main/func_time.result | 22 | ||||
| -rw-r--r-- | mysql-test/main/func_time.test | 14 | ||||
| -rw-r--r-- | mysql-test/main/gis.result | 12 | ||||
| -rw-r--r-- | sql/item_timefunc.cc | 21 | ||||
| -rw-r--r-- | sql/item_timefunc.h | 6 |
5 files changed, 46 insertions, 29 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)); diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index b4d929e0f70..d29181a3446 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -2817,27 +2817,6 @@ bool Item_func_add_time::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date) } -void Item_func_add_time::print(String *str, enum_query_type query_type) -{ - if (is_date) - { - DBUG_ASSERT(sign > 0); - str->append(STRING_WITH_LEN("timestamp(")); - } - else - { - if (sign > 0) - str->append(STRING_WITH_LEN("addtime(")); - else - str->append(STRING_WITH_LEN("subtime(")); - } - args[0]->print(str, query_type); - str->append(','); - args[1]->print(str, query_type); - str->append(')'); -} - - /** TIMEDIFF(t,s) is a time function that calculates the time value between a start and end time. diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index 51ce3bf2988..7aacdec85e0 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -1245,8 +1245,10 @@ public: { sign= neg_arg ? -1 : 1; } bool fix_length_and_dec(); bool get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date); - void print(String *str, enum_query_type query_type); - const char *func_name() const { return "add_time"; } + const char *func_name() const + { + return is_date ? "timestamp" : sign > 0 ? "addtime" : "subtime"; + } Item *get_copy(THD *thd) { return get_item_copy<Item_func_add_time>(thd, this); } }; |