summaryrefslogtreecommitdiff
path: root/mysql-test/r/subselect_sj.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/subselect_sj.result')
-rw-r--r--mysql-test/r/subselect_sj.result923
1 files changed, 794 insertions, 129 deletions
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index f24b294fa90..c1ed2a03973 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -1,4 +1,10 @@
drop table if exists t0, t1, t2, t3, t4, t10, t11, t12;
+drop view if exists v1, v2, v3, v4;
+drop procedure if exists p1;
+set @subselect_sj_tmp= @@optimizer_switch;
+set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on';
+set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
+set @save_optimizer_switch=@@optimizer_switch;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int, b int);
@@ -12,7 +18,7 @@ insert into t12 select * from t10;
Flattened because of dependency, t10=func(t1)
explain select * from t1 where a in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
select * from t1 where a in (select pk from t10);
a b
@@ -39,7 +45,7 @@ select * from t1 where a in (select a from t11);
a b
explain select * from t1 where a in (select pk from t10) and b in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index
select * from t1 where a in (select pk from t10) and b in (select pk from t10);
@@ -50,8 +56,8 @@ a b
flattening a nested subquery
explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 Using index
select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
a b
@@ -61,8 +67,8 @@ a b
flattening subquery w/ several tables
explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
-1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using where
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t12` join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t12`.`pk` = `test`.`t10`.`a`))
@@ -100,75 +106,75 @@ t1 m10, t1 m11, t1 m12, t1 m13, t1 m14,t1 m15,t1 m16,t1 m17,t1 m18,t1 m19
);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY s00 ALL NULL NULL NULL NULL 3 Using where
-1 PRIMARY s01 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s02 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s03 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s04 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s05 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s06 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s07 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s08 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s09 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s10 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s11 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s12 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s13 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s14 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s15 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s16 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s17 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s18 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s19 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s20 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s21 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s22 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s23 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s24 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s25 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s26 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s27 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s28 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s29 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s30 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s31 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s32 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s33 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s34 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s35 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s36 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s37 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s38 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s39 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s40 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s41 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s42 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s43 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s44 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s45 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s46 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s47 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s48 ALL NULL NULL NULL NULL 3 Using join buffer
-1 PRIMARY s49 ALL NULL NULL NULL NULL 3 Using join buffer
+1 PRIMARY s01 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s02 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s03 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s04 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s05 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s06 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s07 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s08 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s09 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s10 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s11 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s12 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s13 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s14 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s15 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s16 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s17 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s18 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s19 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s20 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s21 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s22 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s23 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s24 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s25 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s26 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s27 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s28 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s29 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s30 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s31 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s32 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s33 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s34 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s35 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s36 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s37 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s38 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s39 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s40 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s41 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s42 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s43 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s44 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s45 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s46 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s47 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s48 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+1 PRIMARY s49 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
2 DEPENDENT SUBQUERY m00 ALL NULL NULL NULL NULL 3 Using where
-2 DEPENDENT SUBQUERY m01 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m02 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m03 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m04 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m05 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m06 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m07 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m08 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m09 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m10 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m11 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m12 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m13 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m14 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m15 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
+2 DEPENDENT SUBQUERY m01 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m02 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m03 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m04 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m05 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m06 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m07 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m08 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m09 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m10 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m11 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m12 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m13 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m14 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m15 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
@@ -194,7 +200,7 @@ insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B;
explain extended select * from t1 where a in (select pk from t10 where pk<3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t10 range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index
-1 PRIMARY t1 ALL NULL NULL NULL NULL 103 100.00 Using where; Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 103 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t10` join `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t10`.`pk`) and (`test`.`t10`.`pk` < 3))
drop table t0, t1, t2;
@@ -322,7 +328,8 @@ INSERT INTO WORKS VALUES ('E3','P2',20);
INSERT INTO WORKS VALUES ('E4','P2',20);
INSERT INTO WORKS VALUES ('E4','P4',40);
INSERT INTO WORKS VALUES ('E4','P5',80);
-set optimizer_switch='default,materialization=off';
+set optimizer_switch=@save_optimizer_switch;
+set optimizer_switch='materialization=off';
explain SELECT EMPNUM, EMPNAME
FROM STAFF
WHERE EMPNUM IN
@@ -344,7 +351,7 @@ E1 Alice
E2 Betty
E3 Carmen
E4 Don
-set optimizer_switch='default';
+set optimizer_switch=@save_optimizer_switch;
drop table STAFF,WORKS,PROJ;
# End of bug#45191
#
@@ -404,24 +411,6 @@ SELECT t1 .varchar_key from t1
int_key
9
7
-SELECT t0.int_key
-FROM t0
-WHERE t0.varchar_nokey IN (
-SELECT t1_1 .varchar_key
-FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
-);
-int_key
-9
-7
-SELECT t0.int_key
-FROM t0, t2
-WHERE t0.varchar_nokey IN (
-SELECT t1_1 .varchar_key
-FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
-);
-int_key
-9
-7
DROP TABLE t0, t1, t2;
# End of bug#46550
#
@@ -450,7 +439,7 @@ COUNT(*)
drop table t1, t2;
drop view v1;
drop procedure p1;
-set SESSION optimizer_switch='default';
+set SESSION optimizer_switch=@save_optimizer_switch;
# End of bug#46744
Bug#46797 "Crash in fix_semijoin_strategies_for_picked_join_order
@@ -506,7 +495,7 @@ EXPLAIN EXTENDED SELECT vkey FROM t0 WHERE pk IN
(SELECT t1.pk FROM t0 t1 JOIN t0 t2 ON t2.vkey = t1.vnokey);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t0 ALL PRIMARY NULL NULL NULL 5 100.00
-1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t0.pk 1 100.00
+1 PRIMARY t1 eq_ref PRIMARY PRIMARY 4 test.t0.pk 1 100.00 Using where
1 PRIMARY t2 ref vkey vkey 4 test.t1.vnokey 2 100.00 Using index; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t0`.`vkey` AS `vkey` from `test`.`t0` `t1` semi join (`test`.`t0` `t2`) join `test`.`t0` where ((`test`.`t2`.`vkey` = `test`.`t1`.`vnokey`) and (`test`.`t1`.`pk` = `test`.`t0`.`pk`))
@@ -601,7 +590,7 @@ v1field
DROP TABLE t1,t2;
DROP VIEW v1,v2;
DROP PROCEDURE p1;
-set SESSION optimizer_switch='default';
+set SESSION optimizer_switch=@save_optimizer_switch;
# End of BUG#48834
Bug#49097 subquery with view generates wrong result with
@@ -715,29 +704,29 @@ FROM it1 LEFT JOIN it2 ON it2.datetime_key);
int_key
0
0
-2
0
-3
0
-7
0
+0
+2
+2
+3
+5
+5
7
7
+7
+8
9
-2
9
-5
-0
-8
-5
EXPLAIN
SELECT int_key FROM ot1
WHERE int_nokey IN (SELECT it2.int_key
FROM it1 LEFT JOIN it2 ON it2.datetime_key);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY it1 index NULL int_key 4 NULL 2 Using index; Start temporary
-1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 Using join buffer
-1 PRIMARY it2 ALL NULL NULL NULL NULL 20 Using where; End temporary
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)
+1 PRIMARY it2 ALL int_key,datetime_key NULL NULL NULL 20 Using where; End temporary; Using join buffer (flat, BNL join)
DROP TABLE ot1, it1, it2;
# End of BUG#38075
#
@@ -768,15 +757,15 @@ select a from t1
where a in (select c from t2 where d >= some(select e from t3 where b=e));
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Start temporary
-1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where; End temporary; Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where; End temporary; Using join buffer (flat, BNL join)
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select 1 from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))
show warnings;
Level Code Message
Note 1276 Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<expr_cache><`test`.`t2`.`d`,`test`.`t1`.`b`>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select 1 from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`)))))))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t1`.`a` = `test`.`t2`.`c`) and <nop>(<in_optimizer>(`test`.`t2`.`d`,<exists>(select `test`.`t3`.`e` from `test`.`t3` where ((`test`.`t1`.`b` = `test`.`t3`.`e`) and (<cache>(`test`.`t2`.`d`) >= `test`.`t3`.`e`))))))
select a from t1
where a in (select c from t2 where d >= some(select e from t3 where b=e));
a
@@ -809,17 +798,16 @@ INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii','iiii','ffff','ffff','ffff','f
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
-1 PRIMARY subselect2 eq_ref unique_key unique_key 13 func 1 1.00
-2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using MRR
+1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
-Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`pk` > 0))
+Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`a` = `test`.`t1`.`a`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (a, b) IN (SELECT a, b FROM t2 WHERE pk > 0);
pk
2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
-1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Using MRR; FirstMatch(t1)
+1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`c` = `test`.`t1`.`c`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
@@ -829,7 +817,7 @@ pk
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
-1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Using MRR; FirstMatch(t1)
+1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
@@ -838,7 +826,7 @@ pk
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
-1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Using MRR; FirstMatch(t1)
+1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`e` = `test`.`t1`.`e`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
@@ -848,7 +836,7 @@ pk
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
-1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Using MRR; FirstMatch(t1)
+1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`f` = `test`.`t1`.`f`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
@@ -858,7 +846,7 @@ pk
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
-1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Using MRR; FirstMatch(t1)
+1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`g` = `test`.`t1`.`g`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
@@ -868,7 +856,7 @@ pk
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
-1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Using MRR; FirstMatch(t1)
+1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`h` = `test`.`t1`.`h`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
@@ -878,7 +866,7 @@ pk
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
-1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Using MRR; FirstMatch(t1)
+1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`i` = `test`.`t1`.`i`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
@@ -888,7 +876,7 @@ pk
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
-1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Using MRR; FirstMatch(t1)
+1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`j` = `test`.`t1`.`j`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
@@ -898,7 +886,7 @@ pk
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
-1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Using MRR; FirstMatch(t1)
+1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 100.00 Using index condition; Using where; Rowid-ordered scan; FirstMatch(t1)
Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`k` = `test`.`t1`.`k`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
@@ -978,10 +966,9 @@ FROM t1
WHERE `varchar_nokey` < 'n' XOR `pk` ) ;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 18 100.00
-1 PRIMARY subselect2 eq_ref unique_key unique_key 8 func 1 1.00
-2 SUBQUERY t1 ALL varchar_key NULL NULL NULL 15 100.00 Using where
+1 PRIMARY t1 ref varchar_key varchar_key 3 test.t2.varchar_nokey 2 100.00 Using where; FirstMatch(t2)
Warnings:
-Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`))
+Note 1003 select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_key` = `test`.`t2`.`varchar_nokey`) and (`test`.`t1`.`varchar_nokey` = `test`.`t2`.`varchar_nokey`) and ((`test`.`t2`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`))
SELECT varchar_nokey
FROM t2
WHERE ( `varchar_nokey` , `varchar_nokey` ) IN (
@@ -1059,9 +1046,9 @@ WHERE t2.val LIKE 'a%' OR t2.val LIKE 'e%')
AND t1.val IN (SELECT t3.val FROM t3
WHERE t3.val LIKE 'a%' OR t3.val LIKE 'e%');
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 5
-1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1)
-1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; FirstMatch(t3)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Start temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join)
SELECT *
FROM t1
WHERE t1.val IN (SELECT t2.val FROM t2
@@ -1075,3 +1062,681 @@ DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
# End of Bug#48623
+#
+# LPBUG#602574: RQG: sql_select.cc:5385: bool greedy_search(JOIN*, table_map, uint,
+# uint): Assertion `join->best_read <
+#
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='materialization=off';
+CREATE TABLE t1 (
+varchar_key varchar(1) DEFAULT NULL,
+KEY varchar_key (varchar_key)
+);
+CREATE TABLE t2 (
+varchar_key varchar(1) DEFAULT NULL,
+KEY varchar_key (varchar_key)
+);
+INSERT INTO t2 VALUES
+(NULL),(NULL),(NULL),(NULL),('a'),('a'),('a'),('b'),('b'),('b'),('b'),('c'),
+('c'),('c'),('c'),('c'),('c'),('c'),('d'),('d'),('d'),('d'),('d'),('d'),('e'),
+('e'),('e'),('e'),('e'),('e'),('f'),('f'),('f'),('g'),('g'),('h'),('h'),('h'),
+('h'),('i'),('j'),('j'),('j'),('k'),('k'),('l'),('l'),('m'),('m'),('m'),('m'),
+('n'),('n'),('n'),('o'),('o'),('o'),('p'),('p'),('p'),('q'),('q'),('q'),('r'),
+('r'),('r'),('r'),('s'),('s'),('s'),('s'),('t'),('t'),('t'),('t'),('u'),('u'),
+('u'),('u'),('v'),('v'),('v'),('v'),('w'),('w'),('w'),('w'),('w'),('w'),('x'),
+('x'),('x'),('y'),('y'),('y'),('y'),('z'),('z'),('z'),('z');
+CREATE TABLE t3 (
+varchar_key varchar(1) DEFAULT NULL,
+KEY varchar_key (varchar_key)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO t3 VALUES
+(NULL),('c'),('d'),('e'),('f'),('h'),('j'),('k'),('k'),('m'),('m'),('m'),
+('n'),('o'),('r'),('t'),('t'),('u'),('w'),('y');
+SELECT varchar_key FROM t3
+WHERE (SELECT varchar_key FROM t3
+WHERE (varchar_key,varchar_key)
+IN (SELECT t1.varchar_key, t2 .varchar_key
+FROM t1 RIGHT JOIN t2 ON t1.varchar_key
+)
+);
+varchar_key
+set optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1, t2, t3;
+#
+# Bug#46692 "Crash occurring on queries with nested FROM subqueries
+# using materialization."
+#
+CREATE TABLE t1 (
+pk INTEGER PRIMARY KEY,
+int_key INTEGER,
+KEY int_key(int_key)
+);
+INSERT INTO t1 VALUES (10,186),(11,NULL),(12,2),(13,3),(14,0),(15,133),(16,1);
+CREATE TABLE t2 (
+pk INTEGER PRIMARY KEY,
+int_key INTEGER,
+KEY int_key(int_key)
+);
+INSERT INTO t2 VALUES (1,7),(2,2);
+SELECT * FROM t1 WHERE (140, 4) IN
+(SELECT t2.int_key, t2 .pk FROM t2 STRAIGHT_JOIN t1 ON t2.int_key);
+pk int_key
+DROP TABLE t1, t2;
+#
+# Bug#42353 "SELECT ... WHERE oe IN (SELECT w/ LEFT JOIN) query
+# causes crash."
+#
+CREATE TABLE t1 (
+pk INTEGER PRIMARY KEY,
+int_nokey INTEGER,
+int_key INTEGER,
+date_key DATE,
+datetime_nokey DATETIME,
+varchar_nokey VARCHAR(1)
+);
+CREATE TABLE t2 (
+date_nokey DATE
+);
+CREATE TABLE t3 (
+pk INTEGER PRIMARY KEY,
+int_nokey INTEGER,
+date_key date,
+varchar_key VARCHAR(1),
+varchar_nokey VARCHAR(1),
+KEY date_key (date_key)
+);
+SELECT date_key FROM t1
+WHERE (int_key, int_nokey)
+IN (SELECT t3.int_nokey, t3.pk
+FROM t2 LEFT JOIN t3 ON (t2.date_nokey < t3.date_key)
+WHERE t3.varchar_key <= t3.varchar_nokey OR t3.int_nokey <= t3.pk
+)
+AND (varchar_nokey <> 'f' OR NOT int_key < 7);
+date_key
+#
+# Bug#45933 "Crash in optimize_semijoin_nests on JOIN in subquery
+# + AND in outer query".
+#
+INSERT INTO t1 VALUES (10,7,5,'2009-06-16','2002-04-10 14:25:30','w'),
+(11,7,0,'0000-00-00','0000-00-00 00:00:00','s'),
+(12,4,0,'2003-07-14','2006-09-14 04:01:02','y'),
+(13,0,4,'2002-07-25','0000-00-00 00:00:00','c'),
+(14,1,8,'2007-07-03','0000-00-00 00:00:00','q'),
+(15,6,5,'2001-11-12','0000-00-00 00:00:00',''),
+(16,2,9,'0000-00-00','0000-00-00 00:00:00','j'),
+(29,9,1,'0000-00-00','2003-08-11 00:00:00','m');
+INSERT INTO t3 VALUES (1,9,'0000-00-00','b','b'),
+(2,2,'2002-09-17','h','h');
+SELECT t1.varchar_nokey FROM t1 JOIN t3 ON t1.datetime_nokey
+WHERE t1.varchar_nokey
+IN (SELECT varchar_nokey FROM t1
+WHERE (pk)
+IN (SELECT t3.int_nokey
+FROM t3 LEFT JOIN t1 ON t1.varchar_nokey
+WHERE t3.date_key BETWEEN '2008-06-07' AND '2006-06-26'
+ )
+);
+varchar_nokey
+DROP TABLE t1, t2, t3;
+#
+# Bug#45219 "Crash on SELECT DISTINCT query containing a
+# LEFT JOIN in subquery"
+#
+CREATE TABLE t1 (
+pk INTEGER NOT NULL,
+int_nokey INTEGER NOT NULL,
+datetime_key DATETIME NOT NULL,
+varchar_key VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY datetime_key (datetime_key),
+KEY varchar_key (varchar_key)
+);
+INSERT INTO t1 VALUES
+(1,9,'0000-00-00 00:00:00','p'),(2,0,'2002-02-09 07:38:13','v'),
+(3,8,'2001-05-03 12:08:14','t'),(4,3,'0000-00-00 00:00:00','u'),
+(5,7,'2009-07-28 03:43:30','n'),(6,0,'2009-08-04 00:00:00','l'),
+(7,1,'0000-00-00 00:00:00','h'),(8,9,'0000-00-00 00:00:00','u'),
+(9,0,'2005-08-02 17:16:54','n'),(10,9,'2002-12-21 00:00:00','j'),
+(11,0,'2005-08-15 12:37:35','k'),(12,5,'0000-00-00 00:00:00','e'),
+(13,0,'2006-03-10 00:00:00','i'),(14,8,'2005-05-16 11:02:36','u'),
+(15,8,'2008-11-02 00:00:00','n'),(16,5,'2006-03-15 00:00:00','b'),
+(17,1,'0000-00-00 00:00:00','x'),(18,7,'0000-00-00 00:00:00',''),
+(19,0,'2008-12-17 20:15:40','q'),(20,9,'0000-00-00 00:00:00','u');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(10,0,'2006-07-07 07:26:28','q'),(11,5,'2002-09-23 00:00:00','m'),
+(12,7,'0000-00-00 00:00:00','j'),(13,1,'2006-06-07 00:00:00','z'),
+(14,8,'2000-09-16 12:15:34','a'),(15,2,'2007-08-05 15:47:52',''),
+(16,1,'0000-00-00 00:00:00','e'),(17,8,'2005-12-02 19:34:26','t'),
+(18,5,'0000-00-00 00:00:00','q'),(19,4,'0000-00-00 00:00:00','b'),
+(20,5,'2007-12-28 00:00:00','w'),(21,3,'2004-08-02 11:48:43','m'),
+(22,0,'0000-00-00 00:00:00','x'),(23,8,'2004-04-19 12:18:43',''),
+(24,0,'2009-04-27 00:00:00','w'),(25,4,'2006-10-20 14:52:15','x'),
+(26,0,'0000-00-00 00:00:00','e'),(27,0,'2002-03-22 11:48:37','e'),
+(28,2,'0000-00-00 00:00:00','p'),(29,0,'2001-01-04 03:55:07','x');
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(10,8,'2007-08-19 08:08:38','i'),(11,0,'2000-05-21 03:51:51','');
+SELECT DISTINCT datetime_key FROM t1
+WHERE (int_nokey, pk)
+IN (SELECT t3.pk, t3.pk FROM t2 LEFT JOIN t3 ON t3.varchar_key)
+AND pk = 9;
+datetime_key
+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));
+CREATE TABLE t2 ( t2field integer, primary key (t2field));
+INSERT INTO t1 VALUES (1),(2),(3);
+INSERT INTO t2 VALUES (2),(3),(4);
+explain
+SELECT * FROM t1 A
+WHERE
+A.t1field IN (SELECT A.t1field FROM t2 B) AND
+A.t1field IN (SELECT C.t2field FROM t2 C
+WHERE C.t2field IN (SELECT D.t2field FROM t2 D));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY A index PRIMARY PRIMARY 4 NULL 3 Using index; Start temporary
+1 PRIMARY B index NULL PRIMARY 4 NULL 3 Using index; End temporary; Using join buffer (flat, BNL join)
+1 PRIMARY C eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index
+1 PRIMARY D eq_ref PRIMARY PRIMARY 4 test.A.t1field 1 Using index
+SELECT * FROM t1 A
+WHERE
+A.t1field IN (SELECT A.t1field FROM t2 B) AND
+A.t1field IN (SELECT C.t2field FROM t2 C
+WHERE C.t2field IN (SELECT D.t2field FROM t2 D));
+t1field
+2
+3
+drop table t1,t2;
+#
+# BUG#787299: Valgrind complains on a join query with two IN subqueries
+#
+create table t1 (a int);
+insert into t1 values (1), (2), (3);
+create table t2 as select * from t1;
+select * from t1 A, t1 B
+where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D);
+a a
+1 1
+2 2
+3 3
+explain
+select * from t1 A, t1 B
+where A.a = B.a and A.a in (select a from t2 C) and B.a in (select a from t2 D);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY A ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY B ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY C ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY D ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (flat, BNL join)
+drop table t1, t2;
+#
+# BUG#784441: Abort on semijoin with a view as the inner table
+#
+CREATE TABLE t1 (a int) ;
+INSERT INTO t1 VALUES (1), (1);
+CREATE TABLE t2 (a int) ;
+INSERT INTO t2 VALUES (1), (1);
+CREATE VIEW v1 AS SELECT 1;
+EXPLAIN
+SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+2 DEPENDENT SUBQUERY <derived3> system NULL NULL NULL NULL 1
+3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used
+SELECT * FROM t1 INNER JOIN t2 ON t2.a != 0 AND t2.a IN (SELECT * FROM v1);
+a a
+1 1
+1 1
+1 1
+1 1
+DROP VIEW v1;
+DROP TABLE t1,t2;
+#
+# BUG#751439 Assertion `!table->file || table->file->inited == handler::NONE' failed with subquery
+#
+CREATE TABLE t1 ( f10 int, f11 int) ;
+INSERT IGNORE INTO t1 VALUES (0,0),(0,0);
+CREATE TABLE t2 ( f11 int);
+INSERT IGNORE INTO t2 VALUES (0),(0);
+CREATE TABLE t3 ( f11 int) ;
+INSERT IGNORE INTO t3 VALUES (0);
+SELECT alias1.f11 AS field2
+FROM ( t3 AS alias2 JOIN t1 AS alias3 ON alias3.f10 = 1)
+LEFT JOIN ( t2 AS alias1 ) ON alias3.f11 = 1
+WHERE alias2.f11 IN ( SELECT f11 FROM t2 )
+GROUP BY field2 ;
+field2
+drop table t1, t2, t3;
+#
+# BUG#778406 Crash in hp_movelink with Aria engine and subqueries
+#
+CREATE TABLE t4 (f10 varchar(32) , KEY (f10)) ENGINE=Aria;
+INSERT INTO t4 VALUES ('x'),('m'),('c');
+CREATE TABLE t1 (f11 int) ENGINE=Aria;
+INSERT INTO t1 VALUES (0),(0),(0);
+CREATE TABLE t2 ( f10 int) ENGINE=Aria;
+INSERT INTO t2 VALUES (0),(0),(0);
+CREATE TABLE t3 ( f10 int, f11 int) ENGINE=Aria;
+SELECT *
+FROM t4
+WHERE f10 IN
+( SELECT t1.f11
+FROM t1
+LEFT JOIN t2 JOIN t3 ON t3.f10 = t2.f10 ON t3.f11 != 0 );
+f10
+x
+m
+c
+Warnings:
+Warning 1292 Truncated incorrect DOUBLE value: 'x'
+Warning 1292 Truncated incorrect DOUBLE value: 'm'
+Warning 1292 Truncated incorrect DOUBLE value: 'c'
+Warning 1292 Truncated incorrect DOUBLE value: 'x'
+Warning 1292 Truncated incorrect DOUBLE value: 'm'
+Warning 1292 Truncated incorrect DOUBLE value: 'c'
+Warning 1292 Truncated incorrect DOUBLE value: 'x'
+Warning 1292 Truncated incorrect DOUBLE value: 'm'
+Warning 1292 Truncated incorrect DOUBLE value: 'c'
+drop table t1,t2,t3,t4;
+#
+# BUG#751484: Valgrind warning / sporadic crash in evaluate_join_record sql_select.cc:14099 with semijoin
+#
+CREATE TABLE t1 ( f10 int, f11 int, KEY (f10));
+INSERT IGNORE INTO t1 VALUES (0, 0),(0, 0);
+CREATE TABLE t3 ( f10 int);
+INSERT IGNORE INTO t3 VALUES (0);
+set @tmp_751484= @@optimizer_switch;
+set optimizer_switch='materialization=on';
+SELECT * FROM t1
+WHERE f11 IN (
+SELECT C_SQ1_alias1.f11
+FROM t1 AS C_SQ1_alias1
+JOIN t3 AS C_SQ1_alias2
+ON C_SQ1_alias2.f10 = C_SQ1_alias1.f10
+);
+f10 f11
+0 0
+0 0
+set optimizer_switch='materialization=off';
+SELECT * FROM t1
+WHERE f11 IN (
+SELECT C_SQ1_alias1.f11
+FROM t1 AS C_SQ1_alias1
+JOIN t3 AS C_SQ1_alias2
+ON C_SQ1_alias2.f10 = C_SQ1_alias1.f10
+);
+f10 f11
+0 0
+0 0
+set optimizer_switch=@tmp_751484;
+drop table t1, t3;
+# BUG#795530 Wrong result with subquery semijoin materialization and outer join
+# Simplified testcase that uses DuplicateElimination
+#
+create table t1 (a int);
+create table t2 (a int, b char(10));
+insert into t1 values (1),(2);
+insert into t2 values (1, 'one'), (3, 'three');
+create table t3 (b char(10));
+insert into t3 values('three'),( 'four');
+insert into t3 values('three'),( 'four');
+insert into t3 values('three'),( 'four');
+insert into t3 values('three'),( 'four');
+explain select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (flat, BNL join)
+select * from t3 where t3.b in (select t2.b from t1 left join t2 on t1.a=t2.a);
+b
+drop table t1, t2, t3;
+#
+# BUG#600958 RQG: Crash in optimize_semijoin_nests
+#
+CREATE TABLE t1 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_key int(11) DEFAULT NULL,
+col_date_key date DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_date_key (col_date_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
+INSERT INTO t1 VALUES (10,8,'2002-02-21',NULL);
+CREATE TABLE t2 (
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_key int(11) DEFAULT NULL,
+col_date_key date DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_date_key (col_date_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
+INSERT INTO t2 VALUES (1,7,'1900-01-01','f');
+SELECT col_date_key FROM t1
+WHERE 5 IN (
+SELECT SUBQUERY3_t1 .col_int_key
+FROM t2 SUBQUERY3_t1
+LEFT JOIN t1 SUBQUERY3_t2 ON SUBQUERY3_t1 .col_varchar_key
+);
+col_date_key
+drop table t2, t1;
+#
+# No BUG#: Duplicate weedout check is not done for outer joins
+#
+create table t1 (a int);
+create table t2 (a int);
+insert into t1 values (1),(1),(2),(2);
+insert into t2 values (1);
+create table t0 (a int);
+insert into t0 values (1),(2);
+set @tmp_20110622= @@optimizer_switch;
+set optimizer_switch='firstmatch=off,loosescan=off,materialization=off';
+# Check DuplicateWeedout + join buffer
+explain
+select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; End temporary
+select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a);
+a
+1
+2
+# Check DuplicateWeedout without join buffer
+set @tmp_jcl_20110622= @@join_cache_level;
+set join_cache_level= 0;
+explain
+select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; End temporary
+select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a);
+a
+1
+2
+# Check FirstMatch without join buffer:
+set optimizer_switch='firstmatch=on';
+explain
+select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; End temporary
+select * from t0 where a in (select t1.a from t1 left join t2 on t1.a=t2.a);
+a
+1
+2
+#
+# Now, check the same for multiple inner tables:
+alter table t2 add b int;
+update t2 set b=a;
+create table t3 as select * from t2;
+set optimizer_switch='firstmatch=off';
+set join_cache_level= 0;
+# DuplicateWeedout without join buffer
+explain
+select * from t0
+where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Start temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; End temporary
+select * from t0
+where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a);
+a
+1
+2
+set @@join_cache_level=@tmp_jcl_20110622;
+# DuplicateWeedout + join buffer
+explain
+select * from t0
+where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where; End temporary
+select * from t0
+where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a);
+a
+1
+2
+# Now, let the inner join side have a 'partial' match
+select * from t3;
+a b
+1 1
+insert into t3 values(2,2);
+explain
+select * from t0
+where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary
+select * from t0
+where a in (select t1.a from t1 left join (t3 join t2 on t3.b=t2.b) on t1.a=t3.a);
+a
+1
+2
+set @@optimizer_switch=@tmp_20110622;
+drop table t0, t1, t2, t3;
+#
+# BUG#802965: Crash in do_copy_not_null with semijoin=on in maria-5.3
+#
+set @save_802965= @@optimizer_switch;
+set optimizer_switch='semijoin=on,materialization=off,firstmatch=off,loosescan=off';
+CREATE TABLE t2 ( f1 int NOT NULL , PRIMARY KEY (f1)) ;
+INSERT IGNORE INTO t2 VALUES (19),(20);
+CREATE TABLE t1 ( f1 int NOT NULL , PRIMARY KEY (f1)) ;
+INSERT IGNORE INTO t1 VALUES (21),(22),(23),(24);
+SELECT *
+FROM t2 , t1
+WHERE t2.f1 IN
+(
+SELECT SQ1_alias1.f1
+FROM t1 AS SQ1_alias1 LEFT JOIN t2 AS SQ1_alias2 JOIN t2 AS SQ1_alias3 ON SQ1_alias3.f1 ON SQ1_alias3.f1
+)
+AND t1.f1 = t2.f1 ;
+f1 f1
+DROP TABLE t1, t2;
+set optimizer_switch=@save_802965;
+#
+# BUG#803365: Crash in pull_out_semijoin_tables with outer join + semijoin + derived tables in maria-5.3 with WL#106
+#
+CREATE TABLE t1 ( f1 int) ;
+INSERT INTO t1 VALUES (1),(1);
+CREATE TABLE t2 ( f2 int) ;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t3 ( f3 int) ;
+INSERT INTO t3 VALUES (1),(1);
+SELECT *
+FROM t1
+WHERE t1.f1 IN (
+SELECT t2.f2
+FROM t2
+LEFT JOIN (
+SELECT *
+FROM t3
+) AS alias1
+ON alias1.f3 = t2.f2
+);
+f1
+1
+1
+DROP TABLE t1,t2,t3;
+#
+# BUG#611704: Crash in replace_where_subcondition with nested subquery and semijoin=on
+#
+CREATE TABLE t1 ( f1 int) ;
+CREATE TABLE t2 ( f1 int) ;
+CREATE TABLE t3 ( f1 int) ;
+SELECT * FROM (
+SELECT t3.*
+FROM t2 STRAIGHT_JOIN t3
+ON t3.f1
+AND (t3.f1 ) IN (
+SELECT t1.f1
+FROM t1
+)
+) AS alias1;
+f1
+DROP TABLE t1,t2,t3;
+# BUG#611704: another testcase:
+CREATE TABLE t1 ( f1 int(11), f3 varchar(1), f4 varchar(1)) ;
+CREATE TABLE t2 ( f2 int(11), KEY (f2));
+CREATE TABLE t3 ( f4 varchar(1)) ;
+PREPARE st1 FROM '
+SELECT *
+FROM t1
+STRAIGHT_JOIN ( t2 STRAIGHT_JOIN t3 ON t2.f2 )
+ON (t1.f3) IN ( SELECT f4 FROM t1 )
+';
+EXECUTE st1;
+f1 f3 f4 f2 f4
+DROP TABLE t1,t2,t3;
+#
+# BUG#803457: Wrong result with semijoin + view + outer join in maria-5.3-subqueries-mwl90
+# (Original testcase)
+#
+CREATE TABLE t1 (f1 int, f2 int );
+INSERT INTO t1 VALUES (2,0),(4,0),(0,NULL);
+CREATE TABLE t2 (f2 int, f3 int );
+INSERT INTO t2 VALUES (NULL,NULL),(0,0);
+CREATE TABLE t3 ( f1 int, f3 int );
+INSERT INTO t3 VALUES (2,0),(4,0),(0,NULL),(4,0),(8,0);
+CREATE TABLE t4 ( f2 int, KEY (f2) );
+INSERT INTO t4 VALUES (0),(NULL);
+CREATE VIEW v4 AS SELECT DISTINCT f2 FROM t4 ;
+# The following must not have outer joins:
+explain extended
+SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 PRIMARY t4 ref f2 f2 5 test.t2.f3 2 100.00 Using index; FirstMatch(t2)
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t2`.`f3` AS `f3`,`test`.`t3`.`f3` AS `f3` from `test`.`t1` semi join (`test`.`t4`) join `test`.`t2` join `test`.`t3` where ((`test`.`t1`.`f2` = `test`.`t2`.`f2`) and (`test`.`t3`.`f1` = `test`.`t1`.`f1`) and (`test`.`t4`.`f2` = `test`.`t2`.`f3`))
+SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4);
+f1 f2 f3 f3
+2 0 0 0
+4 0 0 0
+4 0 0 0
+drop view v4;
+drop table t1, t2, t3, t4;
+#
+# BUG#803303: Wrong result with semijoin=on, outer join in maria-5.3-subqueries-mwl90
+#
+# Testcase#1:
+set @tmp803303= @@optimizer_switch;
+set optimizer_switch = 'semijoin=on,materialization=off,firstmatch=off,loosescan=off';
+CREATE TABLE t2 ( f1 int) ;
+INSERT IGNORE INTO t2 VALUES (6),(8);
+CREATE TABLE t1 ( f1 int, f2 int, f3 int) ;
+INSERT IGNORE INTO t1 VALUES (8,0,0),(7,0,0),(9,0,0);
+SELECT alias2.f1
+FROM t2 AS alias1
+LEFT JOIN ( t1 AS alias2 JOIN t1 AS alias3 ON alias3.f2 = alias2.f3 )
+ON alias3.f2 = alias2.f2
+WHERE alias2.f1 IN ( SELECT f1 FROM t2 AS alias4 ) ;
+f1
+8
+8
+8
+8
+8
+8
+drop table t1,t2;
+set optimizer_switch= @tmp803303;
+# Testcase #2:
+CREATE TABLE t1 ( f10 int) ;
+INSERT INTO t1 VALUES (0),(0);
+CREATE TABLE t2 ( f10 int, f11 varchar(1)) ;
+INSERT INTO t2 VALUES (0,'a'),(0,'b');
+CREATE TABLE t3 ( f10 int) ;
+INSERT INTO t3 VALUES (0),(0),(0),(0),(0);
+CREATE TABLE t4 ( f10 varchar(1), f11 int) ;
+INSERT INTO t4 VALUES ('a',0),('b',0);
+SELECT * FROM t1
+LEFT JOIN ( t2 JOIN t3 ON t3.f10 = t2.f10 ) ON t1.f10 = t2.f10
+WHERE t2.f10 IN (
+SELECT t4.f11
+FROM t4
+WHERE t4.f10 != t2.f11
+);
+f10 f10 f11 f10
+0 0 b 0
+0 0 b 0
+0 0 a 0
+0 0 a 0
+0 0 b 0
+0 0 b 0
+0 0 a 0
+0 0 a 0
+0 0 b 0
+0 0 b 0
+0 0 a 0
+0 0 a 0
+0 0 b 0
+0 0 b 0
+0 0 a 0
+0 0 a 0
+0 0 b 0
+0 0 b 0
+0 0 a 0
+0 0 a 0
+drop table t1,t2,t3,t4;
+#
+# BUG#803457: Wrong result with semijoin + view + outer join in maria-5.3-subqueries-mwl90
+#
+set @tmp803457=@@optimizer_switch;
+set optimizer_switch='materialization=off';
+CREATE TABLE t1 (f1 int, f2 int );
+INSERT INTO t1 VALUES (2,0),(4,0),(0,NULL);
+CREATE TABLE t2 (f2 int, f3 int );
+INSERT INTO t2 VALUES (NULL,NULL),(0,0);
+CREATE TABLE t3 ( f1 int, f3 int );
+INSERT INTO t3 VALUES (2,0),(4,0),(0,NULL),(4,0),(8,0);
+CREATE TABLE t4 ( f2 int);
+INSERT INTO t4 VALUES (0),(NULL);
+# The following uses Duplicate Weedout, and "End temporary" must not be
+# in the middle of the inner side of an outer join:
+explain
+SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE IFNULL(t2.f3,'foo') IN (SELECT * FROM t4);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join)
+SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3 ) WHERE IFNULL(t2.f3,'foo') IN (SELECT * FROM t4);
+f1 f2 f3 f3
+2 0 0 0
+4 0 0 0
+4 0 0 0
+0 NULL NULL NULL
+DROP TABLE t1, t2, t3, t4;
+set @tmp803457=@@optimizer_switch;
+#
+# BUG#818280: crash in do_copy_not_null() in maria-5.3 with semijoin
+#
+CREATE TABLE t1 ( c1 int NOT NULL , c2 int NOT NULL, PRIMARY KEY (c1)) ;
+INSERT IGNORE INTO t1 VALUES (2,7),(1,3),(5,6);
+CREATE TABLE t3 ( c1 int NOT NULL , c2 int NOT NULL, PRIMARY KEY (c1)) ;
+INSERT IGNORE INTO t3 VALUES (2,7),(1,3),(5,6);
+CREATE TABLE t2 ( c1 int NOT NULL , c5 int NOT NULL );
+INSERT IGNORE INTO t2 VALUES (2,2),(2,2),(5,6);
+SELECT * FROM t1 WHERE c1 IN ( SELECT t3.c1 FROM t3 LEFT JOIN t2 ON t2 .c1 = t3 .c1 WHERE t2.c5 != 0 );
+c1 c2
+2 7
+5 6
+DROP TABLE t1, t2, t3;
+set optimizer_switch=@subselect_sj_tmp;
View Lorry Controller Status