From ca8aa3901c5596d8d393f0f0ded5ea7292ed01c8 Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Thu, 26 Apr 2012 06:40:36 +0530
Subject: MWL#182: Explain running statements - Code cleanup

---
 mysql-test/r/show_explain.result | 58 +++++++++++++++++++++++++++++++-
 mysql-test/t/show_explain.test   | 71 +++++++++++++++++++++++++++++++++++++---
 2 files changed, 124 insertions(+), 5 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index 6742c6eacfe..07ccab1419e 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -1,4 +1,4 @@
-drop table if exists t0, t1;
+drop table if exists t0, t1, t2;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int);
@@ -125,4 +125,60 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
 a	(select max(a) from t0 b where b.a+a.a<10)
 0	9
+# Try to do SHOW EXPLAIN for a query that runs a  SET command:
+#   I've found experimentally that select_id==2 here...
+# 
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+set @foo= (select max(a) from t0 where sin(a) >0);
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+#
+# Attempt SHOW EXPLAIN for an UPDATE
+#
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+drop table t2;
+#
+# Attempt SHOW EXPLAIN for a DELETE
+# 
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+drop table t2;
+#
+# Multiple SHOW EXPLAIN calls for one select 
+# 
+create table t2 as select a as a, a as dummy from t0 limit 3;
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+a	SUBQ
+0	0
+1	0
+2	0
+drop table t2;
 drop table t0,t1;
diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test
index 5ed78be1ea4..717949d5cc5 100644
--- a/mysql-test/t/show_explain.test
+++ b/mysql-test/t/show_explain.test
@@ -4,7 +4,7 @@
 --source include/have_debug.inc
 
 --disable_warnings
-drop table if exists t0, t1;
+drop table if exists t0, t1, t2;
 --enable_warnings
 
 # 
@@ -186,12 +186,75 @@ reap;
 
 # TODO: explain in the parent subuqery when the un-correlated child has been
 # run (and have done irreversible cleanups)
+#  ^^ Is this at all possible after 5.3? 
+#     Maybe, for 5.3 try this: 
+#       - run before/after the parent has invoked child's optimization
+#       - run after materialization 
+
+--echo # Try to do SHOW EXPLAIN for a query that runs a  SET command:
+--echo #   I've found experimentally that select_id==2 here...
+--echo # 
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+send set @foo= (select max(a) from t0 where sin(a) >0);
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
 
-# TODO: hit JOIN::optimize for non-select commands: UPDATE/DELETE, SET.
+--echo #
+--echo # Attempt SHOW EXPLAIN for an UPDATE
+--echo #
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+send update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+drop table t2;
 
+--echo #
+--echo # Attempt SHOW EXPLAIN for a DELETE
+--echo # 
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+send delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+drop table t2;
+
+
+--echo #
+--echo # Multiple SHOW EXPLAIN calls for one select 
+--echo # 
+create table t2 as select a as a, a as dummy from t0 limit 3;
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_1';
+send select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+evalp show explain for $thr2;
+evalp show explain for $thr2;
+connection con1;
+reap;
+drop table t2;
 
-## TODO: Test this: multiple SHOW EXPLAIN calls in course of running of one select
-## 
 ## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
 ##       thread and served together.
 
-- 
cgit v1.2.1


From ff40705f89b977d966427710e1b32a0774a2f0b3 Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Thu, 26 Apr 2012 08:48:31 +0530
Subject: Make SHOW EXPLAIN FOR produce a warning with the original text of
 query that the EXPLAIN is for.

---
 mysql-test/r/show_explain.result | 28 ++++++++++++++++++++++++++++
 1 file changed, 28 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index 07ccab1419e..e451ef3108b 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -20,12 +20,16 @@ select count(*) from t1 where a < 100000;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	a	a	5	NULL	1000	Using where; Using index
+Warnings:
+Note	1003	select count(*) from t1 where a < 100000
 count(*)
 1000
 select max(c) from t1 where a < 10;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	a	a	5	NULL	10	Using index condition
+Warnings:
+Note	1003	select max(c) from t1 where a < 10
 max(c)
 9
 # We can catch EXPLAIN, too.
