1drop table if exists x1; 2drop table if exists x2; 3set @tmp_subselect_nulls=@@optimizer_switch; 4set optimizer_switch='semijoin=off'; 5create table x1(k int primary key, d1 int, d2 int); 6create table x2(k int primary key, d1 int, d2 int); 7insert into x1 values 8(10, 10, 10), 9(20, 20, 20), 10(21, 20, null), 11(30, null, 30), 12(40, 40, 40); 13insert into x2 values 14(10, 10, 10), 15(20, 20, 20), 16(21, 20, null), 17(30, null, 30); 18select * 19from x1 20where (d1, d2) in (select d1, d2 21from x2); 22k d1 d2 2310 10 10 2420 20 20 25select * 26from x1 27where (d1, d2) in (select d1, d2 28from x2) is true; 29k d1 d2 3010 10 10 3120 20 20 32select * 33from x1 34where (d1, d2) in (select d1, d2 35from x2) is false; 36k d1 d2 3740 40 40 38select * 39from x1 40where (d1, d2) in (select d1, d2 41from x2) is unknown; 42k d1 d2 4321 20 NULL 4430 NULL 30 45select * 46from x1 47where d1 in (select d1 48from x2 49where x1.d2=x2.d2); 50k d1 d2 5110 10 10 5220 20 20 53select * 54from x1 55where d1 in (select d1 56from x2 57where x1.d2=x2.d2) is true; 58k d1 d2 5910 10 10 6020 20 20 61select * 62from x1 63where d1 in (select d1 64from x2 65where x1.d2=x2.d2) is false; 66k d1 d2 6721 20 NULL 6840 40 40 69select * 70from x1 71where d1 in (select d1 72from x2 73where x1.d2=x2.d2) is unknown; 74k d1 d2 7530 NULL 30 76select * 77from x1 78where 1 in (select 1 79from x2 80where x1.d1=x2.d1 and x1.d2=x2.d2); 81k d1 d2 8210 10 10 8320 20 20 84select * 85from x1 86where 1 in (select 1 87from x2 88where x1.d1=x2.d1 and x1.d2=x2.d2) is true; 89k d1 d2 9010 10 10 9120 20 20 92select * 93from x1 94where 1 in (select 1 95from x2 96where x1.d1=x2.d1 and x1.d2=x2.d2) is false; 97k d1 d2 9821 20 NULL 9930 NULL 30 10040 40 40 101select * 102from x1 103where 1 in (select 1 104from x2 105where x1.d1=x2.d1 and x1.d2=x2.d2) is unknown; 106k d1 d2 107select * 108from x1 109where exists (select * 110from x2 111where x1.d1=x2.d1 and x1.d2=x2.d2); 112k d1 d2 11310 10 10 11420 20 20 115set optimizer_switch= @tmp_subselect_nulls; 116drop table x1; 117drop table x2; 118select (select 1, 2) in (select 3, 4); 119(select 1, 2) in (select 3, 4) 1200 121select (select NULL, NULL) in (select 3, 4); 122(select NULL, NULL) in (select 3, 4) 123NULL 124