Merge branch 10.4 into 10.5st-10.5-julius

17 files changed, 227 insertions, 16 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 1f901e2a2b3..cb8bc0122c4 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/opt_trace.result b/mysql-test/main/opt_trace.result
index a20d3c419d3..47076cd49f7 100644
--- a/mysql-test/main/opt_trace.result
+++ b/mysql-test/main/opt_trace.result
@@ -571,7 +571,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": [
@@ -685,7 +684,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": [
@@ -2361,7 +2359,8 @@ select t1.a from t1 left join t2 on t1.a=t2.a	{
             "best_join_order": ["t2", "t1"]
           },
           {
-            "condition_on_constant_tables": "1"
+            "condition_on_constant_tables": "1",
+            "computing_condition": []
           },
           {
             "attaching_conditions_to_tables": {
@@ -2518,7 +2517,8 @@ explain select * from t1 left join t2 on t2.a=t1.a	{
             "best_join_order": ["t1", "t2"]
           },
           {
-            "condition_on_constant_tables": "1"
+            "condition_on_constant_tables": "1",
+            "computing_condition": []
           },
           {
             "attaching_conditions_to_tables": {
@@ -2676,7 +2676,8 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and
             "best_join_order": ["t3", "t2", "t1"]
           },
           {
-            "condition_on_constant_tables": "1"
+            "condition_on_constant_tables": "1",
+            "computing_condition": []
           },
           {
             "attaching_conditions_to_tables": {
@@ -2989,7 +2990,8 @@ explain extended select * from t1 where a in (select pk from t10)	{
             "best_join_order": ["t1", "<subquery2>"]
           },
           {
-            "condition_on_constant_tables": "1"
+            "condition_on_constant_tables": "1",
+            "computing_condition": []
           },
           {
             "attaching_conditions_to_tables": {
@@ -4686,7 +4688,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_
             "best_join_order": ["t1", "<subquery2>"]
           },
           {
-            "condition_on_constant_tables": "1"
+            "condition_on_constant_tables": "1",
+            "computing_condition": []
           },
           {
             "attaching_conditions_to_tables": {
@@ -7332,7 +7335,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": {
@@ -8598,6 +8602,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';
diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test
index c5eb6a74fa9..b82357b6468 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;
 
@@ -644,6 +644,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 #
 
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 1b77d713185..f718bfb5dce 100644
--- a/mysql-test/main/ps.result
+++ b/mysql-test/main/ps.result
@@ -5569,6 +5569,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 a65c54c8788..9b781f32631 100644
--- a/mysql-test/main/ps.test
+++ b/mysql-test/main/ps.test
@@ -4990,6 +4990,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 b3881a77d0e..a80c77e86f2 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1650,6 +1650,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 c1fab2d3609..8b6d2becfa9 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
 # This may work or fail as different servers uses different amount of
 # memory and the statement may work or not. What is important is that we