diff options
Diffstat (limited to 'mysql-test/main')
69 files changed, 2127 insertions, 253 deletions
diff --git a/mysql-test/main/check_constraint.result b/mysql-test/main/check_constraint.result index f851b99e5c1..dab1d61cdb9 100644 --- a/mysql-test/main/check_constraint.result +++ b/mysql-test/main/check_constraint.result @@ -236,6 +236,66 @@ insert t1 (b) values (1); ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` drop table t1; # +# MDEV-25638 Assertion `!result' failed in convert_const_to_int +# +create table t1 (v1 bigint check (v1 not in ('x' , 'x111'))) ; +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DOUBLE value: 'x111' +select * from t1; +v1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DOUBLE value: 'x111' +select v1 from t1; +v1 +select * from t1; +v1 +prepare stmt from "select * from t1"; +execute stmt; +v1 +execute stmt; +v1 +flush tables; +select * from t1; +v1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DOUBLE value: 'x111' +select * from t1; +v1 +deallocate prepare stmt; +drop table t1; +# +# MDEV-26061 MariaDB server crash at Field::set_default +# +create table t1 (v2 date check (v1 like default (v1)), v1 date default (from_days ('x'))); +Warnings: +Warning 1292 Truncated incorrect INTEGER value: 'x' +insert ignore into t1 values ( 'x' , 'x' ) ; +Warnings: +Warning 1265 Data truncated for column 'v2' at row 1 +Warning 1265 Data truncated for column 'v1' at row 1 +Warning 1292 Truncated incorrect INTEGER value: 'x' +drop table t1; +# +# End of 10.2 tests +# +# +# MDEV-26061 MariaDB server crash at Field::set_default +# +create table t1 (d timestamp check (default (d) is true)) as select 1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() CHECK (default(`d`) is true), + `1` int(1) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +# +# End of 10.3 tests +# +# # MDEV-24274 ALTER TABLE with CHECK CONSTRAINTS gives "Out of Memory" error # create table t1 (id varchar(2), constraint id check (id regexp '[a-z]')); @@ -247,3 +307,6 @@ t1 CREATE TABLE `t1` ( CONSTRAINT `id` CHECK (`id` regexp '[a-z]') ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/check_constraint.test b/mysql-test/main/check_constraint.test index 1258a9e3be6..194fdc04399 100644 --- a/mysql-test/main/check_constraint.test +++ b/mysql-test/main/check_constraint.test @@ -178,9 +178,56 @@ insert t1 (b) values (1); drop table t1; --echo # +--echo # MDEV-25638 Assertion `!result' failed in convert_const_to_int +--echo # + +--enable_prepare_warnings +create table t1 (v1 bigint check (v1 not in ('x' , 'x111'))) ; +select * from t1; +select v1 from t1; +select * from t1; +prepare stmt from "select * from t1"; +execute stmt; +execute stmt; +flush tables; +select * from t1; +select * from t1; +deallocate prepare stmt; +drop table t1; +--disable_prepare_warnings + +--echo # +--echo # MDEV-26061 MariaDB server crash at Field::set_default +--echo # + +create table t1 (v2 date check (v1 like default (v1)), v1 date default (from_days ('x'))); +insert ignore into t1 values ( 'x' , 'x' ) ; +drop table t1; + +--echo # +--echo # End of 10.2 tests +--echo # + +--echo # +--echo # MDEV-26061 MariaDB server crash at Field::set_default +--echo # + +create table t1 (d timestamp check (default (d) is true)) as select 1; +show create table t1; +drop table t1; + +--echo # +--echo # End of 10.3 tests +--echo # + +--echo # --echo # MDEV-24274 ALTER TABLE with CHECK CONSTRAINTS gives "Out of Memory" error --echo # create table t1 (id varchar(2), constraint id check (id regexp '[a-z]')); alter table t1 force; show create table t1; drop table t1; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/create_select_tmp.result b/mysql-test/main/create_select.result index 2842ab26c42..803f9a06213 100644 --- a/mysql-test/main/create_select_tmp.result +++ b/mysql-test/main/create_select.result @@ -1,4 +1,7 @@ -drop table if exists t1, t2; +CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); +# +# Testcase for BUG#4551 +# CREATE TABLE t1 ( a int ); INSERT INTO t1 VALUES (1),(2),(1); CREATE TABLE t2 ( PRIMARY KEY (a) ) ENGINE=INNODB SELECT a FROM t1; @@ -18,6 +21,20 @@ ERROR 23000: Duplicate entry '1' for key 'PRIMARY' select * from t2; ERROR 42S02: Table 'test.t2' doesn't exist drop table t1; +# +# End of 4.1 tests +# +# +# MDEV-28393 Server crashes in TABLE::mark_default_fields_for_write +# +create table t1 (a int, b text not null default ''); +alter table t1 character set = utf8; +create table t2 select * from t1; +insert into t1 values (1,''); +drop table t1, t2; +# +# End of 10.2 tests +# set sql_mode='ignore_bad_table_options'; create table t1 ( f1 int invisible, @@ -39,3 +56,6 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING drop table t1; set sql_mode=default; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/create_select_tmp.test b/mysql-test/main/create_select.test index 3ed885ea382..d91f71133a0 100644 --- a/mysql-test/main/create_select_tmp.test +++ b/mysql-test/main/create_select.test @@ -1,42 +1,55 @@ -# Testcase for BUG#4551 +# This does not work for RBR yet. +--source include/have_innodb.inc +--source include/have_binlog_format_mixed_or_statement.inc + +CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); + +--echo # +--echo # Testcase for BUG#4551 +--echo # + # The bug was that when the table was TEMPORARY, it was not deleted if # the CREATE SELECT failed (the code intended too, but it actually # didn't). And as the CREATE TEMPORARY TABLE was not written to the # binlog if it was a transactional table, it resulted in an # inconsistency between binlog and the internal list of temp tables. -# This does not work for RBR yet. ---source include/have_binlog_format_mixed_or_statement.inc - ---disable_query_log -CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); ---enable_query_log - --- source include/have_innodb.inc ---disable_warnings -drop table if exists t1, t2; ---enable_warnings CREATE TABLE t1 ( a int ); INSERT INTO t1 VALUES (1),(2),(1); --error ER_DUP_ENTRY CREATE TABLE t2 ( PRIMARY KEY (a) ) ENGINE=INNODB SELECT a FROM t1; ---error 1146 +--error ER_NO_SUCH_TABLE select * from t2; --error ER_DUP_ENTRY CREATE TEMPORARY TABLE t2 ( PRIMARY KEY (a) ) ENGINE=INNODB SELECT a FROM t1; ---error 1146 +--error ER_NO_SUCH_TABLE select * from t2; --error ER_DUP_ENTRY CREATE TABLE t2 ( PRIMARY KEY (a) ) ENGINE=MYISAM SELECT a FROM t1; ---error 1146 +--error ER_NO_SUCH_TABLE select * from t2; --error ER_DUP_ENTRY CREATE TEMPORARY TABLE t2 ( PRIMARY KEY (a) ) ENGINE=MYISAM SELECT a FROM t1; ---error 1146 +--error ER_NO_SUCH_TABLE select * from t2; drop table t1; -# End of 4.1 tests +--echo # +--echo # End of 4.1 tests +--echo # + +--echo # +--echo # MDEV-28393 Server crashes in TABLE::mark_default_fields_for_write +--echo # +create table t1 (a int, b text not null default ''); +alter table t1 character set = utf8; +create table t2 select * from t1; +insert into t1 values (1,''); +drop table t1, t2; + +--echo # +--echo # End of 10.2 tests +--echo # set sql_mode='ignore_bad_table_options'; create table t1 ( @@ -50,4 +63,6 @@ show create table t1; drop table t1; set sql_mode=default; -# End of 10.4 tests +--echo # +--echo # End of 10.4 tests +--echo # 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/default.result b/mysql-test/main/default.result index e8f5bd8e48a..55b448229b5 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)' # @@ -3395,7 +3404,37 @@ ERROR 01000: Expression for field `a` is referring to uninitialized field `a` show warnings; Level Code Message Error 4029 Expression for field `a` is referring to uninitialized field `a` -# end of 10.2 test +# +# MDEV-26423: MariaDB server crash in Create_tmp_table::finalize +# +CREATE TABLE t1 (pk varchar(36) DEFAULT uuid()); +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, +# Assertion `l_offset >= 0 && table->s->rec_buff_length - l_offset > 0' +# +CREATE TABLE t (a INT, KEY (a)); +INSERT INTO t VALUES (1),(2); +SELECT DISTINCT DEFAULT(a), CASE a WHEN 0 THEN 1 ELSE 2 END FROM t GROUP BY a WITH ROLLUP; +DEFAULT(a) CASE a WHEN 0 THEN 1 ELSE 2 END +NULL 2 +DROP TABLE t; +CREATE TABLE t (a INT, KEY (a)); +INSERT INTO t VALUES (1),(2); +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; +DEFAULT(a) CASE a WHEN 0 THEN 1 ELSE 2 END +NULL 2 +DROP TABLE t; +DROP VIEW v; +# +# 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 @@ -3411,4 +3450,18 @@ length(DEFAULT(h)) 25 INSERT INTO t1 () VALUES (); drop table t1; -# end of 10.3 test +# +# 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 bcd6ef7a9fb..3064209a4a2 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))))); @@ -2116,7 +2112,34 @@ DROP TABLE t1; create table t1 (a int as (a)); show warnings; ---echo # end of 10.2 test + +--echo # +--echo # MDEV-26423: MariaDB server crash in Create_tmp_table::finalize +--echo # + +CREATE TABLE t1 (pk varchar(36) DEFAULT uuid()); +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' +--echo # +CREATE TABLE t (a INT, KEY (a)); +INSERT INTO t VALUES (1),(2); +SELECT DISTINCT DEFAULT(a), CASE a WHEN 0 THEN 1 ELSE 2 END FROM t GROUP BY a WITH ROLLUP; +DROP TABLE t; + +CREATE TABLE t (a INT, KEY (a)); +INSERT INTO t VALUES (1),(2); +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 # +--echo # End of 10.2 test +--echo # --echo # --echo # MDEV-22703 DEFAULT() on a BLOB column can overwrite the default @@ -2134,4 +2157,18 @@ 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 # + +--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 # diff --git a/mysql-test/main/default_innodb.result b/mysql-test/main/default_innodb.result new file mode 100644 index 00000000000..81e9672df24 --- /dev/null +++ b/mysql-test/main/default_innodb.result @@ -0,0 +1,18 @@ +# +# MDEV-26423: MariaDB server crash in Create_tmp_table::finalize +# +CREATE TABLE v0 ( +v2 DATE DEFAULT ( v1 MOD 68321183.000000 ) , +v1 DATETIME NULL ) engine=innodb; +SHOW DATABASES LIKE 'x'; +Database (x) +SELECT DISTINCT v2 , v1 , DEFAULT ( v2 ) FROM v0; +v2 v1 DEFAULT ( v2 ) +DROP TABLE v0; +CREATE TABLE t1 (v1 DATE, v2 DATE DEFAULT(v1)) engine=innodb; +SELECT DISTINCT DEFAULT(v2) FROM t1 ; +DEFAULT(v2) +DROP TABLE t1; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/default_innodb.test b/mysql-test/main/default_innodb.test new file mode 100644 index 00000000000..2fc74950845 --- /dev/null +++ b/mysql-test/main/default_innodb.test @@ -0,0 +1,22 @@ + +--source include/have_innodb.inc + +--echo # +--echo # MDEV-26423: MariaDB server crash in Create_tmp_table::finalize +--echo # + +CREATE TABLE v0 ( + v2 DATE DEFAULT ( v1 MOD 68321183.000000 ) , + v1 DATETIME NULL ) engine=innodb; +SHOW DATABASES LIKE 'x'; +SELECT DISTINCT v2 , v1 , DEFAULT ( v2 ) FROM v0; +DROP TABLE v0; + +CREATE TABLE t1 (v1 DATE, v2 DATE DEFAULT(v1)) engine=innodb; +SELECT DISTINCT DEFAULT(v2) FROM t1 ; +DROP TABLE t1; + + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index b91625b5003..15a7784c890 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3628,4 +3628,64 @@ f2 f3 DROP PROCEDURE p1; DROP VIEW v1,v2,v3; DROP TABLE t1; +# +# MDEV-27212: 2-nd execution of PS for select with embedded derived tables +# and correlated subquery in select list of outer derived +# +create table t1 ( id int, id2 int ) engine=myisam; +create table t2 ( x3 int , x1 int , x2 int, a1 int) engine=myisam; +insert into t1 values (3, 2), (4, 2), (3, 4); +insert into t2 values (1, 2, 2, 1), (1, 3, 3, 2), (2, 3, 3, 1); +prepare stmt from "select id from t1 +join +( select dt2.x1, +( select sum(a1) from t2 where t2.x1 = dt2.x1 ) m +from ( select x1 from t2 u where x3 = 1 ) dt2 +) dt +on t1.id = dt.x1 +where t1.id2 < dt.m"; +execute stmt; +id +3 +execute stmt; +id +3 +deallocate prepare stmt; +create procedure sp1() select id from t1 +join +( select dt2.x1, +( select sum(a1) from t2 where t2.x1 = dt2.x1 ) m +from ( select x1 from t2 u where x3 = 1 ) dt2 +) dt +on t1.id = dt.x1 +where t1.id2 < dt.m; +call sp1(); +id +3 +call sp1(); +id +3 +create view v2 as select x1 from t2 u where x3 = 1; +create view v as +select v2.x1, +( select sum(a1) from t2 where t2.x1 = v2.x1 ) m from v2; +prepare stmt from "select id from t1 join v on t1.id = v.x1 where t1.id2 < v.m"; +execute stmt; +id +3 +execute stmt; +id +3 +deallocate prepare stmt; +create procedure sp2() select id from t1 join v on t1.id = v.x1 where t1.id2 < v.m; +call sp2(); +id +3 +call sp2(); +id +3 +drop procedure sp1; +drop procedure sp2; +drop view v, v2; +drop table t1,t2; # End of 10.2 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index 584bde2d75d..006bdaea5ef 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2400,4 +2400,56 @@ DROP PROCEDURE p1; DROP VIEW v1,v2,v3; DROP TABLE t1; +--echo # +--echo # MDEV-27212: 2-nd execution of PS for select with embedded derived tables +--echo # and correlated subquery in select list of outer derived +--echo # +create table t1 ( id int, id2 int ) engine=myisam; +create table t2 ( x3 int , x1 int , x2 int, a1 int) engine=myisam; +insert into t1 values (3, 2), (4, 2), (3, 4); +insert into t2 values (1, 2, 2, 1), (1, 3, 3, 2), (2, 3, 3, 1); + +let $q= +select id from t1 + join + ( select dt2.x1, + ( select sum(a1) from t2 where t2.x1 = dt2.x1 ) m + from ( select x1 from t2 u where x3 = 1 ) dt2 + ) dt + on t1.id = dt.x1 +where t1.id2 < dt.m; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +eval create procedure sp1() $q; +call sp1(); +call sp1(); + +create view v2 as select x1 from t2 u where x3 = 1; +create view v as +select v2.x1, + ( select sum(a1) from t2 where t2.x1 = v2.x1 ) m from v2; + +let $q= +select id from t1 join v on t1.id = v.x1 where t1.id2 < v.m; + +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +eval create procedure sp2() $q; +call sp2(); +call sp2(); + +drop procedure sp1; +drop procedure sp2; + +drop view v, v2; + +drop table t1,t2; + --echo # End of 10.2 tests diff --git a/mysql-test/main/func_default.result b/mysql-test/main/func_default.result index 8721270ca1c..55efbda546d 100644 --- a/mysql-test/main/func_default.result +++ b/mysql-test/main/func_default.result @@ -159,5 +159,57 @@ a 10 DROP TABLE t1; # +# MDEV-21028 Server crashes in Query_arena::set_query_arena upon SELECT from view +# +create table t1 (a datetime default current_timestamp); +insert into t1 () values (),(); +create algorithm=temptable view v1 as select * from t1; +create algorithm=merge view v2 as select * from t1; +select default(a) = now() from v1; +default(a) = now() +1 +1 +select default(a) = now() from v2; +default(a) = now() +1 +1 +drop view v1, v2; +drop table t1; +create table t1 (v1 timestamp) select 'x'; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v1` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `x` varchar(1) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select default(v1) from (select v1 from t1) dt; +default(v1) +2001-01-01 10:20:30 +select default(v1) from (select v1 from t1 group by v1) dt; +default(v1) +2001-01-01 10:20:30 +drop table t1; +create table t1 (a text default ''); +create algorithm=temptable view v1 as select * from t1; +insert into t1 values ('a'); +select default(a) from v1; +default(a) + +drop view v1; +drop table t1; +# +# MDEV-28403 ASAN heap-use-after-free in String::copy / get_field_default_value +# +create table t (a blob default 'x'); +create view v as select * from t; +insert into t () values (); +update t set a = default; +select table_name,column_name,column_default from information_schema.columns where table_name = 'v'; +table_name v +column_name a +column_default 'x' +drop view v; +drop table t; +# # End of 10.2 tests # diff --git a/mysql-test/main/func_default.test b/mysql-test/main/func_default.test index cba7842c68f..d2099d3ef4c 100644 --- a/mysql-test/main/func_default.test +++ b/mysql-test/main/func_default.test @@ -140,5 +140,41 @@ SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END IS FALSE; DROP TABLE t1; --echo # +--echo # MDEV-21028 Server crashes in Query_arena::set_query_arena upon SELECT from view +--echo # +create table t1 (a datetime default current_timestamp); +insert into t1 () values (),(); +create algorithm=temptable view v1 as select * from t1; +create algorithm=merge view v2 as select * from t1; +select default(a) = now() from v1; +select default(a) = now() from v2; +drop view v1, v2; +drop table t1; + +create table t1 (v1 timestamp) select 'x'; +show create table t1; +select default(v1) from (select v1 from t1) dt; +select default(v1) from (select v1 from t1 group by v1) dt; +drop table t1; + +create table t1 (a text default ''); +create algorithm=temptable view v1 as select * from t1; +insert into t1 values ('a'); +select default(a) from v1; +drop view v1; +drop table t1; + +--echo # +--echo # MDEV-28403 ASAN heap-use-after-free in String::copy / get_field_default_value +--echo # +create table t (a blob default 'x'); +create view v as select * from t; +insert into t () values (); +update t set a = default; +query_vertical select table_name,column_name,column_default from information_schema.columns where table_name = 'v'; +drop view v; +drop table t; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result index d44f634db08..d4b87340c35 100644 --- a/mysql-test/main/func_time.result +++ b/mysql-test/main/func_time.result @@ -991,6 +991,12 @@ ADDTIME('916:40:00', '416:40:00') Warnings: Warning 1292 Truncated incorrect time value: '916:40:00' Warning 1292 Truncated incorrect time value: '1255:39:59.999999' +SELECT ADDTIME(20010101,1e0), ADDTIME(20010101,1.1e0); +ADDTIME(20010101,1e0) ADDTIME(20010101,1.1e0) +2001-01-01 00:00:01.000000 2001-01-01 00:00:01.100000 +SELECT ADDTIME(ADDTIME(20010101,1e0), 0); +ADDTIME(ADDTIME(20010101,1e0), 0) +2001-01-01 00:00:01.000000 SELECT SUBTIME('916:40:00', '416:40:00'); SUBTIME('916:40:00', '416:40:00') 422:19:59.999999 diff --git a/mysql-test/main/func_time.test b/mysql-test/main/func_time.test index 970d3e44a77..ad2fcf6d68f 100644 --- a/mysql-test/main/func_time.test +++ b/mysql-test/main/func_time.test @@ -513,6 +513,10 @@ SELECT TIME_TO_SEC('916:40:00'); SELECT ADDTIME('500:00:00', '416:40:00'); SELECT ADDTIME('916:40:00', '416:40:00'); +# check if ADDTIME() handles NOT_FIXED_DEC correctly +SELECT ADDTIME(20010101,1e0), ADDTIME(20010101,1.1e0); +SELECT ADDTIME(ADDTIME(20010101,1e0), 0); + # check if SUBTIME() handles out-of-range values correctly SELECT SUBTIME('916:40:00', '416:40:00'); SELECT SUBTIME('-916:40:00', '416:40:00'); diff --git a/mysql-test/main/get_diagnostics.result b/mysql-test/main/get_diagnostics.result index ca495d6954d..8842df8cd58 100644 --- a/mysql-test/main/get_diagnostics.result +++ b/mysql-test/main/get_diagnostics.result @@ -791,3 +791,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 4667ec727dd..a5ce30d56eb 100644 --- a/mysql-test/main/get_diagnostics.test +++ b/mysql-test/main/get_diagnostics.test @@ -853,3 +853,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; diff --git a/mysql-test/main/grant.result b/mysql-test/main/grant.result index 92073388b69..80fff0e6dff 100644 --- a/mysql-test/main/grant.result +++ b/mysql-test/main/grant.result @@ -3,8 +3,6 @@ set LOCAL sql_mode=""; SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; SET GLOBAL log_bin_trust_function_creators = 1; select priv into @root_priv from mysql.global_priv where user='root' and host='localhost'; -drop table if exists t1; -drop database if exists mysqltest; connect master,localhost,root,,; connection master; SET NAMES binary; @@ -2794,6 +2792,14 @@ DROP USER foo; DROP TABLE db.t; DROP DATABASE db; # +# Bug#33578113: DROP privilege on performance_schema.* can't be revoked +# +connection default; +CREATE USER bug33578113; +GRANT DROP ON performance_schema.* TO bug33578113; +REVOKE DROP ON performance_schema.* FROM bug33578113; +DROP USER bug33578113; +# # End of 10.2 tests # # diff --git a/mysql-test/main/grant.test b/mysql-test/main/grant.test index a81e77567b5..b67c59cd559 100644 --- a/mysql-test/main/grant.test +++ b/mysql-test/main/grant.test @@ -1,7 +1,7 @@ # Test of GRANT commands # Grant tests not performed with embedded server --- source include/not_embedded.inc +--source include/not_embedded.inc # Save the initial number of concurrent sessions --source include/count_sessions.inc @@ -12,12 +12,6 @@ SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creato SET GLOBAL log_bin_trust_function_creators = 1; select priv into @root_priv from mysql.global_priv where user='root' and host='localhost'; -# Cleanup ---disable_warnings -drop table if exists t1; -drop database if exists mysqltest; ---enable_warnings - connect (master,localhost,root,,); connection master; SET NAMES binary; @@ -2286,6 +2280,16 @@ DROP TABLE db.t; DROP DATABASE db; --echo # +--echo # Bug#33578113: DROP privilege on performance_schema.* can't be revoked +--echo # +connection default; +CREATE USER bug33578113; +GRANT DROP ON performance_schema.* TO bug33578113; +REVOKE DROP ON performance_schema.* FROM bug33578113; +DROP USER bug33578113; +--source include/wait_until_count_sessions.inc + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/having.result b/mysql-test/main/having.result index 51b88c5b8d2..b4ca607ec84 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 @@ -280,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 @@ -880,8 +875,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 +903,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..3f4e8a8e710 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; @@ -253,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 @@ -273,7 +270,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 +299,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 +315,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 +349,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 +909,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 +929,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 +941,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/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result index 9b124296e3d..ad8befb29d5 100644 --- a/mysql-test/main/having_cond_pushdown.result +++ b/mysql-test/main/having_cond_pushdown.result @@ -4924,3 +4924,49 @@ 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; +# +# 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 fc75122615c..aee5a770fa9 100644 --- a/mysql-test/main/having_cond_pushdown.test +++ b/mysql-test/main/having_cond_pushdown.test @@ -1440,3 +1440,46 @@ 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; + +--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 diff --git a/mysql-test/main/information_schema_columns.result b/mysql-test/main/information_schema_columns.result new file mode 100644 index 00000000000..3624a6db368 --- /dev/null +++ b/mysql-test/main/information_schema_columns.result @@ -0,0 +1,47 @@ +# +# Start of 10.2 tests +# +# +# MDEV-25243 ASAN heap-use-after-free in Item_func_sp::execute_impl upon concurrent view DDL and I_S query with view and function +# +CREATE FUNCTION f1() RETURNS INT RETURN 1; +CREATE VIEW v01 AS SELECT f1(); +CREATE VIEW v02 AS SELECT f1(); +connect con1,localhost,root,,; +SELECT GET_LOCK('v01',30); +GET_LOCK('v01',30) +1 +SELECT GET_LOCK('v02',30); +GET_LOCK('v02',30) +1 +connection default; +SELECT * FROM INFORMATION_SCHEMA.COLUMNS +WHERE TABLE_SCHEMA='test' + AND TABLE_NAME LIKE 'v0%' + AND GET_LOCK(TABLE_NAME,30) +AND RELEASE_LOCK(TABLE_NAME) +AND f1()=1 +ORDER BY TABLE_NAME; +connection con1; +connection con1; +SELECT RELEASE_LOCK('v01') /* Let the first row evaluate f1 */; +RELEASE_LOCK('v01') +1 +CREATE FUNCTION f2() RETURNS INT RETURN 1 /* Invalidate SP cache*/; +SELECT RELEASE_LOCK('v02') /* Let the second row evaluate f1() */; +RELEASE_LOCK('v02') +1 +DROP FUNCTION f2; +disconnect con1; +connection default; +SELECT RELEASE_LOCK('v01'); +RELEASE_LOCK('v01') +NULL +SELECT RELEASE_LOCK('v02'); +RELEASE_LOCK('v02') +NULL +DROP VIEW v01, v02; +DROP FUNCTION f1; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/information_schema_columns.test b/mysql-test/main/information_schema_columns.test new file mode 100644 index 00000000000..0171b221110 --- /dev/null +++ b/mysql-test/main/information_schema_columns.test @@ -0,0 +1,48 @@ +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-25243 ASAN heap-use-after-free in Item_func_sp::execute_impl upon concurrent view DDL and I_S query with view and function +--echo # + + +CREATE FUNCTION f1() RETURNS INT RETURN 1; +CREATE VIEW v01 AS SELECT f1(); +CREATE VIEW v02 AS SELECT f1(); + +--connect(con1,localhost,root,,) +SELECT GET_LOCK('v01',30); +SELECT GET_LOCK('v02',30); +--connection default + +--send + SELECT * FROM INFORMATION_SCHEMA.COLUMNS + WHERE TABLE_SCHEMA='test' + AND TABLE_NAME LIKE 'v0%' + AND GET_LOCK(TABLE_NAME,30) + AND RELEASE_LOCK(TABLE_NAME) + AND f1()=1 + ORDER BY TABLE_NAME; + +--connection con1 +--connection con1 +SELECT RELEASE_LOCK('v01') /* Let the first row evaluate f1 */; +CREATE FUNCTION f2() RETURNS INT RETURN 1 /* Invalidate SP cache*/; +SELECT RELEASE_LOCK('v02') /* Let the second row evaluate f1() */; +DROP FUNCTION f2; +--disconnect con1 + +--connection default +--disable_result_log +--reap +--enable_result_log +SELECT RELEASE_LOCK('v01'); +SELECT RELEASE_LOCK('v02'); + +DROP VIEW v01, v02; +DROP FUNCTION f1; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/main/information_schema_tables.result b/mysql-test/main/information_schema_tables.result new file mode 100644 index 00000000000..a3dd068831c --- /dev/null +++ b/mysql-test/main/information_schema_tables.result @@ -0,0 +1,77 @@ +# +# Start of 10.2 tests +# +# +# MDEV-25243 ASAN heap-use-after-free in Item_func_sp::execute_impl upon concurrent view DDL and I_S query with view and function +# +# The originally reported non-deterministic test. +# It did not fail reliably on every run. +CREATE TABLE t (a INT); +INSERT INTO t VALUES (1),(2); +CREATE FUNCTION f(b INT) RETURNS INT RETURN 1; +CREATE VIEW v AS SELECT f(SUM(a)) FROM t; +connect con1,localhost,root,,test; +LOOP +CREATE OR REPLACE VIEW vv AS SELECT 1; +END LOOP $ +connection default; +SELECT v.* FROM v JOIN INFORMATION_SCHEMA.TABLES WHERE DATA_LENGTH = -1; +f(SUM(a)) +KILL CONID; +disconnect con1; +connection default; +DROP VIEW IF EXISTS vv; +DROP VIEW v; +DROP FUNCTION f; +DROP TABLE t; +# The second test version from the MDEV. +# It failed more reliably, but still was not deterministic. +CREATE FUNCTION f() RETURNS INT RETURN 1; +CREATE VIEW v AS SELECT f() FROM seq_1_to_10; +SELECT * FROM INFORMATION_SCHEMA.TABLES, v;; +connect con1,localhost,root,,; +CREATE VIEW v2 AS SELECT 1; +connection default; +disconnect con1; +DROP VIEW v; +DROP VIEW v2; +DROP FUNCTION f; +# The third test version from the MDEV. +# It failed reliably, and should be deterninistic. +CREATE FUNCTION f1() RETURNS INT RETURN 1; +CREATE VIEW v01 AS SELECT f1(); +CREATE VIEW v02 AS SELECT f1(); +connect con1,localhost,root,,; +SELECT GET_LOCK('v01',30); +GET_LOCK('v01',30) +1 +SELECT GET_LOCK('v02',30); +GET_LOCK('v02',30) +1 +connection default; +SELECT * FROM INFORMATION_SCHEMA.TABLES +WHERE TABLE_SCHEMA='test' + AND TABLE_NAME LIKE 'v0%' + AND GET_LOCK(TABLE_NAME,30) +AND RELEASE_LOCK(TABLE_NAME) +AND f1()=1 +ORDER BY TABLE_NAME; +connection con1; +SELECT RELEASE_LOCK('v01') /* Let the first row evaluate f1 */; +RELEASE_LOCK('v01') +1 +CREATE FUNCTION f2() RETURNS INT RETURN 1 /* Invalidate SP cache*/; +SELECT RELEASE_LOCK('v02') /* Let the second row evaluate f1() */; +RELEASE_LOCK('v02') +1 +DROP FUNCTION f2; +disconnect con1; +connection default; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT MAX_INDEX_LENGTH TEMPORARY +def test v01 VIEW NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL VIEW NULL NULL +def test v02 VIEW NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL VIEW NULL NULL +DROP VIEW v01, v02; +DROP FUNCTION f1; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/information_schema_tables.test b/mysql-test/main/information_schema_tables.test new file mode 100644 index 00000000000..bc4f269a3fb --- /dev/null +++ b/mysql-test/main/information_schema_tables.test @@ -0,0 +1,100 @@ +--source include/have_sequence.inc + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-25243 ASAN heap-use-after-free in Item_func_sp::execute_impl upon concurrent view DDL and I_S query with view and function +--echo # + +--echo # The originally reported non-deterministic test. +--echo # It did not fail reliably on every run. + +CREATE TABLE t (a INT); +INSERT INTO t VALUES (1),(2); +CREATE FUNCTION f(b INT) RETURNS INT RETURN 1; +CREATE VIEW v AS SELECT f(SUM(a)) FROM t; +--connect (con1,localhost,root,,test) +--let $conid= `SELECT CONNECTION_ID()` +--delimiter $ +--send +LOOP + CREATE OR REPLACE VIEW vv AS SELECT 1; +END LOOP $ +--delimiter ; +--connection default +# Avoid "Prepared statement needs to be re-prepared" +# Note, the code could probably eventually fixed to avoid forcing re-pepare if +# the *temporary* instance of Sp_caches (not the permanent one) was invalidated. +--disable_ps_protocol +--disable_warnings +SELECT v.* FROM v JOIN INFORMATION_SCHEMA.TABLES WHERE DATA_LENGTH = -1; +--enable_warnings +--enable_ps_protocol +# Cleanup +--replace_result $conid CONID +--eval KILL $conid +--disconnect con1 +--connection default +DROP VIEW IF EXISTS vv; +DROP VIEW v; +DROP FUNCTION f; +DROP TABLE t; + + +--echo # The second test version from the MDEV. +--echo # It failed more reliably, but still was not deterministic. + + +CREATE FUNCTION f() RETURNS INT RETURN 1; +CREATE VIEW v AS SELECT f() FROM seq_1_to_10; +--send SELECT * FROM INFORMATION_SCHEMA.TABLES, v; +--connect(con1,localhost,root,,) +CREATE VIEW v2 AS SELECT 1; +--connection default +--disable_result_log +--reap +--enable_result_log +--disconnect con1 +DROP VIEW v; +DROP VIEW v2; +DROP FUNCTION f; + +--echo # The third test version from the MDEV. +--echo # It failed reliably, and should be deterninistic. + +CREATE FUNCTION f1() RETURNS INT RETURN 1; +CREATE VIEW v01 AS SELECT f1(); +CREATE VIEW v02 AS SELECT f1(); + +--connect(con1,localhost,root,,) +SELECT GET_LOCK('v01',30); +SELECT GET_LOCK('v02',30); +--connection default + +--send + SELECT * FROM INFORMATION_SCHEMA.TABLES + WHERE TABLE_SCHEMA='test' + AND TABLE_NAME LIKE 'v0%' + AND GET_LOCK(TABLE_NAME,30) + AND RELEASE_LOCK(TABLE_NAME) + AND f1()=1 + ORDER BY TABLE_NAME; + +--connection con1 +SELECT RELEASE_LOCK('v01') /* Let the first row evaluate f1 */; +CREATE FUNCTION f2() RETURNS INT RETURN 1 /* Invalidate SP cache*/; +SELECT RELEASE_LOCK('v02') /* Let the second row evaluate f1() */; +DROP FUNCTION f2; +--disconnect con1 +--connection default +--reap + + +DROP VIEW v01, v02; +DROP FUNCTION f1; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/main/insert.result b/mysql-test/main/insert.result index d8f40b7383a..674223c17a4 100644 --- a/mysql-test/main/insert.result +++ b/mysql-test/main/insert.result @@ -745,12 +745,42 @@ f1 f2 drop view v1; drop table t1; SET @@sql_mode= @save_mode; +# +# MDEV-13861 Assertion `0' failed in Protocol::end_statement +# CREATE TABLE t1 (f INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE f <=> 'foo' WITH CHECK OPTION; 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; +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 +# +# +# outer references in subqueries in INSERT +# create table t1 (a int default 5); insert t1 values (1); insert t1 values (a); @@ -767,3 +797,6 @@ a 7 8 drop table t1; +# +# End of 10.5 tests +# diff --git a/mysql-test/main/insert.test b/mysql-test/main/insert.test index 4ccaa20ece8..44da0d860f2 100644 --- a/mysql-test/main/insert.test +++ b/mysql-test/main/insert.test @@ -603,9 +603,9 @@ drop view v1; drop table t1; SET @@sql_mode= @save_mode; -# -# MDEV-13861 Assertion `0' failed in Protocol::end_statement -# +--echo # +--echo # MDEV-13861 Assertion `0' failed in Protocol::end_statement +--echo # CREATE TABLE t1 (f INT); CREATE VIEW v1 AS SELECT * FROM t1 WHERE f <=> 'foo' WITH CHECK OPTION; --error ER_TRUNCATED_WRONG_VALUE @@ -613,9 +613,40 @@ REPLACE INTO v1 SET f = NULL; DROP VIEW v1; DROP TABLE t1; -# -# outer referencesin subqueries in INSERT -# +--echo # +--echo # End of 10.0 tests +--echo # + +--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; + +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 # +--echo # End of 10.4 tests +--echo # + +--echo # +--echo # outer references in subqueries in INSERT +--echo # create table t1 (a int default 5); insert t1 values (1); insert t1 values (a); @@ -625,3 +656,8 @@ insert t1 set a=a+2; insert t1 set a=(select a+3); select * from t1; drop table t1; + +--echo # +--echo # End of 10.5 tests +--echo # + diff --git a/mysql-test/main/long_unique_bugs.result b/mysql-test/main/long_unique_bugs.result index 94a39372b1b..709ed929fba 100644 --- a/mysql-test/main/long_unique_bugs.result +++ b/mysql-test/main/long_unique_bugs.result @@ -303,7 +303,24 @@ 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 +# +# +# MDEV-22113 SIGSEGV, ASAN use-after-poison, Assertion `next_insert_id == 0' in handler::ha_external_lock +# create temporary table tmp ( a int, b int, c blob not null, d int, e int default 0, f int, unique key (c)) engine=innodb; create table t2 (x int); lock table t2 write; @@ -312,6 +329,9 @@ start transaction; alter table tmp alter column a set default 8; unlock tables; drop table t2; +# +# MDEV-22218 InnoDB: Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON upon LOAD DATA with NO_BACKSLASH_ESCAPES in SQL_MODE and unique blob in table +# create table t1 (pk int primary key, f blob, unique(f)) engine=innodb; insert t1 values (1, null); select * into outfile 't1.data' from t1; @@ -370,8 +390,13 @@ a b 1 foo 3 bar drop table if exists t1, t2; +# +# MDEV-22185 Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON or ER_KEY_NOT_FOUND or Assertion `inited==NONE' failed in handler::ha_index_init +# create table t1 (a int, b int, unique (b) using hash) engine=innodb partition by key (a) partitions 2; insert into t1 values (1,10),(2,20); update t1 set b = 30 limit 1; drop table t1; +# # End of 10.5 tests +# diff --git a/mysql-test/main/long_unique_bugs.test b/mysql-test/main/long_unique_bugs.test index 05242e0d1ac..c5eb208d810 100644 --- a/mysql-test/main/long_unique_bugs.test +++ b/mysql-test/main/long_unique_bugs.test @@ -234,7 +234,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'); @@ -280,7 +279,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 ( @@ -386,11 +384,26 @@ 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 # -# -# MDEV-22113 SIGSEGV, ASAN use-after-poison, Assertion `next_insert_id == 0' in handler::ha_external_lock -# +--echo # +--echo # MDEV-22113 SIGSEGV, ASAN use-after-poison, Assertion `next_insert_id == 0' in handler::ha_external_lock +--echo # create temporary table tmp ( a int, b int, c blob not null, d int, e int default 0, f int, unique key (c)) engine=innodb; create table t2 (x int); lock table t2 write; @@ -401,9 +414,9 @@ unlock tables; drop table t2; --source include/have_innodb.inc -# -# MDEV-22218 InnoDB: Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON upon LOAD DATA with NO_BACKSLASH_ESCAPES in SQL_MODE and unique blob in table -# +--echo # +--echo # MDEV-22218 InnoDB: Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON upon LOAD DATA with NO_BACKSLASH_ESCAPES in SQL_MODE and unique blob in table +--echo # create table t1 (pk int primary key, f blob, unique(f)) engine=innodb; insert t1 values (1, null); select * into outfile 't1.data' from t1; @@ -449,12 +462,14 @@ create or replace table t2 (a int, b blob, unique(b)) replace as select * from t drop table if exists t1, t2; -# -# MDEV-22185 Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON or ER_KEY_NOT_FOUND or Assertion `inited==NONE' failed in handler::ha_index_init -# +--echo # +--echo # MDEV-22185 Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON or ER_KEY_NOT_FOUND or Assertion `inited==NONE' failed in handler::ha_index_init +--echo # create table t1 (a int, b int, unique (b) using hash) engine=innodb partition by key (a) partitions 2; insert into t1 values (1,10),(2,20); update t1 set b = 30 limit 1; drop table t1; +--echo # --echo # End of 10.5 tests +--echo # 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 diff --git a/mysql-test/main/mysql_tzinfo_to_sql_symlink.result b/mysql-test/main/mysql_tzinfo_to_sql_symlink.result index 9456b5515e3..a9ada2d6fdb 100644 --- a/mysql-test/main/mysql_tzinfo_to_sql_symlink.result +++ b/mysql-test/main/mysql_tzinfo_to_sql_symlink.result @@ -142,9 +142,9 @@ COUNT(*) # # Run on zoneinfo directory --skip-write-binlog # -set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION SQL_LOG_BIN=?, WSREP_ON=OFF;', 'do ?'); -prepare set_wsrep_write_binlog from @prep1; -set @toggle=0; execute set_wsrep_write_binlog using @toggle; +set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION WSREP_ON=OFF', 'do 0'); +SET SESSION SQL_LOG_BIN=0; +execute immediate @prep1; TRUNCATE TABLE time_zone; TRUNCATE TABLE time_zone_name; TRUNCATE TABLE time_zone_transition; @@ -250,9 +250,9 @@ TRUNCATE TABLE time_zone_leap_second; # # Testing with explicit timezonefile --skip-write-binlog # -set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION SQL_LOG_BIN=?, WSREP_ON=OFF;', 'do ?'); -prepare set_wsrep_write_binlog from @prep1; -set @toggle=0; execute set_wsrep_write_binlog using @toggle; +set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION WSREP_ON=OFF', 'do 0'); +SET SESSION SQL_LOG_BIN=0; +execute immediate @prep1; SELECT 'skip truncate tables'; LOCK TABLES time_zone WRITE, time_zone_leap_second WRITE, @@ -353,9 +353,9 @@ TRUNCATE TABLE time_zone_leap_second; # # Testing --skip-write-binlog --leap # -set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION SQL_LOG_BIN=?, WSREP_ON=OFF;', 'do ?'); -prepare set_wsrep_write_binlog from @prep1; -set @toggle=0; execute set_wsrep_write_binlog using @toggle; +set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION WSREP_ON=OFF', 'do 0'); +SET SESSION SQL_LOG_BIN=0; +execute immediate @prep1; SELECT 'skip truncate tables'; LOCK TABLES time_zone WRITE, time_zone_leap_second WRITE, @@ -384,6 +384,45 @@ SELECT COUNT(*) FROM time_zone_leap_second; COUNT(*) 0 # +# MDEV-28263: mariadb-tzinfo-to-sql improve wsrep and binlog cases +# +# +# Testing --skip-write-binlog +# +set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION WSREP_ON=OFF', 'do 0'); +SET SESSION SQL_LOG_BIN=0; +execute immediate @prep1; +SELECT 'skip truncate tables'; +LOCK TABLES time_zone WRITE, + time_zone_leap_second WRITE, + time_zone_name WRITE, + time_zone_transition WRITE, + time_zone_transition_type WRITE; +INSERT INTO time_zone (Use_leap_seconds) VALUES ('N'); +SET @time_zone_id= LAST_INSERT_ID(); +INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('XXX', @time_zone_id); +INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES + (@time_zone_id, 0, 0, 0, 'GMT') +; +UNLOCK TABLES; +COMMIT; +set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION WSREP_ON=OFF', 'do 0'); +SET SESSION SQL_LOG_BIN=0; +execute immediate @prep1; +SELECT 'skip truncate tables'; +LOCK TABLES time_zone WRITE, + time_zone_leap_second WRITE, + time_zone_name WRITE, + time_zone_transition WRITE, + time_zone_transition_type WRITE; +TRUNCATE TABLE time_zone_leap_second; +ALTER TABLE time_zone_leap_second ORDER BY Transition_time; +UNLOCK TABLES; +COMMIT; +# +# End of 10.2 tests +# +# # MDEV-6236 - [PATCH] mysql_tzinfo_to_sql may produce invalid SQL # \d | diff --git a/mysql-test/main/mysql_tzinfo_to_sql_symlink.test b/mysql-test/main/mysql_tzinfo_to_sql_symlink.test index fe7275294d5..8970bf88341 100644 --- a/mysql-test/main/mysql_tzinfo_to_sql_symlink.test +++ b/mysql-test/main/mysql_tzinfo_to_sql_symlink.test @@ -130,7 +130,23 @@ SELECT COUNT(*) FROM time_zone_transition; SELECT COUNT(*) FROM time_zone_transition_type; SELECT COUNT(*) FROM time_zone_leap_second; -# +--echo # +--echo # MDEV-28263: mariadb-tzinfo-to-sql improve wsrep and binlog cases +--echo # + +--echo # +--echo # Testing --skip-write-binlog +--echo # + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--exec $MYSQL_TZINFO_TO_SQL --skip-write-binlog $MYSQLTEST_VARDIR/zoneinfo/GMT XXX 2>&1 + +--exec $MYSQL_TZINFO_TO_SQL --skip-write-binlog --leap $MYSQLTEST_VARDIR/zoneinfo/GMT 2>&1 + +--echo # +--echo # End of 10.2 tests +--echo # + # # Cleanup # diff --git a/mysql-test/main/mysqldump-compat-102.result b/mysql-test/main/mysqldump-compat-102.result index aa8fe5a4de3..86a2ebc47e0 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 e9da5553e1a..31252070adf 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -2779,6 +2779,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 */ ; @@ -2786,8 +2788,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 ;; @@ -2796,6 +2796,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 */ ; @@ -2803,8 +2805,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 @@ -2816,6 +2816,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 */ ; @@ -2823,8 +2825,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 ;; @@ -2833,6 +2833,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 */ ; @@ -2840,8 +2842,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 ;; @@ -2850,6 +2850,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 */ ; @@ -2857,8 +2859,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 @@ -3854,14 +3854,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' ;; @@ -5429,6 +5429,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 */ ; @@ -5436,8 +5438,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 ;; @@ -5740,34 +5740,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` ( @@ -5797,6 +5769,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` ( @@ -5835,6 +5835,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 */; @@ -5875,35 +5904,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, @@ -5940,6 +5940,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 */; @@ -5980,35 +6009,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, @@ -6330,4 +6330,119 @@ 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; +# +# 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.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 +<?xml version="1.0"?> +<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> +<database name="mysql"> + <table_structure name="general_log"> + <field Field="event_time" Type="timestamp(6)" Null="NO" Key="" Default="current_timestamp(6)" Extra="on update current_timestamp(6)" Comment="" /> + <field Field="user_host" Type="mediumtext" Null="NO" Key="" Extra="" Comment="" /> + <field Field="thread_id" Type="bigint(21) unsigned" Null="NO" Key="" Extra="" Comment="" /> + <field Field="server_id" Type="int(10) unsigned" Null="NO" Key="" Extra="" Comment="" /> + <field Field="command_type" Type="varchar(64)" Null="NO" Key="" Extra="" Comment="" /> + <field Field="argument" Type="mediumtext" Null="NO" Key="" Extra="" Comment="" /> + <options Name="general_log" Engine="CSV" Version="10" Row_format="Dynamic" Rows="2" Avg_row_length="0" Data_length="0" Max_data_length="0" Index_length="0" Data_free="0" Collation="utf8_general_ci" Create_options="" Comment="General log" Max_index_length="0" Temporary="N" /> + </table_structure> + <table_structure name="slow_log"> + <field Field="start_time" Type="timestamp(6)" Null="NO" Key="" Default="current_timestamp(6)" Extra="on update current_timestamp(6)" Comment="" /> + <field Field="user_host" Type="mediumtext" Null="NO" Key="" Extra="" Comment="" /> + <field Field="query_time" Type="time(6)" Null="NO" Key="" Extra="" Comment="" /> + <field Field="lock_time" Type="time(6)" Null="NO" Key="" Extra="" Comment="" /> + <field Field="rows_sent" Type="int(11)" Null="NO" Key="" Extra="" Comment="" /> + <field Field="rows_examined" Type="int(11)" Null="NO" Key="" Extra="" Comment="" /> + <field Field="db" Type="varchar(512)" Null="NO" Key="" Extra="" Comment="" /> + <field Field="last_insert_id" Type="int(11)" Null="NO" Key="" Extra="" Comment="" /> + <field Field="insert_id" Type="int(11)" Null="NO" Key="" Extra="" Comment="" /> + <field Field="server_id" Type="int(10) unsigned" Null="NO" Key="" Extra="" Comment="" /> + <field Field="sql_text" Type="mediumtext" Null="NO" Key="" Extra="" Comment="" /> + <field Field="thread_id" Type="bigint(21) unsigned" Null="NO" Key="" Extra="" Comment="" /> + <field Field="rows_affected" Type="int(11)" Null="NO" Key="" Extra="" Comment="" /> + <options Name="slow_log" Engine="CSV" Version="10" Row_format="Dynamic" Rows="2" Avg_row_length="0" Data_length="0" Max_data_length="0" Index_length="0" Data_free="0" Collation="utf8_general_ci" Create_options="" Comment="Slow log" Max_index_length="0" Temporary="N" /> + </table_structure> + +/*!50106 SET GLOBAL LOG_OUTPUT=@save_log_output*/; + + <table_structure name="transaction_registry"> + <field Field="transaction_id" Type="bigint(20) unsigned" Null="NO" Key="PRI" Extra="" Comment="" /> + <field Field="commit_id" Type="bigint(20) unsigned" Null="NO" Key="UNI" Extra="" Comment="" /> + <field Field="begin_timestamp" Type="timestamp(6)" Null="NO" Key="MUL" Default="'0000-00-00 00:00:00.000000'" Extra="" Comment="" /> + <field Field="commit_timestamp" Type="timestamp(6)" Null="NO" Key="MUL" Default="'0000-00-00 00:00:00.000000'" Extra="" Comment="" /> + <field Field="isolation_level" Type="enum('READ-UNCOMMITTED','READ-COMMITTED','REPEATABLE-READ','SERIALIZABLE')" Null="NO" Key="" Extra="" Comment="" /> + <key Table="transaction_registry" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="transaction_id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" /> + <key Table="transaction_registry" Non_unique="0" Key_name="commit_id" Seq_in_index="1" Column_name="commit_id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" /> + <key Table="transaction_registry" Non_unique="1" Key_name="begin_timestamp" Seq_in_index="1" Column_name="begin_timestamp" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" /> + <key Table="transaction_registry" Non_unique="1" Key_name="commit_timestamp" Seq_in_index="1" Column_name="commit_timestamp" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" /> + <key Table="transaction_registry" Non_unique="1" Key_name="commit_timestamp" Seq_in_index="2" Column_name="transaction_id" Collation="A" Cardinality="0" Null="" Index_type="BTREE" Comment="" Index_comment="" /> + <options Name="transaction_registry" Engine="InnoDB" Version="10" Row_format="Dynamic" Rows="0" Avg_row_length="0" Data_length="16384" Max_data_length="0" Index_length="49152" Data_free="0" Create_time="TIMESTAMP" Collation="utf8_bin" Create_options="stats_persistent=0" Comment="" Max_index_length="0" Temporary="N" /> + </table_structure> +</database> +<database name="test1"> +</database> +</mysqldump> +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 799f24665ee..135787973bd 100644 --- a/mysql-test/main/mysqldump.test +++ b/mysql-test/main/mysqldump.test @@ -2885,4 +2885,94 @@ 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 # +--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.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 diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index 219f579df48..9dcd8b9e5b1 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -3585,6 +3585,26 @@ DELETE FROM t1 ORDER BY c; DROP TABLE t1; SET @@SESSION.max_sort_length=DEFAULT; SET sql_mode=DEFAULT; +# +# MDEV-25994 Crash with union of my_decimal type in ORDER BY clause +# +CREATE TABLE t1 (v1 INTEGER) ; +INSERT INTO t1 (v1) VALUES (8); +UPDATE t1 SET v1 = 1 ORDER BY (SELECT 1.1 UNION SELECT -1); +ERROR 21000: Subquery returns more than 1 row +# This one must be successful +UPDATE t1 SET v1 = 2 ORDER BY (SELECT 1 UNION SELECT 1); +UPDATE t1 SET v1 = 3 ORDER BY (SELECT 'a' UNION SELECT 'b'); +ERROR 21000: Subquery returns more than 1 row +# Insert some more data +INSERT INTO t1 (v1) VALUES (8),(9),(100),(-234),(46584),(0); +UPDATE t1 SET v1 = v1+1 ORDER BY (SELECT 100.122 UNION SELECT -189.2); +ERROR 21000: Subquery returns more than 1 row +# This one must be successful +UPDATE t1 SET v1 = v1-200 ORDER BY (SELECT 1 UNION SELECT 1); +UPDATE t1 SET v1 = v1 ORDER BY (SELECT 'abc' UNION SELECT 'bbb'); +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1; # End of 10.2 tests # # MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY diff --git a/mysql-test/main/order_by.test b/mysql-test/main/order_by.test index 9082ac43c5d..9791d6ee96c 100644 --- a/mysql-test/main/order_by.test +++ b/mysql-test/main/order_by.test @@ -2333,6 +2333,31 @@ DROP TABLE t1; SET @@SESSION.max_sort_length=DEFAULT; SET sql_mode=DEFAULT; +--echo # +--echo # MDEV-25994 Crash with union of my_decimal type in ORDER BY clause +--echo # + +CREATE TABLE t1 (v1 INTEGER) ; +INSERT INTO t1 (v1) VALUES (8); +--error ER_SUBQUERY_NO_1_ROW +UPDATE t1 SET v1 = 1 ORDER BY (SELECT 1.1 UNION SELECT -1); +--echo # This one must be successful +UPDATE t1 SET v1 = 2 ORDER BY (SELECT 1 UNION SELECT 1); +--error ER_SUBQUERY_NO_1_ROW +UPDATE t1 SET v1 = 3 ORDER BY (SELECT 'a' UNION SELECT 'b'); + +-- echo # Insert some more data +INSERT INTO t1 (v1) VALUES (8),(9),(100),(-234),(46584),(0); +--error ER_SUBQUERY_NO_1_ROW +UPDATE t1 SET v1 = v1+1 ORDER BY (SELECT 100.122 UNION SELECT -189.2); +--echo # This one must be successful +UPDATE t1 SET v1 = v1-200 ORDER BY (SELECT 1 UNION SELECT 1); +--error ER_SUBQUERY_NO_1_ROW +UPDATE t1 SET v1 = v1 ORDER BY (SELECT 'abc' UNION SELECT 'bbb'); + + +DROP TABLE t1; + --echo # End of 10.2 tests diff --git a/mysql-test/main/parser.result b/mysql-test/main/parser.result index 526ef3c2931..54d1e6cbf9b 100644 --- a/mysql-test/main/parser.result +++ b/mysql-test/main/parser.result @@ -1364,6 +1364,48 @@ SELECT tmp 1.e.test FROM scientific_notation AS tmp; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1.e.test FROM scientific_notation AS tmp' at line 1 DROP TABLE scientific_notation; # +# MDEV-6899 extra semicolon in show create event syntax +# +set timestamp=unix_timestamp('2020-10-10 5:5:5'); +create table t1 (a int); +create trigger a before insert on t1 for each row set @a:=1;select 2$ +2 +2 +show create trigger a; +Trigger a +sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +SQL Original Statement CREATE DEFINER=`root`@`localhost` trigger a before insert on t1 for each row set @a:=1 +character_set_client latin1 +collation_connection latin1_swedish_ci +Database Collation latin1_swedish_ci +Created 2020-10-10 05:05:05.00 +drop table t1; +create procedure a() select 1;select 2$ +2 +2 +show create procedure a; +Procedure a +sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `a`() +select 1 +character_set_client latin1 +collation_connection latin1_swedish_ci +Database Collation latin1_swedish_ci +drop procedure a; +create function a() returns int return 1;select 2$ +2 +2 +show create function a; +Function a +sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `a`() RETURNS int(11) +return 1 +character_set_client latin1 +collation_connection latin1_swedish_ci +Database Collation latin1_swedish_ci +drop function a; +set timestamp=default; +# # End of 10.2 tests # # diff --git a/mysql-test/main/parser.test b/mysql-test/main/parser.test index 873949c7332..6ce87e80174 100644 --- a/mysql-test/main/parser.test +++ b/mysql-test/main/parser.test @@ -1403,6 +1403,30 @@ SELECT tmp 1.e.test FROM scientific_notation AS tmp; DROP TABLE scientific_notation; --echo # +--echo # MDEV-6899 extra semicolon in show create event syntax +--echo # +set timestamp=unix_timestamp('2020-10-10 5:5:5'); +create table t1 (a int); +delimiter $; +create trigger a before insert on t1 for each row set @a:=1;select 2$ +delimiter ;$ +query_vertical show create trigger a; +drop table t1; + +delimiter $; +create procedure a() select 1;select 2$ +delimiter ;$ +query_vertical show create procedure a; +drop procedure a; + +delimiter $; +create function a() returns int return 1;select 2$ +delimiter ;$ +query_vertical show create function a; +drop function a; +set timestamp=default; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/parser_not_embedded.result b/mysql-test/main/parser_not_embedded.result index 25349e51577..2147e25b3b1 100644 --- a/mysql-test/main/parser_not_embedded.result +++ b/mysql-test/main/parser_not_embedded.result @@ -102,3 +102,23 @@ ROLLBACK AND NO CHAIN NO RELEASE; # # End of 5.5 tests # +# +# MDEV-6899 extra semicolon in show create event syntax +# +set timestamp=unix_timestamp('2020-10-10 5:5:5'); +create event a on schedule every 1 day do set @a:=1;select 2$ +2 +2 +show create event a; +Event a +sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION +time_zone SYSTEM +Create Event CREATE DEFINER=`root`@`localhost` EVENT `a` ON SCHEDULE EVERY 1 DAY STARTS '2020-10-10 05:05:05' ON COMPLETION NOT PRESERVE ENABLE DO set @a:=1 +character_set_client latin1 +collation_connection latin1_swedish_ci +Database Collation latin1_swedish_ci +drop event a; +set timestamp=default; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/parser_not_embedded.test b/mysql-test/main/parser_not_embedded.test index 3af1260f4ad..270573ece93 100644 --- a/mysql-test/main/parser_not_embedded.test +++ b/mysql-test/main/parser_not_embedded.test @@ -99,3 +99,18 @@ ROLLBACK AND NO CHAIN NO RELEASE; --echo # --echo # End of 5.5 tests --echo # + +--echo # +--echo # MDEV-6899 extra semicolon in show create event syntax +--echo # +set timestamp=unix_timestamp('2020-10-10 5:5:5'); +delimiter $; +create event a on schedule every 1 day do set @a:=1;select 2$ +delimiter ;$ +query_vertical show create event a; +drop event a; +set timestamp=default; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/main/plugin.result b/mysql-test/main/plugin.result index 6cc6afd38ff..032bf489f3d 100644 --- a/mysql-test/main/plugin.result +++ b/mysql-test/main/plugin.result @@ -357,6 +357,15 @@ select * from mysql.plugin WHERE name='unexisting_plugin'; name dl UNINSTALL PLUGIN unexisting_plugin; ERROR 42000: PLUGIN unexisting_plugin does not exist +# +# MDEV-26323 use-after-poison issue of MariaDB server +# +INSTALL PLUGIN DEALLOCATE SONAME ''; +ERROR HY000: Can't open shared library '.so' +INSTALL PLUGIN DEALLOCATE SONAME 'x'; +ERROR HY000: Can't open shared library 'x.so' +INSTALL PLUGIN DEALLOCATE SONAME 'xx'; +ERROR HY000: Can't open shared library 'xx.so' # End of 10.2 tests # # MDEV-16294: INSTALL PLUGIN IF NOT EXISTS / UNINSTALL PLUGIN IF EXISTS diff --git a/mysql-test/main/plugin.test b/mysql-test/main/plugin.test index 60773c3e190..967364a3e03 100644 --- a/mysql-test/main/plugin.test +++ b/mysql-test/main/plugin.test @@ -296,6 +296,23 @@ select * from mysql.plugin WHERE name='unexisting_plugin'; --error ER_SP_DOES_NOT_EXIST UNINSTALL PLUGIN unexisting_plugin; +--echo # +--echo # MDEV-26323 use-after-poison issue of MariaDB server +--echo # + +--replace_regex /library '.*[\\/].(dll|so)' [(].*[)]/library '.so'/ +--error ER_CANT_OPEN_LIBRARY +INSTALL PLUGIN DEALLOCATE SONAME ''; + +--replace_regex /library '.*[\\/]x.(dll|so)' [(].*[)]/library 'x.so'/ +--error ER_CANT_OPEN_LIBRARY +INSTALL PLUGIN DEALLOCATE SONAME 'x'; + +--replace_regex /library '.*[\\/]xx.(dll|so)' [(].*[)]/library 'xx.so'/ +--error ER_CANT_OPEN_LIBRARY +INSTALL PLUGIN DEALLOCATE SONAME 'xx'; + + --echo # End of 10.2 tests --source include/install_plugin_if_exists.inc diff --git a/mysql-test/main/repair_symlink-5543.result b/mysql-test/main/repair_symlink-5543.result index 2024c9f5684..e99bd4ea312 100644 --- a/mysql-test/main/repair_symlink-5543.result +++ b/mysql-test/main/repair_symlink-5543.result @@ -1,7 +1,5 @@ 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, -# while others don't have openat and fail with errcode 20. repair table t1; Table Op Msg_type Msg_text test.t1 repair error 20 for record at pos 0 diff --git a/mysql-test/main/repair_symlink-5543.test b/mysql-test/main/repair_symlink-5543.test index 7c4ad7db0dc..002b8d8023b 100644 --- a/mysql-test/main/repair_symlink-5543.test +++ b/mysql-test/main/repair_symlink-5543.test @@ -9,9 +9,10 @@ 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 # while others don't have openat and fail with errcode 20. ---replace_regex / '.*\/t1/ 'MYSQL_TMP_DIR\/t1/ /[49]0/20/ /85/20/ /".*"/"<errmsg>"/ +# Some systems fail with errcode 31 (FreeBSD), 40 (Linux), 85 (AIX), +# or 90 (MIPS) when doing openat, +# while others don't have openat and fail with errcode 20. +--replace_regex / '.*\/t1/ 'MYSQL_TMP_DIR\/t1/ /[49]0|31|85/20/ /".*"/"<errmsg>"/ repair table t1; drop table t1; @@ -19,7 +20,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/ /85/20/ /".*"/"<errmsg>"/ +--replace_regex / '.*\/t2/ 'MYSQL_TMP_DIR\/t2/ /[49]0|31|85/20/ /".*"/"<errmsg>"/ repair table t2; drop table t2; diff --git a/mysql-test/main/row-checksum-old.result b/mysql-test/main/row-checksum-old.result index d374013f61c..7caed4fc7dc 100644 --- a/mysql-test/main/row-checksum-old.result +++ b/mysql-test/main/row-checksum-old.result @@ -1,4 +1,3 @@ -drop table if exists t1; create table t1 (a int null, v varchar(100)) engine=myisam checksum=0; insert into t1 values(null, null), (1, "hello"); checksum table t1; @@ -98,4 +97,48 @@ CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 2326430205 drop table t1; +# # End of 5.5 tests +# +# +# MDEV-28020 CHECKSUM TABLE calculates different checksums +# +create table t1 ( a int, b int as (a) virtual, c text) engine=myisam checksum=1; +insert ignore t1 values (1,2,'foo'),(2,3,'bar'); +Warnings: +Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored +Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored +checksum table t1 extended; +Table Checksum +test.t1 4101438232 +checksum table t1; +Table Checksum +test.t1 4101438232 +drop table t1; +create table t1 ( a int, b int as (a) virtual, c text, key(b)) engine=myisam checksum=1; +insert ignore t1 values (1,2,'foo'),(2,3,'bar'); +Warnings: +Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored +Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored +checksum table t1 extended; +Table Checksum +test.t1 4101438232 +checksum table t1; +Table Checksum +test.t1 4101438232 +drop table t1; +create table t1 ( a int, b int as (a) stored, c text) engine=myisam checksum=1; +insert ignore t1 values (1,2,'foo'),(2,3,'bar'); +Warnings: +Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored +Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored +checksum table t1 extended; +Table Checksum +test.t1 2897795735 +checksum table t1; +Table Checksum +test.t1 2897795735 +drop table t1; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/row-checksum.result b/mysql-test/main/row-checksum.result index 4625e09c060..c80ca4eed9a 100644 --- a/mysql-test/main/row-checksum.result +++ b/mysql-test/main/row-checksum.result @@ -1,4 +1,3 @@ -drop table if exists t1; create table t1 (a int null, v varchar(100)) engine=myisam checksum=0; insert into t1 values(null, null), (1, "hello"); checksum table t1; @@ -98,4 +97,48 @@ CHECKSUM TABLE t1 EXTENDED; Table Checksum test.t1 2326430205 drop table t1; +# # End of 5.5 tests +# +# +# MDEV-28020 CHECKSUM TABLE calculates different checksums +# +create table t1 ( a int, b int as (a) virtual, c text) engine=myisam checksum=1; +insert ignore t1 values (1,2,'foo'),(2,3,'bar'); +Warnings: +Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored +Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored +checksum table t1 extended; +Table Checksum +test.t1 4101438232 +checksum table t1; +Table Checksum +test.t1 4101438232 +drop table t1; +create table t1 ( a int, b int as (a) virtual, c text, key(b)) engine=myisam checksum=1; +insert ignore t1 values (1,2,'foo'),(2,3,'bar'); +Warnings: +Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored +Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored +checksum table t1 extended; +Table Checksum +test.t1 4101438232 +checksum table t1; +Table Checksum +test.t1 4101438232 +drop table t1; +create table t1 ( a int, b int as (a) stored, c text) engine=myisam checksum=1; +insert ignore t1 values (1,2,'foo'),(2,3,'bar'); +Warnings: +Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored +Warning 1906 The value specified for generated column 'b' in table 't1' has been ignored +checksum table t1 extended; +Table Checksum +test.t1 2897795735 +checksum table t1; +Table Checksum +test.t1 2897795735 +drop table t1; +# +# End of 10.2 tests +# diff --git a/mysql-test/main/row-checksum.test b/mysql-test/main/row-checksum.test index 5acfda45f75..3b510abba84 100644 --- a/mysql-test/main/row-checksum.test +++ b/mysql-test/main/row-checksum.test @@ -2,12 +2,8 @@ # Test checksum # --- source include/have_innodb.inc --- source include/have_maria.inc - ---disable_warnings -drop table if exists t1; ---enable_warnings +--source include/have_innodb.inc +--source include/have_maria.inc create table t1 (a int null, v varchar(100)) engine=myisam checksum=0; insert into t1 values(null, null), (1, "hello"); @@ -76,4 +72,31 @@ CHECKSUM TABLE t1 EXTENDED; drop table t1; +--echo # --echo # End of 5.5 tests +--echo # + +--echo # +--echo # MDEV-28020 CHECKSUM TABLE calculates different checksums +--echo # +create table t1 ( a int, b int as (a) virtual, c text) engine=myisam checksum=1; +insert ignore t1 values (1,2,'foo'),(2,3,'bar'); +checksum table t1 extended; +checksum table t1; +drop table t1; + +create table t1 ( a int, b int as (a) virtual, c text, key(b)) engine=myisam checksum=1; +insert ignore t1 values (1,2,'foo'),(2,3,'bar'); +checksum table t1 extended; +checksum table t1; +drop table t1; + +create table t1 ( a int, b int as (a) stored, c text) engine=myisam checksum=1; +insert ignore t1 values (1,2,'foo'),(2,3,'bar'); +checksum table t1 extended; +checksum table t1; +drop table t1; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 167382fc328..22a814c1756 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -1259,7 +1259,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(3) NOT NULL + `a` int(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index cadb6e7bced..070be6dcf19 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -2906,6 +2906,84 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY tn eq_ref PRIMARY PRIMARY 32 test.tms.key1 1 Using where set optimizer_switch=@tmp_os; drop table t1, t10, t11; +# +# MDEV-28268: Server crashes in Expression_cache_tracker::fetch_current_stats +# +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2),(3,4); +ANALYZE FORMAT=JSON +SELECT DISTINCT +(SELECT MIN(a) FROM t1 WHERE b <= ANY (SELECT a FROM t1)) AS f +FROM t1; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "duplicate_removal": { + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "subqueries": [ + { + "expression_cache": { + "state": "disabled", + "r_loops": 0, + "query_block": { + "select_id": 2, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 50, + "attached_condition": "<nop>(<in_optimizer>(t1.b,(subquery#3) >= 4))" + }, + "subqueries": [ + { + "query_block": { + "select_id": 3, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + ] + } + } + } + ] + } + } + } +} +DROP TABLE t1; # End of 10.2 tests # End of 10.3 tests # diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index a2523beeaef..95e211292ae 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2423,6 +2423,21 @@ set optimizer_switch=@tmp_os; drop table t1, t10, t11; +--echo # +--echo # MDEV-28268: Server crashes in Expression_cache_tracker::fetch_current_stats +--echo # +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,2),(3,4); + +--source include/analyze-format.inc +ANALYZE FORMAT=JSON +SELECT DISTINCT + (SELECT MIN(a) FROM t1 WHERE b <= ANY (SELECT a FROM t1)) AS f +FROM t1; + +# Cleanup +DROP TABLE t1; + --echo # End of 10.2 tests --echo # End of 10.3 tests diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result index 73ca2640491..00a013b4468 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 # @@ -577,7 +580,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t2 ref key1 key1 5 test.t1.a # Using where; Using filesort drop table t1,t2; # -# mdev-12931: semi-join in ON expression of STRAIGHT_JOIN +# MDEV-12931: semi-join in ON expression of STRAIGHT_JOIN # joining a base table and a mergeable derived table # CREATE TABLE t1 (f1 int) ENGINE=InnoDB; @@ -629,6 +632,52 @@ a b 2019-03-10 02:55:05 2019-03-10 02:55:05 DROP TABLE t1,t2; set character_set_connection=@save_character_set_connection; +# +# MDEV-26047: MariaDB server crash at Item_subselect::init_expr_cache_tracker +# +CREATE TABLE t1 (a int) engine=innodb; +SELECT 1 IN ( +SELECT NULL +FROM t1 +WHERE +a IS NOT NULL +GROUP BY +(SELECT NULL from dual WHERE a = 1) +); +1 IN ( +SELECT NULL +FROM t1 +WHERE +a IS NOT NULL +GROUP BY +(SELECT NULL from dual WHERE a = 1) +) +0 +drop table t1; +# Testcase from MDEV-26164 +create table t1(a int); +# Disable the warning as it includes current time and changes for every test run. +select 1 from t1 where not exists +( +select 1 from t1 where binary current_time() +group by (select a),(select 1) +); +1 +drop table t1; +# +# MDEV-28437: Assertion `!eliminated' failed in Item_subselect::exec +# +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (3),(4); +SELECT 1 IN (SELECT a FROM t1 LEFT JOIN t2 ON (a = b AND EXISTS (SELECT * FROM t1))); +1 IN (SELECT a FROM t1 LEFT JOIN t2 ON (a = b AND EXISTS (SELECT * FROM t1))) +1 +drop table t1,t2; +# +# End of 10.2 tests +# # # MDEV-17362: SIGSEGV in JOIN::optimize_inner or Assertion `fixed == 0' # failed in Item_equal::fix_fields, server crashes after 2nd execution @@ -662,4 +711,18 @@ a b 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 37f8f40200e..fef954edc28 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); @@ -578,7 +575,7 @@ from drop table t1,t2; --echo # ---echo # mdev-12931: semi-join in ON expression of STRAIGHT_JOIN +--echo # MDEV-12931: semi-join in ON expression of STRAIGHT_JOIN --echo # joining a base table and a mergeable derived table --echo # @@ -628,6 +625,49 @@ DROP TABLE t1,t2; set character_set_connection=@save_character_set_connection; +--echo # +--echo # MDEV-26047: MariaDB server crash at Item_subselect::init_expr_cache_tracker +--echo # +CREATE TABLE t1 (a int) engine=innodb; + +SELECT 1 IN ( + SELECT NULL + FROM t1 + WHERE + a IS NOT NULL + GROUP BY + (SELECT NULL from dual WHERE a = 1) +); +drop table t1; + +--echo # Testcase from MDEV-26164 +create table t1(a int); +--echo # Disable the warning as it includes current time and changes for every test run. +--disable_warnings +select 1 from t1 where not exists +( + select 1 from t1 where binary current_time() + group by (select a),(select 1) +); +--enable_warnings +drop table t1; + +--echo # +--echo # MDEV-28437: Assertion `!eliminated' failed in Item_subselect::exec +--echo # +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (3),(4); + +SELECT 1 IN (SELECT a FROM t1 LEFT JOIN t2 ON (a = b AND EXISTS (SELECT * FROM t1))); + +drop table t1,t2; + +--echo # +--echo # End of 10.2 tests +--echo # + --echo # --echo # MDEV-17362: SIGSEGV in JOIN::optimize_inner or Assertion `fixed == 0' --echo # failed in Item_equal::fix_fields, server crashes after 2nd execution @@ -661,4 +701,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 # diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 70add4d1740..def116c0f52 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -1263,7 +1263,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(3) NOT NULL + `a` int(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index f0c7033803c..7eb37343051 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -1266,7 +1266,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(3) NOT NULL + `a` int(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 8ff79c1790b..f2981c0c25f 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -1262,7 +1262,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(3) NOT NULL + `a` int(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 37ca0d4c030..17bec03c53d 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -1265,7 +1265,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(3) NOT NULL + `a` int(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index 1abdde5ff7f..98b0719f993 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -1262,7 +1262,7 @@ a SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` int(3) NOT NULL + `a` int(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a int); diff --git a/mysql-test/main/type_newdecimal.result b/mysql-test/main/type_newdecimal.result index 13974c84e15..b76ba029ad6 100644 --- a/mysql-test/main/type_newdecimal.result +++ b/mysql-test/main/type_newdecimal.result @@ -2456,6 +2456,30 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; # +# MDEV-25317 Assertion `scale <= precision' failed in +# decimal_bin_size And Assertion `scale >= 0 && precision > 0 && scale <= precision' +# failed in decimal_bin_size_inline/decimal_bin_size. +# +SELECT AVG(DISTINCT 0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001); +AVG(DISTINCT 0.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 +0.00000000000000000000000000000000000000 +CREATE TABLE t1 AS SELECT NULL AS v1; +SELECT 1 FROM t1 GROUP BY v1 ORDER BY AVG ( from_unixtime ( '' ) ) ; +1 +1 +Warnings: +Warning 1292 Truncated incorrect DECIMAL value: '' +DROP TABLE t1; +SELECT SUM(DISTINCT 0.000000000000000000000000000000000000001); +SUM(DISTINCT 0.000000000000000000000000000000000000001) +0.00000000000000000000000000000000000000 +CREATE TABLE t1 AS SELECT 1.000000000000000000000000000000000 AS a; +ALTER TABLE t1 ADD COLUMN b INT; +SELECT ROUND (a,b) AS c FROM t1 ORDER BY c; +c +NULL +DROP TABLE t1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/type_newdecimal.test b/mysql-test/main/type_newdecimal.test index 5b7ecf89a07..011c6dbc743 100644 --- a/mysql-test/main/type_newdecimal.test +++ b/mysql-test/main/type_newdecimal.test @@ -1895,6 +1895,25 @@ show create table t1; drop table t1; --echo # +--echo # MDEV-25317 Assertion `scale <= precision' failed in +--echo # decimal_bin_size And Assertion `scale >= 0 && precision > 0 && scale <= precision' +--echo # failed in decimal_bin_size_inline/decimal_bin_size. +--echo # + +SELECT AVG(DISTINCT 0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001); + +CREATE TABLE t1 AS SELECT NULL AS v1; +SELECT 1 FROM t1 GROUP BY v1 ORDER BY AVG ( from_unixtime ( '' ) ) ; +DROP TABLE t1; + +SELECT SUM(DISTINCT 0.000000000000000000000000000000000000001); + +CREATE TABLE t1 AS SELECT 1.000000000000000000000000000000000 AS a; +ALTER TABLE t1 ADD COLUMN b INT; +SELECT ROUND (a,b) AS c FROM t1 ORDER BY c; +DROP TABLE t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result index 12e96335bdf..1f2b70b1a5a 100644 --- a/mysql-test/main/win.result +++ b/mysql-test/main/win.result @@ -4239,6 +4239,48 @@ SELECT 1 UNION SELECT a FROM t1 ORDER BY (row_number() over ()); ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION DROP TABLE t1; # +# MDEV-19398: Assertion `item1->type() == Item::FIELD_ITEM && +# item2->type() == Item::FIELD_ITEM' failed in compare_order_elements +# +CREATE TABLE t1 ( id varchar(10)); +INSERT INTO t1 values (1),(2),(3); +SELECT +dense_rank() over (ORDER BY avg(1)+3), +rank() over (ORDER BY avg(1)) +FROM t1 +GROUP BY nullif(id, 15532); +dense_rank() over (ORDER BY avg(1)+3) rank() over (ORDER BY avg(1)) +1 1 +1 1 +1 1 +SELECT +dense_rank() over (ORDER BY avg(1)), +rank() over (ORDER BY avg(1)) +FROM t1 +GROUP BY nullif(id, 15532); +dense_rank() over (ORDER BY avg(1)) rank() over (ORDER BY avg(1)) +1 1 +1 1 +1 1 +drop table t1; +CREATE TABLE t1 ( a char(25), b text); +INSERT INTO t1 VALUES ('foo','bar'); +SELECT +SUM(b) OVER (PARTITION BY a), +ROW_NUMBER() OVER (PARTITION BY b) +FROM t1 +GROUP BY +LEFT((SYSDATE()), 'foo') +WITH ROLLUP; +SUM(b) OVER (PARTITION BY a) ROW_NUMBER() OVER (PARTITION BY b) +NULL 1 +NULL 1 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: 'foo' +Warning 1292 Truncated incorrect INTEGER value: 'foo' +drop table t1; +# +# # End of 10.2 tests # # diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test index 56e7b4baf8c..d5fdd1e79f0 100644 --- a/mysql-test/main/win.test +++ b/mysql-test/main/win.test @@ -2740,6 +2740,39 @@ SELECT 1 UNION SELECT a FROM t1 ORDER BY (row_number() over ()); DROP TABLE t1; --echo # +--echo # MDEV-19398: Assertion `item1->type() == Item::FIELD_ITEM && +--echo # item2->type() == Item::FIELD_ITEM' failed in compare_order_elements +--echo # +CREATE TABLE t1 ( id varchar(10)); +INSERT INTO t1 values (1),(2),(3); + +SELECT + dense_rank() over (ORDER BY avg(1)+3), + rank() over (ORDER BY avg(1)) +FROM t1 +GROUP BY nullif(id, 15532); + +SELECT + dense_rank() over (ORDER BY avg(1)), + rank() over (ORDER BY avg(1)) +FROM t1 +GROUP BY nullif(id, 15532); +drop table t1; + +CREATE TABLE t1 ( a char(25), b text); +INSERT INTO t1 VALUES ('foo','bar'); + +SELECT + SUM(b) OVER (PARTITION BY a), + ROW_NUMBER() OVER (PARTITION BY b) +FROM t1 +GROUP BY + LEFT((SYSDATE()), 'foo') +WITH ROLLUP; +drop table t1; + +--echo # +--echo # --echo # End of 10.2 tests --echo # 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 # |