[Commits] Rev 3512: MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery in file:///home/psergey/dev2/5.5-look7/

Sergey Petrunya psergey at askmonty.org
Tue Aug 28 14:40:43 EEST 2012


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

------------------------------------------------------------
revno: 3512
revision-id: psergey at askmonty.org-20120828114038-kdjs5yfeyx8mb5d2
parent: psergey at askmonty.org-20120828112037-16ga9yq0akyd0fzc
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.5-look7
timestamp: Tue 2012-08-28 15:40:38 +0400
message:
  MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
  - Testcase
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2012-08-28 11:20:37 +0000
+++ b/mysql-test/r/subselect.result	2012-08-28 11:40:38 +0000
@@ -6846,3 +6846,16 @@ id	select_type	table	type	possible_keys	
 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;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+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;

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2012-08-28 11:20:37 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2012-08-28 11:40:38 +0000
@@ -6844,6 +6844,19 @@ id	select_type	table	type	possible_keys	
 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;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+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;
 set optimizer_switch=default;
 select @@optimizer_switch like '%materialization=on%';
 @@optimizer_switch like '%materialization=on%'

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2012-08-28 11:20:37 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2012-08-28 11:40:38 +0000
@@ -6841,4 +6841,17 @@ id	select_type	table	type	possible_keys	
 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;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+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;
 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-28 11:20:37 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2012-08-28 11:40:38 +0000
@@ -6852,6 +6852,19 @@ id	select_type	table	type	possible_keys	
 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;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+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;
 set optimizer_switch=default;
 select @@optimizer_switch like '%subquery_cache=on%';
 @@optimizer_switch like '%subquery_cache=on%'

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2012-08-28 11:20:37 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2012-08-28 11:40:38 +0000
@@ -6841,5 +6841,18 @@ id	select_type	table	type	possible_keys	
 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;
+#
+# MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+#
+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;
 set @optimizer_switch_for_subselect_test=null;
 set @join_cache_level_for_subselect_test=NULL;

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2012-08-28 11:20:37 +0000
+++ b/mysql-test/t/subselect.test	2012-08-28 11:40:38 +0000
@@ -5756,3 +5756,16 @@ EXPLAIN SELECT * FROM t1 WHERE EXISTS ( 
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # MDEV-405: Server crashes in test_if_skip_sort_order on EXPLAIN with GROUP BY and HAVING in EXISTS subquery
+--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;
+



More information about the commits mailing list