From 1b87cd80a268d333acbf4fc4a3d7527cb5da3d4d Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Thu, 2 Aug 2018 10:48:55 +0400 Subject: MDEV-16878 Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer --- mysql-test/main/func_time.result | 22 ++++++++++++++++++++++ mysql-test/main/func_time.test | 14 ++++++++++++++ mysql-test/main/gis.result | 12 ++++++------ 3 files changed, 42 insertions(+), 6 deletions(-) (limited to 'mysql-test/main') 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)); -- cgit v1.2.1 From 8ae2a2dbe6fc52d40ec88c325b9a44de52f83f2f Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Fri, 3 Aug 2018 09:03:11 +0400 Subject: MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME) This problem was earlier fixed by the patch for MDEV-15340. Adding tests only. --- mysql-test/main/func_time.result | 9 +++++++++ mysql-test/main/func_time.test | 10 ++++++++++ 2 files changed, 19 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result index cda36932be0..8a7a990bb85 100644 --- a/mysql-test/main/func_time.result +++ b/mysql-test/main/func_time.result @@ -3559,3 +3559,12 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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; +# +# MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME) +# +SELECT +LAST_DAY(TIME'00:00:00') AS c1, +CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2, +CAST(LAST_DAY(TIME'00:00:00') AS TIME) AS c3; +c1 c2 c3 +2018-08-31 00:00:00 00:00:00 diff --git a/mysql-test/main/func_time.test b/mysql-test/main/func_time.test index cbce3dc26bc..53cfd29dc9c 100644 --- a/mysql-test/main/func_time.test +++ b/mysql-test/main/func_time.test @@ -2149,3 +2149,13 @@ 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; + + +--echo # +--echo # MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME) +--echo # + +SELECT + LAST_DAY(TIME'00:00:00') AS c1, + CAST(CAST(LAST_DAY(TIME'00:00:00') AS DATE) AS TIME) AS c2, + CAST(LAST_DAY(TIME'00:00:00') AS TIME) AS c3; -- cgit v1.2.1 From d453374fc480112266996a1026b97654cc174c09 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 3 Aug 2018 14:54:02 -0700 Subject: MDEV-16801 seg_fault on a query The bug was in the in the code of JOIN::check_for_splittable_materialized() where the structures describing the fields of a materialized derived table that potentially could be used in split optimization were build. As a result of this bug some fields that were not usable for splitting were detected as usable. This could trigger crashes further in st_join_table::choose_best_splitting(). --- mysql-test/main/derived_cond_pushdown.result | 54 ++++++++++++++++++++++++++++ mysql-test/main/derived_cond_pushdown.test | 33 +++++++++++++++++ 2 files changed, 87 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index a2268ced84f..832b9a46289 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16026,3 +16026,57 @@ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT a aa DROP FUNCTION f1; +# +# MDEV-16801: splittable materialized derived/views with +# one grouping field from table without keys +# +CREATE TABLE t1 (a int, b int, INDEX idx_a(a), INDEX idx_b(b)) ENGINE=MYISAM; +CREATE TABLE t2 (c int) ENGINE=MYISAM; +CREATE TABLE t3 (d int) ENGINE=MYISAM; +INSERT INTO t1 VALUES +(77,7), (11,1), (33,3), (44,4), (8,88), +(78,7), (98,9), (38,3), (28,2), (79,7), +(58,5), (42,4), (71,7), (27,2), (91,9); +INSERT INTO t1 SELECT a+100, b+10 FROM t1; +INSERT INTO t2 VALUES +(100), (700), (200), (100), (200); +INSERT INTO t3 VALUES +(3), (4), (1), (8), (3); +ANALYZE tables t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +SELECT * +FROM t3, +(SELECT t1.b, t2.c +FROM t1, t2 +GROUP BY t1.b,t2.c) dt +WHERE t3.d = dt.b; +d b c +3 3 700 +3 3 200 +3 3 100 +4 4 700 +4 4 200 +4 4 100 +1 1 700 +1 1 200 +1 1 100 +3 3 700 +3 3 200 +3 3 100 +EXPLAIN EXTENDED SELECT * +FROM t3, +(SELECT t1.b, t2.c +FROM t1, t2 +GROUP BY t1.b,t2.c) dt +WHERE t3.d = dt.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using where +1 PRIMARY ref key0 key0 5 test.t3.d 2 100.00 +2 LATERAL DERIVED t1 ref idx_b idx_b 5 test.t3.d 2 100.00 Using index; Using temporary; Using filesort +2 LATERAL DERIVED t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 /* select#1 */ select `test`.`t3`.`d` AS `d`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from `test`.`t3` join (/* select#2 */ select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`b` = `test`.`t3`.`d` group by `test`.`t1`.`b`,`test`.`t2`.`c`) `dt` where `dt`.`b` = `test`.`t3`.`d` +DROP TABLE t1,t2,t3; diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 0c3a542f907..b75c56e9ff2 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2986,3 +2986,36 @@ END;$$ DELIMITER ;$$ SELECT a FROM (SELECT "aa" a) t WHERE f1(t.a, (SELECT MAX('aa') FROM DUAL LIMIT 1)); DROP FUNCTION f1; + +--echo # +--echo # MDEV-16801: splittable materialized derived/views with +--echo # one grouping field from table without keys +--echo # + +CREATE TABLE t1 (a int, b int, INDEX idx_a(a), INDEX idx_b(b)) ENGINE=MYISAM; +CREATE TABLE t2 (c int) ENGINE=MYISAM; +CREATE TABLE t3 (d int) ENGINE=MYISAM; +INSERT INTO t1 VALUES + (77,7), (11,1), (33,3), (44,4), (8,88), + (78,7), (98,9), (38,3), (28,2), (79,7), + (58,5), (42,4), (71,7), (27,2), (91,9); +INSERT INTO t1 SELECT a+100, b+10 FROM t1; +INSERT INTO t2 VALUES + (100), (700), (200), (100), (200); +INSERT INTO t3 VALUES + (3), (4), (1), (8), (3); + +ANALYZE tables t1,t2,t3; + +let $q= +SELECT * + FROM t3, + (SELECT t1.b, t2.c + FROM t1, t2 + GROUP BY t1.b,t2.c) dt +WHERE t3.d = dt.b; + +eval $q; +eval EXPLAIN EXTENDED $q; + +DROP TABLE t1,t2,t3; -- cgit v1.2.1 From 482d4da0a7a7f76e8ea9026978d49565f57a4192 Mon Sep 17 00:00:00 2001 From: Sachin Date: Tue, 7 Aug 2018 16:05:48 +0530 Subject: MDEV-15127 AddressSanitizer: stack-buffer-overflow in base_list::push_back .. Problem:- If we try to run this query with -WITH_ASAN=ON compiled server CREATE TABLE t1 (i INT); SET debug_dbug="+d,test_completely_invisible,test_invisible_index"; CREATE TABLE t2 LIKE t1; This will generate a stack buffer overflow error. ==8922==ERROR: AddressSanitizer: stack-buffer-overflow on address #ADDR Analyze:- Error is generated on this line if (((*last)=new list_node(info, &end_of_list))) So info is our Key*, &end_of_list is global variable and last == #ADDR So last is suspicious variable. And last is the variable present in alter_info ->key_list. Now the question is how this key_list->last gets wrong/ different stack variable. In the backtrace, we can see that key_list is generated in mysql_create_table_like_table by calling mysql_preapre_alter_table_function and dummy key_list is created by mysql_create_like_table. In the end on mysql_prepare_alter_table we call alter_info->key_list.swap(new_key_list); So there is two options either key_list is empty or not empty , IF it is not empty then there is no issues last ptr is replaced by thd->mem_root (allocated ptr) So problem arises when key_list is empty. It swaps the dummy last ptr by mysql_prepare_alter_table declared ptr. which is wrong. Solution:- We wont swap variable if list does not have any element. --- mysql-test/main/invisible_field_debug.result | 5 +++++ mysql-test/main/invisible_field_debug.test | 6 ++++++ 2 files changed, 11 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/invisible_field_debug.result b/mysql-test/main/invisible_field_debug.result index 4c4ebffbdfc..b3c84d18333 100644 --- a/mysql-test/main/invisible_field_debug.result +++ b/mysql-test/main/invisible_field_debug.result @@ -369,3 +369,8 @@ t1 1 invisible 1 c A NULL NULL NULL YES BTREE t1 1 invisible_2 1 invisible A NULL NULL NULL YES BTREE drop table t1; set @old_debug= @@debug_dbug; +CREATE TABLE t1 (i INT ); +SET debug_dbug="+d,test_completely_invisible,test_invisible_index"; +CREATE TABLE t2 LIKE t1; +SET debug_dbug= DEFAULT; +DROP TABLE t1, t2; diff --git a/mysql-test/main/invisible_field_debug.test b/mysql-test/main/invisible_field_debug.test index 8674620e055..c1d6899d16e 100644 --- a/mysql-test/main/invisible_field_debug.test +++ b/mysql-test/main/invisible_field_debug.test @@ -270,3 +270,9 @@ explain select * from t1 where invisible =9; show indexes in t1; drop table t1; set @old_debug= @@debug_dbug; +## MDEV 15127 +CREATE TABLE t1 (i INT ); +SET debug_dbug="+d,test_completely_invisible,test_invisible_index"; +CREATE TABLE t2 LIKE t1; +SET debug_dbug= DEFAULT; +DROP TABLE t1, t2; -- cgit v1.2.1 From 655cba619d2e6556d4a7c6a3b9127f248bb8e5a0 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 10 Aug 2018 15:03:21 +0200 Subject: Update C/C to v3.0.6 --- mysql-test/main/session_tracker_last_gtid.result | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/session_tracker_last_gtid.result b/mysql-test/main/session_tracker_last_gtid.result index 795d0aaa2a2..deffeca1333 100644 --- a/mysql-test/main/session_tracker_last_gtid.result +++ b/mysql-test/main/session_tracker_last_gtid.result @@ -2,7 +2,13 @@ # MDEV-15477: SESSION_SYSVARS_TRACKER does not track last_gtid # SET gtid_seq_no=1000; +-- Tracker : SESSION_TRACK_SCHEMA +-- test + SET @@session.session_track_system_variables='last_gtid'; +-- Tracker : SESSION_TRACK_SCHEMA +-- test + create table t1 (a int) engine=innodb; -- Tracker : SESSION_TRACK_SYSTEM_VARIABLES -- last_gtid -- cgit v1.2.1