Back-ported test cases for bugs #53060, #53305, #50358, #49453 from subquery_sj

of mysql-5.6 code line. The bugs could not be reproduced in the latest release
of mariadb-5.3 as they were fixed either when the code of subquery optimization
was back-ported from mysql-6.0 or later when some other bugs were fixed.

1 files changed, 97 insertions, 0 deletions

diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 362158cfdbe..db869ba434a 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -5389,4 +5389,101 @@ x	5	5	4
 SET join_cache_level = DEFAULT;
 SET optimizer_switch=@tmp_optimizer_switch;
 DROP TABLE t1,t2,t3,t4;
+#
+# Bug#53305 Duplicate weedout + join buffer (join cache --level=7,8)
+#
+create table t1 (uid int, fid int, index(uid));
+insert into t1 values
+(1,1), (1,2), (1,3), (1,4),
+(2,5), (2,6), (2,7), (2,8),
+(3,1), (3,2), (3,9);
+create table t2 (uid int primary key, name varchar(128), index(name));
+insert into t2 values 
+(1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"),
+(6, "F"), (7, "G"), (8, "H"), (9, "I");
+create table t3 (uid int, fid int, index(uid));
+insert into t3 values
+(1,1), (1,2), (1,3),(1,4),
+(2,5), (2,6), (2,7), (2,8),
+(3,1), (3,2), (3,9);
+set @tmp_optimizer_switch=@@optimizer_switch;
+set  @@optimizer_switch='semijoin=on';
+set optimizer_switch='materialization=off';
+set optimizer_switch='loosescan=off,firstmatch=off';
+set optimizer_switch='mrr_sort_keys=off';
+set join_cache_level=7;
+create table t4 (uid int primary key, name varchar(128), index(name));
+insert into t4 values 
+(1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E"),
+(6, "F"), (7, "G"), (8, "H"), (9, "I");
+explain select name from t2, t1 
+where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
+and t2.uid=t1.fid;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
+1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
+1	PRIMARY	t1	ALL	uid	NULL	NULL	NULL	11	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.fid	1	Using join buffer (flat, BKAH join); Rowid-ordered scan
+select name from t2, t1 
+where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
+and t2.uid=t1.fid;
+name
+A
+A
+B
+B
+C
+D
+E
+F
+G
+H
+I
+set join_cache_level = default;
+set optimizer_switch=@tmp_optimizer_switch;
+drop table t1,t2,t3,t4;
+#
+# Bug#50358 - semijoin execution of subquery with outerjoin
+#             emplying join buffer
+#
+CREATE TABLE t1 (i int);
+CREATE TABLE t2 (i int);
+CREATE TABLE t3 (i int);
+INSERT INTO t1 VALUES (1), (2);
+INSERT INTO t2 VALUES (6);
+INSERT INTO t3 VALUES (1), (2);
+set @tmp_optimizer_switch=@@optimizer_switch;
+set  @@optimizer_switch='semijoin=on';
+set optimizer_switch='materialization=on';
+set join_cache_level=0;
+EXPLAIN
+SELECT * FROM t1 WHERE t1.i IN 
+(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
+SELECT * FROM t1 WHERE t1.i IN 
+(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
+i
+1
+2
+set join_cache_level=2;
+EXPLAIN
+SELECT * FROM t1 WHERE t1.i IN 
+(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	2	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
+SELECT * FROM t1 WHERE t1.i IN 
+(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
+i
+1
+2
+set join_cache_level = default;
+set optimizer_switch=@tmp_optimizer_switch;
+DROP TABLE t1,t2,t3;
 set @@optimizer_switch=@save_optimizer_switch;