@@ -35,6 +39,8 @@ explain select max(c) from t1 where a < 10;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	a	a	5	NULL	10	Using index condition; Rowid-ordered scan
+Warnings:
+Note	1003	explain select max(c) from t1 where a < 10
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	a	a	5	NULL	10	Using index condition; Rowid-ordered scan
 set optimizer_switch= @show_expl_tmp;
@@ -47,6 +53,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
 2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	
 NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	explain select a from t0 A union select a+1 from t0 B
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
 2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	
@@ -60,6 +68,8 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
 2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	
 NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	explain select a from t0 A union select a+1 from t0 B
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
 2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	
@@ -72,6 +82,8 @@ show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	
+Warnings:
+Note	1003	select a, (select max(a) from t0 B) from t0 A where a<1
 a	(select max(a) from t0 B)
 0	9
 # Uncorrelated  subquery, explain
@@ -82,6 +94,8 @@ show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	
+Warnings:
+Note	1003	explain select a, (select max(a) from t0 B) from t0 A where a<1
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	
@@ -93,6 +107,8 @@ show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
 a	(select max(a) from t0 b where b.a+a.a<10)
 0	9
 # correlated  subquery, explain
@@ -103,6 +119,8 @@ show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
 a	(select max(a) from t0 b where b.a+a.a<10)
 0	9
 # correlated  subquery, select, while inside the subquery
@@ -113,6 +131,8 @@ show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
 a	(select max(a) from t0 b where b.a+a.a<10)
 0	9
 # correlated  subquery, explain, while inside the subquery
@@ -123,6 +143,8 @@ show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
 a	(select max(a) from t0 b where b.a+a.a<10)
 0	9
 # Try to do SHOW EXPLAIN for a query that runs a  SET command:
@@ -168,14 +190,20 @@ show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
 2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
 2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
 2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
 a	SUBQ
 0	0
 1	0
-- 
cgit v1.2.1


From cdc9a1172d7b75b16d92a6179478c2689ac04bae Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Thu, 10 May 2012 01:45:38 +0530
Subject: MWL#182: Explain running statements: Make SHOW EXPLAIN work for
 queries that do "Using temporary" and/or "Using filesort" - Patch#1: Don't
 lose "Using temporary/filesort" in the SHOW EXPLAIN output.

---
 mysql-test/r/show_explain.result | 75 ++++++++++++++++++++++++++++++++++++++++
 mysql-test/t/show_explain.test   | 46 ++++++++++++++++++++++++
 2 files changed, 121 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index e451ef3108b..74def21585f 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -209,4 +209,79 @@ a	SUBQ
 1	0
 2	0
 drop table t2;
+#
+# SHOW EXPLAIN for SELECT ... ORDER BY with "Using filesort"
+#
+explain select * from t0 order by a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using filesort
+set debug='d,show_explain_probe_1';
+set @show_explain_probe_select_id=1;
+select * from t0 order by a;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using filesort
+Warnings:
+Note	1003	select * from t0 order by a
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+#
+# SHOW EXPLAIN for SELECT ... with "Using temporary"
+#
+explain select distinct a from t0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using temporary
+set debug='d,show_explain_probe_1';
+set @show_explain_probe_select_id=1;
+select distinct a from t0;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using temporary
+Warnings:
+Note	1003	select distinct a from t0
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+#
+# SHOW EXPLAIN for SELECT ... with "Using temporary; Using filesort"
+#
+explain select distinct a from t0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using temporary
+set debug='d,show_explain_probe_1';
+set @show_explain_probe_select_id=1;
+select distinct a from t0;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using temporary
+Warnings:
+Note	1003	select distinct a from t0
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
 drop table t0,t1;
diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test
index 717949d5cc5..4fc6aaad932 100644
--- a/mysql-test/t/show_explain.test
+++ b/mysql-test/t/show_explain.test
@@ -255,6 +255,52 @@ connection con1;
 reap;
 drop table t2;
 
