Merge 10.2 into 10.3

5 files changed, 102 insertions, 5 deletions

diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result
index 9e934b19efc..0417cf0a7f0 100644
--- a/mysql-test/main/cte_recursive.result
+++ b/mysql-test/main/cte_recursive.result
@@ -3606,6 +3606,54 @@ Mandelbrot Set
                 ..............................................................................        
                   ...........................................................................         
                    ........................................................................           
+#
+# MDEV-17871: EXPLAIN for query with not used recursive cte
+#
+create table t1 (a int);
+insert into t1 values (2), (1), (4), (3);
+explain extended
+with recursive cte as
+(select * from t1 where a=1 union select a+1 from cte where a<3)
+select * from cte as t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	100.00	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	with recursive cte as (/* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 union /* select#3 */ select `cte`.`a` + 1 AS `a+1` from `cte` where `cte`.`a` < 3)/* select#1 */ select `t`.`a` AS `a` from `cte` `t`
+with recursive cte as
+(select * from t1 where a=1 union select a+1 from cte where a<3)
+select * from cte as t;
+a
+1
+2
+3
+explain extended
+with recursive cte as
+(select * from t1 where a=1 union select a+1 from cte where a<3)
+select * from t1 as t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	4	100.00	
+Warnings:
+Note	1003	with recursive cte as (/* select#2 */ select `*` AS `*` from `test`.`t1` where `a` = 1 union /* select#3 */ select `a` + 1 AS `a+1` from `cte` where `a` < 3)/* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t1` `t`
+with recursive cte as
+(select * from t1 where a=1 union select a+1 from cte where a<3)
+select * from t1 as t;
+a
+2
+1
+4
+3
+create table t2 ( i1 int, i2 int);
+insert into t2 values (1,1),(2,2);
+explain
+with recursive cte as
+( select * from t1 union select s1.* from t1 as s1, cte where s1.i1 = cte.i2 )
+select * from t1 as t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	4	
+drop table t1,t2;
 # End of 10.2 tests
 #
 # MDEV-14217 [db crash] Recursive CTE when SELECT includes new field
diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test
index 79809044c00..0ed9c2d56e3 100644
--- a/mysql-test/main/cte_recursive.test
+++ b/mysql-test/main/cte_recursive.test
@@ -2496,6 +2496,45 @@ SELECT GROUP_CONCAT(
 GROUP BY Iy
 ORDER BY Iy;
 
+--echo #
+--echo # MDEV-17871: EXPLAIN for query with not used recursive cte
+--echo #
+
+create table t1 (a int);
+insert into t1 values (2), (1), (4), (3);
+
+let $rec_cte =
+with recursive cte as
+  (select * from t1 where a=1 union select a+1 from cte where a<3);
+
+eval
+explain extended
+$rec_cte
+select * from cte as t;
+
+eval
+$rec_cte
+select * from cte as t;
+
+eval
+explain extended
+$rec_cte
+select * from t1 as t;
+
+eval
+$rec_cte
+select * from t1 as t;
+
+create table t2 ( i1 int, i2 int);
+insert into t2 values (1,1),(2,2);
+
+explain
+with recursive cte as
+ ( select * from t1 union select s1.* from t1 as s1, cte where s1.i1 = cte.i2 )
+select * from t1 as t;
+
+drop table t1,t2;
+
 --echo # End of 10.2 tests
 
 --echo #
diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def
index 5df9fa72b8f..d1d054b87ad 100644
--- a/mysql-test/suite/galera/disabled.def
+++ b/mysql-test/suite/galera/disabled.def
@@ -29,12 +29,12 @@ MW-416 : MDEV-13549 Galera test failures
 MW-388 : MDEV-13549 Galera test failures
 MW-44 : MDEV-15809 Test failure on galera.MW-44
 galera_pc_ignore_sb : MDEV-15811 Test failure on galera_pc_ignore_sb
+galera_drop_database : MDEV-17421 mtr does not restart the server whose parameters were changed
 galera_kill_applier : race condition at the start of the test
 galera_ist_progress: MDEV-15236 galera_ist_progress fails when trying to read transfer status
 pxc-421: Lock timeout exceeded
 galera_sst_mysqldump_with_key : MDEV-16890 Galera test failure
 galera_sst_xtrabackup-v2-options : Failed to read uuid:seqno and wsrep_gtid_domain_id from joiner script
-MW-328C : Timeouts
 galera_gcs_fc_limit : Timeouts
 pool_of_threads: WSREP has not yet prepared node for application use
 galera_var_innodb_disallow_writes : Timeout
@@ -47,7 +47,6 @@ galera_gc_fc_limit : MDEV-17061 Test failure on galera.galera_gc_fc_limit
 galera_as_slave_replication_budle : MDEV-15785 Test case galera_as_slave_replication_bundle caused debug assertion
 galera_wan : MDEV-17259: Test failure on galera.galera_wan
 galera_pc_ignore_sb : MDEV-17357 Test failure on galera.galera_pc_ignore_sb
-galera_drop_database : test
-galera.MW-328A : MDEV-17847 Galera test failure on MW-328[A|B|C]
-galera.MW-328B : MDEV-17847 Galera test failure on MW-328[A|B|C]
-galera.MW-328C : MDEV-17847 Galera test failure on MW-328[A|B|C]
+MW-328A : MDEV-17847 Galera test failure on MW-328[A|B|C]
+MW-328B : MDEV-17847 Galera test failure on MW-328[A|B|C]
+MW-328C : MDEV-17847 Galera test failure on MW-328[A|B|C]
diff --git a/mysql-test/suite/innodb/r/table_flags.result b/mysql-test/suite/innodb/r/table_flags.result
index c82cd6ceccb..8c4280738e1 100644
--- a/mysql-test/suite/innodb/r/table_flags.result
+++ b/mysql-test/suite/innodb/r/table_flags.result
@@ -192,3 +192,8 @@ ib_logfile0
 ib_logfile1
 ibdata1
 sys_tables.bin
+call mtr.add_suppression("ERROR HY000: Can't create table `test`.`t1`");
+CREATE TABLE t1(f1 INT, f2 VARCHAR(1), KEY k1(f2),
+FULLTEXT KEY(f2),
+FOREIGN KEY (f2) REFERENCES t1(f3))ENGINE=InnoDB;
+ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed")
diff --git a/mysql-test/suite/innodb/t/table_flags.test b/mysql-test/suite/innodb/t/table_flags.test
index 284589a17c8..e979b5fffe0 100644
--- a/mysql-test/suite/innodb/t/table_flags.test
+++ b/mysql-test/suite/innodb/t/table_flags.test
@@ -216,3 +216,9 @@ DROP TABLE tr,tc,td,tz,tp;
 --list_files $bugdir
 --remove_files_wildcard $bugdir
 --rmdir $bugdir
+
+call mtr.add_suppression("ERROR HY000: Can't create table `test`.`t1`");
+--error ER_CANT_CREATE_TABLE
+CREATE TABLE t1(f1 INT, f2 VARCHAR(1), KEY k1(f2),
+		FULLTEXT KEY(f2),
+		FOREIGN KEY (f2) REFERENCES t1(f3))ENGINE=InnoDB;