Merging upstream version 1:10.11.8.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

3 files changed, 124 insertions, 3 deletions

diff --git a/mysql-test/suite/federated/federatedx.result b/mysql-test/suite/federated/federatedx.result
index 49deff81..3054060b 100644
--- a/mysql-test/suite/federated/federatedx.result
+++ b/mysql-test/suite/federated/federatedx.result
@@ -2243,6 +2243,7 @@ connection master;
 CREATE TABLE t1(id VARCHAR(20) NOT NULL, PRIMARY KEY(id)) ENGINE=FEDERATED 
 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/test/t1';
 # Dump table t1 using mysqldump tool
+/*!999999\- enable the sandbox mode */ 
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `t1` (
diff --git a/mysql-test/suite/federated/federatedx_create_handlers.result b/mysql-test/suite/federated/federatedx_create_handlers.result
index 1a2f8c5d..7288c520 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.result
+++ b/mysql-test/suite/federated/federatedx_create_handlers.result
@@ -521,6 +521,23 @@ use federated;
 SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
 WHERE id=2) dt2) dt;
 id	name
+PREPARE stmt FROM "
+SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
+  WHERE id=3) dt2) dt;
+";
+EXECUTE stmt;
+id	name
+3	xxx
+EXECUTE stmt;
+id	name
+3	xxx
+DEALLOCATE PREPARE stmt;
+EXPLAIN
+SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
+WHERE id=3) dt2) dt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived4>	ALL	NULL	NULL	NULL	NULL	5	Using where
+4	PUSHED DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
 connection slave;
 CREATE TABLE federated.t10 (a INT,b INT);
 CREATE TABLE federated.t11 (a INT, b INT);
@@ -541,6 +558,54 @@ WHERE id=2) dt2) dt
 a	b	a	b	id	name
 1	1	NULL	NULL	NULL	NULL
 2	2	NULL	NULL	NULL	NULL
+#
+# MDEV-31361: Second execution of PS for query with derived table
+#
+connection slave;
+DROP TABLE IF EXISTS federated.t1;
+CREATE TABLE federated.t1 (
+id int(20) NOT NULL,
+name varchar(16) NOT NULL default ''
+)
+DEFAULT CHARSET=latin1;
+INSERT INTO federated.t1 VALUES
+(3,'xxx'), (7,'yyy'), (4,'xxx'), (1,'zzz'), (5,'yyy');
+connection master;
+DROP TABLE IF EXISTS federated.t1;
+CREATE TABLE federated.t1 (
+id int(20) NOT NULL,
+name varchar(16) NOT NULL default ''
+)
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1';
+use federated;
+SELECT * FROM
+(SELECT * FROM
+(SELECT * FROM
+(SELECT * FROM t1 where id>3) dt3
+WHERE id>3) dt2
+) dt;
+id	name
+7	yyy
+4	xxx
+5	yyy
+PREPARE stmt FROM "SELECT * FROM
+(SELECT * FROM
+(SELECT * FROM
+(SELECT * FROM t1 where id>3) dt3
+WHERE id>3) dt2
+) dt";
+EXECUTE stmt;
+id	name
+7	yyy
+4	xxx
+5	yyy
+EXECUTE stmt;
+id	name
+7	yyy
+4	xxx
+5	yyy
+DEALLOCATE PREPARE stmt;
 set global federated_pushdown=0;
 connection master;
 DROP TABLE IF EXISTS federated.t1;
diff --git a/mysql-test/suite/federated/federatedx_create_handlers.test b/mysql-test/suite/federated/federatedx_create_handlers.test
index 61dc32a2..c5ac36de 100644
--- a/mysql-test/suite/federated/federatedx_create_handlers.test
+++ b/mysql-test/suite/federated/federatedx_create_handlers.test
@@ -94,12 +94,9 @@ DEFAULT CHARSET=latin1;
 INSERT INTO federated.t3 VALUES
   ('yyy'), ('www'), ('yyy'), ('xxx'), ('www'), ('yyy'), ('www');
 
-#Enable after fix MDEV-31361
---disable_ps2_protocol
 SELECT *
 FROM federated.t3, (SELECT * FROM federated.t1 WHERE id > 3) t
 WHERE federated.t3.name=t.name;
---enable_ps2_protocol
 
 EXPLAIN
 SELECT *
@@ -354,6 +351,18 @@ use federated;
 SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
   WHERE id=2) dt2) dt;
 
+PREPARE stmt FROM "
+SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
+  WHERE id=3) dt2) dt;
+";
+EXECUTE stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+EXPLAIN
+SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM t1 where id=3) dt3
+  WHERE id=3) dt2) dt;
+
 connection slave;
 CREATE TABLE federated.t10 (a INT,b INT);
 CREATE TABLE federated.t11 (a INT, b INT);
@@ -379,6 +388,52 @@ SELECT * FROM t10 LEFT JOIN
     WHERE id=2) dt2) dt
   ) ON t10.a=t11.a;
 
+--echo #
+--echo # MDEV-31361: Second execution of PS for query with derived table
+--echo #
+
+connection slave;
+DROP TABLE IF EXISTS federated.t1;
+
+CREATE TABLE federated.t1 (
+  id int(20) NOT NULL,
+  name varchar(16) NOT NULL default ''
+)
+DEFAULT CHARSET=latin1;
+
+INSERT INTO federated.t1 VALUES
+  (3,'xxx'), (7,'yyy'), (4,'xxx'), (1,'zzz'), (5,'yyy');
+
+connection master;
+DROP TABLE IF EXISTS federated.t1;
+
+--replace_result $SLAVE_MYPORT SLAVE_PORT
+eval
+CREATE TABLE federated.t1 (
+  id int(20) NOT NULL,
+  name varchar(16) NOT NULL default ''
+)
+ENGINE="FEDERATED" DEFAULT CHARSET=latin1
+CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';
+
+use federated;
+
+let $q=
+SELECT * FROM
+  (SELECT * FROM
+    (SELECT * FROM
+        (SELECT * FROM t1 where id>3) dt3
+      WHERE id>3) dt2
+  ) dt;
+
+eval $q;
+
+eval PREPARE stmt FROM "$q";
+EXECUTE stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+
 set global federated_pushdown=0;
 
 source include/federated_cleanup.inc;