[Commits] Rev 3502: MDEV-430: Server crashes in select_describe on EXPLAIN with materialization+semijoin, etc in file:///home/psergey/dev2/5.5-look7/

Sergey Petrunya psergey at askmonty.org
Tue Aug 28 14:15:07 EEST 2012


At file:///home/psergey/dev2/5.5-look7/

------------------------------------------------------------
revno: 3502
revision-id: psergey at askmonty.org-20120828111505-89iwwmxjkyr3hvkg
parent: sergii at pisem.net-20120822075620-4desl15sge421oy8
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.5-look7
timestamp: Tue 2012-08-28 15:15:05 +0400
message:
  MDEV-430: Server crashes in select_describe on EXPLAIN with materialization+semijoin, etc
  - Don't do early cleanup of uncorrelated subqueries if we're running an EXPLAIN.
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2012-08-21 12:24:43 +0000
+++ b/mysql-test/r/subselect.result	2012-08-28 11:15:05 +0000
@@ -5966,7 +5966,7 @@ id	select_type	table	type	possible_keys	
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6127,7 +6127,7 @@ set optimizer_switch=@tmp_optimizer_swit
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6806,5 +6806,19 @@ Table_schema	Table_name	Index_name	Rows_
 test	t2	b	1
 set global userstat=@tmp_mdev410;
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2012-08-21 12:24:43 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2012-08-28 11:15:05 +0000
@@ -5967,7 +5967,7 @@ id	select_type	table	type	possible_keys	
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6126,7 +6126,7 @@ set optimizer_switch=@tmp_optimizer_swit
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6804,6 +6804,20 @@ Table_schema	Table_name	Index_name	Rows_
 test	t2	b	1
 set global userstat=@tmp_mdev410;
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2012-08-21 12:24:43 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2012-08-28 11:15:05 +0000
@@ -5963,7 +5963,7 @@ id	select_type	table	type	possible_keys	
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6122,7 +6122,7 @@ set optimizer_switch=@tmp_optimizer_swit
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6801,6 +6801,20 @@ Table_schema	Table_name	Index_name	Rows_
 test	t2	b	1
 set global userstat=@tmp_mdev410;
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set @optimizer_switch_for_subselect_test=null;

=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2012-08-21 12:24:43 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2012-08-28 11:15:05 +0000
@@ -5972,7 +5972,7 @@ id	select_type	table	type	possible_keys	
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6133,7 +6133,7 @@ set optimizer_switch=@tmp_optimizer_swit
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6812,6 +6812,20 @@ Table_schema	Table_name	Index_name	Rows_
 test	t2	b	1
 set global userstat=@tmp_mdev410;
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2012-08-21 12:24:43 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2012-08-28 11:15:05 +0000
@@ -5963,7 +5963,7 @@ id	select_type	table	type	possible_keys	
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 DROP TABLE t1;
 #
 # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
@@ -6122,7 +6122,7 @@ set optimizer_switch=@tmp_optimizer_swit
 EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
-2	SUBQUERY	t1	ref	a	a	5	const	1	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using index
 
 DROP TABLE t1;
 #
@@ -6801,6 +6801,20 @@ Table_schema	Table_name	Index_name	Rows_
 test	t2	b	1
 set global userstat=@tmp_mdev410;
 DROP TABLE t1,t2,t3,t4;
+#
+# MDEV-430: Server crashes in select_describe on EXPLAIN with 
+#    materialization+semijoin, 2 nested subqueries, aggregate functions
+#
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;
 set @optimizer_switch_for_subselect_test=null;

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2012-07-25 16:41:48 +0000
+++ b/mysql-test/t/subselect.test	2012-08-28 11:15:05 +0000
@@ -5721,5 +5721,19 @@ set global userstat=@tmp_mdev410;
 
 DROP TABLE t1,t2,t3,t4;
 
+--echo #
+--echo # MDEV-430: Server crashes in select_describe on EXPLAIN with 
+--echo #    materialization+semijoin, 2 nested subqueries, aggregate functions
+--echo #
+CREATE TABLE t1 (a INT, KEY(a));
+INSERT INTO t1 VALUES (1),(8);
+
+CREATE TABLE t2 (b INT, KEY(b));
+INSERT INTO t2 VALUES (45),(17),(20);
+
+EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
+
+DROP TABLE t1,t2;
+
 --echo # return optimizer switch changed in the beginning of this test
 set optimizer_switch=@subselect_tmp;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-08-21 12:24:43 +0000
+++ b/sql/sql_select.cc	2012-08-28 11:15:05 +0000
@@ -10543,7 +10543,7 @@ void JOIN::join_free()
     Optimization: if not EXPLAIN and we are done with the JOIN,
     free all tables.
   */
-  bool full= !(select_lex->uncacheable);
+  bool full= !(select_lex->uncacheable) &&  !(thd->lex->describe);
   bool can_unlock= full;
   DBUG_ENTER("JOIN::join_free");
 



More information about the commits mailing list