+--echo #
+--echo # SHOW EXPLAIN for SELECT ... ORDER BY with "Using filesort"
+--echo #
+explain select * from t0 order by a;
+
+set debug='d,show_explain_probe_1';
+set @show_explain_probe_select_id=1;
+send select * from t0 order by a; 
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo #
+--echo # SHOW EXPLAIN for SELECT ... with "Using temporary"
+--echo #
+connection default;
+explain select distinct a from t0;
+connection con1;
+
+set debug='d,show_explain_probe_1';
+set @show_explain_probe_select_id=1;
+send select distinct a from t0; 
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo #
+--echo # SHOW EXPLAIN for SELECT ... with "Using temporary; Using filesort"
+--echo #
+connection default;
+explain select distinct a from t0;
+connection con1;
+
+set debug='d,show_explain_probe_1';
+set @show_explain_probe_select_id=1;
+send select distinct a from t0; 
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
 ## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
 ##       thread and served together.
 
-- 
cgit v1.2.1


From 58b9164f0468768eff64841f4f066840b9ff2206 Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Thu, 10 May 2012 13:43:48 +0400
Subject: MDEV-238: SHOW EXPLAIN: Server crashes in JOIN::print_explain with
 FROM subquery and GROUP BY - Support SHOW EXPLAIN for selects that have
 "Using temporary; Using filesort".

---
 mysql-test/r/show_explain.result | 25 +++++++++++++++++++++++++
 mysql-test/t/show_explain.test   | 22 ++++++++++++++++++++++
 2 files changed, 47 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index 74def21585f..db1d57b7004 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -284,4 +284,29 @@ a
 7
 8
 9
+set debug='';
+#
+# MDEV-238: SHOW EXPLAIN: Server crashes in JOIN::print_explain with FROM subquery and GROUP BY
+#
+CREATE TABLE t2 ( a INT );
+INSERT INTO t2 VALUES (1),(2),(1),(4),(2);
+explain SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
+set debug='d,show_explain_in_find_all_keys';
+SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a;
+# NOTE: current code will not show "Using join buffer":
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	
+Warnings:
+Note	1003	SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a
+a
+1
+2
+4
+set debug='';
+DROP TABLE t2;
 drop table t0,t1;
diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test
index 4fc6aaad932..8c9d5f4c1e8 100644
--- a/mysql-test/t/show_explain.test
+++ b/mysql-test/t/show_explain.test
@@ -300,8 +300,30 @@ connection default;
 evalp show explain for $thr2;
 connection con1;
 reap;
+set debug='';
+
+--echo #
+--echo # MDEV-238: SHOW EXPLAIN: Server crashes in JOIN::print_explain with FROM subquery and GROUP BY
+--echo #
+CREATE TABLE t2 ( a INT ); 
+INSERT INTO t2 VALUES (1),(2),(1),(4),(2);
+explain SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a;
+
+set debug='d,show_explain_in_find_all_keys';
+send SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a;
+
+connection default;
+--source include/wait_condition.inc
+--echo # NOTE: current code will not show "Using join buffer":
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug='';
+
 
 ## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
 ##       thread and served together.
 
+DROP TABLE t2;
+
 drop table t0,t1;
-- 
cgit v1.2.1


From 6fae4447f0873c159d94d1a3d8deafbd224d8100 Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Thu, 10 May 2012 15:13:57 +0400
Subject: # MDEV-239: Assertion `field_types == 0 ... ' failed in
 Protocol_text::store... - Make all functions that produce parts of EXPLAIN
 output take   explain_flags as parameter, instead of looking into
 thd->lex->describe

---
 mysql-test/r/show_explain.result | 28 ++++++++++++++++++++++++++++
 mysql-test/t/show_explain.test   | 27 +++++++++++++++++++++++++--
 2 files changed, 53 insertions(+), 2 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index db1d57b7004..4f0e89975ff 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -309,4 +309,32 @@ a
 4
 set debug='';
 DROP TABLE t2;
