[Commits] Rev 3394: Back-ported test cases for bug #59919 of mysql-5.6 code line. The bug could in file:///home/igor/maria/maria-5.3-trunk/

Igor Babaev igor at askmonty.org
Sat Jan 28 05:01:27 EET 2012


At file:///home/igor/maria/maria-5.3-trunk/

------------------------------------------------------------
revno: 3394
revision-id: igor at askmonty.org-20120128030126-kuqny1kdwvde4220
parent: sanja at askmonty.org-20120123131413-uv5t0muz46ukwy2u
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-trunk
timestamp: Fri 2012-01-27 19:01:26 -0800
message:
  Back-ported test cases for bug #59919 of mysql-5.6 code line. The bug could
  not be reproduced in the latest release of mariadb-5.3 as it was was fixed
  by Sergey Petrunia when working on the problems concerning outer joins within
  in subqueries converted to semi-joins.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2012-01-22 20:54:30 +0000
+++ b/mysql-test/r/subselect_sj.result	2012-01-28 03:01:26 +0000
@@ -1538,6 +1538,58 @@
 set optimizer_switch=@save_optimizer_switch;
 DROP TABLE ot1, ot2, ot3, it1;
 #
+# Bug#59919/11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t3 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+INSERT INTO t3 VALUES 
+(1,1), (2,1), (5,4), (7,3), (8,2), (8,1), (7,3),
+(9,5), (4,3), (7,2), (7,7), (3,1), (5,8), (9,7);
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin=off,materialization=on';
+EXPLAIN
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	14	Using where
+2	MATERIALIZED	t1	system	NULL	NULL	NULL	NULL	1	
+2	MATERIALIZED	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	MATERIALIZED	b2	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+8	1
+3	1
+set optimizer_switch='semijoin=on,materialization=on';
+EXPLAIN
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	14	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	MATERIALIZED	b2	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+8	1
+3	1
+set optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1, t2, t3 ;
+#
+#
 # BUG#784723: Wrong result with semijoin + nested subqueries in maria-5.3  
 #
 CREATE TABLE t1 ( t1field integer, primary key (t1field));

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2012-01-22 20:54:30 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2012-01-28 03:01:26 +0000
@@ -1551,6 +1551,58 @@
 set optimizer_switch=@save_optimizer_switch;
 DROP TABLE ot1, ot2, ot3, it1;
 #
+# Bug#59919/11766739: Crash in tmp_table_param::init() with semijoin=on
+#
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t3 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+INSERT INTO t3 VALUES 
+(1,1), (2,1), (5,4), (7,3), (8,2), (8,1), (7,3),
+(9,5), (4,3), (7,2), (7,7), (3,1), (5,8), (9,7);
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin=off,materialization=on';
+EXPLAIN
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	14	Using where
+2	MATERIALIZED	t1	system	NULL	NULL	NULL	NULL	1	
+2	MATERIALIZED	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	MATERIALIZED	b2	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+8	1
+3	1
+set optimizer_switch='semijoin=on,materialization=on';
+EXPLAIN
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	14	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	b1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	MATERIALIZED	b2	ALL	NULL	NULL	NULL	NULL	2	
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+f1	f2
+1	1
+2	1
+8	1
+3	1
+set optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1, t2, t3 ;
+#
+#
 # BUG#784723: Wrong result with semijoin + nested subqueries in maria-5.3  
 #
 CREATE TABLE t1 ( t1field integer, primary key (t1field));

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2012-01-22 20:54:30 +0000
+++ b/mysql-test/t/subselect_sj.test	2012-01-28 03:01:26 +0000
@@ -1354,6 +1354,45 @@
 DROP TABLE ot1, ot2, ot3, it1;
 
 --echo #
+--echo # Bug#59919/11766739: Crash in tmp_table_param::init() with semijoin=on
+--echo #
+
+CREATE TABLE t1 (f1 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t2 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+CREATE TABLE t3 (f1 INTEGER, f2 INTEGER) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1,1), (2,1);
+INSERT INTO t3 VALUES 
+  (1,1), (2,1), (5,4), (7,3), (8,2), (8,1), (7,3),
+  (9,5), (4,3), (7,2), (7,7), (3,1), (5,8), (9,7);
+
+set @save_optimizer_switch=@@optimizer_switch;
+
+set optimizer_switch='semijoin=off,materialization=on';
+EXPLAIN
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+             FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+             FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+
+set optimizer_switch='semijoin=on,materialization=on';
+EXPLAIN
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+             FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+SELECT * FROM t3
+WHERE f2 IN (SELECT t1.f1
+             FROM t1 LEFT OUTER JOIN (t2 AS b1 JOIN t2 AS b2 ON TRUE) ON TRUE);
+
+set optimizer_switch=@save_optimizer_switch;
+
+DROP TABLE t1, t2, t3 ;
+
+--echo #
+--echo #
 --echo # BUG#784723: Wrong result with semijoin + nested subqueries in maria-5.3  
 --echo #
 CREATE TABLE t1 ( t1field integer, primary key (t1field));



More information about the commits mailing list