From 807945f2eb5fa22e6f233cc17b85a2e141efe2c8 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Thu, 21 Apr 2022 22:45:31 +0300 Subject: MDEV-26402: A SEGV in Item_field::used_tables/update_depend_map_for_order... When doing condition pushdown from HAVING into WHERE, Item_equal::create_pushable_equalities() calls item->set_extraction_flag(IMMUTABLE_FL) for constant items. Then, Item::cleanup_excluding_immutables_processor() checks for this flag to see if it should call item->cleanup() or leave the item as-is. The failure happens when a constant item has a non-constant one inside it, like: (tbl.col=0 AND impossible_cond) item->walk(cleanup_excluding_immutables_processor) works in a bottom-up way so it 1. will call Item_func_eq(tbl.col=0)->cleanup() 2. will not call Item_cond_and->cleanup (as the AND is constant) This creates an item tree where a fixed Item has an un-fixed Item inside it which eventually causes an assertion failure. Fixed by introducing this rule: instead of just calling item->set_extraction_flag(IMMUTABLE_FL); we call Item::walk() to set the flag for all sub-items of the item. --- mysql-test/main/having_cond_pushdown.result | 15 +++++++++++++++ mysql-test/main/having_cond_pushdown.test | 10 ++++++++++ 2 files changed, 25 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result index 9b124296e3d..9a9ed2b9213 100644 --- a/mysql-test/main/having_cond_pushdown.result +++ b/mysql-test/main/having_cond_pushdown.result @@ -4924,3 +4924,18 @@ SELECT a FROM t1 GROUP BY a HAVING a = ( SELECT MIN(c) FROM t2 ); a 2 DROP TABLE t1,t2; +# +# MDEV-26402: A SEGV in Item_field::used_tables/update_depend_map_for_order or Assertion `fixed == 1' +# +CREATE TABLE t1 (i int NOT NULL); +SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL); +i +SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL AND 'x' = 0); +i +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +SELECT * FROM t1 GROUP BY i HAVING i='1' IN ( i IS NULL AND 'x' = 0); +i +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +DROP TABLE t1; diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test index fc75122615c..270eac2a46f 100644 --- a/mysql-test/main/having_cond_pushdown.test +++ b/mysql-test/main/having_cond_pushdown.test @@ -1440,3 +1440,13 @@ eval EXPLAIN FORMAT=JSON $q; eval $q; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-26402: A SEGV in Item_field::used_tables/update_depend_map_for_order or Assertion `fixed == 1' +--echo # + +CREATE TABLE t1 (i int NOT NULL); +SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL); +SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL AND 'x' = 0); +SELECT * FROM t1 GROUP BY i HAVING i='1' IN ( i IS NULL AND 'x' = 0); +DROP TABLE t1; -- cgit v1.2.1 From d45841b9be6fe069383cc05405f747ae36d08362 Mon Sep 17 00:00:00 2001 From: Rucha Deodhar Date: Tue, 26 Apr 2022 12:58:48 +0530 Subject: MDEV-26695: Number of an invalid row is not calculated for table value constructor Analysis: counter does not increment while sending rows for table value constructor and so row_number assumes the default value (0 in this case). Fix: Increment the counter to avoid counter using default value. --- mysql-test/main/get_diagnostics.result | 10 ++++++++++ mysql-test/main/get_diagnostics.test | 9 +++++++++ 2 files changed, 19 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/get_diagnostics.result b/mysql-test/main/get_diagnostics.result index 6944103c805..41511d8a521 100644 --- a/mysql-test/main/get_diagnostics.result +++ b/mysql-test/main/get_diagnostics.result @@ -790,3 +790,13 @@ GET DIAGNOSTICS @var1 = NUMBER; SHOW STATUS LIKE 'Com%get_diagnostics'; Variable_name Value Com_get_diagnostics 1 +# +# MDEV-26695: Number of an invalid row is not calculated for table value constructor +# +CREATE TABLE t1 (a CHAR(1)) VALUES ('a'),('b'),('foo'); +Warnings: +Warning 1406 Data too long for column 'a' at row 3 +CREATE TABLE t2 (a char(1)) VALUES ('a'),('b') UNION VALUES ('foo'); +Warnings: +Warning 1406 Data too long for column 'a' at row 3 +DROP TABLE t1, t2; diff --git a/mysql-test/main/get_diagnostics.test b/mysql-test/main/get_diagnostics.test index 1553eb500b7..78ed3ea811b 100644 --- a/mysql-test/main/get_diagnostics.test +++ b/mysql-test/main/get_diagnostics.test @@ -851,3 +851,12 @@ FLUSH STATUS; SHOW STATUS LIKE 'Com%get_diagnostics'; GET DIAGNOSTICS @var1 = NUMBER; SHOW STATUS LIKE 'Com%get_diagnostics'; + +--echo # +--echo # MDEV-26695: Number of an invalid row is not calculated for table value constructor +--echo # + +CREATE TABLE t1 (a CHAR(1)) VALUES ('a'),('b'),('foo'); +CREATE TABLE t2 (a char(1)) VALUES ('a'),('b') UNION VALUES ('foo'); + +DROP TABLE t1, t2; -- cgit v1.2.1 From 39feab3cd31b5414aa9b428eaba915c251ac34a2 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 25 Apr 2022 18:08:57 -0700 Subject: MDEV-26412 Server crash in Item_field::fix_outer_field for INSERT SELECT IF an INSERT/REPLACE SELECT statement contained an ON expression in the top level select and this expression used a subquery with a column reference that could not be resolved then an attempt to resolve this reference as an outer reference caused a crash of the server. This happened because the outer context field in the Name_resolution_context structure was not set to NULL for such references. Rather it pointed to the first element in the select_stack. Note that starting from 10.4 we cannot use the SELECT_LEX::outer_select() method when parsing a SELECT construct. Approved by Oleksandr Byelkin --- mysql-test/main/insert.result | 16 ++++++++++++++++ mysql-test/main/insert.test | 22 ++++++++++++++++++++++ 2 files changed, 38 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/insert.result b/mysql-test/main/insert.result index 4aea81262d2..1062ddd6a94 100644 --- a/mysql-test/main/insert.result +++ b/mysql-test/main/insert.result @@ -751,3 +751,19 @@ REPLACE INTO v1 SET f = NULL; ERROR 22007: Truncated incorrect DOUBLE value: 'foo' DROP VIEW v1; DROP TABLE t1; +# End of 10.0 tests +# +# MDEV-26412: INSERT CREATE with subquery in ON expression +# +create table t1 (a int); +create table t2 (b int); +create table t3 (c int); +create table t4 (d1 int, d2 int); +insert into t4 +select * from t1 left join t2 on (select t1.i from t3); +ERROR 42S22: Unknown column 't1.i' in 'field list' +replace t4 +select * from t1 left join t2 on (select t1.i from t3); +ERROR 42S22: Unknown column 't1.i' in 'field list' +drop table t1,t2,t3,t4; +# End of 10.4 tests diff --git a/mysql-test/main/insert.test b/mysql-test/main/insert.test index e00c9cd7a0d..9fd0933cc7c 100644 --- a/mysql-test/main/insert.test +++ b/mysql-test/main/insert.test @@ -612,3 +612,25 @@ CREATE VIEW v1 AS SELECT * FROM t1 WHERE f <=> 'foo' WITH CHECK OPTION; REPLACE INTO v1 SET f = NULL; DROP VIEW v1; DROP TABLE t1; + +--echo # End of 10.0 tests + +--echo # +--echo # MDEV-26412: INSERT CREATE with subquery in ON expression +--echo # + +create table t1 (a int); +create table t2 (b int); +create table t3 (c int); +create table t4 (d1 int, d2 int); + +--error ER_BAD_FIELD_ERROR +insert into t4 + select * from t1 left join t2 on (select t1.i from t3); +--error ER_BAD_FIELD_ERROR +replace t4 + select * from t1 left join t2 on (select t1.i from t3); + +drop table t1,t2,t3,t4; + +--echo # End of 10.4 tests -- cgit v1.2.1 From af810407f78b7f792a9bb8c47c8c532eb3b3a758 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 28 Apr 2022 01:58:31 +0200 Subject: MDEV-28098 incorrect key in "dup value" error after long unique reset errkey after using it, so that it wouldn't affect the next error message in the next statement --- mysql-test/main/long_unique_bugs.result | 14 ++++++++++++++ mysql-test/main/long_unique_bugs.test | 17 +++++++++++++++-- 2 files changed, 29 insertions(+), 2 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index bf53b8f1d88..db4c8b2f3f3 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -302,4 +302,18 @@ c 1 DELETE FROM t; DROP TABLE t; +# +# MDEV-28098 incorrect key in "dup value" error after long unique +# +create table t1 (v3 int primary key, v2 text(100) unique not null, v1 int unique) engine=innodb; +insert into t1 values ( -32768 , -128 , 58 ) , ( -1 , 44 , -128 ); +create table t2 (v6 int primary key, v5 text, a int not null) engine=innodb; +insert into t2 values ( 50 , 61 , -1 ) , ( -2147483648 , -128 , 0 ); +update t1 set v2 = 1, v3 = -128; +ERROR 23000: Duplicate entry '1' for key 'v2' +update t1,t2 set v1 = v2 , v5 = 0; +ERROR 23000: Duplicate entry '-128' for key 'v1' +drop table t1, t2; +# # End of 10.4 tests +# diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index b0e6c2b3bf6..02852cf08e5 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -233,7 +233,6 @@ DROP TABLE t1; # # MDEV-18901 Wrong results after ADD UNIQUE INDEX(blob_column) # ---source include/have_innodb.inc CREATE TABLE t1 (data VARCHAR(7961)) ENGINE=InnoDB; INSERT INTO t1 VALUES ('f'), ('o'), ('o'); @@ -279,7 +278,6 @@ DROP TABLE t1; # MDEV-18820 Assertion `lock_table_has(trx, index->table, LOCK_IX)' failed in lock_rec_insert_check_and_lock upon INSERT into table with blob key' # ---source include/have_innodb.inc set innodb_lock_wait_timeout= 10; CREATE TABLE t1 ( @@ -385,4 +383,19 @@ DELETE FROM t; DROP TABLE t; +--echo # +--echo # MDEV-28098 incorrect key in "dup value" error after long unique +--echo # +create table t1 (v3 int primary key, v2 text(100) unique not null, v1 int unique) engine=innodb; +insert into t1 values ( -32768 , -128 , 58 ) , ( -1 , 44 , -128 ); +create table t2 (v6 int primary key, v5 text, a int not null) engine=innodb; +insert into t2 values ( 50 , 61 , -1 ) , ( -2147483648 , -128 , 0 ); +--error ER_DUP_ENTRY +update t1 set v2 = 1, v3 = -128; +--error ER_DUP_ENTRY +update t1,t2 set v1 = v2 , v5 = 0; +drop table t1, t2; + +--echo # --echo # End of 10.4 tests +--echo # -- cgit v1.2.1 From bc9102eb811cc39a76b04cab316bbdcceaa16026 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 28 Apr 2022 16:59:50 +0200 Subject: cleanup: (*order->item) -> item and minor style fixes --- mysql-test/main/having.result | 5 ++++- mysql-test/main/having.test | 25 ++++++++++++------------- mysql-test/main/subselect_innodb.result | 11 ++++++++--- mysql-test/main/subselect_innodb.test | 17 ++++++++--------- 4 files changed, 32 insertions(+), 26 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/having.result b/mysql-test/main/having.result index 51b88c5b8d2..8800402dc35 100644 --- a/mysql-test/main/having.result +++ b/mysql-test/main/having.result @@ -1,4 +1,3 @@ -drop table if exists t1,t2,t3; create table t1 (a int); select count(a) as b from t1 where a=0 having b > 0; b @@ -880,8 +879,10 @@ h # # drop table t1; +# # End of 10.3 tests # +# # MDEV-18681: AND formula in HAVING with several occurances # of the same field f in different conjuncts + f=constant # @@ -906,4 +907,6 @@ INSERT INTO t VALUES ('a'),('b'); SELECT * FROM t HAVING f = 'foo'; f DROP TABLE t; +# # End of 10.4 tests +# diff --git a/mysql-test/main/having.test b/mysql-test/main/having.test index 7e0a0439f8e..b3b128684a3 100644 --- a/mysql-test/main/having.test +++ b/mysql-test/main/having.test @@ -1,10 +1,6 @@ # test of problems with having (Reported by Mark Rogers) # ---disable_warnings -drop table if exists t1,t2,t3; ---enable_warnings - create table t1 (a int); select count(a) as b from t1 where a=0 having b > 0; insert into t1 values (null); @@ -207,7 +203,7 @@ select count(*) from t1 group by col1 having col1 = 10; select count(*) as count_col1 from t1 group by col1 having col1 = 10; select count(*) as count_col1 from t1 as tmp1 group by col1 having col1 = 10; select count(*) from t1 group by col2 having col2 = 'hello'; ---error 1054 +--error ER_BAD_FIELD_ERROR select count(*) from t1 group by col2 having col1 = 10; select col1 as count_col1 from t1 as tmp1 group by col1 having col1 = 10; select col1 as count_col1 from t1 as tmp1 group by col1 having count_col1 = 10; @@ -221,10 +217,10 @@ select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col1 = select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having count_col1 = 10; select col1 as count_col1,col2 from t1 as tmp1 group by col1,col2 having col2 = 'hello'; select col1 as count_col1,col2 as group_col2 from t1 as tmp1 group by col1,col2 having group_col2 = 'hello'; ---error 1064 +--error ER_PARSE_ERROR select sum(col1) as co12 from t1 group by col2 having col2 10; select sum(col1) as co2, count(col2) as cc from t1 group by col1 having col1 =10; ---error 1054 +--error ER_BAD_FIELD_ERROR select t2.col2 from t2 group by t2.col1, t2.col2 having t1.col1 <= 10; @@ -273,7 +269,7 @@ having (select col_t1 from t2 where col_t1 = col_t2 order by col_t2 limit 1); # nested queries with HAVING, inner having column resolved in outer FROM clause # the outer having column is not referenced in GROUP BY which results in an error ---error 1054 +--error ER_BAD_FIELD_ERROR select t1.col1 from t1 where t1.col2 in (select t2.col2 from t2 @@ -302,7 +298,7 @@ having col_t1 > 10 and # correlated subqueries - inner having column 't1.col2' resolves to # the outer FROM clause, which cannot be used because the outer query # is grouped ---error 1054 +--error ER_BAD_FIELD_ERROR select sum(col1) from t1 group by col_t1 having col_t1 in (select sum(t2.col1) from t2 @@ -318,11 +314,11 @@ having col_t1 in (select sum(t2.col1) from t2 # # queries with joins and ambiguous column names # ---error 1052 +--error ER_NON_UNIQ_ERROR select t1.col1, t2.col1 from t1, t2 where t1.col1 = t2.col1 group by t1.col1, t2.col1 having col1 = 2; ---error 1052 +--error ER_NON_UNIQ_ERROR select t1.col1*10+t2.col1 from t1,t2 where t1.col1=t2.col1 group by t1.col1, t2.col1 having col1 = 2; @@ -352,7 +348,7 @@ select count(s1) from t1 group by s1 having count(1+1)=2; select count(s1) from t1 group by s1 having s1*0=0; --- error 1052 +-- error ER_NON_UNIQ_ERROR select * from t1 a, t1 b group by a.s1 having s1 is null; # ANSI requires: 0 rows # MySQL returns: @@ -912,7 +908,9 @@ alter table t1 add column b int default (rand()+1+3); select default(b) AS h FROM t1 HAVING h > "2"; drop table t1; +--echo # --echo # End of 10.3 tests +--echo # --echo # --echo # MDEV-18681: AND formula in HAVING with several occurances @@ -930,7 +928,6 @@ HAVING t.f != 112 AND t.f = 'x' AND t.f != 'a'; DROP TABLE t1,t2; - --echo # --echo # MDEV-20200: AddressSanitizer: use-after-poison in --echo # Item_direct_view_ref::get_null_ref_table @@ -943,4 +940,6 @@ SELECT * FROM t HAVING f = 'foo'; # Cleanup DROP TABLE t; +--echo # --echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result index f1e07ce09fe..ae22329f62a 100644 --- a/mysql-test/main/subselect_innodb.result +++ b/mysql-test/main/subselect_innodb.result @@ -1,6 +1,5 @@ set @subselect_innodb_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; -drop table if exists t1,t2,t3; CREATE TABLE t1 ( FOLDERID VARCHAR(32)BINARY NOT NULL @@ -154,6 +153,9 @@ EXECUTE my_stmt; b count(*) deallocate prepare my_stmt; drop table t1,t2; +# +# End of 4.1 tests +# CREATE TABLE t1 ( school_name varchar(45) NOT NULL, country varchar(45) NOT NULL, @@ -287,7 +289,6 @@ LIMIT 10; col_time_key col_datetime_key DROP TABLE t1; DROP TABLE t2; -# End of Bug #58756 # # Bug#60085 crash in Item::save_in_field() with time data type # @@ -356,7 +357,9 @@ LIMIT 1; maxkey NULL DROP TABLE t1,t2; -End of 5.1 tests +# +# End of 5.1 tests +# # # lp:827416 Crash in select_describe() on EXPLAIN with DISTINCT in nested subqueries # @@ -663,4 +666,6 @@ a b execute stmt; a b drop table t1,t2; +# # End of 10.4 tests +# diff --git a/mysql-test/main/subselect_innodb.test b/mysql-test/main/subselect_innodb.test index 37f8f40200e..e767891c8db 100644 --- a/mysql-test/main/subselect_innodb.test +++ b/mysql-test/main/subselect_innodb.test @@ -4,9 +4,6 @@ # settings are not relevant. set @subselect_innodb_tmp=@@optimizer_switch; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; ---disable_warnings -drop table if exists t1,t2,t3; ---enable_warnings # # key field overflow test @@ -164,7 +161,9 @@ EXECUTE my_stmt; deallocate prepare my_stmt; drop table t1,t2; -# End of 4.1 tests +--echo # +--echo # End of 4.1 tests +--echo # CREATE TABLE t1 ( school_name varchar(45) NOT NULL, @@ -289,8 +288,6 @@ LIMIT 10; DROP TABLE t1; DROP TABLE t2; ---echo # End of Bug #58756 - --echo # --echo # Bug#60085 crash in Item::save_in_field() with time data type --echo # @@ -354,7 +351,9 @@ eval $query; DROP TABLE t1,t2; ---echo End of 5.1 tests +--echo # +--echo # End of 5.1 tests +--echo # --echo # --echo # lp:827416 Crash in select_describe() on EXPLAIN with DISTINCT in nested subqueries @@ -507,8 +506,6 @@ drop table t1,t2; --echo # for a subquery from the expression used in ref access --echo # ---source include/have_innodb.inc - CREATE TABLE t1 (i1 INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2); @@ -661,4 +658,6 @@ execute stmt; drop table t1,t2; +--echo # --echo # End of 10.4 tests +--echo # -- cgit v1.2.1 From 8c34eab9688b4face54f15f89f5d62bdfd93b8a7 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 28 Apr 2022 17:40:49 +0200 Subject: MDEV-28094 Window function in expression in ORDER BY call item->split_sum_func() in setup_order() just as it's done in setup_fields() --- mysql-test/main/win_sum.result | 12 ++++++++++++ mysql-test/main/win_sum.test | 13 +++++++++++++ 2 files changed, 25 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/win_sum.result b/mysql-test/main/win_sum.result index 66a48fe8293..71d87bd6eca 100644 --- a/mysql-test/main/win_sum.result +++ b/mysql-test/main/win_sum.result @@ -93,3 +93,15 @@ pk a c sum(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FO 126 6 NULL NULL 127 6 NULL NULL drop table t1; +# +# End of 10.2 tests +# +# +# MDEV-28094 Window function in expression in ORDER BY +# +SELECT EXISTS (SELECT 1 ORDER BY 1+sum(2) OVER ()); +EXISTS (SELECT 1 ORDER BY 1+sum(2) OVER ()) +1 +# +# End of 10.4 tests +# diff --git a/mysql-test/main/win_sum.test b/mysql-test/main/win_sum.test index aa4965bfd5a..640576acc53 100644 --- a/mysql-test/main/win_sum.test +++ b/mysql-test/main/win_sum.test @@ -45,3 +45,16 @@ select pk, a, c, sum(c) over (partition by a order by pk ROWS BETWEEN 1 PRECEDIN from t1; drop table t1; + +--echo # +--echo # End of 10.2 tests +--echo # + +--echo # +--echo # MDEV-28094 Window function in expression in ORDER BY +--echo # +SELECT EXISTS (SELECT 1 ORDER BY 1+sum(2) OVER ()); + +--echo # +--echo # End of 10.4 tests +--echo # -- cgit v1.2.1 From 0beed9b5e933f0ff79b3bb346524f7a451d14e38 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 29 Apr 2022 14:50:47 +0200 Subject: MDEV-28097 use-after-free when WHERE has subquery with an outer reference in HAVING when resolving WHERE and ON clauses, do not look in SELECT list/aliases. --- mysql-test/main/having.result | 6 +----- mysql-test/main/having.test | 3 ++- mysql-test/main/subselect_innodb.result | 12 ++++++++++++ mysql-test/main/subselect_innodb.test | 8 ++++++++ 4 files changed, 23 insertions(+), 6 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/having.result b/mysql-test/main/having.result index 8800402dc35..b4ca607ec84 100644 --- a/mysql-test/main/having.result +++ b/mysql-test/main/having.result @@ -279,11 +279,7 @@ select t1.col1 as tmp_col from t1 where t1.col2 in (select t2.col2 from t2 group by t2.col1, t2.col2 having tmp_col <= 10); -tmp_col -10 -10 -10 -10 +ERROR 42S22: Unknown column 'tmp_col' in 'having clause' select t1.col1 from t1 where t1.col2 in (select t2.col2 from t2 diff --git a/mysql-test/main/having.test b/mysql-test/main/having.test index b3b128684a3..3f4e8a8e710 100644 --- a/mysql-test/main/having.test +++ b/mysql-test/main/having.test @@ -249,7 +249,8 @@ where t1.col2 in group by t2.col1, t2.col2 having t1.col1 <= 10); # the having column is resolved in the SELECT clause of the outer query - -# error in ANSI, works with MySQL extension +# error in ANSI +--error ER_BAD_FIELD_ERROR select t1.col1 as tmp_col from t1 where t1.col2 in (select t2.col2 from t2 diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result index ae22329f62a..467ed218198 100644 --- a/mysql-test/main/subselect_innodb.result +++ b/mysql-test/main/subselect_innodb.result @@ -667,5 +667,17 @@ execute stmt; a b drop table t1,t2; # +# MDEV-28097 use-after-free when WHERE has subquery with an outer reference in HAVING +# +create table t1 (a text(60) not null) engine=innodb; +insert into t1 values ('1'),('0'); +select distinct a from t1 where '' in (select 'x' like a having a like a); +a +1 +0 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: '' +drop table t1; +# # End of 10.4 tests # diff --git a/mysql-test/main/subselect_innodb.test b/mysql-test/main/subselect_innodb.test index e767891c8db..8ff3a5acf7d 100644 --- a/mysql-test/main/subselect_innodb.test +++ b/mysql-test/main/subselect_innodb.test @@ -658,6 +658,14 @@ execute stmt; drop table t1,t2; +--echo # +--echo # MDEV-28097 use-after-free when WHERE has subquery with an outer reference in HAVING +--echo # +create table t1 (a text(60) not null) engine=innodb; +insert into t1 values ('1'),('0'); +select distinct a from t1 where '' in (select 'x' like a having a like a); +drop table t1; + --echo # --echo # End of 10.4 tests --echo # -- cgit v1.2.1 From c8228369f6dad5cfd17c5a9d9ea1c5c3ecd30fe7 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 29 Apr 2022 10:37:01 -0700 Subject: MDEV-28080 Crash when using HAVING with NOT EXIST predicate in an equality MDEV-28082 Crash when using HAVING with IS NULL predicate in an equality These bugs have been fixed by the patch for MDEV-26402. Only test cases are added. --- mysql-test/main/having_cond_pushdown.result | 31 +++++++++++++++++++++++++++ mysql-test/main/having_cond_pushdown.test | 33 +++++++++++++++++++++++++++++ 2 files changed, 64 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result index 9a9ed2b9213..ad8befb29d5 100644 --- a/mysql-test/main/having_cond_pushdown.result +++ b/mysql-test/main/having_cond_pushdown.result @@ -4939,3 +4939,34 @@ i Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'x' DROP TABLE t1; +# +# MDEV-28080: HAVING with NOT EXIST predicate in an equality +# (fixed by the patch for MDEV-26402) +# +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); +INSERT INTO t1 VALUES (0), (1), (1), (0); +INSERT INTO t2 VALUES (3), (7); +SELECT a FROM t1 +GROUP BY a HAVING a= (NOT EXISTS (SELECT b FROM t2 WHERE b = 1)); +a +1 +SELECT a FROM t1 +GROUP BY a HAVING a= (NOT EXISTS (SELECT b FROM t2 WHERE b = 7)); +a +0 +DROP TABLE t1, t2; +# +# MDEV-28082: HAVING with IS NULL predicate in an equality +# (fixed by the patch for MDEV-26402) +# +CREATE TABLE t1 (a int, b int NOT NULL) ; +INSERT INTO t1 VALUES (1,10), (0,11), (0,11), (1,10); +SELECT a,b FROM t1 GROUP BY a HAVING a = (b IS NULL); +a b +0 11 +SELECT a,b FROM t1 GROUP BY a,b HAVING a = (b IS NULL); +a b +0 11 +DROP TABLE t1; +End of 10.4 tests diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test index 270eac2a46f..aee5a770fa9 100644 --- a/mysql-test/main/having_cond_pushdown.test +++ b/mysql-test/main/having_cond_pushdown.test @@ -1450,3 +1450,36 @@ SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL); SELECT * FROM t1 GROUP BY i HAVING i IN ( i IS NULL AND 'x' = 0); SELECT * FROM t1 GROUP BY i HAVING i='1' IN ( i IS NULL AND 'x' = 0); DROP TABLE t1; + +--echo # +--echo # MDEV-28080: HAVING with NOT EXIST predicate in an equality +--echo # (fixed by the patch for MDEV-26402) +--echo # + +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); +INSERT INTO t1 VALUES (0), (1), (1), (0); +INSERT INTO t2 VALUES (3), (7); + +SELECT a FROM t1 + GROUP BY a HAVING a= (NOT EXISTS (SELECT b FROM t2 WHERE b = 1)); +SELECT a FROM t1 + GROUP BY a HAVING a= (NOT EXISTS (SELECT b FROM t2 WHERE b = 7)); + +DROP TABLE t1, t2; + +--echo # +--echo # MDEV-28082: HAVING with IS NULL predicate in an equality +--echo # (fixed by the patch for MDEV-26402) +--echo # + +CREATE TABLE t1 (a int, b int NOT NULL) ; +INSERT INTO t1 VALUES (1,10), (0,11), (0,11), (1,10); + +SELECT a,b FROM t1 GROUP BY a HAVING a = (b IS NULL); + +SELECT a,b FROM t1 GROUP BY a,b HAVING a = (b IS NULL); + +DROP TABLE t1; + +--echo End of 10.4 tests -- cgit v1.2.1 From 6e7c6fcfd1f1ac131c423c1ba084d61abad10e8b Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Sat, 30 Apr 2022 13:25:34 -0700 Subject: MDEV-28448 Assertion failure for SELECT with subquery using ON expression This patch corrects the fix for MDEV-26412. Note that when parsing an ON expression the pointer to the current select is always in select_stack[select_stack_top - 1]. So the pointer to the outer select (if any) is in select_stack[select_stack_top - 2]. The query manifesting this bug is added to the test case of MDEV-26412. --- mysql-test/main/insert.result | 4 ++++ mysql-test/main/insert.test | 5 +++++ 2 files changed, 9 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/insert.result b/mysql-test/main/insert.result index 1062ddd6a94..af7dcbedd1f 100644 --- a/mysql-test/main/insert.result +++ b/mysql-test/main/insert.result @@ -766,4 +766,8 @@ replace t4 select * from t1 left join t2 on (select t1.i from t3); ERROR 42S22: Unknown column 't1.i' in 'field list' drop table t1,t2,t3,t4; +create table t (a int); +select 1 in (select count(*) from t t1 join (t t2 join t t3 on (t1.a != 0))); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +drop table t; # End of 10.4 tests diff --git a/mysql-test/main/insert.test b/mysql-test/main/insert.test index 9fd0933cc7c..27d44918bbb 100644 --- a/mysql-test/main/insert.test +++ b/mysql-test/main/insert.test @@ -633,4 +633,9 @@ replace t4 drop table t1,t2,t3,t4; +create table t (a int); +--error ER_BAD_FIELD_ERROR +select 1 in (select count(*) from t t1 join (t t2 join t t3 on (t1.a != 0))); +drop table t; + --echo # End of 10.4 tests -- cgit v1.2.1 From 531935992adbf2bea4e772583d93a6c4b5bd78fd Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Mon, 2 May 2022 12:22:57 +0200 Subject: test fixes for FreeBSD * FreeBSD returns errno 31 (EMLINK, Too many links), not 40 (ELOOP, Too many levels of symbolic links) * (`mysqlbinlog|mysql`) was just crazy, why did it ever work? * socket_ipv6.inc check (that checked whether ipv6 is supported) only worked correctly when ipv6 was supported * perfschema.socket_summary_by_instance was changing global variables and then skip-ing the test (because on missing ipv6) --- mysql-test/main/repair_symlink-5543.result | 2 +- mysql-test/main/repair_symlink-5543.test | 6 +++--- mysql-test/main/temp_table_symlink.test | 1 + 3 files changed, 5 insertions(+), 4 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/repair_symlink-5543.result b/mysql-test/main/repair_symlink-5543.result index 2024c9f5684..4f816d407b9 100644 --- a/mysql-test/main/repair_symlink-5543.result +++ b/mysql-test/main/repair_symlink-5543.result @@ -1,6 +1,6 @@ create table t1 (a int) engine=myisam data directory='MYSQL_TMP_DIR'; insert t1 values (1); -# Some systems fail with errcode 40, or 90 (MIPS) when doing openat, +# Some systems fail with errcode 31 (FreeBSD), 40, or 90 (MIPS) when doing openat, # while others don't have openat and fail with errcode 20. repair table t1; Table Op Msg_type Msg_text diff --git a/mysql-test/main/repair_symlink-5543.test b/mysql-test/main/repair_symlink-5543.test index ac7bb497f24..747e3f6ca1c 100644 --- a/mysql-test/main/repair_symlink-5543.test +++ b/mysql-test/main/repair_symlink-5543.test @@ -9,9 +9,9 @@ eval create table t1 (a int) engine=myisam data directory='$MYSQL_TMP_DIR'; insert t1 values (1); --system ln -s $MYSQL_TMP_DIR/foobar5543 $MYSQL_TMP_DIR/t1.TMD ---echo # Some systems fail with errcode 40, or 90 (MIPS) when doing openat, +--echo # Some systems fail with errcode 31 (FreeBSD), 40, or 90 (MIPS) when doing openat, --echo # while others don't have openat and fail with errcode 20. ---replace_regex / '.*\/t1/ 'MYSQL_TMP_DIR\/t1/ /[49]0/20/ /".*"/""/ +--replace_regex / '.*\/t1/ 'MYSQL_TMP_DIR\/t1/ /[49]0|31/20/ /".*"/""/ repair table t1; drop table t1; @@ -19,7 +19,7 @@ drop table t1; eval create table t2 (a int) engine=aria data directory='$MYSQL_TMP_DIR'; insert t2 values (1); --system ln -s $MYSQL_TMP_DIR/foobar5543 $MYSQL_TMP_DIR/t2.TMD ---replace_regex / '.*\/t2/ 'MYSQL_TMP_DIR\/t2/ /[49]0/20/ /".*"/""/ +--replace_regex / '.*\/t2/ 'MYSQL_TMP_DIR\/t2/ /[49]0|31/20/ /".*"/""/ repair table t2; drop table t2; diff --git a/mysql-test/main/temp_table_symlink.test b/mysql-test/main/temp_table_symlink.test index 9297b472805..5bd8c5ac5bc 100644 --- a/mysql-test/main/temp_table_symlink.test +++ b/mysql-test/main/temp_table_symlink.test @@ -14,6 +14,7 @@ for (<#sql*.MYI>) { symlink "$ENV{datadir}/test/d1.MYI", sprintf "#sql$1_$2%x.MYI", hex($3)+1; symlink "$ENV{datadir}/test/d1.MYI", sprintf "#sql$1_$2%x.MAI", hex($3)+1; symlink "$ENV{datadir}/test/d1.MYI", sprintf "#sql$1_$2%x.MAI", hex($3)+2; + symlink "$ENV{datadir}/test/d1.MYI", sprintf "#sql$1_$2%x.MAI", hex($3)+3; symlink "$ENV{datadir}/test/d1.MYI", "#sql_$1_0.MAI"; } EOF -- cgit v1.2.1 From 9fe3bc2aa881118def3987358935e77c39b9fdb8 Mon Sep 17 00:00:00 2001 From: Hartmut Holzgraefe Date: Fri, 29 Apr 2022 10:10:02 +0200 Subject: MDEV-27816 Set sql_mode before DROP IF EXISTS already Previously the correct SQL mode for a stored routine or package was only set before doing the CREATE part, this worked out for PROCEDUREs and FUNCTIONs, but with ORACLE mode specific PACKAGEs the DROP also only works in ORACLE mode. Moving the setting of the sql_mode a few lines up to happen right before the DROP statement is writen fixes this. --- mysql-test/main/ddl_i18n_koi8r.result | 16 ++++----- mysql-test/main/ddl_i18n_utf8.result | 16 ++++----- mysql-test/main/mysqldump-compat-102.result | 4 +-- mysql-test/main/mysqldump-nl.result | 4 +-- mysql-test/main/mysqldump.result | 50 +++++++++++++++++++++-------- mysql-test/main/mysqldump.test | 31 ++++++++++++++++++ 6 files changed, 87 insertions(+), 34 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/ddl_i18n_koi8r.result b/mysql-test/main/ddl_i18n_koi8r.result index 612a4a4cbff..66c85f12034 100644 --- a/mysql-test/main/ddl_i18n_koi8r.result +++ b/mysql-test/main/ddl_i18n_koi8r.result @@ -723,6 +723,8 @@ utf8_general_ci utf8_general_ci CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqltest1` /*!40100 DEFAULT CHARACTER SET cp866 */; USE `mysqltest1`; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; ALTER DATABASE `mysqltest1` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -730,8 +732,6 @@ ALTER DATABASE `mysqltest1` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET character_set_client = koi8r */ ; /*!50003 SET character_set_results = koi8r */ ; /*!50003 SET collation_connection = koi8r_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`( INOUT ÐÁÒÁÍ1 CHAR(10), @@ -757,6 +757,8 @@ DELIMITER ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; ALTER DATABASE `mysqltest1` CHARACTER SET cp866 COLLATE cp866_general_ci ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; ALTER DATABASE `mysqltest1` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -764,8 +766,6 @@ ALTER DATABASE `mysqltest1` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET character_set_client = koi8r */ ; /*!50003 SET character_set_results = koi8r */ ; /*!50003 SET collation_connection = koi8r_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `p2`( INOUT ÐÁÒÁÍ1 CHAR(10) CHARACTER SET utf8, @@ -799,6 +799,8 @@ ALTER DATABASE `mysqltest1` CHARACTER SET cp866 COLLATE cp866_general_ci ; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqltest2` /*!40100 DEFAULT CHARACTER SET cp866 */; USE `mysqltest2`; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; ALTER DATABASE `mysqltest2` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -806,8 +808,6 @@ ALTER DATABASE `mysqltest2` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET character_set_client = koi8r */ ; /*!50003 SET character_set_results = koi8r */ ; /*!50003 SET collation_connection = koi8r_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `p3`( INOUT ÐÁÒÁÍ1 CHAR(10), @@ -833,6 +833,8 @@ DELIMITER ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; ALTER DATABASE `mysqltest2` CHARACTER SET cp866 COLLATE cp866_general_ci ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; ALTER DATABASE `mysqltest2` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -840,8 +842,6 @@ ALTER DATABASE `mysqltest2` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET character_set_client = koi8r */ ; /*!50003 SET character_set_results = koi8r */ ; /*!50003 SET collation_connection = koi8r_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `p4`( INOUT ÐÁÒÁÍ1 CHAR(10) CHARACTER SET utf8, diff --git a/mysql-test/main/ddl_i18n_utf8.result b/mysql-test/main/ddl_i18n_utf8.result index 2be5413f182..78ef487cdaf 100644 --- a/mysql-test/main/ddl_i18n_utf8.result +++ b/mysql-test/main/ddl_i18n_utf8.result @@ -723,6 +723,8 @@ utf8_general_ci utf8_general_ci CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqltest1` /*!40100 DEFAULT CHARACTER SET cp866 */; USE `mysqltest1`; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; ALTER DATABASE `mysqltest1` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -730,8 +732,6 @@ ALTER DATABASE `mysqltest1` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`( INOUT парам1 CHAR(10), @@ -757,6 +757,8 @@ DELIMITER ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; ALTER DATABASE `mysqltest1` CHARACTER SET cp866 COLLATE cp866_general_ci ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; ALTER DATABASE `mysqltest1` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -764,8 +766,6 @@ ALTER DATABASE `mysqltest1` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `p2`( INOUT парам1 CHAR(10) CHARACTER SET utf8, @@ -799,6 +799,8 @@ ALTER DATABASE `mysqltest1` CHARACTER SET cp866 COLLATE cp866_general_ci ; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysqltest2` /*!40100 DEFAULT CHARACTER SET cp866 */; USE `mysqltest2`; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; ALTER DATABASE `mysqltest2` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -806,8 +808,6 @@ ALTER DATABASE `mysqltest2` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `p3`( INOUT парам1 CHAR(10), @@ -833,6 +833,8 @@ DELIMITER ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; ALTER DATABASE `mysqltest2` CHARACTER SET cp866 COLLATE cp866_general_ci ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; ALTER DATABASE `mysqltest2` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -840,8 +842,6 @@ ALTER DATABASE `mysqltest2` CHARACTER SET utf8 COLLATE utf8_unicode_ci ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `p4`( INOUT парам1 CHAR(10) CHARACTER SET utf8, diff --git a/mysql-test/main/mysqldump-compat-102.result b/mysql-test/main/mysqldump-compat-102.result index b58c33927cf..1789f74f034 100644 --- a/mysql-test/main/mysqldump-compat-102.result +++ b/mysql-test/main/mysqldump-compat-102.result @@ -77,6 +77,8 @@ $$ -- -- Dumping routines for database 'db1_mdev17429' -- +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT' */ ; /*!50003 DROP PROCEDURE IF EXISTS `p1` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -84,8 +86,6 @@ $$ /*!50003 SET character_set_client = latin1 */ ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = 'PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT' */ ; DELIMITER ;; CREATE DEFINER="root"@"localhost" PROCEDURE "p1"(a INT) AS BEGIN diff --git a/mysql-test/main/mysqldump-nl.result b/mysql-test/main/mysqldump-nl.result index d2d0e09546b..89fb3144867 100644 --- a/mysql-test/main/mysqldump-nl.result +++ b/mysql-test/main/mysqldump-nl.result @@ -64,14 +64,14 @@ SET character_set_client = @saved_cs_client; -- Dumping routines for database 'mysqltest1 -- 1tsetlqsym' -- +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = latin1 */ ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `sp`() select * from `v1 diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result index 9f1432f8f70..b5ab596370e 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -2777,6 +2777,8 @@ LOCK TABLES `t1` WRITE; INSERT INTO `t1` VALUES (1),(2),(3),(4),(5); /*!40000 ALTER TABLE `t1` ENABLE KEYS */; UNLOCK TABLES; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP FUNCTION IF EXISTS `bug9056_func1` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -2784,8 +2786,6 @@ UNLOCK TABLES; /*!50003 SET character_set_client = latin1 */ ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` FUNCTION `bug9056_func1`(a INT, b INT) RETURNS int(11) RETURN a+b ;; @@ -2794,6 +2794,8 @@ DELIMITER ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP FUNCTION IF EXISTS `bug9056_func2` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -2801,8 +2803,6 @@ DELIMITER ; /*!50003 SET character_set_client = latin1 */ ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` FUNCTION `bug9056_func2`(f1 char binary) RETURNS char(1) CHARSET latin1 begin @@ -2814,6 +2814,8 @@ DELIMITER ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI' */ ; /*!50003 DROP PROCEDURE IF EXISTS `a'b` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -2821,8 +2823,6 @@ DELIMITER ; /*!50003 SET character_set_client = latin1 */ ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI' */ ; DELIMITER ;; CREATE DEFINER="root"@"localhost" PROCEDURE "a'b"() select 1 ;; @@ -2831,6 +2831,8 @@ DELIMITER ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `bug9056_proc1` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -2838,8 +2840,6 @@ DELIMITER ; /*!50003 SET character_set_client = latin1 */ ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `bug9056_proc1`(IN a INT, IN b INT, OUT c INT) BEGIN SELECT a+b INTO c; end ;; @@ -2848,6 +2848,8 @@ DELIMITER ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 DROP PROCEDURE IF EXISTS `bug9056_proc2` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -2855,8 +2857,6 @@ DELIMITER ; /*!50003 SET character_set_client = latin1 */ ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `bug9056_proc2`(OUT a INT) BEGIN @@ -3852,14 +3852,14 @@ create procedure mysqldump_test_db.sp1() select 'hello'; -- insufficient privileges to SHOW CREATE PROCEDURE `sp1` -- does user2 have permissions on mysql.proc? +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = latin1 */ ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`user1`@`%` PROCEDURE `sp1`() select 'hello' ;; @@ -5427,6 +5427,8 @@ CREATE DATABASE `a\"'``b`; USE `a\"'``b`; CREATE PROCEDURE p1() BEGIN END; ALTER DATABASE `a\"'``b` COLLATE utf8_general_ci; +/*!50003 SET @saved_sql_mode = @@sql_mode */ ; +/*!50003 SET sql_mode = '' */ ; ALTER DATABASE `a\"'``b` CHARACTER SET latin1 COLLATE latin1_swedish_ci ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; @@ -5434,8 +5436,6 @@ ALTER DATABASE `a\"'``b` CHARACTER SET latin1 COLLATE latin1_swedish_ci ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; -/*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() BEGIN END ;; @@ -6328,4 +6328,26 @@ SETVAL(`seq_t_i`, 1, 0) 1 DROP DATABASE IF EXISTS test1; DROP DATABASE IF EXISTS test2; +# +# MDEV-27186 Server fails to load a dump, taken on the same version +# Oracle mode with packages +# +CREATE DATABASE test1; +CREATE DATABASE test2; +USE test1; +SET @save_sql_mode=@@sql_mode; +SET sql_mode=ORACLE; +CREATE OR REPLACE PACKAGE pkg AS +END; +$$ +# Dump database 1 +# Restore from database 1 to database 2 +use test2; +SHOW CREATE PACKAGE pkg; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT CREATE DEFINER="root"@"localhost" PACKAGE "pkg" AS +END utf8 utf8_general_ci latin1_swedish_ci +DROP DATABASE test1; +DROP DATABASE test2; +SET sql_mode=@save_sql_mode; # End of 10.3 tests diff --git a/mysql-test/main/mysqldump.test b/mysql-test/main/mysqldump.test index 3e88ff3bc39..aeae62664d9 100644 --- a/mysql-test/main/mysqldump.test +++ b/mysql-test/main/mysqldump.test @@ -2885,4 +2885,35 @@ INSERT INTO t VALUES (1,1),(2,2),(3,3),(4,4); DROP DATABASE IF EXISTS test1; DROP DATABASE IF EXISTS test2; +--echo # +--echo # MDEV-27186 Server fails to load a dump, taken on the same version +--echo # Oracle mode with packages +--echo # + +CREATE DATABASE test1; +CREATE DATABASE test2; +USE test1; +SET @save_sql_mode=@@sql_mode; +SET sql_mode=ORACLE; + +DELIMITER $$; +CREATE OR REPLACE PACKAGE pkg AS +END; +$$ + +DELIMITER ;$$ + +--echo # Dump database 1 +--exec $MYSQL_DUMP --routines test1 > $MYSQLTEST_VARDIR/tmp/dumptest1.sql +--echo # Restore from database 1 to database 2 + +--exec $MYSQL test2 < $MYSQLTEST_VARDIR/tmp/dumptest1.sql +use test2; +SHOW CREATE PACKAGE pkg; + +DROP DATABASE test1; +DROP DATABASE test2; +SET sql_mode=@save_sql_mode; +--remove_file $MYSQLTEST_VARDIR/tmp/dumptest1.sql + --echo # End of 10.3 tests -- cgit v1.2.1 From 221ced92aa02d42d26a5a4945a0e90ba5dcbcbfa Mon Sep 17 00:00:00 2001 From: Daniel Black Date: Fri, 6 May 2022 17:44:33 +1000 Subject: MDEV-4875 Can't restore a mysqldump if --add-drop-database meets general_log or slow query log when the log_output=TABLE. When this happens, we temporary disable by changing log_output until we've created the general_log and slow_log tables again. Move in xml mode until after the transaction_registry. General_log and slow_log tables where moved to be first to be dumped so that the disabling of the general/slow queries is minimal. --- mysql-test/main/mysqldump.result | 266 ++++++++++++++++++++++++++------------- mysql-test/main/mysqldump.test | 60 +++++++++ 2 files changed, 240 insertions(+), 86 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result index b5ab596370e..1ddda58e368 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -5738,34 +5738,6 @@ DROP TABLE t1; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -DROP TABLE IF EXISTS `innodb_index_stats`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `innodb_index_stats` ( - `database_name` varchar(64) COLLATE utf8_bin NOT NULL, - `table_name` varchar(199) COLLATE utf8_bin NOT NULL, - `index_name` varchar(64) COLLATE utf8_bin NOT NULL, - `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), - `stat_name` varchar(64) COLLATE utf8_bin NOT NULL, - `stat_value` bigint(20) unsigned NOT NULL, - `sample_size` bigint(20) unsigned DEFAULT NULL, - `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL, - PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; -/*!40101 SET character_set_client = @saved_cs_client */; -DROP TABLE IF EXISTS `innodb_table_stats`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `innodb_table_stats` ( - `database_name` varchar(64) COLLATE utf8_bin NOT NULL, - `table_name` varchar(199) COLLATE utf8_bin NOT NULL, - `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), - `n_rows` bigint(20) unsigned NOT NULL, - `clustered_index_size` bigint(20) unsigned NOT NULL, - `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL, - PRIMARY KEY (`database_name`,`table_name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; -/*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE IF NOT EXISTS `general_log` ( @@ -5795,6 +5767,34 @@ CREATE TABLE IF NOT EXISTS `slow_log` ( `rows_affected` int(11) NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; /*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `innodb_index_stats`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `innodb_index_stats` ( + `database_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(199) COLLATE utf8_bin NOT NULL, + `index_name` varchar(64) COLLATE utf8_bin NOT NULL, + `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `stat_name` varchar(64) COLLATE utf8_bin NOT NULL, + `stat_value` bigint(20) unsigned NOT NULL, + `sample_size` bigint(20) unsigned DEFAULT NULL, + `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL, + PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `innodb_table_stats`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `innodb_table_stats` ( + `database_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(199) COLLATE utf8_bin NOT NULL, + `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `n_rows` bigint(20) unsigned NOT NULL, + `clustered_index_size` bigint(20) unsigned NOT NULL, + `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL, + PRIMARY KEY (`database_name`,`table_name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; +/*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE IF NOT EXISTS `transaction_registry` ( @@ -5833,6 +5833,35 @@ CREATE TABLE IF NOT EXISTS `transaction_registry` ( /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `general_log` ( + `event_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), + `user_host` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, + `server_id` int(10) unsigned NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL +) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `slow_log` ( + `start_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), + `user_host` mediumtext NOT NULL, + `query_time` time(6) NOT NULL, + `lock_time` time(6) NOT NULL, + `rows_sent` int(11) NOT NULL, + `rows_examined` int(11) NOT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(10) unsigned NOT NULL, + `sql_text` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, + `rows_affected` int(11) NOT NULL +) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; +/*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `innodb_index_stats`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -5873,35 +5902,6 @@ LOCK TABLES `innodb_table_stats` WRITE; UNLOCK TABLES; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; -CREATE TABLE IF NOT EXISTS `general_log` ( - `event_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), - `user_host` mediumtext NOT NULL, - `thread_id` bigint(21) unsigned NOT NULL, - `server_id` int(10) unsigned NOT NULL, - `command_type` varchar(64) NOT NULL, - `argument` mediumtext NOT NULL -) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'; -/*!40101 SET character_set_client = @saved_cs_client */; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE IF NOT EXISTS `slow_log` ( - `start_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), - `user_host` mediumtext NOT NULL, - `query_time` time(6) NOT NULL, - `lock_time` time(6) NOT NULL, - `rows_sent` int(11) NOT NULL, - `rows_examined` int(11) NOT NULL, - `db` varchar(512) NOT NULL, - `last_insert_id` int(11) NOT NULL, - `insert_id` int(11) NOT NULL, - `server_id` int(10) unsigned NOT NULL, - `sql_text` mediumtext NOT NULL, - `thread_id` bigint(21) unsigned NOT NULL, - `rows_affected` int(11) NOT NULL -) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; -/*!40101 SET character_set_client = @saved_cs_client */; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; CREATE TABLE IF NOT EXISTS `transaction_registry` ( `transaction_id` bigint(20) unsigned NOT NULL, `commit_id` bigint(20) unsigned NOT NULL, @@ -5938,6 +5938,35 @@ CREATE TABLE IF NOT EXISTS `transaction_registry` ( /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `general_log` ( + `event_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), + `user_host` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, + `server_id` int(10) unsigned NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL +) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE IF NOT EXISTS `slow_log` ( + `start_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), + `user_host` mediumtext NOT NULL, + `query_time` time(6) NOT NULL, + `lock_time` time(6) NOT NULL, + `rows_sent` int(11) NOT NULL, + `rows_examined` int(11) NOT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(10) unsigned NOT NULL, + `sql_text` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, + `rows_affected` int(11) NOT NULL +) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; +/*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `innodb_index_stats`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -5978,35 +6007,6 @@ LOCK TABLES `innodb_table_stats` WRITE; UNLOCK TABLES; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; -CREATE TABLE IF NOT EXISTS `general_log` ( - `event_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), - `user_host` mediumtext NOT NULL, - `thread_id` bigint(21) unsigned NOT NULL, - `server_id` int(10) unsigned NOT NULL, - `command_type` varchar(64) NOT NULL, - `argument` mediumtext NOT NULL -) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'; -/*!40101 SET character_set_client = @saved_cs_client */; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE IF NOT EXISTS `slow_log` ( - `start_time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6), - `user_host` mediumtext NOT NULL, - `query_time` time(6) NOT NULL, - `lock_time` time(6) NOT NULL, - `rows_sent` int(11) NOT NULL, - `rows_examined` int(11) NOT NULL, - `db` varchar(512) NOT NULL, - `last_insert_id` int(11) NOT NULL, - `insert_id` int(11) NOT NULL, - `server_id` int(10) unsigned NOT NULL, - `sql_text` mediumtext NOT NULL, - `thread_id` bigint(21) unsigned NOT NULL, - `rows_affected` int(11) NOT NULL -) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; -/*!40101 SET character_set_client = @saved_cs_client */; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; CREATE TABLE IF NOT EXISTS `transaction_registry` ( `transaction_id` bigint(20) unsigned NOT NULL, `commit_id` bigint(20) unsigned NOT NULL, @@ -6350,4 +6350,98 @@ END utf8 utf8_general_ci latin1_swedish_ci DROP DATABASE test1; DROP DATABASE test2; SET sql_mode=@save_sql_mode; +# +# MDEV-4875 Can't restore a mysqldump if --add-drop-database meets general_log +# +CREATE DATABASE test1; +# Dump mysql database +DROP VIEW IF EXISTS mysql.user; +DROP TABLE IF EXISTS mysql.global_priv; +DROP TABLE IF EXISTS mysql.user; +DROP TABLE IF EXISTS mysql.time_zone_transition_type; +DROP TABLE IF EXISTS mysql.time_zone_transition; +DROP TABLE IF EXISTS mysql.time_zone_name; +DROP TABLE IF EXISTS mysql.time_zone_leap_second; +DROP TABLE IF EXISTS mysql.time_zone; +DROP TABLE IF EXISTS mysql.tables_priv; +DROP TABLE IF EXISTS mysql.table_stats; +DROP TABLE IF EXISTS mysql.servers; +DROP TABLE IF EXISTS mysql.roles_mapping; +DROP TABLE IF EXISTS mysql.proxies_priv; +DROP TABLE IF EXISTS mysql.procs_priv; +DROP TABLE IF EXISTS mysql.proc; +DROP TABLE IF EXISTS mysql.plugin; +DROP TABLE IF EXISTS mysql.innodb_table_stats; +DROP TABLE IF EXISTS mysql.innodb_index_stats; +DROP TABLE IF EXISTS mysql.index_stats; +DROP TABLE IF EXISTS mysql.host; +DROP TABLE IF EXISTS mysql.help_topic; +DROP TABLE IF EXISTS mysql.help_relation; +DROP TABLE IF EXISTS mysql.help_keyword; +DROP TABLE IF EXISTS mysql.help_category; +DROP TABLE IF EXISTS mysql.gtid_slave_pos; +DROP TABLE IF EXISTS mysql.func; +DROP TABLE IF EXISTS mysql.event; +DROP TABLE IF EXISTS mysql.db; +DROP TABLE IF EXISTS mysql.columns_priv; +DROP TABLE IF EXISTS mysql.column_stats; +# Abbreviated contents + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +/*!50106 SET GLOBAL LOG_OUTPUT=@save_log_output*/; + + + + + + + + + + + + + + + + + + +SET @save_general_log=@@GENERAL_LOG; +SET GLOBAL LOG_OUTPUT='TABLE', GLOBAL GENERAL_LOG=1; +# Restore mysql database while general log is active +# No failure at this stage is the object of the test +SELECT @@GLOBAL.LOG_OUTPUT, @@GLOBAL.GENERAL_LOG; +@@GLOBAL.LOG_OUTPUT @@GLOBAL.GENERAL_LOG +TABLE 1 +SET GLOBAL LOG_OUTPUT=DEFAULT, GLOBAL GENERAL_LOG=@save_general_log; +TRUNCATE TABLE mysql.general_log; +DROP DATABASE test1; # End of 10.3 tests diff --git a/mysql-test/main/mysqldump.test b/mysql-test/main/mysqldump.test index aeae62664d9..bf98a44bc34 100644 --- a/mysql-test/main/mysqldump.test +++ b/mysql-test/main/mysqldump.test @@ -2916,4 +2916,64 @@ DROP DATABASE test2; SET sql_mode=@save_sql_mode; --remove_file $MYSQLTEST_VARDIR/tmp/dumptest1.sql +--echo # +--echo # MDEV-4875 Can't restore a mysqldump if --add-drop-database meets general_log +--echo # + +CREATE DATABASE test1; +--echo # Dump mysql database +--exec $MYSQL_DUMP --add-drop-database --databases mysql test1 > $MYSQLTEST_VARDIR/tmp/dumptest1.sql + +--disable_warnings +DROP VIEW IF EXISTS mysql.user; +DROP TABLE IF EXISTS mysql.global_priv; +DROP TABLE IF EXISTS mysql.user; +--enable_warnings +#DROP TABLE IF EXISTS mysql.transaction_registry; +#DROP TABLE IF EXISTS mysql.slow_log; +#DROP TABLE IF EXISTS mysql.general_log; +DROP TABLE IF EXISTS mysql.time_zone_transition_type; +DROP TABLE IF EXISTS mysql.time_zone_transition; +DROP TABLE IF EXISTS mysql.time_zone_name; +DROP TABLE IF EXISTS mysql.time_zone_leap_second; +DROP TABLE IF EXISTS mysql.time_zone; +DROP TABLE IF EXISTS mysql.tables_priv; +DROP TABLE IF EXISTS mysql.table_stats; +DROP TABLE IF EXISTS mysql.servers; +DROP TABLE IF EXISTS mysql.roles_mapping; +DROP TABLE IF EXISTS mysql.proxies_priv; +DROP TABLE IF EXISTS mysql.procs_priv; +DROP TABLE IF EXISTS mysql.proc; +DROP TABLE IF EXISTS mysql.plugin; +DROP TABLE IF EXISTS mysql.innodb_table_stats; +DROP TABLE IF EXISTS mysql.innodb_index_stats; +DROP TABLE IF EXISTS mysql.index_stats; +DROP TABLE IF EXISTS mysql.host; +DROP TABLE IF EXISTS mysql.help_topic; +DROP TABLE IF EXISTS mysql.help_relation; +DROP TABLE IF EXISTS mysql.help_keyword; +DROP TABLE IF EXISTS mysql.help_category; +DROP TABLE IF EXISTS mysql.gtid_slave_pos; +DROP TABLE IF EXISTS mysql.func; +DROP TABLE IF EXISTS mysql.event; +DROP TABLE IF EXISTS mysql.db; +DROP TABLE IF EXISTS mysql.columns_priv; +DROP TABLE IF EXISTS mysql.column_stats; + +--echo # Abbreviated contents +--replace_regex /Create_time="[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}"/Create_time="TIMESTAMP"/ +--exec $MYSQL_DUMP --xml --skip-comments --no-data --add-drop-database --databases mysql test1 + +SET @save_general_log=@@GENERAL_LOG; +SET GLOBAL LOG_OUTPUT='TABLE', GLOBAL GENERAL_LOG=1; + +--echo # Restore mysql database while general log is active +--exec $MYSQL < $MYSQLTEST_VARDIR/tmp/dumptest1.sql +--echo # No failure at this stage is the object of the test +SELECT @@GLOBAL.LOG_OUTPUT, @@GLOBAL.GENERAL_LOG; +SET GLOBAL LOG_OUTPUT=DEFAULT, GLOBAL GENERAL_LOG=@save_general_log; +TRUNCATE TABLE mysql.general_log; +DROP DATABASE test1; +--remove_file $MYSQLTEST_VARDIR/tmp/dumptest1.sql + --echo # End of 10.3 tests -- cgit v1.2.1 From e9a28940c5124aaeb0715718251b8802d2ee2c59 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Sun, 8 May 2022 00:42:43 +0200 Subject: these tests need ipv6 --- mysql-test/main/mysql_client_test_comp.test | 3 ++- mysql-test/main/mysql_client_test_nonblock.test | 3 ++- 2 files changed, 4 insertions(+), 2 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/mysql_client_test_comp.test b/mysql-test/main/mysql_client_test_comp.test index 13a9d4944a4..f8bd80fc48a 100644 --- a/mysql-test/main/mysql_client_test_comp.test +++ b/mysql-test/main/mysql_client_test_comp.test @@ -1,7 +1,8 @@ # run mysql_client_test with performance schema # No need to run this with embedded server --- source include/not_embedded.inc +--source include/not_embedded.inc +--source include/check_ipv6.inc # need to have the dynamic loading turned on for the client plugin tests --source include/have_plugin_auth.inc diff --git a/mysql-test/main/mysql_client_test_nonblock.test b/mysql-test/main/mysql_client_test_nonblock.test index 19489bf9e0e..73e7a6d378d 100644 --- a/mysql-test/main/mysql_client_test_nonblock.test +++ b/mysql-test/main/mysql_client_test_nonblock.test @@ -2,7 +2,8 @@ # This runs the mysql_client_test using the non-blocking API. # The non-blocking API is not supported in the embedded server. --- source include/not_embedded.inc +--source include/not_embedded.inc +--source include/check_ipv6.inc # This test is slow on buildbot. --source include/big_test.inc -- cgit v1.2.1 From a2dd86df9cacb041b3bb1fd6818459b33056a8c3 Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Mon, 9 May 2022 12:16:35 +0200 Subject: cleanup: test --- mysql-test/main/default.result | 21 +++++++++++++++++---- mysql-test/main/default.test | 37 ++++++++++++++++++------------------- 2 files changed, 35 insertions(+), 23 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/default.result b/mysql-test/main/default.result index cbd18cdf9a8..bd5ffba0d46 100644 --- a/mysql-test/main/default.result +++ b/mysql-test/main/default.result @@ -1,5 +1,3 @@ -drop table if exists t1,t2,t3,t4,t5,t6; -drop database if exists mysqltest; set sql_mode=""; CREATE TABLE t1 (a varchar(30) binary NOT NULL DEFAULT ' ', b varchar(1) binary NOT NULL DEFAULT ' ', @@ -3135,7 +3133,9 @@ t3 CREATE TABLE `t3` ( `max(c)` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2, t3; +# # MDEV-11359: Implement IGNORE for bulk operation +# create table t1 (a int primary key default 0, b int default 3); insert into t1 values (1, ignore); insert into t1 values (2, ignore); @@ -3354,6 +3354,9 @@ a b 30 31 drop table t1; set sql_mode=default; +# +# MDEV-10201 Bad results for CREATE TABLE t1 (a INT DEFAULT b, b INT DEFAULT 4) +# create table t1 (a int default b, b int default 4, t text); insert t1 (b, t) values (5, '1 column is omitted'); insert t1 values (default, 5, '2 column gets DEFAULT, keyword'); @@ -3376,8 +3379,14 @@ a b t 5 5 8 reversed, also expression DEFAULT(0)+0 5 5 9 reversed, the value of the DEFAULT(a), that is b drop table t1; +# +# MDEV-10352 Server crashes in Field::set_default on CREATE TABLE +# create table t1 (col1 int default(-(default(col1)))); ERROR 01000: Expression for field `col1` is referring to uninitialized field `col1` +# +# MDEV-10354 Assertion `! is_set()' failed in Diagnostics_area::set_ok_status on CREATE TABLE with invalid default +# create table t1 (col int default (yearweek((exp(710))))); ERROR 22003: DOUBLE value is out of range in 'exp(710)' # @@ -3422,7 +3431,9 @@ DEFAULT(a) CASE a WHEN 0 THEN 1 ELSE 2 END NULL 2 DROP TABLE t; DROP VIEW v; -# end of 10.2 test +# +# End of 10.2 test +# # # MDEV-22703 DEFAULT() on a BLOB column can overwrite the default # record, which can cause crashes when accessing already released @@ -3438,4 +3449,6 @@ length(DEFAULT(h)) 25 INSERT INTO t1 () VALUES (); drop table t1; -# end of 10.3 test +# +# End of 10.3 test +# diff --git a/mysql-test/main/default.test b/mysql-test/main/default.test index 1230f67c7d2..a88817230e3 100644 --- a/mysql-test/main/default.test +++ b/mysql-test/main/default.test @@ -1,16 +1,10 @@ --source include/have_innodb.inc -# -# test of already fixed bugs -# ---disable_warnings -drop table if exists t1,t2,t3,t4,t5,t6; -drop database if exists mysqltest; - # # Bug 10838 # Insert causes warnings for no default values and corrupts tables # +--disable_warnings set sql_mode=""; CREATE TABLE t1 (a varchar(30) binary NOT NULL DEFAULT ' ', b varchar(1) binary NOT NULL DEFAULT ' ', @@ -1891,7 +1885,9 @@ show create table t2; show create table t3; drop table t1, t2, t3; +--echo # --echo # MDEV-11359: Implement IGNORE for bulk operation +--echo # create table t1 (a int primary key default 0, b int default 3); insert into t1 values (1, ignore); insert into t1 values (2, ignore); @@ -2071,9 +2067,9 @@ select * from t1; drop table t1; set sql_mode=default; -# -# MDEV-10201 Bad results for CREATE TABLE t1 (a INT DEFAULT b, b INT DEFAULT 4) -# +--echo # +--echo # MDEV-10201 Bad results for CREATE TABLE t1 (a INT DEFAULT b, b INT DEFAULT 4) +--echo # create table t1 (a int default b, b int default 4, t text); insert t1 (b, t) values (5, '1 column is omitted'); insert t1 values (default, 5, '2 column gets DEFAULT, keyword'); @@ -2088,15 +2084,15 @@ insert t1 (t,b,a) values ('9 reversed, the value of the DEFAULT(a), that is b', select * from t1 order by t; drop table t1; -# -# MDEV-10352 Server crashes in Field::set_default on CREATE TABLE -# +--echo # +--echo # MDEV-10352 Server crashes in Field::set_default on CREATE TABLE +--echo # --error ER_EXPRESSION_REFERS_TO_UNINIT_FIELD create table t1 (col1 int default(-(default(col1)))); -# -# MDEV-10354 Assertion `! is_set()' failed in Diagnostics_area::set_ok_status on CREATE TABLE with invalid default -# +--echo # +--echo # MDEV-10354 Assertion `! is_set()' failed in Diagnostics_area::set_ok_status on CREATE TABLE with invalid default +--echo # --error ER_DATA_OUT_OF_RANGE create table t1 (col int default (yearweek((exp(710))))); @@ -2126,7 +2122,6 @@ INSERT INTO t1 VALUES (),(); SELECT 1 FROM t1 GROUP BY DEFAULT(pk); DROP TABLE t1; - --echo # --echo # MDEV-28402: ASAN heap-use-after-free in create_tmp_table, --echo # Assertion `l_offset >= 0 && table->s->rec_buff_length - l_offset > 0' @@ -2142,7 +2137,9 @@ CREATE ALGORITHM=TEMPTABLE VIEW v AS SELECT * FROM t; SELECT DISTINCT DEFAULT(a), CASE a WHEN 0 THEN 1 ELSE 2 END FROM v GROUP BY a WITH ROLLUP; DROP TABLE t; DROP VIEW v; ---echo # end of 10.2 test +--echo # +--echo # End of 10.2 test +--echo # --echo # --echo # MDEV-22703 DEFAULT() on a BLOB column can overwrite the default @@ -2160,4 +2157,6 @@ SELECT length(DEFAULT(h)) FROM t1; INSERT INTO t1 () VALUES (); drop table t1; ---echo # end of 10.3 test +--echo # +--echo # End of 10.3 test +--echo # -- cgit v1.2.1 From a8e57906d185bfbb2e4afd296dd26ae53577aa3c Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Mon, 9 May 2022 12:16:04 +0200 Subject: 10.4 specific fixes for DEFAULT() --- mysql-test/main/default.result | 13 +++++++++++++ mysql-test/main/default.test | 12 ++++++++++++ 2 files changed, 25 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/default.result b/mysql-test/main/default.result index bd5ffba0d46..55b448229b5 100644 --- a/mysql-test/main/default.result +++ b/mysql-test/main/default.result @@ -3412,6 +3412,7 @@ INSERT INTO t1 VALUES (),(); SELECT 1 FROM t1 GROUP BY DEFAULT(pk); 1 1 +1 DROP TABLE t1; # # MDEV-28402: ASAN heap-use-after-free in create_tmp_table, @@ -3452,3 +3453,15 @@ drop table t1; # # End of 10.3 test # +# +# MDEV-26423: MariaDB server crash in Create_tmp_table::finalize +# +CREATE TABLE t1 (pk text DEFAULT length(uuid())); +INSERT INTO t1 VALUES (),(); +SELECT 1 FROM t1 GROUP BY DEFAULT(pk); +1 +1 +DROP TABLE t1; +# +# End of 10.4 test +# diff --git a/mysql-test/main/default.test b/mysql-test/main/default.test index a88817230e3..3064209a4a2 100644 --- a/mysql-test/main/default.test +++ b/mysql-test/main/default.test @@ -2160,3 +2160,15 @@ drop table t1; --echo # --echo # End of 10.3 test --echo # + +--echo # +--echo # MDEV-26423: MariaDB server crash in Create_tmp_table::finalize +--echo # +CREATE TABLE t1 (pk text DEFAULT length(uuid())); +INSERT INTO t1 VALUES (),(); +SELECT 1 FROM t1 GROUP BY DEFAULT(pk); +DROP TABLE t1; + +--echo # +--echo # End of 10.4 test +--echo # -- cgit v1.2.1