diff options
Diffstat (limited to 'mysql-test/main')
27 files changed, 470 insertions, 28 deletions
diff --git a/mysql-test/main/alter_table.result b/mysql-test/main/alter_table.result index a896d24d510..7a88f9165da 100644 --- a/mysql-test/main/alter_table.result +++ b/mysql-test/main/alter_table.result @@ -2467,6 +2467,55 @@ ERROR 23000: Duplicate entry '1' for key 'i' UNLOCK TABLES; DROP TABLE t1; # +# MDEV-17599 ALTER TABLE DROP CONSTRAINT does not work for foreign keys. +# +CREATE TABLE t1(id INT PRIMARY KEY, c1 INT) ENGINE= INNODB; +CREATE TABLE t2(id INT PRIMARY KEY, c1 INT, c2 INT NOT NULL, +CONSTRAINT sid FOREIGN KEY (`c1`) REFERENCES t1 (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, +CONSTRAINT UNIQUE `ui`(c2)) ENGINE= INNODB; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) NOT NULL, + `c1` int(11) DEFAULT NULL, + `c2` int(11) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `ui` (`c2`), + KEY `sid` (`c1`), + CONSTRAINT `sid` FOREIGN KEY (`c1`) REFERENCES `t1` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t2 DROP CONSTRAINT sid; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) NOT NULL, + `c1` int(11) DEFAULT NULL, + `c2` int(11) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `ui` (`c2`), + KEY `sid` (`c1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t2 DROP CONSTRAINT ui; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) NOT NULL, + `c1` int(11) DEFAULT NULL, + `c2` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `sid` (`c1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +ALTER TABLE t2 DROP CONSTRAINT PRIMARY KEY; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `id` int(11) NOT NULL, + `c1` int(11) DEFAULT NULL, + `c2` int(11) NOT NULL, + KEY `sid` (`c1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t2, t1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/alter_table.test b/mysql-test/main/alter_table.test index 829f4013cb3..0fab5575f13 100644 --- a/mysql-test/main/alter_table.test +++ b/mysql-test/main/alter_table.test @@ -2019,6 +2019,23 @@ DROP TABLE t1; --echo # +--echo # MDEV-17599 ALTER TABLE DROP CONSTRAINT does not work for foreign keys. +--echo # + +CREATE TABLE t1(id INT PRIMARY KEY, c1 INT) ENGINE= INNODB; +CREATE TABLE t2(id INT PRIMARY KEY, c1 INT, c2 INT NOT NULL, + CONSTRAINT sid FOREIGN KEY (`c1`) REFERENCES t1 (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT UNIQUE `ui`(c2)) ENGINE= INNODB; +SHOW CREATE TABLE t2; +ALTER TABLE t2 DROP CONSTRAINT sid; +SHOW CREATE TABLE t2; +ALTER TABLE t2 DROP CONSTRAINT ui; +SHOW CREATE TABLE t2; +ALTER TABLE t2 DROP CONSTRAINT PRIMARY KEY; +SHOW CREATE TABLE t2; +DROP TABLE t2, t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/alter_table_errors.result b/mysql-test/main/alter_table_errors.result index b26409e3d05..1e2a8100e84 100644 --- a/mysql-test/main/alter_table_errors.result +++ b/mysql-test/main/alter_table_errors.result @@ -27,3 +27,9 @@ t2 CREATE TEMPORARY TABLE `t2` ( `a` int(11) DEFAULT NULL, `v` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop temporary table t1, t2; +create temporary table t1 (a int); +alter table t1 add column f text; +insert into t1 values ('x','foo'); +ERROR 22007: Incorrect integer value: 'x' for column `test`.`t1`.`a` at row 1 +drop temporary table t1; diff --git a/mysql-test/main/alter_table_errors.test b/mysql-test/main/alter_table_errors.test index 8fa65b0f330..8726410ea0a 100644 --- a/mysql-test/main/alter_table_errors.test +++ b/mysql-test/main/alter_table_errors.test @@ -19,3 +19,13 @@ lock table t2 write; --error ER_ALTER_OPERATION_NOT_SUPPORTED alter table t2 change column a b int, algorithm=inplace; show create table t2; +drop temporary table t1, t2; + +# +# MDEV-18083 ASAN heap-use-after-free in Field::set_warning_truncated_wrong_value upon inserting into temporary table +# +create temporary table t1 (a int); +alter table t1 add column f text; +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +insert into t1 values ('x','foo'); +drop temporary table t1; diff --git a/mysql-test/main/check.result b/mysql-test/main/check.result index e882a4cdbe6..11ed734da0c 100644 --- a/mysql-test/main/check.result +++ b/mysql-test/main/check.result @@ -95,3 +95,10 @@ Warnings: Warning 4025 CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` alter table t1; drop table t1; +create temporary table t1 (a int default 0, check (a > 0)); +alter table t1 drop constraint if exists non_existing_constraint; +Warnings: +Note 1091 Can't DROP CONSTRAINT `non_existing_constraint`; check that it exists +insert into t1 () values (); +ERROR 23000: CONSTRAINT `CONSTRAINT_1` failed for `test`.`t1` +drop table t1; diff --git a/mysql-test/main/check.test b/mysql-test/main/check.test index 475a7996a09..29587a9f623 100644 --- a/mysql-test/main/check.test +++ b/mysql-test/main/check.test @@ -115,3 +115,12 @@ insert into t1 () values (); alter ignore table t1 add constraint check (f > 0); alter table t1; drop table t1; + +# +# MDEV-16905 ASAN heap-use-after-free in __interceptor_strnlen / ... / TABLE::verify_constraints upon INSERT into temporary table with CHECK constraint +# +create temporary table t1 (a int default 0, check (a > 0)); +alter table t1 drop constraint if exists non_existing_constraint; +--error ER_CONSTRAINT_FAILED +insert into t1 () values (); +drop table t1; diff --git a/mysql-test/main/check_constraint.result b/mysql-test/main/check_constraint.result index 9a228f9ccc7..8cb1066ba9a 100644 --- a/mysql-test/main/check_constraint.result +++ b/mysql-test/main/check_constraint.result @@ -44,7 +44,7 @@ a b 103 103 set check_constraint_checks=@save_check_constraint; alter table t1 add c int default 0 check (c < 10); -ERROR 23000: CONSTRAINT `max` failed for table +ERROR 23000: CONSTRAINT `max` failed for `test`.`t1` set check_constraint_checks=0; alter table t1 add c int default 0 check (c < 10); alter table t1 add check (a+b+c < 500); diff --git a/mysql-test/main/check_constraint.test b/mysql-test/main/check_constraint.test index be06c3ec579..37fdc7203b1 100644 --- a/mysql-test/main/check_constraint.test +++ b/mysql-test/main/check_constraint.test @@ -27,7 +27,6 @@ insert into t1 values (101,101),(102,102),(600,600),(103,103); select * from t1; set check_constraint_checks=@save_check_constraint; ---replace_regex /failed for.*/failed for table/ --error ER_CONSTRAINT_FAILED alter table t1 add c int default 0 check (c < 10); diff --git a/mysql-test/main/check_constraint_innodb.result b/mysql-test/main/check_constraint_innodb.result new file mode 100644 index 00000000000..45af3f512b7 --- /dev/null +++ b/mysql-test/main/check_constraint_innodb.result @@ -0,0 +1,9 @@ +create table t1 (a int, b smallint) engine=innodb; +connect con1,localhost,root,,test; +alter table t1 add constraint check (b < 8); +alter table t1 modify column b int, algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +connection default; +alter table t1 add primary key (a); +drop table t1; +disconnect con1; diff --git a/mysql-test/main/check_constraint_innodb.test b/mysql-test/main/check_constraint_innodb.test new file mode 100644 index 00000000000..d02aa320e84 --- /dev/null +++ b/mysql-test/main/check_constraint_innodb.test @@ -0,0 +1,14 @@ +--source include/have_innodb.inc + +# +# MDEV-18239 ASAN use-after-poison in process_str_arg / ... / mark_unsupported_func or unexpected ER_BAD_FIELD_ERROR upon ALTER TABLE +# +create table t1 (a int, b smallint) engine=innodb; +connect con1,localhost,root,,test; +alter table t1 add constraint check (b < 8); +error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON; +alter table t1 modify column b int, algorithm=inplace; +connection default; +alter table t1 add primary key (a); +drop table t1; +disconnect con1; diff --git a/mysql-test/main/disabled.def b/mysql-test/main/disabled.def index b489139a59f..b6991cc1d37 100644 --- a/mysql-test/main/disabled.def +++ b/mysql-test/main/disabled.def @@ -21,3 +21,4 @@ innodb-wl5522-debug-zip : broken upstream innodb_bug12902967 : broken upstream file_contents : MDEV-6526 these files are not installed anymore max_statement_time : cannot possibly work, depends on timing +partition_open_files_limit : open_files_limit check broken by MDEV-18360 diff --git a/mysql-test/main/error_simulation.result b/mysql-test/main/error_simulation.result index e0ec26b2d1c..7e728d24fc8 100644 --- a/mysql-test/main/error_simulation.result +++ b/mysql-test/main/error_simulation.result @@ -1,6 +1,3 @@ -DROP TABLE IF EXISTS t1; -Warnings: -Note 1051 Unknown table 'test.t1' CREATE TABLE t1 ( a varchar(32) character set utf8 collate utf8_bin NOT NULL, b varchar(32) character set utf8 collate utf8_bin NOT NULL ) @@ -15,7 +12,7 @@ INSERT INTO t1 VALUES set tmp_table_size=1024; set session debug_dbug="+d,raise_error"; SELECT MAX(a) FROM t1 GROUP BY a,b; -ERROR 23000: Can't write; duplicate key in table 'tmp_table' +ERROR 23000: Can't write; duplicate key in table '(temporary)' set tmp_table_size=default; DROP TABLE t1; # diff --git a/mysql-test/main/error_simulation.test b/mysql-test/main/error_simulation.test index beaaf603754..1debed871c7 100644 --- a/mysql-test/main/error_simulation.test +++ b/mysql-test/main/error_simulation.test @@ -1,12 +1,9 @@ --- source include/have_debug.inc +--source include/have_debug.inc --source include/not_embedded.inc # # Bug #28499: crash for grouping query when tmp_table_size is too small # - -DROP TABLE IF EXISTS t1; - CREATE TABLE t1 ( a varchar(32) character set utf8 collate utf8_bin NOT NULL, b varchar(32) character set utf8 collate utf8_bin NOT NULL ) @@ -26,14 +23,12 @@ set tmp_table_size=1024; # tmp table in query is converted from heap to myisam set session debug_dbug="+d,raise_error"; ---replace_regex /in table '[^']+'/in table 'tmp_table'/ --error ER_DUP_KEY SELECT MAX(a) FROM t1 GROUP BY a,b; set tmp_table_size=default; DROP TABLE t1; - --echo # --echo # Bug #50946: fast index creation still seems to copy the table --echo # diff --git a/mysql-test/main/func_misc.result b/mysql-test/main/func_misc.result index 0ab07664e6c..49f08356471 100644 --- a/mysql-test/main/func_misc.result +++ b/mysql-test/main/func_misc.result @@ -1483,6 +1483,14 @@ ip_full_addr 2000:: DROP PROCEDURE p1; # +# MDEV-18195 ASAN use-after-poison in my_strcasecmp_utf8 / Item::eq upon prepared statement with ORDER BY NAME_CONST +# +PREPARE stmt FROM "SELECT 'x' ORDER BY NAME_CONST( 'f', 'foo' )"; +EXECUTE stmt; +x +x +DEALLOCATE PREPARE stmt; +# # Start of 10.2 tests # # diff --git a/mysql-test/main/func_misc.test b/mysql-test/main/func_misc.test index f8f2147e182..e4a2cd22f69 100644 --- a/mysql-test/main/func_misc.test +++ b/mysql-test/main/func_misc.test @@ -1130,6 +1130,14 @@ DELIMITER ;$$ CALL p1(); DROP PROCEDURE p1; +--echo # +--echo # MDEV-18195 ASAN use-after-poison in my_strcasecmp_utf8 / Item::eq upon prepared statement with ORDER BY NAME_CONST +--echo # + +PREPARE stmt FROM "SELECT 'x' ORDER BY NAME_CONST( 'f', 'foo' )"; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + --echo # --echo # Start of 10.2 tests diff --git a/mysql-test/main/gis.test b/mysql-test/main/gis.test index d22591bbe69..fbc130fcb7e 100644 --- a/mysql-test/main/gis.test +++ b/mysql-test/main/gis.test @@ -762,7 +762,6 @@ drop table t1; --echo # on char > 31 bytes". --echo # create table t1(a char(32) not null) engine=myisam; ---replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/ --error ER_WRONG_ARGUMENTS create spatial index i on t1 (a); drop table t1; diff --git a/mysql-test/main/mysql.result b/mysql-test/main/mysql.result index 06ef7e95bc9..02ca052cfc8 100644 --- a/mysql-test/main/mysql.result +++ b/mysql-test/main/mysql.result @@ -600,3 +600,18 @@ a 2 drop table "a1\""b1"; set sql_mode=default; +create table t1 (a text); +select count(*) from t1; +count(*) +41 +truncate table t1; +select count(*) from t1; +count(*) +41 +truncate table t1; +select count(*) from t1; +count(*) +0 +truncate table t1; +### FIXME: update libmariadb +drop table t1; diff --git a/mysql-test/main/mysql.test b/mysql-test/main/mysql.test index c65860eb822..66ccef18327 100644 --- a/mysql-test/main/mysql.test +++ b/mysql-test/main/mysql.test @@ -680,3 +680,26 @@ show create table "a1\""b1"; select * from "a1\""b1"; drop table "a1\""b1"; set sql_mode=default; + +# +# mysql --local-infile +# +--let $ldli = load data local infile '$MYSQLTEST_VARDIR/tmp/bug.sql' into table test.t1; +create table t1 (a text); +--exec $MYSQL -e "$ldli" +select count(*) from t1; truncate table t1; +--exec $MYSQL --enable-local-infile -e "$ldli" +select count(*) from t1; truncate table t1; +--error 1 +--exec $MYSQL --disable-local-infile -e "$ldli" +select count(*) from t1; truncate table t1; +--echo ### FIXME: update libmariadb +#--error 1 +#--exec $MYSQL -e "/*q*/$ldli" +#select count(*) from t1; truncate table t1; +#--exec $MYSQL --enable-local-infile -e "/*q*/$ldli" +#select count(*) from t1; truncate table t1; +# --error 1 +# --exec $MYSQL --disable-local-infile -e "/*q*/$ldli" +#select count(*) from t1; truncate table t1; +drop table t1; diff --git a/mysql-test/main/mysqlbinlog_row_minimal.result b/mysql-test/main/mysqlbinlog_row_minimal.result index 680fd8196a8..a919cda529e 100644 --- a/mysql-test/main/mysqlbinlog_row_minimal.result +++ b/mysql-test/main/mysqlbinlog_row_minimal.result @@ -237,43 +237,64 @@ BEGIN #Q> UPDATE t2 SET f4=5 WHERE f4>0 or f4 is NULL #<date> server id 1 end_log_pos 2591 CRC32 XXX Table_map: `test`.`t2` mapped to number num # at 2591 +<<<<<<< HEAD:mysql-test/main/mysqlbinlog_row_minimal.result #<date> server id 1 end_log_pos 2665 CRC32 XXX Update_rows: table id 33 flags: STMT_END_F +||||||| merged common ancestors +#<date> server id 1 end_log_pos 2665 CRC32 XXX Update_rows: table id 32 flags: STMT_END_F +======= +#<date> server id 1 end_log_pos 2657 CRC32 XXX Update_rows: table id 32 flags: STMT_END_F +>>>>>>> 10.2:mysql-test/r/mysqlbinlog_row_minimal.result ### UPDATE `test`.`t2` ### WHERE ### @1=10 /* INT meta=0 nullable=0 is_null=0 */ -### @5=4 /* INT meta=0 nullable=1 is_null=0 */ ### SET ### @5=5 /* INT meta=0 nullable=1 is_null=0 */ ### UPDATE `test`.`t2` ### WHERE ### @1=11 /* INT meta=0 nullable=0 is_null=0 */ -### @5=4 /* INT meta=0 nullable=1 is_null=0 */ ### SET ### @5=5 /* INT meta=0 nullable=1 is_null=0 */ ### UPDATE `test`.`t2` ### WHERE ### @1=12 /* INT meta=0 nullable=0 is_null=0 */ -### @5=NULL /* INT meta=0 nullable=1 is_null=1 */ ### SET ### @5=5 /* INT meta=0 nullable=1 is_null=0 */ +<<<<<<< HEAD:mysql-test/main/mysqlbinlog_row_minimal.result # Number of rows: 3 # at 2665 #<date> server id 1 end_log_pos 2738 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 +||||||| merged common ancestors +# at 2665 +#<date> server id 1 end_log_pos 2738 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 +======= +# at 2657 +#<date> server id 1 end_log_pos 2730 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 +>>>>>>> 10.2:mysql-test/r/mysqlbinlog_row_minimal.result SET TIMESTAMP=X/*!*/; COMMIT /*!*/; -# at 2738 -#<date> server id 1 end_log_pos 2780 CRC32 XXX GTID 0-1-9 +# at 2730 +#<date> server id 1 end_log_pos 2772 CRC32 XXX GTID 0-1-9 /*!100001 SET @@session.gtid_seq_no=9*//*!*/; BEGIN /*!*/; -# at 2780 -# at 2817 -#<date> server id 1 end_log_pos 2817 CRC32 XXX Annotate_rows: +# at 2772 +# at 2809 +#<date> server id 1 end_log_pos 2809 CRC32 XXX Annotate_rows: #Q> DELETE FROM t1 +<<<<<<< HEAD:mysql-test/main/mysqlbinlog_row_minimal.result #<date> server id 1 end_log_pos 2873 CRC32 XXX Table_map: `test`.`t1` mapped to number num # at 2873 #<date> server id 1 end_log_pos 2927 CRC32 XXX Delete_rows: table id 32 flags: STMT_END_F +||||||| merged common ancestors +#<date> server id 1 end_log_pos 2873 CRC32 XXX Table_map: `test`.`t1` mapped to number num +# at 2873 +#<date> server id 1 end_log_pos 2927 CRC32 XXX Delete_rows: table id 31 flags: STMT_END_F +======= +#<date> server id 1 end_log_pos 2865 CRC32 XXX Table_map: `test`.`t1` mapped to number num +# at 2865 +#<date> server id 1 end_log_pos 2919 CRC32 XXX Delete_rows: table id 31 flags: STMT_END_F +>>>>>>> 10.2:mysql-test/r/mysqlbinlog_row_minimal.result ### DELETE FROM `test`.`t1` ### WHERE ### @1=10 /* INT meta=0 nullable=0 is_null=0 */ @@ -286,24 +307,42 @@ BEGIN ### DELETE FROM `test`.`t1` ### WHERE ### @1=13 /* INT meta=0 nullable=0 is_null=0 */ +<<<<<<< HEAD:mysql-test/main/mysqlbinlog_row_minimal.result # Number of rows: 4 # at 2927 #<date> server id 1 end_log_pos 3000 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 +||||||| merged common ancestors +# at 2927 +#<date> server id 1 end_log_pos 3000 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 +======= +# at 2919 +#<date> server id 1 end_log_pos 2992 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 +>>>>>>> 10.2:mysql-test/r/mysqlbinlog_row_minimal.result SET TIMESTAMP=X/*!*/; COMMIT /*!*/; -# at 3000 -#<date> server id 1 end_log_pos 3042 CRC32 XXX GTID 0-1-10 +# at 2992 +#<date> server id 1 end_log_pos 3034 CRC32 XXX GTID 0-1-10 /*!100001 SET @@session.gtid_seq_no=10*//*!*/; BEGIN /*!*/; -# at 3042 -# at 3079 -#<date> server id 1 end_log_pos 3079 CRC32 XXX Annotate_rows: +# at 3034 +# at 3071 +#<date> server id 1 end_log_pos 3071 CRC32 XXX Annotate_rows: #Q> DELETE FROM t2 +<<<<<<< HEAD:mysql-test/main/mysqlbinlog_row_minimal.result #<date> server id 1 end_log_pos 3135 CRC32 XXX Table_map: `test`.`t2` mapped to number num # at 3135 #<date> server id 1 end_log_pos 3189 CRC32 XXX Delete_rows: table id 33 flags: STMT_END_F +||||||| merged common ancestors +#<date> server id 1 end_log_pos 3135 CRC32 XXX Table_map: `test`.`t2` mapped to number num +# at 3135 +#<date> server id 1 end_log_pos 3189 CRC32 XXX Delete_rows: table id 32 flags: STMT_END_F +======= +#<date> server id 1 end_log_pos 3127 CRC32 XXX Table_map: `test`.`t2` mapped to number num +# at 3127 +#<date> server id 1 end_log_pos 3181 CRC32 XXX Delete_rows: table id 32 flags: STMT_END_F +>>>>>>> 10.2:mysql-test/r/mysqlbinlog_row_minimal.result ### DELETE FROM `test`.`t2` ### WHERE ### @1=10 /* INT meta=0 nullable=0 is_null=0 */ @@ -316,14 +355,90 @@ BEGIN ### DELETE FROM `test`.`t2` ### WHERE ### @1=13 /* INT meta=0 nullable=0 is_null=0 */ +<<<<<<< HEAD:mysql-test/main/mysqlbinlog_row_minimal.result # Number of rows: 4 # at 3189 #<date> server id 1 end_log_pos 3262 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 +||||||| merged common ancestors +# at 3189 +#<date> server id 1 end_log_pos 3262 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 +======= +# at 3181 +#<date> server id 1 end_log_pos 3254 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 +>>>>>>> 10.2:mysql-test/r/mysqlbinlog_row_minimal.result SET TIMESTAMP=X/*!*/; COMMIT /*!*/; -# at 3262 -#<date> server id 1 end_log_pos 3310 CRC32 XXX Rotate to master-bin.000002 pos: 4 +# at 3254 +#<date> server id 1 end_log_pos 3302 CRC32 XXX Rotate to master-bin.000002 pos: 4 +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; +/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; +DROP TABLE t1,t2; +CREATE TABLE `t1` ( +`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, +`is_deleted` BIT(1) DEFAULT b'0', +`last_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, +`ref_id` BIGINT(20) UNSIGNED NOT NULL, +PRIMARY KEY (`id`), +KEY `last_updated_KEY` (`last_updated`) +); +CREATE TABLE `t2` ( +`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, +`short_desc` VARCHAR(50) NOT NULL, +PRIMARY KEY (`id`) +); +INSERT INTO t2 (id, short_desc) VALUES (1, 'test'); +INSERT INTO t1 (id, is_deleted, ref_id) VALUES (1, b'0', 1); +FLUSH BINARY LOGS; +UPDATE t1 t1 INNER JOIN t2 t2 ON t1.ref_id = t2.id +SET t1.is_deleted = TRUE +WHERE t1.id = 1; +FLUSH BINARY LOGS; +/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +# at 387 +#<date> server id 1 end_log_pos 429 CRC32 XXX GTID 0-1-16 +/*!100101 SET @@session.skip_parallel_replication=0*//*!*/; +/*!100001 SET @@session.gtid_domain_id=0*//*!*/; +/*!100001 SET @@session.server_id=1*//*!*/; +/*!100001 SET @@session.gtid_seq_no=16*//*!*/; +BEGIN +/*!*/; +# at 429 +# at 543 +#<date> server id 1 end_log_pos 543 CRC32 XXX Annotate_rows: +#Q> UPDATE t1 t1 INNER JOIN t2 t2 ON t1.ref_id = t2.id +#Q> SET t1.is_deleted = TRUE +#Q> WHERE t1.id = +#<date> server id 1 end_log_pos 594 CRC32 XXX Table_map: `test`.`t1` mapped to number 34 +# at 594 +#<date> server id 1 end_log_pos 643 CRC32 XXX Update_rows: table id 34 flags: STMT_END_F +### UPDATE `test`.`t1` +### WHERE +### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */ +### SET +### @2=b'1' /* BIT(1) meta=1 nullable=1 is_null=0 */ +### @3=X /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */ +# at 643 +#<date> server id 1 end_log_pos 725 CRC32 XXX Query thread_id=5 exec_time=x error_code=0 +SET TIMESTAMP=X/*!*/; +SET @@session.pseudo_thread_id=5/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/; +SET @@session.sql_mode=1411383296/*!*/; +SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +SET @@session.lc_time_names=0/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +COMMIT +/*!*/; +# at 725 +#<date> server id 1 end_log_pos 773 CRC32 XXX Rotate to master-bin.000004 pos: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; diff --git a/mysql-test/main/mysqlbinlog_row_minimal.test b/mysql-test/main/mysqlbinlog_row_minimal.test index 7909f75e9a1..85b816e1be5 100644 --- a/mysql-test/main/mysqlbinlog_row_minimal.test +++ b/mysql-test/main/mysqlbinlog_row_minimal.test @@ -26,8 +26,49 @@ DELETE FROM t2; --let $datadir = `SELECT @@datadir` FLUSH BINARY LOGS; + --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --replace_regex /\d{6} *\d*:\d\d:\d\d/<date>/ /Start:.*at startup/Start: xxx/ /SET TIMESTAMP=\d*/SET TIMESTAMP=X/ /exec_time=\d*/exec_time=x/ /mapped to number \d*/mapped to number num/ /CRC32 0x[0-9a-f]+/CRC32 XXX/ --exec $MYSQL_BINLOG --verbose --verbose --base64-output=DECODE-ROWS $datadir/$binlog DROP TABLE t1,t2; + +# +# MDEV-14605 ON UPDATE CURRENT_TIMESTAMP fields by multi-table UPDATE are not logged with binlog_row_image=MINIMAL +# + +CREATE TABLE `t1` ( + + `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + `is_deleted` BIT(1) DEFAULT b'0', + `last_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `ref_id` BIGINT(20) UNSIGNED NOT NULL, + PRIMARY KEY (`id`), + KEY `last_updated_KEY` (`last_updated`) +); + +CREATE TABLE `t2` ( + `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, + `short_desc` VARCHAR(50) NOT NULL, + PRIMARY KEY (`id`) +); + + +INSERT INTO t2 (id, short_desc) VALUES (1, 'test'); +INSERT INTO t1 (id, is_deleted, ref_id) VALUES (1, b'0', 1); +FLUSH BINARY LOGS; +--let $binlog_pos= query_get_value(SHOW MASTER STATUS, Position, 1) + +UPDATE t1 t1 INNER JOIN t2 t2 ON t1.ref_id = t2.id + SET t1.is_deleted = TRUE + WHERE t1.id = 1; + +--let $binlog = query_get_value(SHOW MASTER STATUS, File, 1) + +FLUSH BINARY LOGS; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--replace_regex /\d{6} *\d*:\d\d:\d\d/<date>/ /SET TIMESTAMP=\d*/SET TIMESTAMP=X/ /exec_time=\d*/exec_time=x/ /@3=\d*/@3=X/ /CRC32 0x[0-9a-f]+/CRC32 XXX/ +--exec $MYSQL_BINLOG --verbose --verbose --base64-output=DECODE-ROWS $datadir/$binlog --start-position=$binlog_pos + +DROP TABLE t1,t2; + diff --git a/mysql-test/main/row-checksum-old.result b/mysql-test/main/row-checksum-old.result index 87cd1cc89ed..d374013f61c 100644 --- a/mysql-test/main/row-checksum-old.result +++ b/mysql-test/main/row-checksum-old.result @@ -83,3 +83,19 @@ checksum table t1 extended; Table Checksum test.t1 452555338 drop table t1; +# +# MDEV-17085: CHECKSUM TABLE EXTENDED does not work correctly +# +CREATE TABLE t1 ( c1 int NOT NULL, c2 int NOT NULL, c4 varchar(20), c5 varchar(20), c6 varchar(20), c7 varchar(20), c8 varchar(20), c9 varchar(20), c10 varchar(20), c11 varchar(20), c12 varchar(20), c13 varchar(20), c14 varchar(20), c15 varchar(20), c16 varchar(20), c19 int NOT NULL, c20 int NOT NULL, c21 varchar(20), c22 VARCHAR(20), c23 varchar(20)); +insert into t1 values (5,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,"dog",NULL,NULL); +# Important is that checksum is different from following +CHECKSUM TABLE t1 EXTENDED; +Table Checksum +test.t1 2514025256 +UPDATE t1 SET c21='cat' WHERE c1=5; +# Important is that checksum is different from above +CHECKSUM TABLE t1 EXTENDED; +Table Checksum +test.t1 2326430205 +drop table t1; +# End of 5.5 tests diff --git a/mysql-test/main/row-checksum.result b/mysql-test/main/row-checksum.result index f2e687f8b6e..4625e09c060 100644 --- a/mysql-test/main/row-checksum.result +++ b/mysql-test/main/row-checksum.result @@ -83,3 +83,19 @@ checksum table t1 extended; Table Checksum test.t1 229851577 drop table t1; +# +# MDEV-17085: CHECKSUM TABLE EXTENDED does not work correctly +# +CREATE TABLE t1 ( c1 int NOT NULL, c2 int NOT NULL, c4 varchar(20), c5 varchar(20), c6 varchar(20), c7 varchar(20), c8 varchar(20), c9 varchar(20), c10 varchar(20), c11 varchar(20), c12 varchar(20), c13 varchar(20), c14 varchar(20), c15 varchar(20), c16 varchar(20), c19 int NOT NULL, c20 int NOT NULL, c21 varchar(20), c22 VARCHAR(20), c23 varchar(20)); +insert into t1 values (5,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,"dog",NULL,NULL); +# Important is that checksum is different from following +CHECKSUM TABLE t1 EXTENDED; +Table Checksum +test.t1 2514025256 +UPDATE t1 SET c21='cat' WHERE c1=5; +# Important is that checksum is different from above +CHECKSUM TABLE t1 EXTENDED; +Table Checksum +test.t1 2326430205 +drop table t1; +# End of 5.5 tests diff --git a/mysql-test/main/row-checksum.test b/mysql-test/main/row-checksum.test index 82efcf3ef93..5acfda45f75 100644 --- a/mysql-test/main/row-checksum.test +++ b/mysql-test/main/row-checksum.test @@ -60,3 +60,20 @@ checksum table t1; checksum table t1 quick; checksum table t1 extended; drop table t1; + +--echo # +--echo # MDEV-17085: CHECKSUM TABLE EXTENDED does not work correctly +--echo # + +CREATE TABLE t1 ( c1 int NOT NULL, c2 int NOT NULL, c4 varchar(20), c5 varchar(20), c6 varchar(20), c7 varchar(20), c8 varchar(20), c9 varchar(20), c10 varchar(20), c11 varchar(20), c12 varchar(20), c13 varchar(20), c14 varchar(20), c15 varchar(20), c16 varchar(20), c19 int NOT NULL, c20 int NOT NULL, c21 varchar(20), c22 VARCHAR(20), c23 varchar(20)); + +insert into t1 values (5,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,"dog",NULL,NULL); +--echo # Important is that checksum is different from following +CHECKSUM TABLE t1 EXTENDED; +UPDATE t1 SET c21='cat' WHERE c1=5; +--echo # Important is that checksum is different from above +CHECKSUM TABLE t1 EXTENDED; + +drop table t1; + +--echo # End of 5.5 tests diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result index 31e7774734e..cae0f2286c1 100644 --- a/mysql-test/main/subselect2.result +++ b/mysql-test/main/subselect2.result @@ -394,3 +394,25 @@ select null in (select a from t1 where a < out3.a union select a from t2 where (select a from t3) +1 < out3.a+1) from t3 out3; ERROR 21000: Subquery returns more than 1 row drop table t1, t2, t3; +CREATE TABLE t1( +q11 int, q12 int, q13 int, q14 int, q15 int, q16 int, q17 int, q18 int, q19 int, +q21 int, q22 int, q23 int, q24 int, q25 int, q26 int, q27 int, q28 int, q29 int, +f1 int +); +CREATE TABLE t2(f2 int, f21 int, f3 timestamp, f4 int, f5 int, f6 int); +INSERT INTO t1 (f1) VALUES (1),(1),(2),(2); +INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11",0,0,0), (2,2,"2004-02-29 11:11:11",0,0,0); +SELECT f1, +(SELECT t.f21 from t2 t where max( +q11+q12+q13+q14+q15+q16+q17+q18+q19+ +q21+q22+q23+q24+q25+q26+q27+q28+q29) = t.f2 UNION +SELECT t.f3 FROM t2 AS t WHERE t1.f1=t.f2 AND t.f3=MAX(t1.f1) UNION +SELECT 1 LIMIT 1) AS test +FROM t1 GROUP BY f1; +f1 test +1 1 +2 1 +Warnings: +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '2' +DROP TABLE t1,t2; diff --git a/mysql-test/main/subselect2.test b/mysql-test/main/subselect2.test index c106d40b095..5f7e5f80626 100644 --- a/mysql-test/main/subselect2.test +++ b/mysql-test/main/subselect2.test @@ -411,3 +411,23 @@ insert into t3 select a from t1; select null in (select a from t1 where a < out3.a union select a from t2 where (select a from t3) +1 < out3.a+1) from t3 out3; drop table t1, t2, t3; + +# +# Bug #28499924: INCORRECT BEHAVIOR WITH UNION IN SUBQUERY +# +CREATE TABLE t1( + q11 int, q12 int, q13 int, q14 int, q15 int, q16 int, q17 int, q18 int, q19 int, + q21 int, q22 int, q23 int, q24 int, q25 int, q26 int, q27 int, q28 int, q29 int, + f1 int +); +CREATE TABLE t2(f2 int, f21 int, f3 timestamp, f4 int, f5 int, f6 int); +INSERT INTO t1 (f1) VALUES (1),(1),(2),(2); +INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11",0,0,0), (2,2,"2004-02-29 11:11:11",0,0,0); +SELECT f1, + (SELECT t.f21 from t2 t where max( + q11+q12+q13+q14+q15+q16+q17+q18+q19+ + q21+q22+q23+q24+q25+q26+q27+q28+q29) = t.f2 UNION + SELECT t.f3 FROM t2 AS t WHERE t1.f1=t.f2 AND t.f3=MAX(t1.f1) UNION + SELECT 1 LIMIT 1) AS test + FROM t1 GROUP BY f1; +DROP TABLE t1,t2; diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 2ba851808df..3ee904b8d9f 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -2865,6 +2865,22 @@ SELECT * FROM t2 WHERE f IN ( SELECT LEFT('foo',0) FROM t1 ORDER BY 1 ); f DROP TABLE t1, t2; # +# MDEV-18255: Server crashes in Bitmap<64u>::intersect +# +create table t1 (v1 varchar(1)) engine=myisam ; +create table t2 (v1 varchar(1)) engine=myisam ; +explain +select 1 from t1 where exists +(select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +select 1 from t1 where exists +(select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ; +1 +drop table t1,t2; +# # MDEV-9489: Assertion `0' failed in Protocol::end_statement() on # UNION ALL # diff --git a/mysql-test/main/subselect_mat.test b/mysql-test/main/subselect_mat.test index 7055b323906..fc43d19ae11 100644 --- a/mysql-test/main/subselect_mat.test +++ b/mysql-test/main/subselect_mat.test @@ -269,6 +269,19 @@ SELECT * FROM t2 WHERE f IN ( SELECT LEFT('foo',0) FROM t1 ORDER BY 1 ); DROP TABLE t1, t2; --echo # +--echo # MDEV-18255: Server crashes in Bitmap<64u>::intersect +--echo # +create table t1 (v1 varchar(1)) engine=myisam ; +create table t2 (v1 varchar(1)) engine=myisam ; + +explain +select 1 from t1 where exists + (select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ; +select 1 from t1 where exists + (select 1 from t1 where t1.v1 in (select t2.v1 from t2 having t2.v1 < 'j')) ; +drop table t1,t2; + +--echo # --echo # MDEV-9489: Assertion `0' failed in Protocol::end_statement() on --echo # UNION ALL --echo # |