+#
+# MDEV-239: Assertion `field_types == 0 ... ' failed in Protocol_text::store(double, uint32, String*) with 
+#          SHOW EXPLAIN over EXPLAIN EXTENDED
+#
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 VALUES (1),(2),(1),(4),(2);
+EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` join `test`.`t2` group by `test`.`t2`.`a`
+set @show_explain_probe_select_id=1;
+set debug='d,show_explain_probe_join_exec_end';
+EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` join `test`.`t2` group by `test`.`t2`.`a`
+set debug='';
+DROP TABLE t2;
 drop table t0,t1;
diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test
index 8c9d5f4c1e8..02850efedd8 100644
--- a/mysql-test/t/show_explain.test
+++ b/mysql-test/t/show_explain.test
@@ -319,11 +319,34 @@ evalp show explain for $thr2;
 connection con1;
 reap;
 set debug='';
+DROP TABLE t2;
 
 
-## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
-##       thread and served together.
+--echo #
+--echo # MDEV-239: Assertion `field_types == 0 ... ' failed in Protocol_text::store(double, uint32, String*) with 
+--echo #          SHOW EXPLAIN over EXPLAIN EXTENDED
+--echo #
+
+
+CREATE TABLE t2 (a INT); 
+INSERT INTO t2 VALUES (1),(2),(1),(4),(2);
+
+EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ;
+
+set @show_explain_probe_select_id=1;
+set debug='d,show_explain_probe_join_exec_end';
+send EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ;
 
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug='';
 DROP TABLE t2;
 
+
+## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
+##       thread and served together.
+
 drop table t0,t1;
-- 
cgit v1.2.1


From 6bce336624e84f5ec377926b105ec2002b38c96b Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Fri, 11 May 2012 18:13:06 +0400
Subject: MDEV-240: SHOW EXPLAIN: Assertion `this->optimized == 2' failed - Fix
 the bug: SHOW EXPLAIN may hit a case where a join is partially   optimized. -
 Change JOIN::optimized to use enum instead of numeric constants

---
 mysql-test/r/show_explain.result | 33 +++++++++++++++++++++++++++++++++
 mysql-test/t/show_explain.test   | 25 +++++++++++++++++++++++++
 2 files changed, 58 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index 4f0e89975ff..2051a409178 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -1,4 +1,5 @@
 drop table if exists t0, t1, t2;
+drop view if exists v1;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int);
@@ -337,4 +338,36 @@ Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` join `test`.`t2` group by `test`.`t2`.`a`
 set debug='';
 DROP TABLE t2;
+#
+# MDEV-240: SHOW EXPLAIN: Assertion `this->optimized == 2' failed in 
+# JOIN::print_explain on query with a JOIN, TEMPTABLE view,
+#          
+CREATE TABLE t3 (a INT);
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t3;
+INSERT INTO t3 VALUES (8);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (4),(5),(6),(7),(8),(9);
+explain SELECT * FROM v1, t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	
+2	DERIVED	t3	system	NULL	NULL	NULL	NULL	1	
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_join_exec_end';
+SELECT * FROM v1, t2;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Not yet optimized
+Warnings:
+Note	1003	SELECT * FROM v1, t2
+a	b
+8	4
+8	5
+8	6
+8	7
+8	8
+8	9
+set debug='';
+DROP VIEW v1;
+DROP TABLE t2, t3;
 drop table t0,t1;
diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test
index 02850efedd8..36c34c8df5c 100644
--- a/mysql-test/t/show_explain.test
+++ b/mysql-test/t/show_explain.test
@@ -5,6 +5,7 @@
 
 --disable_warnings
 drop table if exists t0, t1, t2;
+drop view if exists v1;
 --enable_warnings
 
 # 
@@ -346,6 +347,30 @@ set debug='';
 DROP TABLE t2;
 
 
+--echo #
+--echo # MDEV-240: SHOW EXPLAIN: Assertion `this->optimized == 2' failed in 
+--echo # JOIN::print_explain on query with a JOIN, TEMPTABLE view,
+--echo #          
+CREATE TABLE t3 (a INT); 
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t3;
+INSERT INTO t3 VALUES (8);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (4),(5),(6),(7),(8),(9);
+explain SELECT * FROM v1, t2;
+
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_join_exec_end';
+send SELECT * FROM v1, t2;
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug='';
+DROP VIEW v1;
+DROP TABLE t2, t3;
+
 ## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
 ##       thread and served together.
 
