[Commits] Rev 2876: Testcase backport: BUG#46548 IN-subqueries return 0 rows with materialization=on in file:///home/psergey/dev2/5.3-fix-subq/

Sergey Petrunya psergey at askmonty.org
Fri Jan 14 12:51:31 EET 2011


At file:///home/psergey/dev2/5.3-fix-subq/

------------------------------------------------------------
revno: 2876
revision-id: psergey at askmonty.org-20110114105130-2yj5mvupx3fs1pkb
parent: psergey at askmonty.org-20110114095503-rw0dhl0ig7x6vgw3
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-fix-subq
timestamp: Fri 2011-01-14 13:51:30 +0300
message:
  Testcase backport: BUG#46548 IN-subqueries return 0 rows with materialization=on
  (the bug itself was fixed during the subquery code backport)
=== modified file 'mysql-test/r/subselect_mat.result'
--- a/mysql-test/r/subselect_mat.result	2010-12-11 07:23:34 +0000
+++ b/mysql-test/r/subselect_mat.result	2011-01-14 10:51:30 +0000
@@ -1344,3 +1344,30 @@
 c2	c2
 10	10
 DROP TABLE t1, t2;
+# 
+# Testcase backport: BUG#46548 IN-subqueries return 0 rows with materialization=on
+# 
+CREATE TABLE t1 (
+pk int,
+a varchar(1),
+b varchar(4),
+c varchar(4),
+d varchar(4),
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff');
+SET @@optimizer_switch='default,semijoin=on,materialization=on';
+EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	5	func	1	
+2	SUBQUERY	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using index condition; Using MRR
+SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
+pk
+2
+SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0);
+pk
+2
+DROP TABLE t1, t2;

=== modified file 'mysql-test/t/subselect_mat.test'
--- a/mysql-test/t/subselect_mat.test	2010-11-25 09:43:23 +0000
+++ b/mysql-test/t/subselect_mat.test	2011-01-14 10:51:30 +0000
@@ -988,3 +988,26 @@
 EXECUTE st1;
 
 DROP TABLE t1, t2;
+
+--echo # 
+--echo # Testcase backport: BUG#46548 IN-subqueries return 0 rows with materialization=on
+--echo # 
+CREATE TABLE t1 (
+  pk int,
+  a varchar(1),
+  b varchar(4),
+  c varchar(4),
+  d varchar(4),
+  PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff');
+
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff');
+
+SET @@optimizer_switch='default,semijoin=on,materialization=on';
+EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
+SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0);
+SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0);
+DROP TABLE t1, t2;
+



More information about the commits mailing list