diff options
Diffstat (limited to 'mysql-test')
38 files changed, 1429 insertions, 89 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index b1225c31575..cb84d10e0fa 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -190,13 +190,13 @@ pla_id test explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY <derived2> ALL NULL $hj 7 test.m2.matintnum 9 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 Using where 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY <derived2> ALL NULL $hj 7 test.m2.matintnum 9 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 Using where 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 drop table t1,t2; diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 9d572a0b223..ec81db7e418 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1480,7 +1480,7 @@ WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary -1 PRIMARY <derived3> ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 3 Using where; End temporary 3 DERIVED t1 ALL NULL NULL NULL NULL 3 SELECT * FROM t3 WHERE t3.b IN (SELECT v1.b FROM v1, t2 diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 955120a86a2..a871e1ae761 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -200,7 +200,7 @@ flush tables; EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary -1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary flush tables; SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); dt @@ -208,7 +208,7 @@ flush tables; EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary -1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary flush tables; SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); dt diff --git a/mysql-test/r/feedback_plugin_install.result b/mysql-test/r/feedback_plugin_install.result index 4b3b0226fae..b1b35072a08 100644 --- a/mysql-test/r/feedback_plugin_install.result +++ b/mysql-test/r/feedback_plugin_install.result @@ -5,7 +5,7 @@ ACTIVE select * from information_schema.feedback where variable_name like 'feed%' and variable_name not like '%_uid'; VARIABLE_NAME VARIABLE_VALUE -FEEDBACK 1.0 +FEEDBACK 1.1 FEEDBACK_SEND_RETRY_WAIT 60 FEEDBACK_SEND_TIMEOUT 60 FEEDBACK_URL http://mariadb.org/feedback_plugin/post diff --git a/mysql-test/r/feedback_plugin_load.result b/mysql-test/r/feedback_plugin_load.result index bc02b920a11..d434d1282c5 100644 --- a/mysql-test/r/feedback_plugin_load.result +++ b/mysql-test/r/feedback_plugin_load.result @@ -4,7 +4,7 @@ ACTIVE select * from information_schema.feedback where variable_name like 'feed%' and variable_name not like '%_uid'; VARIABLE_NAME VARIABLE_VALUE -FEEDBACK 1.0 +FEEDBACK 1.1 FEEDBACK_SEND_RETRY_WAIT 60 FEEDBACK_SEND_TIMEOUT 60 FEEDBACK_URL http://mariadb.org/feedback_plugin/post diff --git a/mysql-test/r/feedback_plugin_send.result b/mysql-test/r/feedback_plugin_send.result index 22379e26248..db622cb3f97 100644 --- a/mysql-test/r/feedback_plugin_send.result +++ b/mysql-test/r/feedback_plugin_send.result @@ -4,7 +4,7 @@ ACTIVE select * from information_schema.feedback where variable_name like 'feed%' and variable_name not like '%_uid'; VARIABLE_NAME VARIABLE_VALUE -FEEDBACK 1.0 +FEEDBACK 1.1 FEEDBACK_SEND_RETRY_WAIT 60 FEEDBACK_SEND_TIMEOUT 60 FEEDBACK_URL http://mariadb.org/feedback_plugin/post diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 1f9ef1dfeda..af3d238c1fd 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5146,4 +5146,37 @@ a 0 SET SESSION join_cache_level = DEFAULT; DROP TABLE t1, t2; +# +# Bug #887479: join_cache_level=3 + semijoin=on +# +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (3914,17), (3710,5), (3888,20); +CREATE TABLE t2 (c int, KEY (c)); +INSERT INTO t2 VALUES (27), (17), (33), (20), (3), (7), (18), (2); +SET @tmp887479_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=on'; +SET SESSION optimizer_switch='semijoin_with_cache=on'; +SET SESSION join_cache_level=1; +EXPLAIN +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY t2 ref c c 5 test.t1.b 2 Using index; Start temporary; End temporary +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); +a b +3914 17 +3888 20 +SET SESSION join_cache_level=3; +EXPLAIN +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY t2 hash_index c #hash#c:c 5:5 test.t1.b 8 End temporary; Using join buffer (flat, BNLH join) +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); +a b +3914 17 +3888 20 +SET SESSION join_cache_level = DEFAULT; +SET optimizer_switch=@tmp887479_optimizer_switch; +DROP TABLE t1,t2; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index f84e0246969..e71887f80ac 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -2,7 +2,7 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; drop view if exists v2; set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, -"semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); +"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; select (select 2); (select 2) @@ -2893,6 +2893,8 @@ select * from (select max(fld) from t1) as foo; max(fld) 1 drop table t1; +set @tmp11867_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin_with_cache=off'; CREATE TABLE t1 (one int, two int, flag char(1)); CREATE TABLE t2 (one int, two int, flag char(1)); INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N'); @@ -2977,6 +2979,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; +set optimizer_switch=@tmp11867_optimizer_switch; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb')); diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 1279fc6b8f4..be922e7cbd4 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1,6 +1,6 @@ drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; set @subselect3_tmp= @@optimizer_switch; -set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'; create table t1 (oref int, grp int, ie int) ; insert into t1 (oref, grp, ie) values (1, 1, 1), @@ -345,8 +345,8 @@ dd NULL 0 bb NULL NULL select oref, a from t2 where a in (select ie from t1 where oref=t2.oref); oref a -ff 2 aa 1 +ff 2 select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref); oref a bb 2 @@ -523,8 +523,8 @@ aa 1 1 1 dd 1 NULL 0 select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref); oref a b -ff 2 2 aa 1 1 +ff 2 2 select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref); oref a b bb 2 1 @@ -1117,7 +1117,7 @@ set @@optimizer_switch=@save_optimizer_switch; explain select * from (select a from t0) X where a in (select a from t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 -1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>) +1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(<derived2>); Using join buffer (flat, BNL join) 2 DERIVED t0 ALL NULL NULL NULL NULL 11 drop table t0, t1; create table t0 (a int); @@ -1424,4 +1424,51 @@ CALL p1; ERROR 42S22: Unknown column 'f1' in 'where clause' DROP PROCEDURE p1; DROP TABLE t1, t2; +# +# fix of LP BUG#824425 (prohibiting subqueries in row in +# left part of IN/ALL/ANY) +# +CREATE TABLE t1 ( a int) ; +INSERT INTO t1 VALUES (20),(30); +CREATE TABLE t2 (a int) ; +INSERT INTO t2 VALUES (3),(9); +CREATE TABLE t3 ( a int, b int) ; +INSERT INTO t3 VALUES (20,5),(30,6); +set @optimizer_switch_save=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=OFF,in_to_exists=OFF,materialization=ON,partial_match_rowid_merge=ON,partial_match_table_scan=OFF'; +SELECT * FROM t1 +WHERE ( +( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( +SELECT b +FROM t3 +); +a +20 +30 +explain extended +SELECT * FROM t1 +WHERE ( +( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( +SELECT b +FROM t3 +); +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 +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9))>(<in_optimizer>((select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)),(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)) in ( <materialize> (select `test`.`t3`.`b` from `test`.`t3` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery3>`.`b`)))))))) +SELECT * FROM t1 +WHERE ( +( SELECT a FROM t2 WHERE a = 9 ), +( SELECT a FROM t2 WHERE a = 3 ) +) NOT IN ( +SELECT b , a +FROM t3 +); +ERROR 42000: This version of MySQL doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' +set optimizer_switch=@optimizer_switch_save; +drop table t1,t2,t3; set @@optimizer_switch=@subselect3_tmp; diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 15dd920d95d..6e2280c1d42 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -9,7 +9,7 @@ Variable_name Value join_cache_level 6 drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; set @subselect3_tmp= @@optimizer_switch; -set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'; create table t1 (oref int, grp int, ie int) ; insert into t1 (oref, grp, ie) values (1, 1, 1), @@ -1433,6 +1433,53 @@ CALL p1; ERROR 42S22: Unknown column 'f1' in 'where clause' DROP PROCEDURE p1; DROP TABLE t1, t2; +# +# fix of LP BUG#824425 (prohibiting subqueries in row in +# left part of IN/ALL/ANY) +# +CREATE TABLE t1 ( a int) ; +INSERT INTO t1 VALUES (20),(30); +CREATE TABLE t2 (a int) ; +INSERT INTO t2 VALUES (3),(9); +CREATE TABLE t3 ( a int, b int) ; +INSERT INTO t3 VALUES (20,5),(30,6); +set @optimizer_switch_save=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=OFF,in_to_exists=OFF,materialization=ON,partial_match_rowid_merge=ON,partial_match_table_scan=OFF'; +SELECT * FROM t1 +WHERE ( +( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( +SELECT b +FROM t3 +); +a +20 +30 +explain extended +SELECT * FROM t1 +WHERE ( +( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( +SELECT b +FROM t3 +); +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 +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9))>(<in_optimizer>((select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)),(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)) in ( <materialize> (select `test`.`t3`.`b` from `test`.`t3` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery3>`.`b`)))))))) +SELECT * FROM t1 +WHERE ( +( SELECT a FROM t2 WHERE a = 9 ), +( SELECT a FROM t2 WHERE a = 3 ) +) NOT IN ( +SELECT b , a +FROM t3 +); +ERROR 42000: This version of MySQL doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' +set optimizer_switch=@optimizer_switch_save; +drop table t1,t2,t3; set @@optimizer_switch=@subselect3_tmp; set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index ddf339ba1b5..0e5adde7178 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -1,6 +1,7 @@ drop table if exists t1,t2,t3,t4,t5,t6; set @subselect4_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set optimizer_switch='semijoin_with_cache=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; # # Bug #46791: Assertion failed:(table->key_read==0),function unknown diff --git a/mysql-test/r/subselect_extra.result b/mysql-test/r/subselect_extra.result index 3dfe2bab4a6..a74b713f11f 100644 --- a/mysql-test/r/subselect_extra.result +++ b/mysql-test/r/subselect_extra.result @@ -1,5 +1,8 @@ drop table if exists t1,t2,t3,t4; drop view if exists v1,v2,v3; +set @subselect_extra_tmp=@@optimizer_switch; +set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_extra_test, +"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); # From explain.test: # # Bug#37870: Usage of uninitialized value caused failed assertion. @@ -226,7 +229,7 @@ group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 range NULL idx_t1_1 147 NULL 17 Using where; Using index for group-by 2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index -2 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index; FirstMatch(t2) +2 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index; FirstMatch(t2); Using join buffer (flat, BNL join) select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t2.c in (select c from t3 where t3.c > t1.b) and @@ -257,7 +260,7 @@ group by a1,a2,b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL idx_t1_1 163 NULL 128 Using where; Using index 2 DEPENDENT SUBQUERY t2 index NULL idx_t2_1 163 NULL 164 Using where; Using index -2 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index; FirstMatch(t2) +2 DEPENDENT SUBQUERY t3 index NULL idx_t3_1 10 NULL 192 Using where; Using index; FirstMatch(t2); Using join buffer (flat, BNL join) select a1,a2,b,c,min(c), max(c) from t1 where exists ( select * from t2 where t2.c in (select c from t3 where t3.c > t1.c) and @@ -304,7 +307,7 @@ EXPLAIN SELECT 1 FROM t1 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t1) +1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; @@ -317,7 +320,7 @@ EXPLAIN SELECT 1 FROM t2 WHERE a IN (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 index a a 5 NULL 4 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2) +1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2); Using join buffer (flat, BNL join) DROP TABLE t1, t2; # # From derived_view.test @@ -427,8 +430,8 @@ WHERE t3.b IN (SELECT v1.b FROM v1, t2 WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary -1 PRIMARY <derived3> ALL NULL NULL NULL NULL 3 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 3 Using where; Start temporary; End temporary 3 DERIVED t1 ALL NULL NULL NULL NULL 3 SELECT * FROM t3 WHERE t3.b IN (SELECT v1.b FROM v1, t2 diff --git a/mysql-test/r/subselect_extra_no_semijoin.result b/mysql-test/r/subselect_extra_no_semijoin.result index c1ca1186259..f0d720d91f3 100644 --- a/mysql-test/r/subselect_extra_no_semijoin.result +++ b/mysql-test/r/subselect_extra_no_semijoin.result @@ -1,7 +1,10 @@ set @subselect_extra_no_sj_tmp=@@optimizer_switch; -set optimizer_switch='semijoin=off,firstmatch=off,loosescan=off'; +set @optimizer_switch_for_subselect_extra_test='semijoin=off,firstmatch=off,loosescan=off,semijoin_with_cache=off'; drop table if exists t1,t2,t3,t4; drop view if exists v1,v2,v3; +set @subselect_extra_tmp=@@optimizer_switch; +set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_extra_test, +"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); # From explain.test: # # Bug#37870: Usage of uninitialized value caused failed assertion. @@ -478,3 +481,4 @@ DROP VIEW v2; DROP TABLE t1,t2; set optimizer_switch= @tmp_subselect_extra_derived; set optimizer_switch= @subselect_extra_no_sj_tmp; +set @optimizer_switch_for_subselect_extra_test=null; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 8241d9027a5..d155a2cd3a8 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1,6 +1,6 @@ set @subselect_mat_test_optimizer_switch_value='materialization=on,in_to_exists=off,semijoin=off'; set @subselect_sj_mat_tmp= @@optimizer_switch; -set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on'); +set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set @optimizer_switch_local_default= @@optimizer_switch; drop table if exists t1, t2, t3, t4, t5, t1i, t2i, t3i; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index b480b274c48..6b6af156a00 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7,7 +7,7 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; drop view if exists v2; set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, -"semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); +"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; select (select 2); (select 2) @@ -2898,6 +2898,8 @@ select * from (select max(fld) from t1) as foo; max(fld) 1 drop table t1; +set @tmp11867_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin_with_cache=off'; CREATE TABLE t1 (one int, two int, flag char(1)); CREATE TABLE t2 (one int, two int, flag char(1)); INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N'); @@ -2982,6 +2984,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; +set optimizer_switch=@tmp11867_optimizer_switch; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb')); diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index ceb9ee40db8..7eb45ae7d9b 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -3,7 +3,7 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; drop view if exists v2; set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, -"semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); +"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; select (select 2); (select 2) @@ -2894,6 +2894,8 @@ select * from (select max(fld) from t1) as foo; max(fld) 1 drop table t1; +set @tmp11867_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin_with_cache=off'; CREATE TABLE t1 (one int, two int, flag char(1)); CREATE TABLE t2 (one int, two int, flag char(1)); INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N'); @@ -2978,6 +2980,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; +set optimizer_switch=@tmp11867_optimizer_switch; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb')); diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 43d8e4e7ad3..f7f593fb8ca 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -6,7 +6,7 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; drop view if exists v2; set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, -"semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); +"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; select (select 2); (select 2) @@ -2897,6 +2897,8 @@ select * from (select max(fld) from t1) as foo; max(fld) 1 drop table t1; +set @tmp11867_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin_with_cache=off'; CREATE TABLE t1 (one int, two int, flag char(1)); CREATE TABLE t2 (one int, two int, flag char(1)); INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N'); @@ -2981,6 +2983,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; +set optimizer_switch=@tmp11867_optimizer_switch; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb')); diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 7f9e3185d2e..bb1ffa6942a 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -3,7 +3,7 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; drop view if exists v2; set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, -"semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); +"semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; select (select 2); (select 2) @@ -2894,6 +2894,8 @@ select * from (select max(fld) from t1) as foo; max(fld) 1 drop table t1; +set @tmp11867_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin_with_cache=off'; CREATE TABLE t1 (one int, two int, flag char(1)); CREATE TABLE t2 (one int, two int, flag char(1)); INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N'); @@ -2978,6 +2980,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where (`test`.`t2`.`flag` = '0') group by `test`.`t2`.`one`,`test`.`t2`.`two` having (trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))) and trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1` DROP TABLE t1,t2; +set optimizer_switch=@tmp11867_optimizer_switch; CREATE TABLE t1 (a char(5), b char(5)); INSERT INTO t1 VALUES (NULL,'aaa'), ('aaa','aaa'); SELECT * FROM t1 WHERE (a,b) IN (('aaa','aaa'), ('aaa','bbb')); diff --git a/mysql-test/r/subselect_partial_match.result b/mysql-test/r/subselect_partial_match.result index 1b39381b42b..dddf5e2d8d6 100644 --- a/mysql-test/r/subselect_partial_match.result +++ b/mysql-test/r/subselect_partial_match.result @@ -3,11 +3,13 @@ set @save_optimizer_switch=@@optimizer_switch; Part 1: Feature tests. ------------------------------- Default for all tests. -set @@optimizer_switch="materialization=on,in_to_exists=off,semijoin=off,subquery_cache=off"; - +set @@optimizer_switch = "materialization=on,in_to_exists=off,semijoin=off,subquery_cache=off"; +set @test_default_opt_switch = @@optimizer_switch; +set @in_exists = "materialization=off,in_to_exists=on,semijoin=off,subquery_cache=off"; +------------------------------------------------------------------------- Schema requires partial matching, but data analysis discoveres there is no need. This is possible only if all outer columns are not NULL. - +------------------------------------------------------------------------- create table t1 (a1 char(8) not null, a2 char(8) not null); create table t2 (b1 char(8), b2 char(8)); insert into t1 values ('1 - 00', '2 - 00'); @@ -26,9 +28,9 @@ a1 a2 in_res 1 - 00 2 - 00 0 1 - 01 2 - 01 NULL drop table t1, t2; - +------------------------------------------------------------------------- NULLs in the outer columns, no NULLs in the suqbuery - +------------------------------------------------------------------------- create table t1 (a1 char(8), a2 char(8)); create table t2 (b1 char(8) not null, b2 char(8) not null); insert into t1 values (NULL , '2 - 00'); @@ -55,17 +57,532 @@ NULL 2 - 00 NULL 1 - 01 2 - 01 1 NULL NULL NULL drop table t1, t2; - +------------------------------------------------------------------------- +NULLs in the outer column, NULLs in the subquery, there is +no value match in any column, but there is a partial match +such that some of the matching NULLs are in the outer columns, +the other NULLs are in the inner columns. +------------------------------------------------------------------------- +create table t1 (a1 char(1), a2 char(1), a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); +insert into t1 values ('c', NULL, 'a'); +insert into t2 values (NULL, 'x', NULL); +insert into t2 values (NULL, 'y', NULL); +insert into t2 values ('o', 'z', 'p'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +c NULL a NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +c NULL a NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +c NULL a NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch = @test_default_opt_switch; +drop table t1, t2; +create table t1 (a1 char(1), a2 char(1) not null, a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); +insert into t1 values (NULL, 'y', NULL); +insert into t2 values ('v', 'x', NULL); +insert into t2 values (NULL, 'y', 'w'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +NULL y NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +NULL y NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +NULL y NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch = @test_default_opt_switch; +drop table t1, t2; +------------------------------------------------------------------------- +There is only one column in the subquery to complement the NULLs in the +outer reference. It is a NULL column, so a match is guaranteed. +------------------------------------------------------------------------- +create table t1 (a1 char(1), a2 char(1), a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); +insert into t1 values (NULL, 'g', NULL); +insert into t2 values ('z', NULL, 'y'); +insert into t2 values (NULL, 'z', 'y'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +NULL g NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +NULL g NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +NULL g NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +set @@optimizer_switch = @test_default_opt_switch; +drop table t1, t2; +------------------------------------------------------------------------- +The intersection of the NULL bitmaps is empty because the ranges +of NULL bits do not overlap. +------------------------------------------------------------------------- +create table t1 (a1 char(1), a2 char(1), a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); +insert into t1 values ('b', 'g', NULL); +insert into t2 values ('z', NULL, 'y'); +insert into t2 values (NULL, 'z', 'y'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +b g NULL 0 +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +b g NULL +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +b g NULL 0 +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +b g NULL +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +a1 a2 a3 in_res +b g NULL 0 +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +a1 a2 a3 +b g NULL +drop table t1, t2; +------------------------------------------------------------------------- +The intersection of the NULL bitmaps is non-empty, and there is a +non-NULL column. +------------------------------------------------------------------------- +create table t1 (a1 char(1), a2 char(1), a3 char(1) not null, a4 char(10)); +create table t2 (b1 char(1), b2 char(1), b3 char(1), b4 char(1)); +insert into t1 values ('a', 'g', 'x', NULL); +insert into t2 values ('z', NULL, 'y', 'x'); +insert into t2 values (NULL, NULL, 'x', 'y'); +insert into t2 values ('x', 'w', 'z', NULL); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +a g x NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +a g x NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +a g x NULL NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +drop table t1, t2; +------------------------------------------------------------------------- +Value match in one row, but the NULL complement match in another. +The result must be false. +------------------------------------------------------------------------- +create table t1 (a1 char(2), a2 char(1), a3 char(1) not null, a4 char(10)); +create table t2 (b1 char(2), b2 char(1), b3 char(1), b4 char(1)); +insert into t1 values ('99', NULL, 'j', 'f'); +insert into t2 values ('01', NULL, 'y', NULL); +insert into t2 values ('02', NULL, 'x', 'y'); +insert into t2 values ('03', 'q', 'y', 'x'); +insert into t2 values (NULL, 'q', 'm', 'p'); +insert into t2 values ('m', 'z', 'j', NULL); +insert into t2 values (NULL, 'z', 'b', NULL); +insert into t2 values (NULL, 'z', 'a', NULL); +insert into t2 values ('34', 'q', 'y', 'x'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 8 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +99 NULL j f 0 +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 8 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +99 NULL j f +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 8 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +99 NULL j f 0 +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 8 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +99 NULL j f +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 8 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +99 NULL j f 0 +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 8 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +99 NULL j f +drop table t1, t2; +------------------------------------------------------------------------- +Test the intersection of larger number of rows with NULL, such that +the number is at the boundary 32. This test is based on the implementation +of MY_BITMAP which uses 32 bit words, and the intersection operation works +by intersecting the bitmap word by word. +------------------------------------------------------------------------- +create table t1 (a1 char(2), a2 char(2), a3 char(1), a4 char(10)) ENGINE=MyISAM; +create table t2 (b1 char(2), b2 char(2), b3 char(1), b4 char(1)) ENGINE=MyISAM; +insert into t1 values (NULL, '98', NULL, 'b'); +insert into t1 values (NULL, '99', NULL, 'c'); +insert into t2 values ('00', 'rr', 'y', NULL); +insert into t2 values ('01', 'rr', 'y', NULL); +insert into t2 values ('02', NULL, 'x', 'y'); +insert into t2 values ('03', 'qq', 'y', 'x'); +insert into t2 values ('04', 'qq', 'm', 'p'); +insert into t2 values ('05', 'rr', 'y', NULL); +insert into t2 values ('06', NULL, 'x', 'y'); +insert into t2 values ('07', 'qq', 'y', 'x'); +insert into t2 values ('08', 'qq', 'm', 'q'); +insert into t2 values ('09', 'rr', 'y', NULL); +insert into t2 values ('10', NULL, 'x', 'y'); +insert into t2 values ('11', 'qq', 'y', 'x'); +insert into t2 values ('12', 'qq', 'm', 'k'); +insert into t2 values ('13', 'rr', 'y', NULL); +insert into t2 values ('14', NULL, 'x', 'y'); +insert into t2 values ('15', 'qq', 'y', 'x'); +insert into t2 values ('16', 'qq', 'm', 's'); +insert into t2 values ('17', 'rr', 'y', NULL); +insert into t2 values ('18', NULL, 'x', 'y'); +insert into t2 values ('19', 'qq', 'y', 'x'); +insert into t2 values ('20', 'qq', 'm', 't'); +insert into t2 values ('21', 'rr', 'y', NULL); +insert into t2 values ('22', NULL, 'x', 'y'); +insert into t2 values ('23', 'qq', 'y', 'x'); +insert into t2 values ('24', 'qq', 'm', 'u'); +insert into t2 values ('25', 'rr', 'y', NULL); +insert into t2 values ('26', NULL, 'x', 'y'); +insert into t2 values ('27', 'qq', 'y', 'x'); +insert into t2 values ('28', 'qq', 'm', 'y'); +insert into t2 values ('29', 'rr', 'y', NULL); +insert into t2 values ('30', NULL, 'x', 'z'); +insert into t2 values ('31', 'ss', 'h', NULL); +insert into t2 values ('32', 'vv', 'i', NULL); +the only partial matching row +insert into t2 values ('33', NULL, 'j', NULL); +insert into t2 values ('34', 'qq', 'y', 'x'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b NULL +NULL 99 NULL c NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b NULL +NULL 99 NULL c NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b NULL +NULL 99 NULL c NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +change the mathcing row to be the last one in the first bitmap word +update t2 set b2 = 'zz' where b1 = 33; +update t2 set b2 = NULL where b1 = 31; +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b NULL +NULL 99 NULL c NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b NULL +NULL 99 NULL c NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b NULL +NULL 99 NULL c NULL +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 35 Using where +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +a1 a2 a3 a4 +drop table t1, t2; +------------------------------------------------------------------------- All columns require partial matching (no non-null columns) - +------------------------------------------------------------------------- TODO - +------------------------------------------------------------------------- Both non-NULL columns and columns with NULLs - +------------------------------------------------------------------------- TODO - +------------------------------------------------------------------------- Covering NULL rows - +------------------------------------------------------------------------- create table t1 (a1 char(8), a2 char(8)); create table t2 (b1 char(8), b2 char(8)); insert into t1 values ('1 - 00', '2 - 00'); @@ -98,9 +615,9 @@ a1 a2 in_res 1 - 00 2 - 00 NULL 1 - 01 2 - 01 1 drop table t1, t2; - +------------------------------------------------------------------------- Covering NULL columns - +------------------------------------------------------------------------- this case affects only the rowid-merge algorithm set @@optimizer_switch="partial_match_rowid_merge=on,partial_match_table_scan=off"; create table t1 (a1 char(8) not null, a2 char(8), a3 char(8) not null); @@ -149,9 +666,9 @@ a1 a2 a3 in_res 1 - 00 2 - 00 3 - 00 NULL 1 - 01 2 - 01 3 - 01 0 drop table t1, t2; - +------------------------------------------------------------------------- Covering NULL row, and a NULL column - +------------------------------------------------------------------------- create table t1 (a1 char(8) not null, a2 char(8), a3 char(8)); create table t2 (b1 char(8), b2 char(8), b3 char(8)); insert into t1 values ('1 - 00', '2 - 00', '3 - 00'); @@ -174,9 +691,9 @@ a1 a2 a3 in_res 1 - 00 2 - 00 3 - 00 NULL 1 - 01 2 - 01 3 - 01 NULL drop table t1, t2; - +------------------------------------------------------------------------- Covering NULL row, and covering NULL columns - +------------------------------------------------------------------------- create table t1 (a1 char(8) not null, a2 char(8), a3 char(8)); create table t2 (b1 char(8), b2 char(8), b3 char(8)); insert into t1 values ('1 - 00', '2 - 00', '3 - 00'); @@ -198,6 +715,32 @@ a1 a2 a3 in_res 1 - 00 2 - 00 3 - 00 NULL 1 - 01 2 - 01 3 - 01 NULL drop table t1, t2; +------------------------------------------------------------------------- +Small buffer for the rowid_merge partial match algorithm that forces +reverting to table scan partial match. +------------------------------------------------------------------------- +set @save_rowid_merge_buff_size = @@rowid_merge_buff_size; +set @@rowid_merge_buff_size = 0; +create table t1 (a1 char(2), a2 char(2), a3 char(1), a4 char(10)); +create table t2 (b1 char(2), b2 char(2), b3 char(1), b4 char(1)); +insert into t1 values (NULL, '98', NULL, 'b'); +insert into t1 values (NULL, '99', NULL, 'c'); +insert into t2 values ('00', 'rr', 'y', NULL); +insert into t2 values ('01', 'rr', 'y', NULL); +insert into t2 values ('02', NULL, 'x', 'y'); +insert into t2 values ('03', 'qq', 'y', 'x'); +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using where +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +a1 a2 a3 a4 in_res +NULL 98 NULL b 0 +NULL 99 NULL c 0 +drop table t1, t2; +set @@rowid_merge_buff_size = @save_rowid_merge_buff_size; ------------------------------- Part 2: Test cases for bugs. ------------------------------- @@ -372,4 +915,37 @@ SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); f3 5 drop table t1, t2; +# +# LP BUG#869036 Wrong result with in_to_exists=ON and NOT IN +# +create table outer_sq (f1 char(1), f2 char(1)); +insert into outer_sq values (NULL, 'c'), ('g', 'c'); +create table inner_sq (f3 char(1), f4 char(1)); +insert into inner_sq values(null, 'i'), ('v', null); +All three strategies below must produce the same result. +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; +SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq; +f1 f2 (f1, f2) IN (select * from inner_sq) +NULL c NULL +g c 0 +SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq); +f1 f2 +g c +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=off,partial_match_table_scan=on'; +SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq; +f1 f2 (f1, f2) IN (select * from inner_sq) +NULL c NULL +g c 0 +SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq); +f1 f2 +g c +set @@optimizer_switch='in_to_exists=on,materialization=off'; +SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq; +f1 f2 (f1, f2) IN (select * from inner_sq) +NULL c NULL +g c 0 +SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq); +f1 f2 +g c +drop table outer_sq, inner_sq; set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index 99d8e9d2d77..c372d0fe0bf 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -726,7 +726,7 @@ 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 (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) +1 PRIMARY it2 ALL int_key,datetime_key NULL NULL NULL 20 Using where; End temporary DROP TABLE ot1, it1, it2; # End of BUG#38075 # @@ -1047,8 +1047,8 @@ 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 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) +1 PRIMARY t3 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary SELECT * FROM t1 WHERE t1.val IN (SELECT t2.val FROM t2 @@ -1238,7 +1238,7 @@ 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 B index NULL PRIMARY 4 NULL 3 Using index; End temporary 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 @@ -1268,8 +1268,8 @@ 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) +1 PRIMARY C ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY D ALL NULL NULL NULL NULL 3 Using where; End temporary drop table t1, t2; # # BUG#784441: Abort on semijoin with a view as the inner table @@ -1378,7 +1378,7 @@ 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 t2 ALL NULL NULL NULL NULL 2 Using where 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 @@ -1432,7 +1432,7 @@ 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 t1 ALL NULL NULL NULL NULL 4 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 t2 on t1.a=t2.a); a @@ -1491,7 +1491,7 @@ 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 t1 ALL NULL NULL NULL NULL 4 Using where 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 @@ -1509,7 +1509,7 @@ 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 t1 ALL NULL NULL NULL NULL 4 Using where 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 @@ -1664,8 +1664,6 @@ 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 @@ -1684,6 +1682,8 @@ f10 f10 f11 f10 0 0 b 0 0 0 a 0 0 0 a 0 +0 0 b 0 +0 0 b 0 drop table t1,t2,t3,t4; # # BUG#803457: Wrong result with semijoin + view + outer join in maria-5.3-subqueries-mwl90 @@ -1706,7 +1706,7 @@ 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) +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary 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 @@ -1766,7 +1766,7 @@ SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3); 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 index PRIMARY PRIMARY 4 NULL 2 Using index; Using join buffer (flat, BNL join) -1 PRIMARY t3 ALL b NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL b NULL NULL NULL 5 Using where; End temporary SELECT * FROM t1, t2 WHERE (t2.a , t1.b) IN (SELECT a, b FROM t3); b a 5 6 @@ -1872,8 +1872,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 20 Start temporary 1 PRIMARY alias2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) 1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join) -1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (flat, BNL join) -1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY SQ3_alias1 ALL NULL NULL NULL NULL 20 Using where +1 PRIMARY SQ3_alias2 index NULL PRIMARY 4 NULL 20 Using index; End temporary 2 DERIVED t2 ALL NULL NULL NULL NULL 20 create table t3 as SELECT @@ -2018,4 +2018,36 @@ EXECUTE st1; b c b d DROP TABLE t1,t2,t3; set optimizer_switch=@tmp878753; +# +# Bug #889750: semijoin=on + firstmatch=off + semijoin_with_cache=off +# +create table t1 (a int); +insert into t1 values (7), (1), (5), (3); +create table t2 (a int); +insert into t2 values (4), (1), (8), (3), (9), (2); +set @tmp_otimizer_switch= @@optimizer_switch; +set optimizer_switch='semijoin=on'; +set optimizer_switch='firstmatch=off'; +set optimizer_switch='semijoin_with_cache=on'; +explain +select * from t1 where t1.a in (select t2.a from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Start temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join) +select * from t1 where t1.a in (select t2.a from t2); +a +1 +3 +set optimizer_switch='semijoin_with_cache=off'; +explain +select * from t1 where t1.a in (select t2.a from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Start temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary +select * from t1 where t1.a in (select t2.a from t2); +a +1 +3 +set optimizer_switch= @tmp_otimizer_switch; +drop table t1,t2; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index db99d77f50c..0de04685fac 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -867,7 +867,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Start temporary 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 -1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary 3 DERIVED t1 ALL NULL NULL NULL NULL 1 SELECT * FROM t3 LEFT JOIN (v1,t2) ON t3.a = t2.a WHERE t3.b IN (SELECT b FROM t4); diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index 2d1da2ea953..26a2f93fc86 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -2029,6 +2029,38 @@ EXECUTE st1; b c b d DROP TABLE t1,t2,t3; set optimizer_switch=@tmp878753; +# +# Bug #889750: semijoin=on + firstmatch=off + semijoin_with_cache=off +# +create table t1 (a int); +insert into t1 values (7), (1), (5), (3); +create table t2 (a int); +insert into t2 values (4), (1), (8), (3), (9), (2); +set @tmp_otimizer_switch= @@optimizer_switch; +set optimizer_switch='semijoin=on'; +set optimizer_switch='firstmatch=off'; +set optimizer_switch='semijoin_with_cache=on'; +explain +select * from t1 where t1.a in (select t2.a from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Start temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer (flat, BNL join) +select * from t1 where t1.a in (select t2.a from t2); +a +1 +3 +set optimizer_switch='semijoin_with_cache=off'; +explain +select * from t1 where t1.a in (select t2.a from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Start temporary +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 Using where; End temporary +select * from t1 where t1.a in (select t2.a from t2); +a +1 +3 +set optimizer_switch= @tmp_otimizer_switch; +drop table t1,t2; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 465e482fb66..350d3fe62d7 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -1,5 +1,5 @@ set @subselect_sj_mat_tmp= @@optimizer_switch; -set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on'); +set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set @optimizer_switch_local_default= @@optimizer_switch; drop table if exists t1, t2, t3, t4, t5, t1i, t2i, t3i; diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result index b4d3a2f6f2f..892ac29d6d5 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -567,3 +567,22 @@ id select_type table type possible_keys key key_len ref rows Extra # ^^ The above must not produce a QEP of t3,t5,t2,t4 # as that violates the "no interleaving of outer join nests" rule. DROP TABLE t1,t2,t3,t4,t5; +# +# BUG#884184: Wrong result with RIGHT JOIN + derived_merge +# +CREATE TABLE t1 (a int(11), b varchar(1)) ; +INSERT IGNORE INTO t1 VALUES (0,'g'); +CREATE TABLE t3 ( a varchar(1)) ; +INSERT IGNORE INTO t3 VALUES ('g'); +CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)) ; +create view v1 as SELECT t1.* FROM t1 LEFT JOIN t2 ON ( t1.a = t2.a ) WHERE t2.a <> 0; +SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); +a b +NULL NULL +EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 system NULL NULL NULL NULL 1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using index +drop view v1; +DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 092be494302..e4c289711a1 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -539,7 +539,7 @@ select * from t1 where id in (select id from t1 as x1 where (t1.cur_date is null)); 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 Using where; Start temporary -1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary Warnings: Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where ((`test`.`x1`.`id` = `test`.`t1`.`id`) and (`test`.`t1`.`cur_date` = 0)) @@ -551,7 +551,7 @@ select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); 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; Start temporary -1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; End temporary Warnings: Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where ((`test`.`x1`.`id` = `test`.`t2`.`id`) and (`test`.`t2`.`cur_date` = 0)) diff --git a/mysql-test/suite/pbxt/r/derived.result b/mysql-test/suite/pbxt/r/derived.result index 75d5fcf8b16..4fc9216f126 100644 --- a/mysql-test/suite/pbxt/r/derived.result +++ b/mysql-test/suite/pbxt/r/derived.result @@ -190,13 +190,13 @@ pla_id test explain SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY <derived2> ALL NULL $hj 7 test.m2.matintnum 9 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 Using where 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY m2 ALL NULL NULL NULL NULL 9 -1 PRIMARY <derived2> ALL NULL $hj 7 test.m2.matintnum 9 Using where +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9 Using where 2 DERIVED mp ALL NULL NULL NULL NULL 9 Using temporary; Using filesort 2 DERIVED m1 eq_ref PRIMARY PRIMARY 3 test.mp.mat_id 1 drop table t1,t2; diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index 4cbbecf284f..f1af0ecc057 100644 --- a/mysql-test/suite/pbxt/r/subselect.result +++ b/mysql-test/suite/pbxt/r/subselect.result @@ -1362,7 +1362,7 @@ explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1 id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index; Start temporary 1 PRIMARY t1 ref a a 5 test.t2.a 1 100.00 Using index -1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary Warnings: Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`)) insert into t1 values (3,31); diff --git a/mysql-test/t/feedback_plugin_send.test b/mysql-test/t/feedback_plugin_send.test index b49c0d0e252..14765ee9543 100644 --- a/mysql-test/t/feedback_plugin_send.test +++ b/mysql-test/t/feedback_plugin_send.test @@ -1,5 +1,4 @@ source t/feedback_plugin_load.test; -source include/big_test.inc; if (!$MTR_FEEDBACK_PLUGIN) { skip MTR_FEEDBACK_PLUGIN is not set; diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 116f19b55eb..530528ead2c 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3225,5 +3225,34 @@ SET SESSION join_cache_level = DEFAULT; DROP TABLE t1, t2; +--echo # +--echo # Bug #887479: join_cache_level=3 + semijoin=on +--echo # + +CREATE TABLE t1 (a int, b int); +INSERT INTO t1 VALUES (3914,17), (3710,5), (3888,20); + +CREATE TABLE t2 (c int, KEY (c)); +INSERT INTO t2 VALUES (27), (17), (33), (20), (3), (7), (18), (2); + +SET @tmp887479_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=on'; +SET SESSION optimizer_switch='semijoin_with_cache=on'; + +SET SESSION join_cache_level=1; +EXPLAIN +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); + +SET SESSION join_cache_level=3; +EXPLAIN +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); +SELECT * FROM t1 WHERE (t1.b) IN (SELECT c FROM t2); + +SET SESSION join_cache_level = DEFAULT; +SET optimizer_switch=@tmp887479_optimizer_switch; + +DROP TABLE t1,t2; + # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 13856ab31cc..c310878f0f8 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -14,7 +14,7 @@ drop view if exists v2; set @subselect_tmp=@@optimizer_switch; set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_test, - "semijoin=on,firstmatch=on,loosescan=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); + "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; select (select 2); @@ -1866,6 +1866,9 @@ drop table t1; # Bug#11867 queries with ROW(,elems>) IN (SELECT DISTINCT <cols> FROM ...) # +set @tmp11867_optimizer_switch=@@optimizer_switch; +set optimizer_switch='semijoin_with_cache=off'; + CREATE TABLE t1 (one int, two int, flag char(1)); CREATE TABLE t2 (one int, two int, flag char(1)); INSERT INTO t1 VALUES(1,2,'Y'),(2,3,'Y'),(3,4,'Y'),(5,6,'N'),(7,8,'N'); @@ -1893,6 +1896,7 @@ explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FR explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1; DROP TABLE t1,t2; +set optimizer_switch=@tmp11867_optimizer_switch; # # Bug#12392 where cond with IN predicate for rows and NULL values in table diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index f3faf44289e..4691ef37f40 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -3,7 +3,7 @@ drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22; --enable_warnings set @subselect3_tmp= @@optimizer_switch; -set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'; # # 1. Subquery with GROUP/HAVING @@ -1187,5 +1187,52 @@ CALL p1; DROP PROCEDURE p1; DROP TABLE t1, t2; + +--echo # +--echo # fix of LP BUG#824425 (prohibiting subqueries in row in +--echo # left part of IN/ALL/ANY) +--echo # + +CREATE TABLE t1 ( a int) ; +INSERT INTO t1 VALUES (20),(30); + +CREATE TABLE t2 (a int) ; +INSERT INTO t2 VALUES (3),(9); + +CREATE TABLE t3 ( a int, b int) ; +INSERT INTO t3 VALUES (20,5),(30,6); + +set @optimizer_switch_save=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=OFF,in_to_exists=OFF,materialization=ON,partial_match_rowid_merge=ON,partial_match_table_scan=OFF'; + +SELECT * FROM t1 +WHERE ( + ( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( + SELECT b + FROM t3 +); +explain extended +SELECT * FROM t1 +WHERE ( + ( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( + SELECT b + FROM t3 +); + +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 +WHERE ( + ( SELECT a FROM t2 WHERE a = 9 ), + ( SELECT a FROM t2 WHERE a = 3 ) +) NOT IN ( + SELECT b , a + FROM t3 +); +set optimizer_switch=@optimizer_switch_save; + +drop table t1,t2,t3; + # The following command must be the last one the file set @@optimizer_switch=@subselect3_tmp; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index db0433ce48e..98d47e7f3ea 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -6,6 +6,7 @@ drop table if exists t1,t2,t3,t4,t5,t6; set @subselect4_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; +set optimizer_switch='semijoin_with_cache=on'; set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; --echo # diff --git a/mysql-test/t/subselect_extra.test b/mysql-test/t/subselect_extra.test index ed1c0ef7e4d..b76f3f70516 100644 --- a/mysql-test/t/subselect_extra.test +++ b/mysql-test/t/subselect_extra.test @@ -12,6 +12,9 @@ drop table if exists t1,t2,t3,t4; drop view if exists v1,v2,v3; --enable_warnings +set @subselect_extra_tmp=@@optimizer_switch; +set @@optimizer_switch=ifnull(@optimizer_switch_for_subselect_extra_test, + "semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on,partial_match_rowid_merge=off,partial_match_table_scan=off"); --echo # From explain.test: --echo # @@ -390,4 +393,3 @@ DROP VIEW v2; DROP TABLE t1,t2; set optimizer_switch= @tmp_subselect_extra_derived; - diff --git a/mysql-test/t/subselect_extra_no_semijoin.test b/mysql-test/t/subselect_extra_no_semijoin.test index 65fbbfc6409..8aba3dde72b 100644 --- a/mysql-test/t/subselect_extra_no_semijoin.test +++ b/mysql-test/t/subselect_extra_no_semijoin.test @@ -1,7 +1,9 @@ set @subselect_extra_no_sj_tmp=@@optimizer_switch; -set optimizer_switch='semijoin=off,firstmatch=off,loosescan=off'; + +set @optimizer_switch_for_subselect_extra_test='semijoin=off,firstmatch=off,loosescan=off,semijoin_with_cache=off'; --source t/subselect_extra.test set optimizer_switch= @subselect_extra_no_sj_tmp; +set @optimizer_switch_for_subselect_extra_test=null;
\ No newline at end of file diff --git a/mysql-test/t/subselect_partial_match.test b/mysql-test/t/subselect_partial_match.test index cb25656e4fc..be78360c76b 100644 --- a/mysql-test/t/subselect_partial_match.test +++ b/mysql-test/t/subselect_partial_match.test @@ -10,12 +10,14 @@ set @save_optimizer_switch=@@optimizer_switch; --echo ------------------------------- --echo Default for all tests. -set @@optimizer_switch="materialization=on,in_to_exists=off,semijoin=off,subquery_cache=off"; +set @@optimizer_switch = "materialization=on,in_to_exists=off,semijoin=off,subquery_cache=off"; +set @test_default_opt_switch = @@optimizer_switch; +set @in_exists = "materialization=off,in_to_exists=on,semijoin=off,subquery_cache=off"; ---echo +--echo ------------------------------------------------------------------------- --echo Schema requires partial matching, but data analysis discoveres there is --echo no need. This is possible only if all outer columns are not NULL. ---echo +--echo ------------------------------------------------------------------------- create table t1 (a1 char(8) not null, a2 char(8) not null); create table t2 (b1 char(8), b2 char(8)); @@ -36,9 +38,9 @@ select a1, a2, (a1, a2) not in (select * from t2) as in_res from t1; drop table t1, t2; ---echo +--echo ------------------------------------------------------------------------- --echo NULLs in the outer columns, no NULLs in the suqbuery ---echo +--echo ------------------------------------------------------------------------- create table t1 (a1 char(8), a2 char(8)); create table t2 (b1 char(8) not null, b2 char(8) not null); @@ -63,21 +65,366 @@ select a1, a2, (a1, a2) in (select * from t2) as in_res from t1; drop table t1, t2; ---echo +--echo ------------------------------------------------------------------------- +--echo NULLs in the outer column, NULLs in the subquery, there is +--echo no value match in any column, but there is a partial match +--echo such that some of the matching NULLs are in the outer columns, +--echo the other NULLs are in the inner columns. +--echo ------------------------------------------------------------------------- + +create table t1 (a1 char(1), a2 char(1), a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); + +insert into t1 values ('c', NULL, 'a'); +insert into t2 values (NULL, 'x', NULL); +insert into t2 values (NULL, 'y', NULL); +insert into t2 values ('o', 'z', 'p'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch = @test_default_opt_switch; + +drop table t1, t2; + +create table t1 (a1 char(1), a2 char(1) not null, a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); + +insert into t1 values (NULL, 'y', NULL); +insert into t2 values ('v', 'x', NULL); +insert into t2 values (NULL, 'y', 'w'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch = @test_default_opt_switch; + +drop table t1, t2; + +--echo ------------------------------------------------------------------------- +--echo There is only one column in the subquery to complement the NULLs in the +--echo outer reference. It is a NULL column, so a match is guaranteed. +--echo ------------------------------------------------------------------------- + +create table t1 (a1 char(1), a2 char(1), a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); + +insert into t1 values (NULL, 'g', NULL); +insert into t2 values ('z', NULL, 'y'); +insert into t2 values (NULL, 'z', 'y'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch = @test_default_opt_switch; + +drop table t1, t2; + +--echo ------------------------------------------------------------------------- +--echo The intersection of the NULL bitmaps is empty because the ranges +--echo of NULL bits do not overlap. +--echo ------------------------------------------------------------------------- + +create table t1 (a1 char(1), a2 char(1), a3 char(1)); +create table t2 (b1 char(1), b2 char(1), b3 char(1)); + +insert into t1 values ('b', 'g', NULL); +insert into t2 values ('z', NULL, 'y'); +insert into t2 values (NULL, 'z', 'y'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3) NOT IN (select * from t2); + +drop table t1, t2; + +--echo ------------------------------------------------------------------------- +--echo The intersection of the NULL bitmaps is non-empty, and there is a +--echo non-NULL column. +--echo ------------------------------------------------------------------------- + +create table t1 (a1 char(1), a2 char(1), a3 char(1) not null, a4 char(10)); +create table t2 (b1 char(1), b2 char(1), b3 char(1), b4 char(1)); + +insert into t1 values ('a', 'g', 'x', NULL); +insert into t2 values ('z', NULL, 'y', 'x'); +insert into t2 values (NULL, NULL, 'x', 'y'); +insert into t2 values ('x', 'w', 'z', NULL); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +drop table t1, t2; + +--echo ------------------------------------------------------------------------- +--echo Value match in one row, but the NULL complement match in another. +--echo The result must be false. +--echo ------------------------------------------------------------------------- + +create table t1 (a1 char(2), a2 char(1), a3 char(1) not null, a4 char(10)); +create table t2 (b1 char(2), b2 char(1), b3 char(1), b4 char(1)); + +insert into t1 values ('99', NULL, 'j', 'f'); + +insert into t2 values ('01', NULL, 'y', NULL); +insert into t2 values ('02', NULL, 'x', 'y'); +insert into t2 values ('03', 'q', 'y', 'x'); +insert into t2 values (NULL, 'q', 'm', 'p'); +insert into t2 values ('m', 'z', 'j', NULL); +insert into t2 values (NULL, 'z', 'b', NULL); +insert into t2 values (NULL, 'z', 'a', NULL); +insert into t2 values ('34', 'q', 'y', 'x'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +drop table t1, t2; + +--echo ------------------------------------------------------------------------- +--echo Test the intersection of larger number of rows with NULL, such that +--echo the number is at the boundary 32. This test is based on the implementation +--echo of MY_BITMAP which uses 32 bit words, and the intersection operation works +--echo by intersecting the bitmap word by word. +--echo ------------------------------------------------------------------------- + +create table t1 (a1 char(2), a2 char(2), a3 char(1), a4 char(10)) ENGINE=MyISAM; +create table t2 (b1 char(2), b2 char(2), b3 char(1), b4 char(1)) ENGINE=MyISAM; + +insert into t1 values (NULL, '98', NULL, 'b'); +insert into t1 values (NULL, '99', NULL, 'c'); + +insert into t2 values ('00', 'rr', 'y', NULL); +insert into t2 values ('01', 'rr', 'y', NULL); +insert into t2 values ('02', NULL, 'x', 'y'); +insert into t2 values ('03', 'qq', 'y', 'x'); +insert into t2 values ('04', 'qq', 'm', 'p'); +insert into t2 values ('05', 'rr', 'y', NULL); +insert into t2 values ('06', NULL, 'x', 'y'); +insert into t2 values ('07', 'qq', 'y', 'x'); +insert into t2 values ('08', 'qq', 'm', 'q'); +insert into t2 values ('09', 'rr', 'y', NULL); +insert into t2 values ('10', NULL, 'x', 'y'); +insert into t2 values ('11', 'qq', 'y', 'x'); +insert into t2 values ('12', 'qq', 'm', 'k'); +insert into t2 values ('13', 'rr', 'y', NULL); +insert into t2 values ('14', NULL, 'x', 'y'); +insert into t2 values ('15', 'qq', 'y', 'x'); +insert into t2 values ('16', 'qq', 'm', 's'); +insert into t2 values ('17', 'rr', 'y', NULL); +insert into t2 values ('18', NULL, 'x', 'y'); +insert into t2 values ('19', 'qq', 'y', 'x'); +insert into t2 values ('20', 'qq', 'm', 't'); +insert into t2 values ('21', 'rr', 'y', NULL); +insert into t2 values ('22', NULL, 'x', 'y'); +insert into t2 values ('23', 'qq', 'y', 'x'); +insert into t2 values ('24', 'qq', 'm', 'u'); +insert into t2 values ('25', 'rr', 'y', NULL); +insert into t2 values ('26', NULL, 'x', 'y'); +insert into t2 values ('27', 'qq', 'y', 'x'); +insert into t2 values ('28', 'qq', 'm', 'y'); +insert into t2 values ('29', 'rr', 'y', NULL); +insert into t2 values ('30', NULL, 'x', 'z'); +insert into t2 values ('31', 'ss', 'h', NULL); +insert into t2 values ('32', 'vv', 'i', NULL); +--echo the only partial matching row +insert into t2 values ('33', NULL, 'j', NULL); +insert into t2 values ('34', 'qq', 'y', 'x'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +--echo change the mathcing row to be the last one in the first bitmap word +update t2 set b2 = 'zz' where b1 = 33; +update t2 set b2 = NULL where b1 = 31; + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=on'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +set @@optimizer_switch=@in_exists; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +EXPLAIN +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); +SELECT * from t1 where (a1, a2, a3, a4) NOT IN (select * from t2); + +drop table t1, t2; + + +--echo ------------------------------------------------------------------------- --echo All columns require partial matching (no non-null columns) ---echo +--echo ------------------------------------------------------------------------- --echo TODO ---echo +--echo ------------------------------------------------------------------------- --echo Both non-NULL columns and columns with NULLs ---echo +--echo ------------------------------------------------------------------------- --echo TODO ---echo +--echo ------------------------------------------------------------------------- --echo Covering NULL rows ---echo +--echo ------------------------------------------------------------------------- create table t1 (a1 char(8), a2 char(8)); create table t2 (b1 char(8), b2 char(8)); @@ -110,9 +457,9 @@ select a1, a2, (a1, a2) in (select * from t2) as in_res from t1; drop table t1, t2; ---echo +--echo ------------------------------------------------------------------------- --echo Covering NULL columns ---echo +--echo ------------------------------------------------------------------------- --echo this case affects only the rowid-merge algorithm set @@optimizer_switch="partial_match_rowid_merge=on,partial_match_table_scan=off"; @@ -161,9 +508,9 @@ select *, (a1, a2, a3) in (select * from t2) as in_res from t1; drop table t1, t2; ---echo +--echo ------------------------------------------------------------------------- --echo Covering NULL row, and a NULL column ---echo +--echo ------------------------------------------------------------------------- create table t1 (a1 char(8) not null, a2 char(8), a3 char(8)); create table t2 (b1 char(8), b2 char(8), b3 char(8)); @@ -188,9 +535,9 @@ select *, (a1, a2, a3) in (select * from t2) as in_res from t1; drop table t1, t2; ---echo +--echo ------------------------------------------------------------------------- --echo Covering NULL row, and covering NULL columns ---echo +--echo ------------------------------------------------------------------------- create table t1 (a1 char(8) not null, a2 char(8), a3 char(8)); create table t2 (b1 char(8), b2 char(8), b3 char(8)); @@ -213,6 +560,34 @@ select *, (a1, a2, a3) in (select * from t2) as in_res from t1; drop table t1, t2; +--echo ------------------------------------------------------------------------- +--echo Small buffer for the rowid_merge partial match algorithm that forces +--echo reverting to table scan partial match. +--echo ------------------------------------------------------------------------- + +set @save_rowid_merge_buff_size = @@rowid_merge_buff_size; +set @@rowid_merge_buff_size = 0; + +create table t1 (a1 char(2), a2 char(2), a3 char(1), a4 char(10)); +create table t2 (b1 char(2), b2 char(2), b3 char(1), b4 char(1)); + +insert into t1 values (NULL, '98', NULL, 'b'); +insert into t1 values (NULL, '99', NULL, 'c'); + +insert into t2 values ('00', 'rr', 'y', NULL); +insert into t2 values ('01', 'rr', 'y', NULL); +insert into t2 values ('02', NULL, 'x', 'y'); +insert into t2 values ('03', 'qq', 'y', 'x'); + +set @@optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off'; +EXPLAIN +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; +SELECT t1.*, (a1, a2, a3, a4) IN (select * from t2) as in_res from t1; + +drop table t1, t2; + +set @@rowid_merge_buff_size = @save_rowid_merge_buff_size; + --echo ------------------------------- --echo Part 2: Test cases for bugs. @@ -374,4 +749,30 @@ SELECT * FROM t2 WHERE ( 3 , 1 ) NOT IN ( SELECT f1 , f2 FROM t1 ); drop table t1, t2; +--echo # +--echo # LP BUG#869036 Wrong result with in_to_exists=ON and NOT IN +--echo # + +create table outer_sq (f1 char(1), f2 char(1)); +insert into outer_sq values (NULL, 'c'), ('g', 'c'); + +create table inner_sq (f3 char(1), f4 char(1)); +insert into inner_sq values(null, 'i'), ('v', null); + +--echo All three strategies below must produce the same result. + +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=on,partial_match_table_scan=off'; +SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq; +SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq); + +set @@optimizer_switch='in_to_exists=off,materialization=on,partial_match_rowid_merge=off,partial_match_table_scan=on'; +SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq; +SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq); + +set @@optimizer_switch='in_to_exists=on,materialization=off'; +SELECT outer_sq.*, (f1, f2) IN (select * from inner_sq) from outer_sq; +SELECT * from outer_sq where (f1, f2) NOT IN (select * from inner_sq); + +drop table outer_sq, inner_sq; + set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 9d618a8590e..740289ffcc8 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -776,6 +776,7 @@ eval EXPLAIN EXTENDED $query; eval $query; DROP TABLE t1, t2; + --echo # End of Bug#48213 --echo # @@ -1852,5 +1853,32 @@ EXECUTE st1; DROP TABLE t1,t2,t3; set optimizer_switch=@tmp878753; +--echo # +--echo # Bug #889750: semijoin=on + firstmatch=off + semijoin_with_cache=off +--echo # + +create table t1 (a int); +insert into t1 values (7), (1), (5), (3); +create table t2 (a int); +insert into t2 values (4), (1), (8), (3), (9), (2); + +set @tmp_otimizer_switch= @@optimizer_switch; +set optimizer_switch='semijoin=on'; +set optimizer_switch='firstmatch=off'; + +set optimizer_switch='semijoin_with_cache=on'; +explain +select * from t1 where t1.a in (select t2.a from t2); +select * from t1 where t1.a in (select t2.a from t2); + +set optimizer_switch='semijoin_with_cache=off'; +explain +select * from t1 where t1.a in (select t2.a from t2); +select * from t1 where t1.a in (select t2.a from t2); + +set optimizer_switch= @tmp_otimizer_switch; + +drop table t1,t2; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index ef4a40172e2..a72128bf5ed 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -4,7 +4,7 @@ # set @subselect_sj_mat_tmp= @@optimizer_switch; -set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on'); +set optimizer_switch=ifnull(@subselect_mat_test_optimizer_switch_value, 'semijoin=on,firstmatch=on,loosescan=on,semijoin_with_cache=on'); set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; set @optimizer_switch_local_default= @@optimizer_switch; diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index 3d17c7f5513..3b584ce2b38 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -500,3 +500,21 @@ WHERE t3.f2 ; DROP TABLE t1,t2,t3,t4,t5; +--echo # +--echo # BUG#884184: Wrong result with RIGHT JOIN + derived_merge +--echo # +CREATE TABLE t1 (a int(11), b varchar(1)) ; +INSERT IGNORE INTO t1 VALUES (0,'g'); + +CREATE TABLE t3 ( a varchar(1)) ; +INSERT IGNORE INTO t3 VALUES ('g'); + +CREATE TABLE t2 ( a int(11) NOT NULL, PRIMARY KEY (a)) ; +create view v1 as SELECT t1.* FROM t1 LEFT JOIN t2 ON ( t1.a = t2.a ) WHERE t2.a <> 0; + +SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); +EXPLAIN SELECT alias1.* FROM t3 LEFT JOIN v1 as alias1 ON ( t3.a = alias1.b ); + +drop view v1; +DROP TABLE t1,t2,t3; + |