-- 
cgit v1.2.1


From b3841ae9e4754e2aae1c106767d4ba50236cbf9b Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Mon, 14 May 2012 22:39:00 +0400
Subject: MDEV-267: SHOW EXPLAIN: Server crashes in JOIN::print_explain on most
 of queries - Make SHOW EXPLAIN code handle degenerate subquery cases (No
 tables, impossible where, etc)

---
 mysql-test/r/show_explain.result | 44 ++++++++++++++++++++++++++++++++++++++++
 mysql-test/t/show_explain.test   | 43 +++++++++++++++++++++++++++++++++++++++
 2 files changed, 87 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index 2051a409178..b09903a617a 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -370,4 +370,48 @@ a	b
 set debug='';
 DROP VIEW v1;
 DROP TABLE t2, t3;
+#
+# MDEV-267: SHOW EXPLAIN: Server crashes in JOIN::print_explain on most of queries
+#
+set @show_explain_probe_select_id=1;
+set debug='d,show_explain_probe_join_exec_end';
+select sleep(1);
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select sleep(1)
+sleep(1)
+0
+set debug='';
+#
+# Same as above, but try another reason for JOIN to be degenerate
+#
+set @show_explain_probe_select_id=1;
+set debug='d,show_explain_probe_join_exec_end';
+select * from t0 where 1>10;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	select * from t0 where 1>10
+a
+set debug='';
+#
+# Same as above, but try another reason for JOIN to be degenerate (2)
+#
+create table t3(a int primary key);
+insert into t3 select a from t0;
+set @show_explain_probe_select_id=1;
+set debug='d,show_explain_probe_join_exec_end';
+select * from t0,t3 where t3.a=112233;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	const	PRIMARY	PRIMARY	4		0	unique row not found
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	
+Warnings:
+Note	1003	select * from t0,t3 where t3.a=112233
+a	a
+set debug='';
+drop table t3;
 drop table t0,t1;
diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test
index 36c34c8df5c..c9f715bbb67 100644
--- a/mysql-test/t/show_explain.test
+++ b/mysql-test/t/show_explain.test
@@ -371,6 +371,49 @@ set debug='';
 DROP VIEW v1;
 DROP TABLE t2, t3;
 
+--echo #
+--echo # MDEV-267: SHOW EXPLAIN: Server crashes in JOIN::print_explain on most of queries
+--echo #
+set @show_explain_probe_select_id=1;
+set debug='d,show_explain_probe_join_exec_end';
+send select sleep(1);
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug='';
+
+
+--echo #
+--echo # Same as above, but try another reason for JOIN to be degenerate
+--echo #
+set @show_explain_probe_select_id=1;
+set debug='d,show_explain_probe_join_exec_end';
+send select * from t0 where 1>10;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug='';
+
+--echo #
+--echo # Same as above, but try another reason for JOIN to be degenerate (2)
+--echo #
+create table t3(a int primary key);
+insert into t3 select a from t0;
+set @show_explain_probe_select_id=1;
+set debug='d,show_explain_probe_join_exec_end';
+send select * from t0,t3 where t3.a=112233;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug='';
+drop table t3;
+
 ## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
 ##       thread and served together.
 
-- 
cgit v1.2.1


From 382e81ca84a51fded86df6f21e0b4e003fc94388 Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Tue, 15 May 2012 15:56:50 +0400
Subject: MDEV-270: SHOW EXPLAIN: server crashes in JOIN::print_explain on a
 query with  select tables optimized away - Take into account, that for some
 degenerate joins instead of "join->table_count=0"   the code sets
 "join->tables_list=0".

---
 mysql-test/r/show_explain.result | 39 +++++++++++++++++++++++++++++++++++++++
 mysql-test/t/show_explain.test   | 34 ++++++++++++++++++++++++++++++++++
 2 files changed, 73 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index b09903a617a..1b1c30b56df 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -414,4 +414,43 @@ Note	1003	select * from t0,t3 where t3.a=112233
 a	a
 set debug='';
 drop table t3;
