MDEV-327: SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN and EXPLAIN

- SHOW EXPLAIN actually produced correct plan
- Apply fix for lp:1013343 to make EXPLAIN and SHOW EXPLAIN uniform.

2 files changed, 71 insertions, 0 deletions

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index 14569b5c14b..1c18112a7e5 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -659,4 +659,40 @@ Note	1003	SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 )
 a
 set debug_dbug='';
 DROP TABLE t1;
+#
+# MDEV-327: SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN 
+#           and standard EXPLAIN: 'SUBQUERY' vs 'DEPENDENT SUBQUERY'
+#
+CREATE TABLE t1 (a INT) ENGINE=Aria;
+INSERT INTO t1 VALUES
+(4),(6),(3),(5),(3),(246),(2),(9),(3),(8),
+(1),(8),(8),(5),(7),(5),(1),(6),(2),(9);
+CREATE TABLE t2 (b INT) ENGINE=Aria;
+INSERT INTO t2 VALUES
+(1),(7),(4),(7),(0),(2),(9),(4),(0),(9),
+(1),(3),(8),(8),(18),(84),(6),(3),(6),(6);
+EXPLAIN
+SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	
+3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	20	
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	
+3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	20	
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where
+Warnings:
+Note	1003	SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b )
+a	b
+set debug_dbug='';
+DROP TABLE t1, t2;
 drop table t0;
diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test
index 1c890262758..d7e5a0bc6fd 100644
--- a/mysql-test/t/show_explain.test
+++ b/mysql-test/t/show_explain.test
@@ -662,6 +662,41 @@ set debug_dbug='';
 
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-327: SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN 
+--echo #           and standard EXPLAIN: 'SUBQUERY' vs 'DEPENDENT SUBQUERY'
+--echo #
+CREATE TABLE t1 (a INT) ENGINE=Aria;
+INSERT INTO t1 VALUES
+(4),(6),(3),(5),(3),(246),(2),(9),(3),(8),
+(1),(8),(8),(5),(7),(5),(1),(6),(2),(9);
+
+CREATE TABLE t2 (b INT) ENGINE=Aria;
+INSERT INTO t2 VALUES
+(1),(7),(4),(7),(0),(2),(9),(4),(0),(9),
+(1),(3),(8),(8),(18),(84),(6),(3),(6),(6);
+
+EXPLAIN
+SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+--send
+SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+
+connection con1;
+reap;
+
+set debug_dbug='';
+
+DROP TABLE t1, t2;
+
 ## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
 ##       thread and served together.