diff options
Diffstat (limited to 'mysql-test/main')
21 files changed, 333 insertions, 26 deletions
diff --git a/mysql-test/main/backup_lock_binlog.result b/mysql-test/main/backup_lock_binlog.result index adf960a9cb1..b07e1b59e58 100644 --- a/mysql-test/main/backup_lock_binlog.result +++ b/mysql-test/main/backup_lock_binlog.result @@ -2,9 +2,6 @@ # MDEV-25334 FTWRL/Backup blocks DDL on temporary tables with binlog # enabled assertion fails in Diagnostics_area::set_error_status # -select @@binlog_format; -@@binlog_format -MIXED connect con1,localhost,root,,; connection default; # diff --git a/mysql-test/main/backup_lock_binlog.test b/mysql-test/main/backup_lock_binlog.test index 45b3f1cfbd9..71463d6a8d5 100644 --- a/mysql-test/main/backup_lock_binlog.test +++ b/mysql-test/main/backup_lock_binlog.test @@ -9,7 +9,6 @@ --echo # enabled assertion fails in Diagnostics_area::set_error_status --echo # -select @@binlog_format; --connect (con1,localhost,root,,) connection default; diff --git a/mysql-test/main/compound.test b/mysql-test/main/compound.test index c902ef77b52..00f90e7a251 100644 --- a/mysql-test/main/compound.test +++ b/mysql-test/main/compound.test @@ -1,7 +1,7 @@ # # MDEV-5317 Compound statement / anonymous blocks # -source include/have_log_bin.inc; +source include/have_binlog_format_mixed_or_statement.inc; delimiter |; CREATE TABLE t1 (a INT PRIMARY KEY)| diff --git a/mysql-test/main/ctype_utf8mb4_unicode_ci_def.opt b/mysql-test/main/ctype_utf8mb4_unicode_ci_def.opt new file mode 100644 index 00000000000..e430a45c10e --- /dev/null +++ b/mysql-test/main/ctype_utf8mb4_unicode_ci_def.opt @@ -0,0 +1 @@ +--character-set-server=utf8mb4,latin1 --collation-server=utf8mb4_unicode_ci diff --git a/mysql-test/main/ctype_utf8mb4_unicode_ci_def.result b/mysql-test/main/ctype_utf8mb4_unicode_ci_def.result new file mode 100644 index 00000000000..2e15931248b --- /dev/null +++ b/mysql-test/main/ctype_utf8mb4_unicode_ci_def.result @@ -0,0 +1,11 @@ +# +# Start of 10.3 tests +# +# +# MDEV-27195 SIGSEGV in Table_scope_and_contents_source_st::vers_check_system_fields +# +CREATE TABLE t1 ENGINE=MyISAM WITH SYSTEM VERSIONING AS SELECT 0; +DROP TABLE t1; +# +# End of 10.3 tests +# diff --git a/mysql-test/main/ctype_utf8mb4_unicode_ci_def.test b/mysql-test/main/ctype_utf8mb4_unicode_ci_def.test new file mode 100644 index 00000000000..fb7fbe04e3b --- /dev/null +++ b/mysql-test/main/ctype_utf8mb4_unicode_ci_def.test @@ -0,0 +1,15 @@ +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-27195 SIGSEGV in Table_scope_and_contents_source_st::vers_check_system_fields +--echo # + +CREATE TABLE t1 ENGINE=MyISAM WITH SYSTEM VERSIONING AS SELECT 0; +DROP TABLE t1; + + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/mysql-test/main/filesort_pack.result b/mysql-test/main/filesort_pack.result new file mode 100644 index 00000000000..c067e0620b9 --- /dev/null +++ b/mysql-test/main/filesort_pack.result @@ -0,0 +1,29 @@ +# +# Start of 10.5 tests +# +# +# MDEV-27307 main.ctype_utf8mb4_uca_allkeys tests fail with Valgrind/MSAN +# +# In this scenario packing is fully disabled: +# - The sortkey is of a fixed width data type +# - The addon fields have too small potential savings +SET NAMES latin1; +CREATE TABLE t1 ( +code INT NOT NULL, +str VARCHAR(5) CHARACTER SET latin1 NOT NULL +) ENGINE=MyISAM; +FOR i IN 0..50 +DO +INSERT INTO t1 VALUES (i, REPEAT('a',1)); +END FOR; +$$ +SELECT COUNT(*) FROM t1; +COUNT(*) +51 +SET sort_buffer_size=1024; +SELECT HEX(code), HEX(str) FROM t1 ORDER BY HEX(str); +SET sort_buffer_size=DEFAULT; +DROP TABLE t1; +# +# End of 10.5 tests +# diff --git a/mysql-test/main/filesort_pack.test b/mysql-test/main/filesort_pack.test new file mode 100644 index 00000000000..6765778904e --- /dev/null +++ b/mysql-test/main/filesort_pack.test @@ -0,0 +1,51 @@ +# +# Tests related to pack modes of the sortkey and addon fields. +# Fields can be either packed or fixed length. +# +# See the comment in filesort.cc: +# +# Heuristic: skip packing if potential savings are less than 10 bytes. +# + +--echo # +--echo # Start of 10.5 tests +--echo # + +--echo # +--echo # MDEV-27307 main.ctype_utf8mb4_uca_allkeys tests fail with Valgrind/MSAN +--echo # +--echo # In this scenario packing is fully disabled: +--echo # - The sortkey is of a fixed width data type +--echo # - The addon fields have too small potential savings + +SET NAMES latin1; + +CREATE TABLE t1 ( + code INT NOT NULL, + str VARCHAR(5) CHARACTER SET latin1 NOT NULL +) ENGINE=MyISAM; + +DELIMITER $$; +FOR i IN 0..50 +DO + INSERT INTO t1 VALUES (i, REPEAT('a',1)); +END FOR; +$$ +DELIMITER ;$$ +SELECT COUNT(*) FROM t1; + +# The result is not very interesting, let's suppress it. +# We just make sure there are no Valgrind/MSAN warnings about +# not initialized memory being written to disk. + +SET sort_buffer_size=1024; +--disable_result_log +SELECT HEX(code), HEX(str) FROM t1 ORDER BY HEX(str); +--enable_result_log +SET sort_buffer_size=DEFAULT; + +DROP TABLE t1; + +--echo # +--echo # End of 10.5 tests +--echo # diff --git a/mysql-test/main/mysqld--help-aria.result b/mysql-test/main/mysqld--help-aria.result index 70e7d8930c4..fb44c3ea3ee 100644 --- a/mysql-test/main/mysqld--help-aria.result +++ b/mysql-test/main/mysqld--help-aria.result @@ -1,7 +1,3 @@ -[ERROR] mariadbd: Can't lock aria aria_log_control for exclusive use, error: #. Will retry for 0 seconds -[ERROR] Plugin 'Aria' init function returned error. -[ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed. -[Warning] Could not open mysql.plugin table: "Unknown storage engine 'Aria'". Some options may be missing from the help text # # Check that we don't write any data to wrong or not existing datadir # diff --git a/mysql-test/main/mysqld--help-aria.test b/mysql-test/main/mysqld--help-aria.test index 253a46492f0..55131e9e4eb 100644 --- a/mysql-test/main/mysqld--help-aria.test +++ b/mysql-test/main/mysqld--help-aria.test @@ -2,15 +2,10 @@ # Check errors from mysqld--help when providing different datadir # -# We can't run this test on windows as windows doesn't provide file locking -# which the first exec requires. - ---source include/not_windows.inc - --let $args=--table-cache=5 --max-connections=10 --log-warnings=1 --silent-startup --lower-case-table-names=1 --help --verbose --exec $MYSQLD_CMD $args > $MYSQL_TMP_DIR/mysqld--help2.txt 2> $MYSQL_TMP_DIR/mysqld--help2.err ---replace_regex /mysqld/mariadbd/ /\d\d\d\d-\d*-\d* *\d*:\d*:\d* \d* // /control file '.*aria_log_control'/aria_log_control/ /error: \d+/error: #/ +--replace_regex /mysqld/mariadbd/ /\d\d\d\d-\d*-\d* *\d*:\d*:\d* \d* // --cat_file $MYSQL_TMP_DIR/mysqld--help2.err --echo # diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index f7f5476ab23..255aad4450b 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -585,7 +585,6 @@ create view v2 as select a from t2; explain select * from v2 ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 -select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from v2 { "steps": [ @@ -698,7 +697,6 @@ explain select * from v1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 2 DERIVED t1 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort -select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from v1 { "steps": [ @@ -2409,7 +2407,8 @@ select t1.a from t1 left join t2 on t1.a=t2.a { } }, { - "condition_on_constant_tables": "1" + "condition_on_constant_tables": "1", + "computing_condition": [] }, { "attaching_conditions_to_tables": { @@ -2585,7 +2584,8 @@ explain select * from t1 left join t2 on t2.a=t1.a { } }, { - "condition_on_constant_tables": "1" + "condition_on_constant_tables": "1", + "computing_condition": [] }, { "attaching_conditions_to_tables": { @@ -2755,7 +2755,8 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and } }, { - "condition_on_constant_tables": "1" + "condition_on_constant_tables": "1", + "computing_condition": [] }, { "attaching_conditions_to_tables": { @@ -3073,7 +3074,8 @@ explain extended select * from t1 where a in (select pk from t10) { } }, { - "condition_on_constant_tables": "1" + "condition_on_constant_tables": "1", + "computing_condition": [] }, { "attaching_conditions_to_tables": { @@ -4782,7 +4784,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ } }, { - "condition_on_constant_tables": "1" + "condition_on_constant_tables": "1", + "computing_condition": [] }, { "attaching_conditions_to_tables": { @@ -7438,7 +7441,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { } }, { - "condition_on_constant_tables": "1" + "condition_on_constant_tables": "1", + "computing_condition": [] }, { "attaching_conditions_to_tables": { @@ -8707,6 +8711,15 @@ SELECT 'a\0' LIMIT 0 { } SET optimizer_trace=DEFAULT; # +# MDEV-27238: Assertion `got_name == named_item_expected()' failed in Json_writer::on_start_object +# +CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=MEMORY; +SET optimizer_trace=1; +INSERT INTO t1 VALUES (0,0); +SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d); +a +DROP TABLE t1; +# # End of 10.4 tests # set optimizer_trace='enabled=on'; @@ -9252,5 +9265,22 @@ json_detailed(json_extract(trace, '$**.best_join_order')) ] ] DROP TABLE t1; +# +# MDEV-27306: SET STATEMENT optimizer_trace=1 Doesn't save the trace +# +set optimizer_trace=0; +set statement optimizer_trace=1 for select * from seq_1_to_10 where seq<2; +seq +1 +# The trace must not be empty: +select left(trace, 100) from information_schema.optimizer_trace; +left(trace, 100) +{ + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + # End of 10.6 tests set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 402caf2a165..2077422fa1b 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -48,12 +48,12 @@ create view v1 as select a from t1 group by b; create view v2 as select a from t2; --echo # Mergeable view +--optimizer_trace explain select * from v2 ; -select * from information_schema.OPTIMIZER_TRACE; --echo # Non-Mergeable view +--optimizer_trace explain select * from v1 ; -select * from information_schema.OPTIMIZER_TRACE; drop table t1,t2; drop view v1,v2; @@ -646,6 +646,16 @@ SELECT query, trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; SET optimizer_trace=DEFAULT; --echo # +--echo # MDEV-27238: Assertion `got_name == named_item_expected()' failed in Json_writer::on_start_object +--echo # + +CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=MEMORY; +SET optimizer_trace=1; +INSERT INTO t1 VALUES (0,0); +SELECT a FROM t1 WHERE (a,b) in (SELECT @c,@d); +DROP TABLE t1; + +--echo # --echo # End of 10.4 tests --echo # @@ -865,5 +875,13 @@ select json_detailed(json_extract(trace, '$**.best_join_order')) from information_schema.OPTIMIZER_TRACE; DROP TABLE t1; +--echo # +--echo # MDEV-27306: SET STATEMENT optimizer_trace=1 Doesn't save the trace +--echo # +set optimizer_trace=0; +set statement optimizer_trace=1 for select * from seq_1_to_10 where seq<2; +--echo # The trace must not be empty: +select left(trace, 100) from information_schema.optimizer_trace; + --echo # End of 10.6 tests set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/order_by_innodb.result b/mysql-test/main/order_by_innodb.result index 14b9b861a14..28922ef65f2 100644 --- a/mysql-test/main/order_by_innodb.result +++ b/mysql-test/main/order_by_innodb.result @@ -198,5 +198,28 @@ id id 1 NULL 2 1 3 3 +# +# MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT +# +# This must NOT have "Range checked for each record" without any +# provisions to produce rows in the required ordering: +explain +select +t1.id,t2.id +from +t1 left join +t2 on t2.id2 = t1.id and +t2.id = (select dd.id +from t2 dd +where +dd.id2 = t1.id and +d1 > '2019-02-06 00:00:00' + order by +dd.d1, dd.d2, dd.id limit 1 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL PRIMARY 4 NULL # Using index +1 PRIMARY t2 eq_ref PRIMARY,id2 PRIMARY 4 func # Using where +2 DEPENDENT SUBQUERY dd range id2,for_latest_sort for_latest_sort 6 NULL # Using where drop table t1,t2; # End of 10.2 tests diff --git a/mysql-test/main/order_by_innodb.test b/mysql-test/main/order_by_innodb.test index 97c043b8dbc..af12644c073 100644 --- a/mysql-test/main/order_by_innodb.test +++ b/mysql-test/main/order_by_innodb.test @@ -184,6 +184,28 @@ from order by dd.d1 desc, dd.d2 desc, dd.id desc limit 1 ); + +--echo # +--echo # MDEV-27270: Wrong query plan with Range Checked for Each Record and ORDER BY ... LIMIT +--echo # + +--echo # This must NOT have "Range checked for each record" without any +--echo # provisions to produce rows in the required ordering: +--replace_column 9 # +explain +select + t1.id,t2.id +from + t1 left join + t2 on t2.id2 = t1.id and + t2.id = (select dd.id + from t2 dd + where + dd.id2 = t1.id and + d1 > '2019-02-06 00:00:00' + order by + dd.d1, dd.d2, dd.id limit 1 + ); drop table t1,t2; --echo # End of 10.2 tests diff --git a/mysql-test/main/partition_open_files_limit.result b/mysql-test/main/partition_open_files_limit.result index fed32a69c44..327fe24d27d 100644 --- a/mysql-test/main/partition_open_files_limit.result +++ b/mysql-test/main/partition_open_files_limit.result @@ -1,4 +1,6 @@ DROP TABLE IF EXISTS `t1`; +call mtr.add_suppression("option 'table_open_cache'"); +call mtr.add_suppression("option 'max_connections'"); # Bug#46922: crash when adding partitions and open_files_limit is reached CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=MyISAM PARTITION BY KEY () PARTITIONS 1; diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index c431f52afe4..cd01a8949bd 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -5563,6 +5563,19 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DEALLOCATE PREPARE stmt; DROP TABLE t1, t2, t3; +# +# MDEV-21866: Assertion `!result' failed in convert_const_to_int upon 2nd execution of PS +# +CREATE TABLE t1 (a BIGINT DEFAULT -1); +CREATE VIEW v1 AS SELECT DISTINCT a FROM t1; +PREPARE stmt FROM 'SELECT * FROM v1 WHERE a <=> NULL'; +EXECUTE stmt; +a +EXECUTE stmt; +a +DEALLOCATE PREPARE stmt; +DROP VIEW v1; +DROP TABLE t1; # End of 10.2 tests # # diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test index fa184ab8250..c4ec92540a2 100644 --- a/mysql-test/main/ps.test +++ b/mysql-test/main/ps.test @@ -4991,6 +4991,22 @@ EXECUTE stmt; DEALLOCATE PREPARE stmt; DROP TABLE t1, t2, t3; +--echo # +--echo # MDEV-21866: Assertion `!result' failed in convert_const_to_int upon 2nd execution of PS +--echo # + +CREATE TABLE t1 (a BIGINT DEFAULT -1); +CREATE VIEW v1 AS SELECT DISTINCT a FROM t1; +PREPARE stmt FROM 'SELECT * FROM v1 WHERE a <=> NULL'; +EXECUTE stmt; +EXECUTE stmt; + +# Cleanup +DEALLOCATE PREPARE stmt; +DROP VIEW v1; +DROP TABLE t1; + + --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/sp-no-valgrind.test b/mysql-test/main/sp-no-valgrind.test index 89f8250bf72..21e52f2d3d5 100644 --- a/mysql-test/main/sp-no-valgrind.test +++ b/mysql-test/main/sp-no-valgrind.test @@ -1,5 +1,5 @@ ---source include/not_valgrind.inc +--source include/not_valgrind_build.inc --echo # MDEV-20699 do not cache SP in SHOW CREATE --echo # Warmup round, this might allocate some memory for session variable diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 8b5df420269..0522e6ae6cc 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -3098,6 +3098,48 @@ select * from (values (3),(7),(1) union values (2),(4) order by 1 limit 2) as dt 1 2 drop table t1; +# +# MDEV-23182: Server crashes in +# Item::fix_fields_if_needed / table_value_constr::prepare upon 2nd execution of PS +# +SET @save_in_predicate_conversion_threshold=@@in_predicate_conversion_threshold; +SET in_predicate_conversion_threshold=2; +CREATE TABLE t1 (c VARCHAR(10)) DEFAULT CHARSET=utf8; +PREPARE stmt FROM "SELECT * FROM t1 WHERE c IN ('10','20')"; +EXECUTE stmt; +c +# Without the patch second execution of the prepared statement 'stmt' +# results in crash. +EXECUTE stmt; +c +DEALLOCATE PREPARE stmt; +DROP TABLE t1; +# Check that the query without conversion doesn't crash server +CREATE TABLE t1 (c VARCHAR(10)); +PREPARE stmt FROM "SELECT * FROM t1 WHERE c IN ('10','20')"; +EXECUTE stmt; +c +EXECUTE stmt; +c +DEALLOCATE PREPARE stmt; +DROP TABLE t1; +# Test case for a row expression in the left part of the IN clause +CREATE TABLE t1 (a VARCHAR(3), b VARCHAR(3)) DEFAULT CHARSET=utf8; +PREPARE stmt FROM "SELECT * FROM t1 WHERE (a, b) IN (('10', '10'), ('20', '20'))"; +EXECUTE stmt; +a b +EXECUTE stmt; +a b +DROP TABLE t1; +# Check that the query without conversion is handled successfully +CREATE TABLE t1 (a VARCHAR(3), b VARCHAR(3)); +PREPARE stmt FROM "SELECT * FROM t1 WHERE (a, b) IN (('10', '10'), ('20', '20'))"; +EXECUTE stmt; +a b +EXECUTE stmt; +a b +DROP TABLE t1; +SET @@in_predicate_conversion_threshold = @save_in_predicate_conversion_threshold; End of 10.3 tests # # MDEV-22610 Crash in INSERT INTO t1 (VALUES (DEFAULT) UNION VALUES (DEFAULT)) diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index cbad7706ca5..05aca941350 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1652,6 +1652,52 @@ select * from (values (3),(7),(1) union values (2),(4) order by 1 limit 2) as dt drop table t1; +--echo # +--echo # MDEV-23182: Server crashes in +--echo # Item::fix_fields_if_needed / table_value_constr::prepare upon 2nd execution of PS +--echo # +SET @save_in_predicate_conversion_threshold=@@in_predicate_conversion_threshold; +SET in_predicate_conversion_threshold=2; + +CREATE TABLE t1 (c VARCHAR(10)) DEFAULT CHARSET=utf8; +PREPARE stmt FROM "SELECT * FROM t1 WHERE c IN ('10','20')"; +EXECUTE stmt; +--echo # Without the patch second execution of the prepared statement 'stmt' +--echo # results in crash. +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +DROP TABLE t1; + +--echo # Check that the query without conversion doesn't crash server +CREATE TABLE t1 (c VARCHAR(10)); +PREPARE stmt FROM "SELECT * FROM t1 WHERE c IN ('10','20')"; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +DROP TABLE t1; + +--echo # Test case for a row expression in the left part of the IN clause +CREATE TABLE t1 (a VARCHAR(3), b VARCHAR(3)) DEFAULT CHARSET=utf8; +PREPARE stmt FROM "SELECT * FROM t1 WHERE (a, b) IN (('10', '10'), ('20', '20'))"; + +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1; + +--echo # Check that the query without conversion is handled successfully +CREATE TABLE t1 (a VARCHAR(3), b VARCHAR(3)); +PREPARE stmt FROM "SELECT * FROM t1 WHERE (a, b) IN (('10', '10'), ('20', '20'))"; + +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1; + +SET @@in_predicate_conversion_threshold = @save_in_predicate_conversion_threshold; + --echo End of 10.3 tests --echo # diff --git a/mysql-test/main/truncate_notembedded.test b/mysql-test/main/truncate_notembedded.test index 8fd5d5dbc86..70da48f5a4f 100644 --- a/mysql-test/main/truncate_notembedded.test +++ b/mysql-test/main/truncate_notembedded.test @@ -18,6 +18,7 @@ SELECT * FROM t1; UNLOCK TABLES; --connection con1 +--error 0,ER_OPTION_PREVENTS_STATEMENT --reap --error ER_OPTION_PREVENTS_STATEMENT TRUNCATE TABLE t1; |