+#
+# MDEV-270: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with 
+#           select tables optimized away
+#
+CREATE TABLE t2 (pk INT PRIMARY KEY, a INT ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES 
+(1,4),(2,62),(3,7),(4,1),(5,0),(6,7),(7,7),(8,1),(9,7),(10,1),
+(11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5) ;
+explain SELECT * FROM t2 WHERE a = 
+(SELECT MAX(a) FROM t2 
+WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where
+2	SUBQUERY	t2	const	PRIMARY	PRIMARY	4	const	1	Using where
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_do_select';
+SELECT * FROM t2 WHERE a = 
+(SELECT MAX(a) FROM t2 
+WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+);
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where
+2	SUBQUERY	t2	const	PRIMARY	PRIMARY	4		1	Using where
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+Warnings:
+Note	1003	SELECT * FROM t2 WHERE a = 
+(SELECT MAX(a) FROM t2 
+WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+)
+pk	a
+3	7
+6	7
+7	7
+9	7
+set debug='';
+drop table t2;
 drop table t0,t1;
diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test
index c9f715bbb67..d813956a1fe 100644
--- a/mysql-test/t/show_explain.test
+++ b/mysql-test/t/show_explain.test
@@ -414,6 +414,40 @@ reap;
 set debug='';
 drop table t3;
 
+--echo #
+--echo # MDEV-270: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with 
+--echo #           select tables optimized away
+--echo #
+
+CREATE TABLE t2 (pk INT PRIMARY KEY, a INT ) ENGINE=MyISAM; 
+INSERT INTO t2 VALUES 
+  (1,4),(2,62),(3,7),(4,1),(5,0),(6,7),(7,7),(8,1),(9,7),(10,1),
+  (11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5) ;
+
+explain SELECT * FROM t2 WHERE a = 
+       (SELECT MAX(a) FROM t2 
+        WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+       );
+
+set @show_explain_probe_select_id=2;
+set debug='d,show_explain_probe_do_select';
+send SELECT * FROM t2 WHERE a = 
+       (SELECT MAX(a) FROM t2 
+        WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+       );
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug='';
+drop table t2;
+
+
+
+
+
+
 ## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
 ##       thread and served together.
 
-- 
cgit v1.2.1


From 533e1d28459ab5aa0604560bf8885ae18e553295 Mon Sep 17 00:00:00 2001
From: Sergey Petrunya <psergey@askmonty.org>
Date: Wed, 16 May 2012 12:49:22 +0400
Subject: MDEV-273: SHOW EXPLAIN: server crashes in JOIN::print_explain on a
 query with impossible WHERE - It turns out, there is a case where the join is
 degenerate, but   join->table_count!= && join->tables_list!=NULL. Need to
 also check   if join->zero_result_cause!=NULL, too.

- There is a slight problem: The code sets
    zero_result_cause= "no matching row in const table"
  when NOT running EXPLAIN. The result is that SHOW EXPLAIN will show this line while
  regular EXPLAIN will not.
---
 mysql-test/r/show_explain.result | 56 +++++++++++++++++++++++++++++++++++++---
 mysql-test/t/show_explain.test   | 45 ++++++++++++++++++++++++++++++--
 2 files changed, 96 insertions(+), 5 deletions(-)

(limited to 'mysql-test')

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index 1b1c30b56df..d19a6f29539 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -1,4 +1,4 @@
-drop table if exists t0, t1, t2;
+drop table if exists t0, t1, t2, t3, t4;
 drop view if exists v1;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
@@ -407,8 +407,7 @@ set debug='d,show_explain_probe_join_exec_end';
 select * from t0,t3 where t3.a=112233;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	const	PRIMARY	PRIMARY	4		0	unique row not found
-1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 Warnings:
 Note	1003	select * from t0,t3 where t3.a=112233
 a	a
@@ -453,4 +452,55 @@ pk	a
 9	7
 set debug='';
 drop table t2;
+#
+# MDEV-273: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with impossible WHERE
+#
+CREATE TABLE t2 (a1 INT, KEY(a1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(4),(6),(7),(1),(0),(7),(7),(1),(7),(1),
+(5),(2),(0),(1),(8),(1),(1),(9),(1),(5);
+CREATE TABLE t3 (b1 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES
+(4),(5),(8),(4),(8),(2),(9),(6),(4),(8),
+(3),(5),(9),(6),(8),(3),(2),(6),(3),(1),
+(4),(3),(1),(7),(0),(0),(9),(5),(9),(0),
+(2),(2),(5),(9),(1),(4),(8),(6),(5),(5),
+(1),(7),(2),(8),(9),(3),(2),(6),(6),(5),
+(4),(3),(2),(7),(4),(6),(0),(8),(5),(8),
+(2),(9),(7),(5),(7),(0),(4),(3),(1),(0),
+(6),(2),(8),(3),(7),(3),(5),(5),(1),(2),
+(1),(7),(1),(9),(9),(8),(3);
+CREATE TABLE t4 (c1 INT) ENGINE=MyISAM;
+EXPLAIN
+SELECT count(*) FROM t2, t3
+WHERE a1 < ALL ( 
+SELECT a1 FROM t2
+WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	NULL	a1	5	NULL	20	Using where; Using index
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	87	Using join buffer (flat, BNL join)
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+set @show_explain_probe_select_id=1;
+set debug='d,show_explain_probe_do_select';
+SELECT count(*) FROM t2, t3
+WHERE a1 < ALL ( 
+SELECT a1 FROM t2
+WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+);
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	NULL	a1	5	NULL	20	Using where; Using index
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	87	Using join buffer (flat, BNL join)
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+Warnings:
+Note	1003	SELECT count(*) FROM t2, t3
+WHERE a1 < ALL ( 
+SELECT a1 FROM t2
+WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+)
+count(*)
+1740
+set debug='';
+drop table t2, t3, t4;
 drop table t0,t1;
diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test
index d813956a1fe..35694e97111 100644
--- a/mysql-test/t/show_explain.test
+++ b/mysql-test/t/show_explain.test
@@ -4,7 +4,7 @@
 --source include/have_debug.inc
 
 --disable_warnings
-drop table if exists t0, t1, t2;
+drop table if exists t0, t1, t2, t3, t4;
 drop view if exists v1;
 --enable_warnings
 
@@ -444,9 +444,50 @@ set debug='';
 drop table t2;
 
 
+--echo #
+--echo # MDEV-273: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with impossible WHERE
+--echo #
+CREATE TABLE t2 (a1 INT, KEY(a1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+  (4),(6),(7),(1),(0),(7),(7),(1),(7),(1),
+  (5),(2),(0),(1),(8),(1),(1),(9),(1),(5);
+
+CREATE TABLE t3 (b1 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES
+  (4),(5),(8),(4),(8),(2),(9),(6),(4),(8),
+  (3),(5),(9),(6),(8),(3),(2),(6),(3),(1),
+  (4),(3),(1),(7),(0),(0),(9),(5),(9),(0),
+  (2),(2),(5),(9),(1),(4),(8),(6),(5),(5),
+  (1),(7),(2),(8),(9),(3),(2),(6),(6),(5),
+  (4),(3),(2),(7),(4),(6),(0),(8),(5),(8),
+  (2),(9),(7),(5),(7),(0),(4),(3),(1),(0),
+  (6),(2),(8),(3),(7),(3),(5),(5),(1),(2),
+  (1),(7),(1),(9),(9),(8),(3);
+CREATE TABLE t4 (c1 INT) ENGINE=MyISAM;
+
+EXPLAIN
+SELECT count(*) FROM t2, t3
+WHERE a1 < ALL ( 
+  SELECT a1 FROM t2
+  WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+);
 
+set @show_explain_probe_select_id=1;
+set debug='d,show_explain_probe_do_select';
+send
+SELECT count(*) FROM t2, t3
+WHERE a1 < ALL ( 
+  SELECT a1 FROM t2
+  WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+);
 
-
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug='';
+drop table t2, t3, t4;
 
 ## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
 ##       thread and served together.
-- 
cgit v1.2.1