2317 lines
120 KiB
Plaintext
2317 lines
120 KiB
Plaintext
set @old_opt_switch=@@optimizer_switch;
|
|
set optimizer_switch='subquery_materialization_cost_based=off';
|
|
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
|
drop table if exists t1, t2, t3, t1i, t2i, t3i;
|
|
drop view if exists v1, v2, v1m, v2m;
|
|
create table t1 (a1 char(8), a2 char(8)) charset utf8mb4;
|
|
create table t2 (b1 char(8), b2 char(8)) charset utf8mb4;
|
|
create table t3 (c1 char(8), c2 char(8)) charset utf8mb4;
|
|
insert into t1 values ('1 - 00', '2 - 00');
|
|
insert into t1 values ('1 - 01', '2 - 01');
|
|
insert into t1 values ('1 - 02', '2 - 02');
|
|
insert into t2 values ('1 - 01', '2 - 01');
|
|
insert into t2 values ('1 - 01', '2 - 01');
|
|
insert into t2 values ('1 - 02', '2 - 02');
|
|
insert into t2 values ('1 - 02', '2 - 02');
|
|
insert into t2 values ('1 - 03', '2 - 03');
|
|
insert into t3 values ('1 - 01', '2 - 01');
|
|
insert into t3 values ('1 - 02', '2 - 02');
|
|
insert into t3 values ('1 - 03', '2 - 03');
|
|
insert into t3 values ('1 - 04', '2 - 04');
|
|
create table t1i (a1 char(8), a2 char(8)) charset utf8mb4;
|
|
create table t2i (b1 char(8), b2 char(8)) charset utf8mb4;
|
|
create table t3i (c1 char(8), c2 char(8)) charset utf8mb4;
|
|
create index it1i1 on t1i (a1);
|
|
create index it1i2 on t1i (a2);
|
|
create index it1i3 on t1i (a1, a2);
|
|
create index it2i1 on t2i (b1);
|
|
create index it2i2 on t2i (b2);
|
|
create index it2i3 on t2i (b1, b2);
|
|
create index it3i1 on t3i (c1);
|
|
create index it3i2 on t3i (c2);
|
|
create index it3i3 on t3i (c1, c2);
|
|
insert into t1i select * from t1;
|
|
insert into t2i select * from t2;
|
|
insert into t3i select * from t3;
|
|
analyze table t1, t2, t3, t1i, t2i, t3i;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status OK
|
|
test.t1i analyze status OK
|
|
test.t2i analyze status OK
|
|
test.t3i analyze status OK
|
|
/******************************************************************************
|
|
* Simple tests.
|
|
******************************************************************************/
|
|
# non-indexed nullable fields
|
|
explain
|
|
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`b1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`))))
|
|
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`b1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`))))
|
|
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` where ((`test`.`t2`.`b1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))
|
|
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) or (`test`.`t2`.`b1` is null)) and ((<cache>(`test`.`t1`.`a2`) = min(`test`.`t2`.`b2`)) or (min(`test`.`t2`.`b2`) is null)) and <is_not_null_test>(`test`.`t2`.`b1`) and <is_not_null_test>(min(`test`.`t2`.`b2`)))))
|
|
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1i NULL index NULL it1i3 66 NULL 3 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i3 it2i1 33 func 2 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>(`test`.`t1i`.`a1`,<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i1 where (`test`.`t2i`.`b1` > '0'))))
|
|
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1i NULL index NULL it1i3 66 NULL 3 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i3 it2i1 33 func 2 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>(`test`.`t1i`.`a1`,<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i1 where (`test`.`t2i`.`b1` > '0'))))
|
|
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1i NULL index NULL it1i3 66 NULL 3 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i3 where (`test`.`t2i`.`b1` > '0'))))
|
|
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1i NULL index NULL it1i3 66 NULL 3 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i3 where (`test`.`t2i`.`b1` > '0'))))
|
|
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1i NULL index NULL it1i3 66 NULL 3 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2i NULL range it2i1,it2i3 it2i3 33 NULL 1 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` having (((<cache>(`test`.`t1i`.`a1`) = `test`.`t2i`.`b1`) or (`test`.`t2i`.`b1` is null)) and ((<cache>(`test`.`t1i`.`a2`) = min(`test`.`t2i`.`b2`)) or (min(`test`.`t2i`.`b2`) is null)) and <is_not_null_test>(`test`.`t2i`.`b1`) and <is_not_null_test>(min(`test`.`t2i`.`b2`)))))
|
|
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2i NULL range it2i1,it2i3 it2i3 33 NULL 2 100.00 Using index for group-by
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2i` group by `test`.`t2i`.`b1` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2i`.`b1`) or (`test`.`t2i`.`b1` is null)) and ((<cache>(`test`.`t1`.`a2`) = max(`test`.`t2i`.`b2`)) or (max(`test`.`t2i`.`b2`) is null)) and <is_not_null_test>(`test`.`t2i`.`b1`) and <is_not_null_test>(max(`test`.`t2i`.`b2`)))))
|
|
select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
|
|
execute st1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2i NULL range it2i1,it2i3 it2i3 33 NULL 2 100.00 Using index for group-by
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` group by `test`.`t2i`.`b1` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2i`.`b1`) or (`test`.`t2i`.`b1` is null)) and ((<cache>(`test`.`t1`.`a2`) = max(`test`.`t2i`.`b2`)) or (max(`test`.`t2i`.`b2`) is null)) and <is_not_null_test>(`test`.`t2i`.`b1`) and <is_not_null_test>(max(`test`.`t2i`.`b2`)))))
|
|
execute st1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2i NULL range it2i1,it2i3 it2i3 33 NULL 2 100.00 Using index for group-by
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select `test`.`t2i`.`b1`,max(`test`.`t2i`.`b2`) from `test`.`t2i` group by `test`.`t2i`.`b1` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2i`.`b1`) or (`test`.`t2i`.`b1` is null)) and ((<cache>(`test`.`t1`.`a2`) = max(`test`.`t2i`.`b2`)) or (max(`test`.`t2i`.`b2`) is null)) and <is_not_null_test>(`test`.`t2i`.`b1`) and <is_not_null_test>(max(`test`.`t2i`.`b2`)))))
|
|
prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
|
|
execute st2;
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
execute st2;
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2i NULL range it2i1,it2i3 it2i3 33 NULL 1 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` having (((<cache>(`test`.`t1`.`a1`) = `test`.`t2i`.`b1`) or (`test`.`t2i`.`b1` is null)) and ((<cache>(`test`.`t1`.`a2`) = min(`test`.`t2i`.`b2`)) or (min(`test`.`t2i`.`b2`) is null)) and <is_not_null_test>(`test`.`t2i`.`b1`) and <is_not_null_test>(min(`test`.`t2i`.`b2`)))))
|
|
select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
select * from t1 where (a1, a2) in (select b1, min(b2) from t2i limit 1,1);
|
|
ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
|
|
explain
|
|
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` where ((<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`))))
|
|
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1i NULL index NULL it1i3 66 NULL 3 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i3)))
|
|
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
/******************************************************************************
|
|
* Views, UNIONs, several levels of nesting.
|
|
******************************************************************************/
|
|
# materialize the result of subquery over temp-table view
|
|
create algorithm=merge view v1 as
|
|
select b1, c2 from t2, t3 where b2 > c2;
|
|
create algorithm=merge view v2 as
|
|
select b1, c2 from t2, t3 group by b2, c2;
|
|
Warnings:
|
|
Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm)
|
|
create algorithm=temptable view v1m as
|
|
select b1, c2 from t2, t3 where b2 > c2;
|
|
create algorithm=temptable view v2m as
|
|
select b1, c2 from t2, t3 group by b2, c2;
|
|
select * from v1 where (c2, b1) in (select c2, b1 from v2 where b1 is not null);
|
|
b1 c2
|
|
1 - 02 2 - 01
|
|
1 - 02 2 - 01
|
|
1 - 03 2 - 01
|
|
1 - 03 2 - 02
|
|
select * from v1 where (c2, b1) in (select distinct c2, b1 from v2 where b1 is not null);
|
|
b1 c2
|
|
1 - 02 2 - 01
|
|
1 - 02 2 - 01
|
|
1 - 03 2 - 01
|
|
1 - 03 2 - 02
|
|
select * from v1m where (c2, b1) in (select c2, b1 from v2m where b1 is not null);
|
|
b1 c2
|
|
1 - 02 2 - 01
|
|
1 - 02 2 - 01
|
|
1 - 03 2 - 01
|
|
1 - 03 2 - 02
|
|
select * from v1m where (c2, b1) in (select distinct c2, b1 from v2m where b1 is not null);
|
|
b1 c2
|
|
1 - 02 2 - 01
|
|
1 - 02 2 - 01
|
|
1 - 03 2 - 01
|
|
1 - 03 2 - 02
|
|
drop view v1, v2, v1m, v2m;
|
|
explain
|
|
select * from t1
|
|
where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and
|
|
(a1, a2) in (select c1, c2 from t3
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
3 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
4 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` where ((`test`.`t2`.`b1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#3 */ select 1,1 from `test`.`t3` where (<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3`.`c1`) in t2i on it2i3 where (`test`.`t2i`.`b2` > '0')))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3`.`c2`)))))
|
|
select * from t1
|
|
where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and
|
|
(a1, a2) in (select c1, c2 from t3
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1i
|
|
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
|
|
(a1, a2) in (select c1, c2 from t3i
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1i NULL index NULL it1i3 66 NULL 3 100.00 Using where; Using index
|
|
3 DEPENDENT SUBQUERY t3i NULL index_subquery it3i1,it3i2,it3i3 it3i3 66 func,func 2 100.00 Using where; Using index
|
|
4 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i3 where (`test`.`t2i`.`b1` > '0')))) and <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t3i on it3i3 where <in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3i`.`c1`) in t2i on it2i3 where (`test`.`t2i`.`b2` > '0'))))))))
|
|
select * from t1i
|
|
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
|
|
(a1, a2) in (select c1, c2 from t3i
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1
|
|
where (a1, a2) in (select b1, b2 from t2
|
|
where b2 in (select c2 from t3 where c2 LIKE '%02') or
|
|
b2 in (select c2 from t3 where c2 LIKE '%03')) and
|
|
(a1, a2) in (select c1, c2 from t3
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
5 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
6 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where
|
|
4 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
3 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select 1 from `test`.`t3` where ((`test`.`t3`.`c2` like '%02') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#4 */ select 1 from `test`.`t3` where ((`test`.`t3`.`c2` like '%03') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3`.`c2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#5 */ select 1,1 from `test`.`t3` where (<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3`.`c1`) in t2i on it2i3 where (`test`.`t2i`.`b2` > '0')))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3`.`c2`)))))
|
|
select * from t1
|
|
where (a1, a2) in (select b1, b2 from t2
|
|
where b2 in (select c2 from t3 where c2 LIKE '%02') or
|
|
b2 in (select c2 from t3 where c2 LIKE '%03')) and
|
|
(a1, a2) in (select c1, c2 from t3
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
|
|
a1 a2
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1
|
|
where (a1, a2) in (select b1, b2 from t2
|
|
where b2 in (select c2 from t3 t3a where c1 = a1) or
|
|
b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
|
|
(a1, a2) in (select c1, c2 from t3 t3c
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
5 DEPENDENT SUBQUERY t3c NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
6 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where
|
|
4 DEPENDENT SUBQUERY t3b NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
3 DEPENDENT SUBQUERY t3a NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select 1 from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#4 */ select 1 from `test`.`t3` `t3b` where ((`test`.`t3b`.`c2` like '%03') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3b`.`c2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#5 */ select 1,1 from `test`.`t3` `t3c` where (<in_optimizer>((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3c`.`c1`) in t2i on it2i3 where (`test`.`t2i`.`b2` > '0')))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3c`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3c`.`c2`)))))
|
|
select * from t1
|
|
where (a1, a2) in (select b1, b2 from t2
|
|
where b2 in (select c2 from t3 t3a where c1 = a1) or
|
|
b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
|
|
(a1, a2) in (select c1, c2 from t3 t3c
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
(select * from t1
|
|
where (a1, a2) in (select b1, b2 from t2
|
|
where b2 in (select c2 from t3 where c2 LIKE '%02') or
|
|
b2 in (select c2 from t3 where c2 LIKE '%03')
|
|
group by b1, b2) and
|
|
(a1, a2) in (select c1, c2 from t3
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))
|
|
UNION
|
|
(select * from t1i
|
|
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
|
|
(a1, a2) in (select c1, c2 from t3i
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
5 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
6 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where
|
|
4 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
3 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
7 UNION t1i NULL index NULL it1i3 66 NULL 3 100.00 Using where; Using index
|
|
9 DEPENDENT SUBQUERY t3i NULL index_subquery it3i1,it3i2,it3i3 it3i3 66 func,func 2 100.00 Using where; Using index
|
|
10 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using where; Using index
|
|
8 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using where; Using index
|
|
NULL UNION RESULT <union1,7> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select 1 from `test`.`t3` where ((`test`.`t3`.`c2` like '%02') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#4 */ select 1 from `test`.`t3` where ((`test`.`t3`.`c2` like '%03') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3`.`c2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#5 */ select 1,1 from `test`.`t3` where (<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3`.`c1`) in t2i on it2i3 where (`test`.`t2i`.`b2` > '0')))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3`.`c2`))))) union /* select#7 */ select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t2i on it2i3 where (`test`.`t2i`.`b1` > '0')))) and <in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),<exists>(<index_lookup>(<cache>(`test`.`t1i`.`a1`) in t3i on it3i3 where <in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3i`.`c1`) in t2i on it2i3 where (`test`.`t2i`.`b2` > '0'))))))))
|
|
(select * from t1
|
|
where (a1, a2) in (select b1, b2 from t2
|
|
where b2 in (select c2 from t3 where c2 LIKE '%02') or
|
|
b2 in (select c2 from t3 where c2 LIKE '%03')
|
|
group by b1, b2) and
|
|
(a1, a2) in (select c1, c2 from t3
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))
|
|
UNION
|
|
(select * from t1i
|
|
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
|
|
(a1, a2) in (select c1, c2 from t3i
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));
|
|
a1 a2
|
|
1 - 02 2 - 02
|
|
1 - 01 2 - 01
|
|
explain
|
|
select * from t1
|
|
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
|
|
(a1, a2) in (select c1, c2 from t3
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
4 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
5 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
3 DEPENDENT UNION t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where
|
|
NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union /* select#3 */ select 1,1 from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#4 */ select 1,1 from `test`.`t3` where (<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3`.`c1`) in t2i on it2i3 where (`test`.`t2i`.`b2` > '0')))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3`.`c2`)))))
|
|
select * from t1
|
|
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
|
|
(a1, a2) in (select c1, c2 from t3
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
explain
|
|
select * from t1, t3
|
|
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
|
|
(c1, c2) in (select c1, c2 from t3
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
|
|
a1 = c1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where; Using join buffer (Block Nested Loop)
|
|
4 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
5 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
3 DEPENDENT UNION t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where
|
|
NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,`test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t1`.`a1`) = `test`.`t1`.`a1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t1`.`a2`)) union /* select#3 */ select 1,1 from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(/* select#4 */ select 1,1 from `test`.`t3` where (<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3`.`c1`) in t2i on it2i3 where (`test`.`t2i`.`b2` > '0')))) and (<cache>(`test`.`t3`.`c1`) = `test`.`t3`.`c1`) and (<cache>(`test`.`t3`.`c2`) = `test`.`t3`.`c2`)))))
|
|
select * from t1, t3
|
|
where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and
|
|
(c1, c2) in (select c1, c2 from t3
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and
|
|
a1 = c1;
|
|
a1 a2 c1 c2
|
|
1 - 01 2 - 01 1 - 01 2 - 01
|
|
1 - 02 2 - 02 1 - 02 2 - 02
|
|
/******************************************************************************
|
|
* Negative tests, where materialization should not be applied.
|
|
******************************************************************************/
|
|
# UNION in a subquery
|
|
explain
|
|
select * from t3
|
|
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 4 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
3 DEPENDENT UNION t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where
|
|
NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where <in_optimizer>(`test`.`t3`.`c1`,<exists>(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a1` > '0') and (<cache>(`test`.`t3`.`c1`) = `test`.`t1`.`a1`)) union /* select#3 */ select 1 from `test`.`t2` where ((`test`.`t2`.`b1` < '9') and (<cache>(`test`.`t3`.`c1`) = `test`.`t2`.`b1`))))
|
|
select * from t3
|
|
where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');
|
|
c1 c2
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
1 - 03 2 - 03
|
|
explain
|
|
select * from t1
|
|
where (a1, a2) in (select b1, b2 from t2
|
|
where b2 in (select c2 from t3 t3a where c1 = a1) or
|
|
b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and
|
|
(a1, a2) in (select c1, c2 from t3 t3c
|
|
where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
5 DEPENDENT SUBQUERY t3c NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
6 DEPENDENT SUBQUERY t2i NULL index_subquery it2i1,it2i2,it2i3 it2i3 66 func,func 2 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 5 20.00 Using where
|
|
4 DEPENDENT SUBQUERY t3b NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
3 DEPENDENT SUBQUERY t3a NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
Warnings:
|
|
Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
|
|
Note 1276 Field or reference 'test.t1.a2' of SELECT #6 was resolved in SELECT #1
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` where ((<in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#3 */ select 1 from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and (<cache>(`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or <in_optimizer>(`test`.`t2`.`b2`,<exists>(/* select#4 */ select 1 from `test`.`t3` `t3b` where ((`test`.`t3b`.`c2` like '%03') and (<cache>(`test`.`t2`.`b2`) = `test`.`t3b`.`c2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#5 */ select 1,1 from `test`.`t3` `t3c` where (<in_optimizer>((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),<exists>(<index_lookup>(<cache>(`test`.`t3c`.`c1`) in t2i on it2i3 where ((`test`.`t2i`.`b2` > '0') or (`test`.`t2i`.`b2` = `test`.`t1`.`a2`))))) and (<cache>(`test`.`t1`.`a1`) = `test`.`t3c`.`c1`) and (<cache>(`test`.`t1`.`a2`) = `test`.`t3c`.`c2`)))))
|
|
explain
|
|
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having ((<cache>(`test`.`t1`.`a1`) = '1 - 01') and (<cache>(`test`.`t1`.`a2`) = '2 - 01') and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))
|
|
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
explain
|
|
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(/* select#2 */ select '1 - 01','2 - 01' having ((<cache>(`test`.`t1`.`a1`) = '1 - 01') and (<cache>(`test`.`t1`.`a2`) = '2 - 01') and <is_not_null_test>('1 - 01') and <is_not_null_test>('2 - 01'))))
|
|
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01' from dual);
|
|
a1 a2
|
|
1 - 01 2 - 01
|
|
/******************************************************************************
|
|
* Subqueries in other uncovered clauses.
|
|
******************************************************************************/
|
|
/* SELECT clause */
|
|
select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1;
|
|
((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL
|
|
0
|
|
0
|
|
0
|
|
/* GROUP BY clause */
|
|
create table columns (col int key);
|
|
insert into columns values (1), (2);
|
|
analyze table columns;
|
|
Table Op Msg_type Msg_text
|
|
test.columns analyze status OK
|
|
explain
|
|
select * from t1 group by (select col from columns limit 1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
|
|
2 SUBQUERY columns NULL index NULL PRIMARY 4 NULL 2 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by (/* select#2 */ select `test`.`columns`.`col` from `test`.`columns` limit 1)
|
|
select * from t1 group by (select col from columns limit 1);
|
|
a1 a2
|
|
1 - 00 2 - 00
|
|
explain
|
|
select * from t1 group by (a1 in (select col from columns));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using temporary
|
|
2 DEPENDENT SUBQUERY columns NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where; Using index; Full scan on NULL key
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` group by <in_optimizer>(`test`.`t1`.`a1`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a1`) in columns on PRIMARY where <if>(outer_field_is_not_null, (<cache>(`test`.`t1`.`a1`) = `test`.`columns`.`col`), true))))
|
|
select * from t1 group by (a1 in (select col from columns));
|
|
a1 a2
|
|
1 - 00 2 - 00
|
|
/* ORDER BY clause */
|
|
explain
|
|
select * from t1 order by (select col from columns limit 1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
|
|
2 SUBQUERY columns NULL index NULL PRIMARY 4 NULL 2 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` order by (/* select#2 */ select `test`.`columns`.`col` from `test`.`columns` limit 1)
|
|
select * from t1 order by (select col from columns limit 1);
|
|
a1 a2
|
|
1 - 00 2 - 00
|
|
1 - 01 2 - 01
|
|
1 - 02 2 - 02
|
|
/******************************************************************************
|
|
* Column types/sizes that affect materialization.
|
|
******************************************************************************/
|
|
# test for BIT fields
|
|
create table t1bit (a1 bit(3), a2 bit(3));
|
|
create table t2bit (b1 bit(3), b2 bit(3));
|
|
insert into t1bit values (b'000', b'100');
|
|
insert into t1bit values (b'001', b'101');
|
|
insert into t1bit values (b'010', b'110');
|
|
insert into t2bit values (b'001', b'101');
|
|
insert into t2bit values (b'010', b'110');
|
|
insert into t2bit values (b'110', b'111');
|
|
analyze table t1bit, t2bit;
|
|
Table Op Msg_type Msg_text
|
|
test.t1bit analyze status OK
|
|
test.t2bit analyze status OK
|
|
explain select bin(a1), bin(a2)
|
|
from t1bit
|
|
where (a1, a2) in (select b1, b2 from t2bit);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1bit NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2bit NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select conv(`test`.`t1bit`.`a1`,10,2) AS `bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from `test`.`t1bit` where <in_optimizer>((`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2bit` where ((<cache>(`test`.`t1bit`.`a1`) = `test`.`t2bit`.`b1`) and (<cache>(`test`.`t1bit`.`a2`) = `test`.`t2bit`.`b2`))))
|
|
select bin(a1), bin(a2)
|
|
from t1bit
|
|
where (a1, a2) in (select b1, b2 from t2bit);
|
|
bin(a1) bin(a2)
|
|
1 101
|
|
10 110
|
|
drop table t1bit, t2bit;
|
|
create table t1bb (a1 bit(3), a2 blob(3));
|
|
create table t2bb (b1 bit(3), b2 blob(3));
|
|
insert into t1bb values (b'000', '100');
|
|
insert into t1bb values (b'001', '101');
|
|
insert into t1bb values (b'010', '110');
|
|
insert into t2bb values (b'001', '101');
|
|
insert into t2bb values (b'010', '110');
|
|
insert into t2bb values (b'110', '111');
|
|
analyze table t1bb, t2bb;
|
|
Table Op Msg_type Msg_text
|
|
test.t1bb analyze status OK
|
|
test.t2bb analyze status OK
|
|
explain select bin(a1), a2
|
|
from t1bb
|
|
where (a1, a2) in (select b1, b2 from t2bb);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1bb NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2bb NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select conv(`test`.`t1bb`.`a1`,10,2) AS `bin(a1)`,`test`.`t1bb`.`a2` AS `a2` from `test`.`t1bb` where <in_optimizer>((`test`.`t1bb`.`a1`,`test`.`t1bb`.`a2`),<exists>(/* select#2 */ select 1,1 from `test`.`t2bb` where ((<cache>(`test`.`t1bb`.`a1`) = `test`.`t2bb`.`b1`) and (<cache>(`test`.`t1bb`.`a2`) = `test`.`t2bb`.`b2`))))
|
|
select bin(a1), a2
|
|
from t1bb
|
|
where (a1, a2) in (select b1, b2 from t2bb);
|
|
bin(a1) a2
|
|
1 101
|
|
10 110
|
|
drop table t1bb, t2bb;
|
|
drop table t1, t2, t3, t1i, t2i, t3i, columns;
|
|
/******************************************************************************
|
|
* Test the cache of the left operand of IN.
|
|
******************************************************************************/
|
|
# Test that default values of Cached_item are not used for comparison
|
|
create table t1 (s1 int);
|
|
create table t2 (s2 int);
|
|
insert into t1 values (5),(1),(0);
|
|
insert into t2 values (0), (1);
|
|
select s2 from t2 where s2 in (select s1 from t1);
|
|
s2
|
|
0
|
|
1
|
|
drop table t1, t2;
|
|
create table t1 (a int not null, b int not null);
|
|
create table t2 (c int not null, d int not null);
|
|
create table t3 (e int not null);
|
|
insert into t1 values (1,10);
|
|
insert into t1 values (1,20);
|
|
insert into t1 values (2,10);
|
|
insert into t1 values (2,20);
|
|
insert into t1 values (2,30);
|
|
insert into t1 values (3,20);
|
|
insert into t1 values (4,40);
|
|
insert into t2 values (2,10);
|
|
insert into t2 values (2,20);
|
|
insert into t2 values (2,40);
|
|
insert into t2 values (3,20);
|
|
insert into t2 values (4,10);
|
|
insert into t2 values (5,10);
|
|
insert into t3 values (10);
|
|
insert into t3 values (10);
|
|
insert into t3 values (20);
|
|
insert into t3 values (30);
|
|
analyze table t1, t2, t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status OK
|
|
explain
|
|
select a from t1 where a in (select c from t2 where d >= 20);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 6 16.67 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`d` >= 20) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))
|
|
select a from t1 where a in (select c from t2 where d >= 20);
|
|
a
|
|
2
|
|
2
|
|
2
|
|
3
|
|
create index it1a on t1(a);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
explain
|
|
select a from t1 where a in (select c from t2 where d >= 20);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL index NULL it1a 4 NULL # 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL # 16.67 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`d` >= 20) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))
|
|
select a from t1 where a in (select c from t2 where d >= 20);
|
|
a
|
|
2
|
|
2
|
|
2
|
|
3
|
|
insert into t2 values (1,10);
|
|
analyze table t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status OK
|
|
explain
|
|
select a from t1 where a in (select c from t2 where d >= 20);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL index NULL it1a 4 NULL # 100.00 Using where; Using index
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL # 14.29 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`d` >= 20) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))
|
|
select a from t1 where a in (select c from t2 where d >= 20);
|
|
a
|
|
2
|
|
2
|
|
2
|
|
3
|
|
drop table t1, t2, t3;
|
|
create table t2 (a int, b int, key(a), key(b));
|
|
insert into t2 values (3,3),(3,3),(3,3);
|
|
select 1 from t2 where
|
|
t2.a > 1
|
|
or
|
|
t2.a = 3 and not t2.a not in (select t2.b from t2);
|
|
1
|
|
1
|
|
1
|
|
1
|
|
drop table t2;
|
|
create table t1 (a1 int key);
|
|
create table t2 (b1 int);
|
|
insert into t1 values (5);
|
|
explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select min(`test`.`t1`.`a1`) AS `min(a1)` from `test`.`t1` where false
|
|
select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
|
|
min(a1)
|
|
NULL
|
|
explain select min(a1) from t1 where 7 in (select b1 from t2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select min(`test`.`t1`.`a1`) AS `min(a1)` from `test`.`t1` where false
|
|
select min(a1) from t1 where 7 in (select b1 from t2);
|
|
min(a1)
|
|
NULL
|
|
drop table t1,t2;
|
|
create table t1 (a char(2), b varchar(10));
|
|
insert into t1 values ('a', 'aaa');
|
|
insert into t1 values ('aa', 'aaaa');
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
explain select a,b from t1 where b in (select a from t1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`b`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t1`.`b`) = `test`.`t1`.`a`)))
|
|
select a,b from t1 where b in (select a from t1);
|
|
a b
|
|
prepare st1 from "select a,b from t1 where b in (select a from t1)";
|
|
execute st1;
|
|
a b
|
|
execute st1;
|
|
a b
|
|
drop table t1;
|
|
CREATE TABLE t1 (a varchar(5), b varchar(10)) charset utf8mb4;
|
|
INSERT INTO t1 VALUES
|
|
('AAA', 5), ('BBB', 4), ('BBB', 1), ('CCC', 2),
|
|
('CCC', 7), ('AAA', 2), ('AAA', 4), ('BBB', 3), ('AAA', 8);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
|
|
a b
|
|
BBB 4
|
|
CCC 7
|
|
AAA 8
|
|
EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 9 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 9 100.00 Using temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select 1,1 from `test`.`t1` group by `test`.`t1`.`a` having (((<cache>(`test`.`t1`.`a`) = `test`.`t1`.`a`) or (`test`.`t1`.`a` is null)) and ((<cache>(`test`.`t1`.`b`) = max(`test`.`t1`.`b`)) or (max(`test`.`t1`.`b`) is null)) and <is_not_null_test>(`test`.`t1`.`a`) and <is_not_null_test>(max(`test`.`t1`.`b`)))))
|
|
ALTER TABLE t1 ADD INDEX(a);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
|
|
a b
|
|
BBB 4
|
|
CCC 7
|
|
AAA 8
|
|
SHOW SESSION STATUS LIKE 'Sort_scan%';
|
|
Variable_name Value
|
|
Sort_scan 0
|
|
EXPLAIN SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL # 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t1 NULL index a a 23 NULL # 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),<exists>(/* select#2 */ select 1,1 from `test`.`t1` group by `test`.`t1`.`a` having (((<cache>(`test`.`t1`.`a`) = `test`.`t1`.`a`) or (`test`.`t1`.`a` is null)) and ((<cache>(`test`.`t1`.`b`) = max(`test`.`t1`.`b`)) or (max(`test`.`t1`.`b`) is null)) and <is_not_null_test>(`test`.`t1`.`a`) and <is_not_null_test>(max(`test`.`t1`.`b`)))))
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1),(2);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
EXPLAIN SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 GROUP BY a);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
2 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where true
|
|
EXPLAIN SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
2 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where false
|
|
DROP TABLE t1;
|
|
#
|
|
# BUG#49630: Segfault in select_describe() with double
|
|
# nested subquery and materialization
|
|
#
|
|
CREATE TABLE t1 (t1i int);
|
|
CREATE TABLE t2 (t2i int);
|
|
CREATE TABLE t3 (t3i int);
|
|
CREATE TABLE t4 (t4i int);
|
|
INSERT INTO t1 VALUES (1);
|
|
INSERT INTO t2 VALUES (1),(2);
|
|
INSERT INTO t3 VALUES (1),(2);
|
|
INSERT INTO t4 VALUES (1),(2);
|
|
ANALYZE TABLE t1, t2, t3, t4;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status OK
|
|
test.t4 analyze status OK
|
|
|
|
EXPLAIN
|
|
SELECT t1i FROM t1 JOIN t4 ON t1i=t4i WHERE (t1i) IN (SELECT t2i FROM t2 WHERE (t2i) IN ( SELECT t3i FROM t3 GROUP BY t3i));
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 PRIMARY t4 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
3 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`t1i` AS `t1i` from `test`.`t1` join `test`.`t4` where ((`test`.`t4`.`t4i` = `test`.`t1`.`t1i`) and <in_optimizer>(`test`.`t1`.`t1i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`t2i`,<exists>(/* select#3 */ select 1 from `test`.`t3` where (<cache>(`test`.`t2`.`t2i`) = `test`.`t3`.`t3i`))) and (<cache>(`test`.`t1`.`t1i`) = `test`.`t2`.`t2i`)))))
|
|
DROP TABLE t1,t2,t3,t4;
|
|
#
|
|
# BUG#46680 - Assertion failed in file item_subselect.cc,
|
|
# line 305 crashing on HAVING subquery
|
|
#
|
|
# Create tables
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk INT,
|
|
v VARCHAR(1) DEFAULT NULL,
|
|
PRIMARY KEY(pk)
|
|
) charset latin1;
|
|
CREATE TABLE t2 LIKE t1;
|
|
CREATE TABLE t3 LIKE t1;
|
|
CREATE TABLE empty1 (a int);
|
|
INSERT INTO t1 VALUES (1,'c'),(2,NULL);
|
|
INSERT INTO t2 VALUES (3,'m'),(4,NULL);
|
|
INSERT INTO t3 VALUES (1,'n');
|
|
ANALYZE TABLE t1, t2, t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status OK
|
|
|
|
#
|
|
# 1) Test that subquery materialization is setup for query with
|
|
# premature optimize() exit due to "Impossible WHERE"
|
|
#
|
|
SELECT MIN(t2.pk)
|
|
FROM t2 JOIN t1 ON t1.pk=t2.pk
|
|
WHERE 'j'
|
|
HAVING ('m') IN (
|
|
SELECT v
|
|
FROM t2);
|
|
MIN(t2.pk)
|
|
NULL
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'j'
|
|
|
|
EXPLAIN
|
|
SELECT MIN(t2.pk)
|
|
FROM t2 JOIN t1 ON t1.pk=t2.pk
|
|
WHERE 'j'
|
|
HAVING ('m') IN (
|
|
SELECT v
|
|
FROM t2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: 'j'
|
|
Note 1003 /* select#1 */ select min(`test`.`t2`.`pk`) AS `MIN(t2.pk)` from `test`.`t2` join `test`.`t1` where false having <in_optimizer>('m',<exists>(/* select#2 */ select 1 from `test`.`t2` where (`test`.`t2`.`v` = <cache>(convert(<cache>('m') using latin1)))))
|
|
|
|
#
|
|
# 2) Test that subquery materialization is setup for query with
|
|
# premature optimize() exit due to "No matching min/max row"
|
|
#
|
|
SELECT MIN(t2.pk)
|
|
FROM t2
|
|
WHERE t2.pk>10
|
|
HAVING ('m') IN (
|
|
SELECT v
|
|
FROM t2);
|
|
MIN(t2.pk)
|
|
NULL
|
|
|
|
EXPLAIN
|
|
SELECT MIN(t2.pk)
|
|
FROM t2
|
|
WHERE t2.pk>10
|
|
HAVING ('m') IN (
|
|
SELECT v
|
|
FROM t2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No matching min/max row
|
|
2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select min(`test`.`t2`.`pk`) AS `MIN(t2.pk)` from `test`.`t2` where (`test`.`t2`.`pk` > 10) having true
|
|
|
|
#
|
|
# 3) Test that subquery materialization is setup for query with
|
|
# premature optimize() exit due to "Select tables optimized away"
|
|
#
|
|
SELECT MIN(pk)
|
|
FROM t1
|
|
WHERE pk=NULL
|
|
HAVING ('m') IN (
|
|
SELECT v
|
|
FROM t2);
|
|
MIN(pk)
|
|
NULL
|
|
|
|
EXPLAIN
|
|
SELECT MIN(pk)
|
|
FROM t1
|
|
WHERE pk=NULL
|
|
HAVING ('m') IN (
|
|
SELECT v
|
|
FROM t2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table
|
|
2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select min(`test`.`t1`.`pk`) AS `MIN(pk)` from `test`.`t1` where (`test`.`t1`.`pk` = NULL) having true
|
|
|
|
#
|
|
# 4) Test that subquery materialization is setup for query with
|
|
# premature optimize() exit due to "No matching row in const table"
|
|
#
|
|
|
|
SELECT MIN(a)
|
|
FROM (SELECT a FROM empty1) tt
|
|
HAVING ('m') IN (
|
|
SELECT v
|
|
FROM t2);
|
|
MIN(a)
|
|
NULL
|
|
|
|
EXPLAIN
|
|
SELECT MIN(a)
|
|
FROM (SELECT a FROM empty1) tt
|
|
HAVING ('m') IN (
|
|
SELECT v
|
|
FROM t2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY empty1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
3 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select min(`test`.`empty1`.`a`) AS `MIN(a)` from `test`.`empty1` having true
|
|
|
|
#
|
|
# 5) Test that subquery materialization is setup for query with
|
|
# premature optimize() exit due to "Impossible WHERE noticed
|
|
# after reading const tables"
|
|
#
|
|
SELECT min(t1.pk)
|
|
FROM t1
|
|
WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
|
|
HAVING ('m') IN (
|
|
SELECT v
|
|
FROM t2);
|
|
min(t1.pk)
|
|
NULL
|
|
|
|
EXPLAIN
|
|
SELECT min(t1.pk)
|
|
FROM t1
|
|
WHERE t1.pk IN (SELECT 1 from t3 where pk>10)
|
|
HAVING ('m') IN (
|
|
SELECT v
|
|
FROM t2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index
|
|
3 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
2 DEPENDENT SUBQUERY t3 NULL index PRIMARY PRIMARY 4 NULL 1 100.00 Using where; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select min(`test`.`t1`.`pk`) AS `min(t1.pk)` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`pk`,<exists>(/* select#2 */ select 1 from `test`.`t3` where ((`test`.`t3`.`pk` > 10) and (<cache>(`test`.`t1`.`pk`) = 1)))) having true
|
|
#
|
|
# Cleanup for BUG#46680
|
|
#
|
|
DROP TABLE IF EXISTS t1,t2,t3,empty1;
|
|
#
|
|
# BUG#52344 - Subquery materialization:
|
|
# Assertion if subquery in on-clause of outer join
|
|
#
|
|
CREATE TABLE t1 (i INTEGER);
|
|
INSERT INTO t1 VALUES (10);
|
|
CREATE TABLE t2 (j INTEGER);
|
|
INSERT INTO t2 VALUES (5);
|
|
CREATE TABLE t3 (k INTEGER);
|
|
EXPLAIN
|
|
SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i`,`test`.`t2`.`j` AS `j` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(`test`.`t2`.`j`,<exists>(/* select#2 */ select 1 from `test`.`t3` where (<cache>(`test`.`t2`.`j`) = `test`.`t3`.`k`)))) where true
|
|
SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
|
|
i j
|
|
10 NULL
|
|
EXPLAIN
|
|
SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i`,`test`.`t2`.`j` AS `j` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(`test`.`t2`.`j`,<exists>(/* select#2 */ select 1 from `test`.`t3` having (<cache>(`test`.`t2`.`j`) = <ref_null_helper>(max(`test`.`t3`.`k`)))))) where true
|
|
SELECT i, j FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
|
|
i j
|
|
10 NULL
|
|
DROP TABLE t1, t2, t3;
|
|
# End BUG#52344
|
|
CREATE TABLE t1 (
|
|
pk INTEGER AUTO_INCREMENT,
|
|
col_int_nokey INTEGER,
|
|
col_int_key INTEGER,
|
|
col_varchar_key VARCHAR(1),
|
|
PRIMARY KEY (pk),
|
|
KEY (col_int_key),
|
|
KEY (col_varchar_key, col_int_key)
|
|
)
|
|
;
|
|
INSERT INTO t1 (
|
|
col_int_key, col_int_nokey, col_varchar_key
|
|
)
|
|
VALUES
|
|
(2, NULL, 'w'),
|
|
(9, 7, 'm'),
|
|
(3, 9, 'm'),
|
|
(9, 7, 'k'),
|
|
(NULL, 4, 'r'),
|
|
(9, 2, 't'),
|
|
(3, 6, 'j'),
|
|
(8, 8, 'u'),
|
|
(8, NULL, 'h'),
|
|
(53, 5, 'o'),
|
|
(0, NULL, NULL),
|
|
(5, 6, 'k'),
|
|
(166, 188, 'e'),
|
|
(3, 2, 'n'),
|
|
(0, 1, 't'),
|
|
(1, 1, 'c'),
|
|
(9, 0, 'm'),
|
|
(5, 9, 'y'),
|
|
(6, NULL, 'f'),
|
|
(2, 4, 'd')
|
|
;
|
|
SELECT table2.col_varchar_key AS field1,
|
|
table2.col_int_nokey AS field2
|
|
FROM ( t1 AS table1 LEFT OUTER JOIN t1 AS table2
|
|
ON (table2.col_varchar_key = table1.col_varchar_key ) )
|
|
WHERE table1.pk = 6
|
|
HAVING ( field2 ) IN
|
|
( SELECT SUBQUERY2_t2.col_int_nokey AS SUBQUERY2_field2
|
|
FROM ( t1 AS SUBQUERY2_t1 JOIN t1 AS SUBQUERY2_t2
|
|
ON (SUBQUERY2_t2.col_varchar_key = SUBQUERY2_t1.col_varchar_key ) ) )
|
|
ORDER BY field2
|
|
;
|
|
field1 field2
|
|
t 1
|
|
t 2
|
|
drop table t1;
|
|
#
|
|
# BUG#53103: MTR test ps crashes in optimize_cond()
|
|
# when running with --debug
|
|
#
|
|
CREATE TABLE t1(track varchar(15));
|
|
INSERT INTO t1 VALUES ('CAD'), ('CAD');
|
|
PREPARE STMT FROM
|
|
"SELECT 1 FROM t1
|
|
WHERE
|
|
track IN (SELECT track FROM t1
|
|
GROUP BY track
|
|
HAVING track>='CAD')";
|
|
EXECUTE STMT ;
|
|
1
|
|
1
|
|
1
|
|
EXECUTE STMT ;
|
|
1
|
|
1
|
|
1
|
|
DEALLOCATE PREPARE STMT;
|
|
DROP TABLE t1;
|
|
# End of BUG#53103
|
|
#
|
|
# BUG#54511 - Assertion failed: cache != 0L in file
|
|
# sql_select.cc::sub_select_cache on HAVING
|
|
#
|
|
CREATE TABLE t1 (i int(11));
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
CREATE TABLE t2 (c char(1));
|
|
CREATE TABLE t3 (c char(1));
|
|
INSERT INTO t1 VALUES (1), (2);
|
|
INSERT INTO t2 VALUES ('a'), ('b');
|
|
INSERT INTO t3 VALUES ('x'), ('y');
|
|
SELECT COUNT( i ),i
|
|
FROM t1
|
|
HAVING ('c')
|
|
IN (SELECT t2.c FROM (t2 JOIN t3));
|
|
COUNT( i ) i
|
|
DROP TABLE t1,t2,t3;
|
|
# End BUG#54511
|
|
#
|
|
# BUG#56367 - Assertion exec_method != EXEC_MATERIALIZATION...
|
|
# on subquery in FROM
|
|
#
|
|
CREATE TABLE t1 (a INTEGER);
|
|
CREATE TABLE t2 (b INTEGER);
|
|
INSERT INTO t2 VALUES (1);
|
|
explain SELECT a FROM (
|
|
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1)
|
|
) table1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
3 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(((`test`.`t1`.`a` > 3) or <in_optimizer>(`test`.`t2`.`b`,<exists>(/* select#3 */ select 1 from `test`.`t1` where (<cache>(`test`.`t2`.`b`) = `test`.`t1`.`a`))))) where true
|
|
SELECT a FROM (
|
|
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1)
|
|
) table1;
|
|
a
|
|
DROP TABLE t1, t2;
|
|
# End BUG#56367
|
|
#
|
|
# Bug#59833 - materialization=on/off leads to different result set
|
|
# when using IN
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk int NOT NULL,
|
|
f1 int DEFAULT NULL,
|
|
PRIMARY KEY (pk)
|
|
);
|
|
CREATE TABLE t2 (
|
|
pk int NOT NULL,
|
|
f1 int DEFAULT NULL,
|
|
PRIMARY KEY (pk)
|
|
);
|
|
INSERT INTO t1 VALUES (10,0);
|
|
INSERT INTO t2 VALUES (10,0),(11,0);
|
|
ANALYZE TABLE t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
explain SELECT * FROM t1 JOIN t2 USING (f1)
|
|
WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY t1 NULL unique_subquery PRIMARY PRIMARY 4 func 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`pk` AS `pk`,`test`.`t2`.`pk` AS `pk` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`f1` = `test`.`t1`.`f1`) and <in_optimizer>(`test`.`t1`.`f1`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`f1`) in t1 on PRIMARY))))
|
|
SELECT * FROM t1 JOIN t2 USING (f1)
|
|
WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1);
|
|
f1 pk pk
|
|
DROP TABLE t1, t2;
|
|
# End Bug#59833
|
|
#
|
|
# Bug#11852644 - CRASH IN ITEM_REF::SAVE_IN_FIELD ON SELECT DISTINCT
|
|
#
|
|
CREATE TABLE t1 (
|
|
col_varchar_key varchar(1) DEFAULT NULL,
|
|
col_varchar_nokey varchar(1) DEFAULT NULL,
|
|
KEY col_varchar_key (col_varchar_key))
|
|
;
|
|
INSERT INTO t1 VALUES
|
|
('v','v'),('r','r');
|
|
CREATE TABLE t2 (
|
|
col_varchar_key varchar(1) DEFAULT NULL,
|
|
col_varchar_nokey varchar(1) DEFAULT NULL,
|
|
KEY col_varchar_key(col_varchar_key))
|
|
;
|
|
INSERT INTO t2 VALUES
|
|
('r','r'),('c','c');
|
|
CREATE VIEW v3 AS SELECT * FROM t2;
|
|
SELECT DISTINCT alias2.col_varchar_key
|
|
FROM t1 AS alias1 JOIN v3 AS alias2
|
|
ON alias2.col_varchar_key = alias1.col_varchar_key
|
|
HAVING col_varchar_key IN (SELECT col_varchar_nokey FROM t2)
|
|
;
|
|
col_varchar_key
|
|
r
|
|
DROP TABLE t1, t2;
|
|
DROP VIEW v3;
|
|
# End Bug#11852644
|
|
|
|
# Bug#12668294 - GROUP BY ON EMPTY RESULT GIVES EMPTY ROW
|
|
# INSTEAD OF NULL WHEN MATERIALIZATION ON
|
|
|
|
CREATE TABLE t1 (col_int_nokey INT) ENGINE=MEMORY;
|
|
CREATE TABLE t2 (col_int_nokey INT) ENGINE=MEMORY;
|
|
INSERT INTO t2 VALUES (8),(7);
|
|
CREATE TABLE t3 (col_int_nokey INT) ENGINE=MEMORY;
|
|
INSERT INTO t3 VALUES (7);
|
|
SELECT MIN(t3.col_int_nokey),t1.col_int_nokey AS field3
|
|
FROM t3
|
|
LEFT JOIN t1
|
|
ON t1.col_int_nokey
|
|
WHERE (194, 200) IN (
|
|
SELECT SQ4_alias1.col_int_nokey,
|
|
SQ4_alias2.col_int_nokey
|
|
FROM t2 AS SQ4_alias1
|
|
JOIN
|
|
t2 AS SQ4_alias2
|
|
ON SQ4_alias2.col_int_nokey = 5
|
|
)
|
|
GROUP BY field3 ;
|
|
MIN(t3.col_int_nokey) field3
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
DROP TABLE t3;
|
|
#
|
|
# Bug#13419028 - SUBQUERY MATERIALIZATION NOT USED IN CREATE
|
|
# SELECT
|
|
#
|
|
CREATE TABLE t1(a int);
|
|
INSERT INTO t1 values(1),(2);
|
|
CREATE TABLE t2(a int);
|
|
INSERT INTO t2 values(1),(2);
|
|
ANALYZE TABLE t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`)))
|
|
FLUSH STATUS;
|
|
SELECT * FROM t1 WHERE a IN (SELECT * FROM t2);
|
|
a
|
|
1
|
|
2
|
|
CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2);
|
|
SELECT * FROM t3;
|
|
a
|
|
1
|
|
2
|
|
SHOW STATUS LIKE "CREATED_TMP_TABLES";
|
|
Variable_name Value
|
|
Created_tmp_tables 0
|
|
DROP TABLE t1,t2,t3;
|
|
#
|
|
# Bug#13552968: Extra row with materialization on join + subquery in
|
|
#
|
|
CREATE TABLE t1 (
|
|
col_varchar_nokey varchar(1) NOT NULL
|
|
);
|
|
INSERT INTO t1 VALUES ('b');
|
|
CREATE TABLE t2 (
|
|
col_varchar_nokey varchar(1) NOT NULL
|
|
);
|
|
INSERT INTO t2 VALUES ('k');
|
|
CREATE TABLE t3 (
|
|
col_varchar_nokey varchar(1) NOT NULL
|
|
);
|
|
explain SELECT STRAIGHT_JOIN *
|
|
FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey
|
|
FROM t3);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select straight_join `test`.`t1`.`col_varchar_nokey` AS `col_varchar_nokey`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(`test`.`t1`.`col_varchar_nokey`,<exists>(/* select#2 */ select 1 from `test`.`t3` where (<cache>(`test`.`t1`.`col_varchar_nokey`) = `test`.`t3`.`col_varchar_nokey`)))) where true
|
|
SELECT STRAIGHT_JOIN *
|
|
FROM t1 LEFT JOIN t2 ON t1.col_varchar_nokey IN (SELECT col_varchar_nokey
|
|
FROM t3);
|
|
col_varchar_nokey col_varchar_nokey
|
|
b NULL
|
|
DROP TABLE t1, t2, t3;
|
|
# End of test for bug#13552968
|
|
#
|
|
# Bug#13591383: Assertion !(*tab->on_expr_ref && .. && is_expensive())
|
|
# in join_read_const_table()
|
|
#
|
|
CREATE TABLE t1 (v INTEGER);
|
|
INSERT INTO t1 VALUES(1);
|
|
CREATE TABLE t2 (v INTEGER);
|
|
SELECT *
|
|
FROM t1 LEFT JOIN t2
|
|
ON t2.v IN(SELECT v FROM t1);
|
|
v v
|
|
1 NULL
|
|
DROP TABLE t1, t2;
|
|
# End of test for bug#13591383.
|
|
#
|
|
# Bug#13607423: Assertion !(*tab->on_expr_ref->is_expensive())
|
|
# in join_read_const_table()
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk int NOT NULL,
|
|
col_int_nokey int DEFAULT NULL,
|
|
col_int_key int DEFAULT NULL,
|
|
PRIMARY KEY (pk),
|
|
KEY col_int_key (col_int_key)
|
|
);
|
|
INSERT INTO t1 VALUES (1,2,4), (2,150,62);
|
|
CREATE TABLE t2 (
|
|
pk int NOT NULL,
|
|
col_int_key int DEFAULT NULL,
|
|
PRIMARY KEY (pk)
|
|
);
|
|
INSERT INTO t2 VALUES (1,7);
|
|
ANALYZE TABLE t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
explain SELECT table1.pk, table2.pk
|
|
FROM t2 AS table1 LEFT JOIN t2 AS table2
|
|
ON table2.pk = table1.pk AND
|
|
table2.col_int_key IN
|
|
(SELECT col_int_key
|
|
FROM t1 AS innr
|
|
WHERE innr.col_int_nokey > innr.col_int_nokey
|
|
GROUP BY col_int_key
|
|
HAVING COUNT(*) > 0
|
|
);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY table1 NULL index NULL PRIMARY 4 NULL 1 100.00 Using index
|
|
1 PRIMARY table2 NULL eq_ref PRIMARY PRIMARY 4 test.table1.pk 1 100.00 Using where
|
|
2 DEPENDENT SUBQUERY innr NULL index col_int_key col_int_key 5 NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`table1`.`pk` AS `pk`,`test`.`table2`.`pk` AS `pk` from `test`.`t2` `table1` left join `test`.`t2` `table2` on(((`test`.`table2`.`pk` = `test`.`table1`.`pk`) and <in_optimizer>(`test`.`table2`.`col_int_key`,<exists>(/* select#2 */ select 1 from `test`.`t1` `innr` where (`test`.`innr`.`col_int_nokey` > `test`.`innr`.`col_int_nokey`) group by `test`.`innr`.`col_int_key` having ((count(0) > 0) and (<cache>(`test`.`table2`.`col_int_key`) = <ref_null_helper>(`test`.`innr`.`col_int_key`))))))) where true
|
|
FLUSH STATUS;
|
|
SELECT table1.pk, table2.pk
|
|
FROM t2 AS table1 LEFT JOIN t2 AS table2
|
|
ON table2.pk = table1.pk AND
|
|
table2.col_int_key IN
|
|
(SELECT col_int_key
|
|
FROM t1 AS innr
|
|
WHERE innr.col_int_nokey > innr.col_int_nokey
|
|
GROUP BY col_int_key
|
|
HAVING COUNT(*) > 0
|
|
);
|
|
pk pk
|
|
1 NULL
|
|
SHOW SESSION STATUS LIKE 'Sort_scan%';
|
|
Variable_name Value
|
|
Sort_scan 0
|
|
DROP TABLE t1, t2;
|
|
# End of test for bug#13607423.
|
|
|
|
Test of WL#6094 "Allow subquery materialization in NOT IN if all
|
|
columns are not nullable"
|
|
|
|
create table t1(a int not null);
|
|
create table t2(a int not null);
|
|
insert into t1 values(1),(2);
|
|
insert into t2 values(1),(2);
|
|
analyze table t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
Test in SELECT list
|
|
|
|
cols not nullable => subq materialization
|
|
explain select a, (a,a) in (select a,a from t2) from t1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` where ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`)) having (<is_not_null_test>(`test`.`t2`.`a`) and <is_not_null_test>(`test`.`t2`.`a`)))) AS `(a,a) in (select a,a from t2)` from `test`.`t1`
|
|
select a, (a,a) in (select a,a from t2) from t1;
|
|
a (a,a) in (select a,a from t2)
|
|
1 1
|
|
2 1
|
|
|
|
cols not nullable => subq materialization
|
|
explain select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3)
|
|
from t1 join t2 on t1.a+t2.a=1000;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where ((<cache>(`test`.`t1`.`a`) = `test`.`t3`.`a`) and (<cache>(`test`.`t1`.`a`) = `test`.`t3`.`a`)) having (<is_not_null_test>(`test`.`t3`.`a`) and <is_not_null_test>(`test`.`t3`.`a`)))) AS `(t1.a,t1.a) in (select a,a from t2 as t3)` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000)
|
|
select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3)
|
|
from t1 join t2 on t1.a+t2.a=1000;
|
|
a a (t1.a,t1.a) in (select a,a from t2 as t3)
|
|
|
|
t2.a is not nullable, but in the query it may appear as NULL
|
|
as it's in an outer join. So, no materialization.
|
|
explain select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3)
|
|
from t1 left join t2 on t1.a+t2.a=1000;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where (<if>(outer_field_is_not_null, ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) or <cache>((`test`.`t3`.`a` is null))), true) and <if>(outer_field_is_not_null, ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) or <cache>((`test`.`t3`.`a` is null))), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true)))) AS `(t2.a,t2.a) in (select a,a from t2 as t3)` from `test`.`t1` left join `test`.`t2` on(((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000)) where true
|
|
select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3)
|
|
from t1 left join t2 on t1.a+t2.a=1000;
|
|
a a (t2.a,t2.a) in (select a,a from t2 as t3)
|
|
1 NULL NULL
|
|
2 NULL NULL
|
|
|
|
alter table t2 modify a int;
|
|
two nullable inner cols => no subq materialization
|
|
explain select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3)
|
|
from t1 join t2 on t1.a+t2.a=1000;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 56.25 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where (((<cache>(`test`.`t1`.`a`) = `test`.`t3`.`a`) or (`test`.`t3`.`a` is null)) and ((<cache>(`test`.`t1`.`a`) = `test`.`t3`.`a`) or (`test`.`t3`.`a` is null))) having (<is_not_null_test>(`test`.`t3`.`a`) and <is_not_null_test>(`test`.`t3`.`a`)))) AS `(t1.a,t1.a) in (select a,a from t2 as t3)` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000)
|
|
select t1.a, t2.a, (t1.a,t1.a) in (select a,a from t2 as t3)
|
|
from t1 join t2 on t1.a+t2.a=1000;
|
|
a a (t1.a,t1.a) in (select a,a from t2 as t3)
|
|
alter table t2 modify a int not null;
|
|
analyze table t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status OK
|
|
|
|
Test in WHERE
|
|
|
|
top-level => subq materialization. With one exception: if
|
|
semijoin is enabled in @@optimizer_switch, semijoin is chosen,
|
|
then rejected (due to outer join), and in that case, the
|
|
fallback is IN->EXISTS, subq-materialization is not tried...
|
|
explain select t1.a, t2.a
|
|
from t1 join t2 on t1.a+t2.a=3
|
|
where (t2.a,t2.a) in (select a,a from t2 as t3);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) and (<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`)))) and ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 3))
|
|
select t1.a, t2.a
|
|
from t1 join t2 on t1.a+t2.a=3
|
|
where (t2.a,t2.a) in (select a,a from t2 as t3);
|
|
a a
|
|
2 1
|
|
1 2
|
|
|
|
cols not nullable => subq materialization
|
|
explain select t1.a, t2.a
|
|
from t1 join t2 on t1.a+t2.a=3
|
|
where (t2.a,t2.a) not in (select a,a from t2 as t3);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where (<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) and (<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`)) having (<is_not_null_test>(`test`.`t3`.`a`) and <is_not_null_test>(`test`.`t3`.`a`))) is false) and ((`test`.`t1`.`a` + `test`.`t2`.`a`) = 3))
|
|
select t1.a, t2.a
|
|
from t1 join t2 on t1.a+t2.a=3
|
|
where (t2.a,t2.a) not in (select a,a from t2 as t3);
|
|
a a
|
|
drop table t1,t2;
|
|
|
|
Test of WL6095 "Allow subquery materialization in NOT IN if
|
|
single-column subquery"
|
|
|
|
create table t1(a int null);
|
|
create table t2(a int null);
|
|
insert into t1 values(1),(2);
|
|
insert into t2 values(1),(2);
|
|
analyze table t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
|
|
one col => subq materialization
|
|
explain select a, a in (select a from t2) from t1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or (`test`.`t2`.`a` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true))) AS `a in (select a from t2)` from `test`.`t1`
|
|
select a, a in (select a from t2) from t1;
|
|
a a in (select a from t2)
|
|
1 1
|
|
2 1
|
|
|
|
t2.a is not nullable, but in the query it may appear as NULL
|
|
as it's in an outer join. But there is only one inner column so
|
|
materialization is possible
|
|
explain select t1.a, t2.a, t2.a in (select * from t2 as t3)
|
|
from t1 left join t2 on t1.a+t2.a=1000;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` `t3` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) or (`test`.`t3`.`a` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true))) AS `t2.a in (select * from t2 as t3)` from `test`.`t1` left join `test`.`t2` on(((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000)) where true
|
|
select t1.a, t2.a, t2.a in (select * from t2 as t3)
|
|
from t1 left join t2 on t1.a+t2.a=1000;
|
|
a a t2.a in (select * from t2 as t3)
|
|
1 NULL NULL
|
|
2 NULL NULL
|
|
|
|
_two_ outer columns, nullable => no materialization
|
|
explain select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3)
|
|
from t1 left join t2 on t1.a+t2.a=1000;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`a`),<exists>(/* select#2 */ select 1,1 from `test`.`t2` `t3` where (<if>(outer_field_is_not_null, ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) or (`test`.`t3`.`a` is null)), true) and <if>(outer_field_is_not_null, ((<cache>(`test`.`t2`.`a`) = `test`.`t3`.`a`) or (`test`.`t3`.`a` is null)), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true) and <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true)))) AS `(t2.a,t2.a) in (select a,a from t2 as t3)` from `test`.`t1` left join `test`.`t2` on(((`test`.`t1`.`a` + `test`.`t2`.`a`) = 1000)) where true
|
|
select t1.a, t2.a, (t2.a,t2.a) in (select a,a from t2 as t3)
|
|
from t1 left join t2 on t1.a+t2.a=1000;
|
|
a a (t2.a,t2.a) in (select a,a from t2 as t3)
|
|
1 NULL NULL
|
|
2 NULL NULL
|
|
drop table t1,t2;
|
|
|
|
Test in HAVING
|
|
create table t1(a int, b int);
|
|
create table t2(a int);
|
|
insert into t1 values(1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
|
|
insert into t2 values(10),(20);
|
|
analyze table t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
no NULLs.
|
|
explain select t1.a as z, sum(t1.b) from t1 group by t1.a
|
|
having (z in (select * from t2)) is null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 6 100.00 Using temporary
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `z`,sum(`test`.`t1`.`b`) AS `sum(t1.b)` from `test`.`t1` group by `test`.`t1`.`a` having (<in_optimizer>(`z`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`z`) = `test`.`t2`.`a`) or (`test`.`t2`.`a` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true))) is null)
|
|
select t1.a as z, sum(t1.b) from t1 group by t1.a
|
|
having (z in (select * from t2)) is null;
|
|
z sum(t1.b)
|
|
one outer NULL
|
|
insert into t1 values(null,null);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
explain select t1.a as z, sum(t1.b) from t1 group by t1.a
|
|
having (z in (select * from t2)) is null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using temporary
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `z`,sum(`test`.`t1`.`b`) AS `sum(t1.b)` from `test`.`t1` group by `test`.`t1`.`a` having (<in_optimizer>(`z`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`z`) = `test`.`t2`.`a`) or (`test`.`t2`.`a` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true))) is null)
|
|
select t1.a as z, sum(t1.b) from t1 group by t1.a
|
|
having (z in (select * from t2)) is null;
|
|
z sum(t1.b)
|
|
NULL NULL
|
|
one outer NULL and one inner NULL
|
|
insert into t2 values(null);
|
|
analyze table t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status OK
|
|
explain select t1.a as z, sum(t1.b) from t1 group by t1.a
|
|
having (z in (select * from t2)) is null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using temporary
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `z`,sum(`test`.`t1`.`b`) AS `sum(t1.b)` from `test`.`t1` group by `test`.`t1`.`a` having (<in_optimizer>(`z`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`z`) = `test`.`t2`.`a`) or (`test`.`t2`.`a` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true))) is null)
|
|
select t1.a as z, sum(t1.b) from t1 group by t1.a
|
|
having (z in (select * from t2)) is null;
|
|
z sum(t1.b)
|
|
1 6
|
|
2 6
|
|
NULL NULL
|
|
one inner NULL
|
|
delete from t1 where a is null;
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
explain select t1.a as z, sum(t1.b) from t1 group by t1.a
|
|
having (z in (select * from t2)) is null;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL # # Using temporary
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL # # Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `z`,sum(`test`.`t1`.`b`) AS `sum(t1.b)` from `test`.`t1` group by `test`.`t1`.`a` having (<in_optimizer>(`z`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`z`) = `test`.`t2`.`a`) or (`test`.`t2`.`a` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true))) is null)
|
|
select t1.a as z, sum(t1.b) from t1 group by t1.a
|
|
having (z in (select * from t2)) is null;
|
|
z sum(t1.b)
|
|
1 6
|
|
2 6
|
|
drop table t1,t2;
|
|
|
|
Verify that an inner NULL is looked up only once (result is
|
|
cached).
|
|
create table t1(a int);
|
|
create table t2(a int);
|
|
insert into t1 values(1),(2),(3),(4),(5),(6);
|
|
insert into t1 select * from t1;
|
|
insert into t2 values(10),(20),(NULL);
|
|
analyze table t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
explain select a, (a in (select * from t2)) from t1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 12 100.00 NULL
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or (`test`.`t2`.`a` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true))) AS `(a in (select * from t2))` from `test`.`t1`
|
|
flush status;
|
|
select a, (a in (select * from t2)) from t1;
|
|
a (a in (select * from t2))
|
|
1 NULL
|
|
2 NULL
|
|
3 NULL
|
|
4 NULL
|
|
5 NULL
|
|
6 NULL
|
|
1 NULL
|
|
2 NULL
|
|
3 NULL
|
|
4 NULL
|
|
5 NULL
|
|
6 NULL
|
|
There will be one look-up in the temporary table for each row
|
|
of t1 (12), plus one additional look-up to check whether table
|
|
contains a NULL value.
|
|
show status like "handler_read_key";
|
|
Variable_name Value
|
|
Handler_read_key 0
|
|
drop table t1,t2;
|
|
#
|
|
# Bug#13495157 - SUBQUERY MATERIALIZATION NOT USED FOR CERTAIN
|
|
# STATEMENTS
|
|
#
|
|
CREATE TABLE t1(a INT);
|
|
INSERT INTO t1 VALUES(1),(2),(3);
|
|
CREATE TABLE t2(a INT);
|
|
INSERT INTO t2 VALUES(1),(2),(4);
|
|
ANALYZE TABLE t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
# subquery materialization used for SELECT:
|
|
EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` <> 2) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`))))
|
|
SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
|
|
a
|
|
1
|
|
# Also used for INSERT SELECT:
|
|
CREATE TABLE t3 SELECT * FROM t1;
|
|
EXPLAIN INSERT INTO t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 INSERT t3 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 insert into `test`.`t3` /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` <> 2) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`))))
|
|
INSERT INTO t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
|
|
SELECT * FROM t3;
|
|
a
|
|
1
|
|
1
|
|
2
|
|
3
|
|
EXPLAIN INSERT INTO t2 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 INSERT t2 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 3 100.00 Using where; Using temporary
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 insert into `test`.`t2` /* select#1 */ select sql_buffer_result `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` <> 2) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`))))
|
|
INSERT INTO t2 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
|
|
SELECT * FROM t2;
|
|
a
|
|
1
|
|
1
|
|
2
|
|
4
|
|
ANALYZE TABLE t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status OK
|
|
EXPLAIN INSERT INTO t2 SELECT * FROM t2 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 INSERT t2 NULL ALL NULL NULL NULL NULL NULL NULL NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 4 100.00 Using where; Using temporary
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 4 25.00 Using where
|
|
Warnings:
|
|
Note 1003 insert into `test`.`t2` /* select#1 */ select sql_buffer_result `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` <> 2) and (<cache>(`test`.`t2`.`a`) = `test`.`t2`.`a`))))
|
|
INSERT INTO t2 SELECT * FROM t2 WHERE a IN (SELECT * FROM t2 WHERE a <> 2);
|
|
SELECT * FROM t2;
|
|
a
|
|
1
|
|
1
|
|
1
|
|
1
|
|
2
|
|
4
|
|
4
|
|
# Not used for single-table UPDATE, DELETE:
|
|
ANALYZE TABLE t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status OK
|
|
EXPLAIN SELECT * FROM t2 WHERE a IN (SELECT * FROM t1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 7 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))
|
|
EXPLAIN UPDATE t2 SET a=a-1 WHERE a IN (SELECT * FROM t1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 UPDATE t2 NULL ALL NULL NULL NULL NULL 7 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 update `test`.`t2` set `test`.`t2`.`a` = (`test`.`t2`.`a` - 1) where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))
|
|
UPDATE t2 SET a=a-1 WHERE a IN (SELECT * FROM t1);
|
|
ANALYZE TABLE t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status OK
|
|
SELECT * FROM t2;
|
|
a
|
|
0
|
|
0
|
|
0
|
|
0
|
|
1
|
|
4
|
|
4
|
|
EXPLAIN DELETE FROM t2 WHERE a IN (SELECT * FROM t1);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 DELETE t2 NULL ALL NULL NULL NULL NULL 12 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 delete from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` where (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))
|
|
DELETE FROM t2 WHERE a IN (SELECT * FROM t1);
|
|
SELECT * FROM t2;
|
|
a
|
|
0
|
|
0
|
|
0
|
|
0
|
|
4
|
|
4
|
|
EXPLAIN UPDATE t2 SET a=a-1 WHERE a IN (SELECT * FROM t2);
|
|
ERROR HY000: You can't specify target table 't2' for update in FROM clause
|
|
EXPLAIN DELETE FROM t2 WHERE a IN (SELECT * FROM t2);
|
|
ERROR HY000: You can't specify target table 't2' for update in FROM clause
|
|
UPDATE t2 SET a=3 WHERE a=0;
|
|
# Used for multi-table UPDATE, DELETE:
|
|
ANALYZE TABLE t2, t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status OK
|
|
EXPLAIN SELECT * FROM t2,t3 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 4 100.00 NULL
|
|
1 PRIMARY t2 NULL ALL NULL NULL NULL NULL 17 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t2` join `test`.`t3` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a` <> 2) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
|
|
EXPLAIN UPDATE t2,t3 SET t2.a=t2.a-2 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 4 100.00 NULL
|
|
1 UPDATE t2 NULL ALL NULL NULL NULL NULL 17 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 update `test`.`t2` join `test`.`t3` set `test`.`t2`.`a` = (`test`.`t2`.`a` - 2) where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a` <> 2) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
|
|
UPDATE t2,t3 SET t2.a=t2.a-2 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2);
|
|
SELECT * FROM t2;
|
|
a
|
|
1
|
|
1
|
|
1
|
|
1
|
|
4
|
|
4
|
|
ANALYZE TABLE t2, t3;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status OK
|
|
EXPLAIN DELETE t2.* FROM t2,t3 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t3 NULL ALL NULL NULL NULL NULL 4 100.00 NULL
|
|
1 DELETE t2 NULL ALL NULL NULL NULL NULL 21 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 3 33.33 Using where
|
|
Warnings:
|
|
Note 1003 delete `test`.`t2` from `test`.`t2` join `test`.`t3` where <in_optimizer>(`test`.`t2`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` where ((`test`.`t1`.`a` <> 2) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))
|
|
DELETE t2.* FROM t2,t3 WHERE t2.a IN (SELECT * FROM t1 WHERE a <> 2);
|
|
SELECT * FROM t2;
|
|
a
|
|
4
|
|
4
|
|
EXPLAIN UPDATE t2,t3 SET t2.a=10 WHERE t2.a IN (SELECT * FROM t2 WHERE a <> 2);
|
|
ERROR HY000: You can't specify target table 't2' for update in FROM clause
|
|
EXPLAIN DELETE t2.* FROM t2,t3 WHERE t2.a IN (SELECT * FROM t2 WHERE a <> 2);
|
|
ERROR HY000: You can't specify target table 't2' for update in FROM clause
|
|
DROP TABLE t1,t2,t3;
|
|
#
|
|
# Test that subquery materialization only does one lookup: does
|
|
# not try to read the next row if the first row failed the
|
|
# subquery's WHERE. We use a case where index lookup is not
|
|
# enough to satisfy IN(), because index has length two when the
|
|
# outer value has length three, and thus the post-filtering
|
|
# WHERE added by subselect_hash_sj_engine::setup() makes the
|
|
# decision.
|
|
#
|
|
create table t1 (a varchar(3));
|
|
create table t2 (a varchar(2));
|
|
insert into t1 values('aaa'), ('aaa');
|
|
insert into t2 values('aa'), ('aa');
|
|
analyze table t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
explain select * from t1 where a in (select a from t2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`)))
|
|
flush status;
|
|
select * from t1 where a in (select a from t2);
|
|
a
|
|
show status like "handler_read%";
|
|
Variable_name Value
|
|
Handler_read_first 0
|
|
Handler_read_key 0
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_next 9
|
|
drop table t1,t2;
|
|
#
|
|
# Bug#13655791 DIFFERENT RESULT WITH WL6094 ON QUERY WITH XOR
|
|
# IN WHERE CLAUSE
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk int NOT NULL,
|
|
col_varchar_nokey varchar(1) DEFAULT NULL,
|
|
PRIMARY KEY (pk)
|
|
);
|
|
INSERT INTO t1 VALUES (10,'x');
|
|
CREATE TABLE t2 (
|
|
pk int NOT NULL,
|
|
col_varchar_nokey varchar(1) DEFAULT NULL,
|
|
PRIMARY KEY (pk)
|
|
);
|
|
INSERT INTO t2 VALUES (1,'v'), (5,'x'), (11,'z'), (12,'c'), (15,'y');
|
|
CREATE TABLE t3 (
|
|
pk int NOT NULL,
|
|
col_int_key int DEFAULT NULL,
|
|
PRIMARY KEY (pk),
|
|
KEY col_int_key (col_int_key)
|
|
);
|
|
INSERT INTO t3 VALUES (10,8);
|
|
CREATE TABLE t4 (
|
|
pk int NOT NULL,
|
|
col_varchar_nokey varchar(1) DEFAULT NULL,
|
|
PRIMARY KEY (pk)
|
|
);
|
|
INSERT INTO t4 VALUES (1,'x');
|
|
ANALYZE TABLE t1, t2, t3, t4;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
test.t3 analyze status OK
|
|
test.t4 analyze status OK
|
|
EXPLAIN SELECT outr.pk, outr.col_varchar_nokey, outr2.col_varchar_nokey
|
|
FROM t2 AS outr2
|
|
JOIN t4 AS outr
|
|
ON (outr2.col_varchar_nokey > outr.col_varchar_nokey)
|
|
WHERE
|
|
outr.col_varchar_nokey IN (
|
|
SELECT innr.col_varchar_nokey
|
|
FROM t3 AS innr2
|
|
LEFT JOIN t1 AS innr
|
|
ON (innr2.col_int_key >= innr.pk)
|
|
)
|
|
XOR outr.pk < 6;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY outr NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
1 PRIMARY outr2 NULL ALL NULL NULL NULL NULL 5 33.33 Using where; Using join buffer (Block Nested Loop)
|
|
2 DEPENDENT SUBQUERY innr2 NULL index NULL col_int_key 5 NULL 1 100.00 Using index
|
|
2 DEPENDENT SUBQUERY innr NULL ALL PRIMARY NULL NULL NULL 1 100.00 Range checked for each record (index map: 0x1)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`outr`.`pk` AS `pk`,`test`.`outr`.`col_varchar_nokey` AS `col_varchar_nokey`,`test`.`outr2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2` `outr2` join `test`.`t4` `outr` where ((<in_optimizer>(`test`.`outr`.`col_varchar_nokey`,<exists>(/* select#2 */ select 1 from `test`.`t3` `innr2` left join `test`.`t1` `innr` on((`test`.`innr2`.`col_int_key` >= `test`.`innr`.`pk`)) where <if>(outer_field_is_not_null, ((<cache>(`test`.`outr`.`col_varchar_nokey`) = `test`.`innr`.`col_varchar_nokey`) or (`test`.`innr`.`col_varchar_nokey` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`innr`.`col_varchar_nokey`), true))) xor (`test`.`outr`.`pk` < 6)) and (`test`.`outr2`.`col_varchar_nokey` > `test`.`outr`.`col_varchar_nokey`))
|
|
FLUSH STATUS;
|
|
SELECT outr.pk, outr.col_varchar_nokey, outr2.col_varchar_nokey
|
|
FROM t2 AS outr2
|
|
JOIN t4 AS outr
|
|
ON (outr2.col_varchar_nokey > outr.col_varchar_nokey)
|
|
WHERE
|
|
outr.col_varchar_nokey IN (
|
|
SELECT innr.col_varchar_nokey
|
|
FROM t3 AS innr2
|
|
LEFT JOIN t1 AS innr
|
|
ON (innr2.col_int_key >= innr.pk)
|
|
)
|
|
XOR outr.pk < 6;
|
|
pk col_varchar_nokey col_varchar_nokey
|
|
SHOW STATUS LIKE "HANDLER_READ%";
|
|
Variable_name Value
|
|
Handler_read_first 1
|
|
Handler_read_key 0
|
|
Handler_read_last 0
|
|
Handler_read_next 1
|
|
Handler_read_prev 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_next 4
|
|
DROP TABLE t1,t2,t3,t4;
|
|
#
|
|
# Bug#13727407: Assert !item->const_item() || !item->not_null_tables()
|
|
#
|
|
CREATE TABLE t1 (
|
|
col_int_key INT,
|
|
KEY col_int_key (col_int_key)
|
|
);
|
|
INSERT INTO t1 VALUES (1);
|
|
CREATE TABLE t2 (
|
|
col_int_key INT,
|
|
col_time_key TIME,
|
|
col_datetime_nokey DATETIME,
|
|
KEY col_int_key (col_int_key),
|
|
KEY col_time_key (col_time_key)
|
|
);
|
|
INSERT INTO t2 VALUES
|
|
(7,'14:03:03','2001-11-28 00:50:27'), (1,'01:46:09','2007-10-09 19:53:04');
|
|
EXPLAIN SELECT col_datetime_nokey AS x
|
|
FROM t2 AS outr
|
|
WHERE col_int_key IN (
|
|
SELECT STRAIGHT_JOIN col_int_key
|
|
FROM t1
|
|
) AND outr.col_int_key = 0
|
|
HAVING x = '2000-09-09'
|
|
ORDER BY col_time_key;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
2 DEPENDENT SUBQUERY t1 NULL index_subquery col_int_key col_int_key 5 func 1 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`outr`.`col_datetime_nokey` AS `x` from `test`.`t2` `outr` where false having (`x` = '2000-09-09') order by `test`.`outr`.`col_time_key`
|
|
SELECT col_datetime_nokey AS x
|
|
FROM t2 AS outr
|
|
WHERE col_int_key IN (
|
|
SELECT STRAIGHT_JOIN col_int_key
|
|
FROM t1
|
|
) AND outr.col_int_key = 0
|
|
HAVING x = '2000-09-09'
|
|
ORDER BY col_time_key;
|
|
x
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# Bug#13838501 ASSERTION `TABLE->FILE->INITED' FAILED IN
|
|
# SUBSELECT_HASH_SJ_ENGINE::EXEC
|
|
#
|
|
CREATE TABLE t1
|
|
(c1 bigint,c2 char,pk INT,c3 char,c4 int,c5 INT,key (c5))
|
|
;
|
|
INSERT INTO t1 VALUES (763078661862588416,0,1,'',1,'');
|
|
Warnings:
|
|
Warning 1366 Incorrect integer value: '' for column 'c5' at row 1
|
|
CREATE TABLE t2 (c4k int,c4 int,cminnuk INT,key (cminnuk));
|
|
INSERT INTO t2 VALUES(0,'','');
|
|
Warnings:
|
|
Warning 1366 Incorrect integer value: '' for column 'c4' at row 1
|
|
Warning 1366 Incorrect integer value: '' for column 'cminnuk' at row 1
|
|
CREATE TABLE t3
|
|
(c4 int,pk INT,c1 bigint,cyk year,cy year,key (cyk))
|
|
;
|
|
INSERT INTO t3 VALUES(0,8,'',0,'');
|
|
Warnings:
|
|
Warning 1366 Incorrect integer value: '' for column 'c1' at row 1
|
|
Warning 1366 Incorrect integer value: '' for column 'cy' at row 1
|
|
EXPLAIN SELECT o.c2 AS x FROM t1 AS o
|
|
WHERE o.c1 IN
|
|
(SELECT innr.c4 AS y
|
|
FROM t2 AS innr2 JOIN t3 AS innr
|
|
ON (innr2.c4k=innr.c4)
|
|
WHERE innr.c1=6 OR NOT innr.c1=innr.pk
|
|
ORDER BY innr.c4)
|
|
AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY o NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using filesort
|
|
2 DEPENDENT SUBQUERY innr2 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
2 DEPENDENT SUBQUERY innr NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (Block Nested Loop)
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`o`.`c2` AS `x` from `test`.`t1` `o` where ((<in_optimizer>(`test`.`o`.`c1`,<exists>(/* select#2 */ select 1 from `test`.`t2` `innr2` join `test`.`t3` `innr` where ((`test`.`innr`.`c4` = `test`.`innr2`.`c4k`) and ((`test`.`innr`.`c1` = 6) or (`test`.`innr`.`c1` <> `test`.`innr`.`pk`)) and <if>(outer_field_is_not_null, ((<cache>(`test`.`o`.`c1`) = `test`.`innr2`.`c4k`) or (`test`.`innr2`.`c4k` is null)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`innr`.`c4`), true))) and (`test`.`o`.`c4` = 7)) xor (`test`.`o`.`pk` = 3)) order by `test`.`o`.`pk`
|
|
SELECT o.c2 AS x FROM t1 AS o
|
|
WHERE o.c1 IN
|
|
(SELECT innr.c4 AS y
|
|
FROM t2 AS innr2 JOIN t3 AS innr
|
|
ON (innr2.c4k=innr.c4)
|
|
WHERE innr.c1=6 OR NOT innr.c1=innr.pk
|
|
ORDER BY innr.c4)
|
|
AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
|
|
x
|
|
DROP TABLE t1,t2,t3;
|
|
# End of 5.6 tests
|
|
#
|
|
# Bug #18770217 DIFFERENT RESULT WITH SP ON 2ND EXECUTION OF QUERY WITH STRAIGHT_JOIN IN NOT IN
|
|
#
|
|
#
|
|
# Bug#19297190 NOT IN DOESN'T RETURN EXPECTED RESULT
|
|
#
|
|
CREATE TABLE t1 (a VARCHAR(500) CHARACTER SET UTF8);
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
SET @str= repeat("a",450);
|
|
SET @num=1000;
|
|
INSERT INTO t1 VALUES (CONCAT((@num:=@num+1), @str));
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
INSERT INTO t1 SELECT CONCAT((@num:=@num+1), @str) FROM t1;
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
SELECT COUNT(*) FROM t1;
|
|
COUNT(*)
|
|
32
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
set @save_heap_size= @@max_heap_table_size;
|
|
set @@max_heap_table_size= 16384;
|
|
EXPLAIN SELECT COUNT(*)
|
|
FROM t1
|
|
WHERE t1.a NOT IN (
|
|
SELECT t2.a FROM t1 as t2
|
|
);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 32 100.00 Using where
|
|
2 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 32 100.00 Using where
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t1` `t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`) or (`test`.`t2`.`a` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a`), true)) is false)
|
|
SELECT COUNT(*)
|
|
FROM t1
|
|
WHERE t1.a NOT IN (
|
|
SELECT t2.a FROM t1 as t2
|
|
);
|
|
COUNT(*)
|
|
0
|
|
DROP TABLE t1;
|
|
set @@max_heap_table_size= @save_heap_size;
|
|
#
|
|
# Bug#19805761 ASSERTION FAILURE IN SUBSELECT_HASH_SJ_ENGINE::EXEC WITH SUBQUERY IN LEFT ARGUME
|
|
#
|
|
CREATE TABLE t1(a INT);
|
|
INSERT INTO t1 VALUES(1);
|
|
CREATE TABLE t2 LIKE t1;
|
|
ANALYZE TABLE t1,t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
EXPLAIN SELECT (SELECT 1,2 FROM t2) NOT IN (SELECT 1,2 FROM t1) FROM t1;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
3 DEPENDENT SUBQUERY t1 NULL ALL NULL NULL NULL NULL 1 100.00 Using where
|
|
2 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,2 from `test`.`t2`),not <exists>(/* select#3 */ select 1,1 from `test`.`t1` where (<if>(outer_field_is_not_null, ((<cache>(<cache>(1)) = 1) or <cache>((1 is null))), true) and <if>(outer_field_is_not_null, ((<cache>(<cache>(2)) = 2) or <cache>((2 is null))), true)) having (<if>(outer_field_is_not_null, <is_not_null_test>(1), true) and <if>(outer_field_is_not_null, <is_not_null_test>(2), true)))) AS `(SELECT 1,2 FROM t2) NOT IN (SELECT 1,2 FROM t1)` from `test`.`t1`
|
|
SELECT (SELECT 1,2 FROM t2) NOT IN (SELECT 1,2 FROM t1) FROM t1;
|
|
(SELECT 1,2 FROM t2) NOT IN (SELECT 1,2 FROM t1)
|
|
NULL
|
|
DROP TABLE t1,t2;
|
|
#
|
|
# Bug#20729351 ASSERTION FAILED: ITEM_IN->LEFT_EXPR->ELEMENT_INDEX(0)->MAYBE_NULL
|
|
#
|
|
CREATE TABLE t1 (a LONGBLOB);
|
|
INSERT INTO t1 VALUES ('a'), ('a'), ('a');
|
|
CREATE TABLE t2 (a INT);
|
|
INSERT INTO t2 VALUES(1), (1), (1), (1);
|
|
ANALYZE TABLE t1, t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
test.t2 analyze status OK
|
|
SELECT
|
|
(SELECT NULL IN (NULL) FROM t1 WHERE a) =
|
|
ANY(SELECT 1 FROM t2)
|
|
FROM t1;
|
|
(SELECT NULL IN (NULL) FROM t1 WHERE a) =
|
|
ANY(SELECT 1 FROM t2)
|
|
NULL
|
|
NULL
|
|
NULL
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# Bug#22089623 ASSERTION IN ITEM_FUNC_TRIG_COND::VAL_INT()
|
|
# WITH SUBQUERY ON LEFT SIDE OF IN
|
|
#
|
|
EXPLAIN SELECT (SELECT NULL, NULL) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select NULL,NULL),<exists>(/* select#3 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(NULL)) = 1) or (1 is null)), true) and <if>(outer_field_is_not_null, ((<cache>(<cache>(NULL)) = 2) or (2 is null)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(1), true) and <if>(outer_field_is_not_null, <is_not_null_test>(2), true)))) AS `(SELECT NULL, NULL) IN (SELECT 1, 2)`
|
|
SELECT (SELECT NULL, NULL) IN (SELECT 1, 2);
|
|
(SELECT NULL, NULL) IN (SELECT 1, 2)
|
|
NULL
|
|
EXPLAIN SELECT (SELECT 1, 1) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,1),<exists>(/* select#3 */ select 1,2 having false)) AS `(SELECT 1, 1) IN (SELECT 1, 2)`
|
|
SELECT (SELECT 1, 1) IN (SELECT 1, 2);
|
|
(SELECT 1, 1) IN (SELECT 1, 2)
|
|
0
|
|
EXPLAIN SELECT (SELECT 1, 2) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,2),<exists>(/* select#3 */ select 1,2 having (<is_not_null_test>(1) and <is_not_null_test>(2)))) AS `(SELECT 1, 2) IN (SELECT 1, 2)`
|
|
SELECT (SELECT 1, 2) IN (SELECT 1, 2);
|
|
(SELECT 1, 2) IN (SELECT 1, 2)
|
|
1
|
|
EXPLAIN SELECT (SELECT NULL, 2) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select NULL,2),<exists>(/* select#3 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(NULL)) = 1) or (1 is null)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(1), true) and <is_not_null_test>(2)))) AS `(SELECT NULL, 2) IN (SELECT 1, 2)`
|
|
SELECT (SELECT NULL, 2) IN (SELECT 1, 2);
|
|
(SELECT NULL, 2) IN (SELECT 1, 2)
|
|
NULL
|
|
EXPLAIN SELECT (SELECT 1, NULL) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,NULL),<exists>(/* select#3 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(NULL)) = 2) or (2 is null)), true) and <is_not_null_test>(1) and <if>(outer_field_is_not_null, <is_not_null_test>(2), true)))) AS `(SELECT 1, NULL) IN (SELECT 1, 2)`
|
|
SELECT (SELECT 1, NULL) IN (SELECT 1, 2);
|
|
(SELECT 1, NULL) IN (SELECT 1, 2)
|
|
NULL
|
|
EXPLAIN SELECT (SELECT NULL, 1) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select NULL,1),<exists>(/* select#3 */ select 1,2 having false)) AS `(SELECT NULL, 1) IN (SELECT 1, 2)`
|
|
SELECT (SELECT NULL, 1) IN (SELECT 1, 2);
|
|
(SELECT NULL, 1) IN (SELECT 1, 2)
|
|
0
|
|
EXPLAIN SELECT (SELECT 2, NULL) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 2,NULL),<exists>(/* select#3 */ select 1,2 having false)) AS `(SELECT 2, NULL) IN (SELECT 1, 2)`
|
|
SELECT (SELECT 2, NULL) IN (SELECT 1, 2);
|
|
(SELECT 2, NULL) IN (SELECT 1, 2)
|
|
0
|
|
EXPLAIN SELECT (SELECT 1, 2 UNION SELECT 1, 2) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,2 union /* select#3 */ select 1,2),<exists>(/* select#4 */ select 1,2 having ((<cache>(<cache>(`<union temporary>`.`1`)) = 1) and (<cache>(<cache>(`<union temporary>`.`2`)) = 2) and <is_not_null_test>(1) and <is_not_null_test>(2)))) AS `(SELECT 1, 2 UNION SELECT 1, 2) IN (SELECT 1, 2)`
|
|
SELECT (SELECT 1, 2 UNION SELECT 1, 2) IN (SELECT 1, 2);
|
|
(SELECT 1, 2 UNION SELECT 1, 2) IN (SELECT 1, 2)
|
|
1
|
|
EXPLAIN SELECT (SELECT 1, 1 UNION SELECT 1, 1) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,1 union /* select#3 */ select 1,1),<exists>(/* select#4 */ select 1,2 having ((<cache>(<cache>(`<union temporary>`.`1`)) = 1) and (<cache>(<cache>(`<union temporary>`.`1`)) = 2) and <is_not_null_test>(1) and <is_not_null_test>(2)))) AS `(SELECT 1, 1 UNION SELECT 1, 1) IN (SELECT 1, 2)`
|
|
SELECT (SELECT 1, 1 UNION SELECT 1, 1) IN (SELECT 1, 2);
|
|
(SELECT 1, 1 UNION SELECT 1, 1) IN (SELECT 1, 2)
|
|
0
|
|
EXPLAIN SELECT (SELECT NULL, NULL UNION SELECT NULL, NULL) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select NULL,NULL union /* select#3 */ select NULL,NULL),<exists>(/* select#4 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(`<union temporary>`.`NULL`)) = 1) or (1 is null)), true) and <if>(outer_field_is_not_null, ((<cache>(<cache>(`<union temporary>`.`NULL`)) = 2) or (2 is null)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(1), true) and <if>(outer_field_is_not_null, <is_not_null_test>(2), true)))) AS `(SELECT NULL, NULL UNION SELECT NULL, NULL) IN (SELECT 1, 2)`
|
|
SELECT (SELECT NULL, NULL UNION SELECT NULL, NULL) IN (SELECT 1, 2);
|
|
(SELECT NULL, NULL UNION SELECT NULL, NULL) IN (SELECT 1, 2)
|
|
NULL
|
|
EXPLAIN SELECT (SELECT 1, 1 UNION SELECT 2, 2) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,1 union /* select#3 */ select 2,2),<exists>(/* select#4 */ select 1,2 having ((<cache>(<cache>(`<union temporary>`.`1`)) = 1) and (<cache>(<cache>(`<union temporary>`.`1`)) = 2) and <is_not_null_test>(1) and <is_not_null_test>(2)))) AS `(SELECT 1, 1 UNION SELECT 2, 2) IN (SELECT 1, 2)`
|
|
SELECT (SELECT 1, 1 UNION SELECT 2, 2) IN (SELECT 1, 2);
|
|
ERROR 21000: Subquery returns more than 1 row
|
|
EXPLAIN SELECT (SELECT 1, 2 WHERE FALSE UNION SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
NULL UNION RESULT <union2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,2 from DUAL where false union /* select#3 */ select 1,2 from DUAL where false),<exists>(/* select#4 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(`<union temporary>`.`1`)) = 1) or (1 is null)), true) and <if>(outer_field_is_not_null, ((<cache>(<cache>(`<union temporary>`.`2`)) = 2) or (2 is null)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(1), true) and <if>(outer_field_is_not_null, <is_not_null_test>(2), true)))) AS `(SELECT 1, 2 WHERE FALSE UNION SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2)`
|
|
SELECT (SELECT 1, 2 WHERE FALSE UNION SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2);
|
|
(SELECT 1, 2 WHERE FALSE UNION SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2)
|
|
NULL
|
|
EXPLAIN SELECT (SELECT 1, 2 UNION ALL SELECT 1, 2) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,2 union all /* select#3 */ select 1,2),<exists>(/* select#4 */ select 1,2 having ((<cache>(<cache>(`<union temporary>`.`1`)) = 1) and (<cache>(<cache>(`<union temporary>`.`2`)) = 2) and <is_not_null_test>(1) and <is_not_null_test>(2)))) AS `(SELECT 1, 2 UNION ALL SELECT 1, 2) IN (SELECT 1, 2)`
|
|
SELECT (SELECT 1, 2 UNION ALL SELECT 1, 2) IN (SELECT 1, 2);
|
|
ERROR 21000: Subquery returns more than 1 row
|
|
EXPLAIN SELECT (SELECT 1, 1 UNION ALL SELECT 1, 1) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,1 union all /* select#3 */ select 1,1),<exists>(/* select#4 */ select 1,2 having ((<cache>(<cache>(`<union temporary>`.`1`)) = 1) and (<cache>(<cache>(`<union temporary>`.`1`)) = 2) and <is_not_null_test>(1) and <is_not_null_test>(2)))) AS `(SELECT 1, 1 UNION ALL SELECT 1, 1) IN (SELECT 1, 2)`
|
|
SELECT (SELECT 1, 1 UNION ALL SELECT 1, 1) IN (SELECT 1, 2);
|
|
ERROR 21000: Subquery returns more than 1 row
|
|
EXPLAIN SELECT (SELECT NULL, NULL UNION ALL SELECT NULL, NULL) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select NULL,NULL union all /* select#3 */ select NULL,NULL),<exists>(/* select#4 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(`<union temporary>`.`NULL`)) = 1) or (1 is null)), true) and <if>(outer_field_is_not_null, ((<cache>(<cache>(`<union temporary>`.`NULL`)) = 2) or (2 is null)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(1), true) and <if>(outer_field_is_not_null, <is_not_null_test>(2), true)))) AS `(SELECT NULL, NULL UNION ALL SELECT NULL, NULL) IN (SELECT 1, 2)`
|
|
SELECT (SELECT NULL, NULL UNION ALL SELECT NULL, NULL) IN (SELECT 1, 2);
|
|
ERROR 21000: Subquery returns more than 1 row
|
|
EXPLAIN SELECT (SELECT 1, 1 UNION ALL SELECT 2, 2) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,1 union all /* select#3 */ select 2,2),<exists>(/* select#4 */ select 1,2 having ((<cache>(<cache>(`<union temporary>`.`1`)) = 1) and (<cache>(<cache>(`<union temporary>`.`1`)) = 2) and <is_not_null_test>(1) and <is_not_null_test>(2)))) AS `(SELECT 1, 1 UNION ALL SELECT 2, 2) IN (SELECT 1, 2)`
|
|
SELECT (SELECT 1, 1 UNION ALL SELECT 2, 2) IN (SELECT 1, 2);
|
|
ERROR 21000: Subquery returns more than 1 row
|
|
EXPLAIN SELECT (SELECT 1, 2 WHERE FALSE UNION ALL SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2);
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select <in_optimizer>((/* select#2 */ select 1,2 from DUAL where false union all /* select#3 */ select 1,2 from DUAL where false),<exists>(/* select#4 */ select 1,2 having (<if>(outer_field_is_not_null, ((<cache>(<cache>(`<union temporary>`.`1`)) = 1) or (1 is null)), true) and <if>(outer_field_is_not_null, ((<cache>(<cache>(`<union temporary>`.`2`)) = 2) or (2 is null)), true) and <if>(outer_field_is_not_null, <is_not_null_test>(1), true) and <if>(outer_field_is_not_null, <is_not_null_test>(2), true)))) AS `(SELECT 1, 2 WHERE FALSE UNION ALL SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2)`
|
|
SELECT (SELECT 1, 2 WHERE FALSE UNION ALL SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2);
|
|
(SELECT 1, 2 WHERE FALSE UNION ALL SELECT 1, 2 WHERE FALSE) IN (SELECT 1, 2)
|
|
NULL
|
|
#
|
|
# Bug#20536077: WRONG RESULT FOR SELECT NULL,NULL IN (SUBQUERY)
|
|
#
|
|
# The following queries correctly returned NULL.
|
|
CREATE TABLE s (s INT);
|
|
INSERT INTO s VALUES(1);
|
|
SELECT NULL IN (SELECT 1);
|
|
NULL IN (SELECT 1)
|
|
NULL
|
|
SELECT NULL = (SELECT 1);
|
|
NULL = (SELECT 1)
|
|
NULL
|
|
SELECT NULL = (SELECT 1 FROM s);
|
|
NULL = (SELECT 1 FROM s)
|
|
NULL
|
|
SELECT (NULL, NULL) IN (SELECT 1, 2);
|
|
(NULL, NULL) IN (SELECT 1, 2)
|
|
NULL
|
|
# The following queries returned 0 instead of NULL.
|
|
SELECT NULL IN (SELECT 1 FROM s);
|
|
NULL IN (SELECT 1 FROM s)
|
|
NULL
|
|
SELECT (SELECT NULL, NULL FROM DUAL) IN (SELECT 1, 2 FROM s) FROM s;
|
|
(SELECT NULL, NULL FROM DUAL) IN (SELECT 1, 2 FROM s)
|
|
NULL
|
|
SELECT (SELECT NULL,NULL) IN (SELECT 1, 2);
|
|
(SELECT NULL,NULL) IN (SELECT 1, 2)
|
|
NULL
|
|
CREATE TABLE u(a INT, b INT);
|
|
INSERT INTO u VALUES (NULL, NULL);
|
|
SELECT (SELECT * FROM u) IN (SELECT 1, 2 FROM s) FROM s;
|
|
(SELECT * FROM u) IN (SELECT 1, 2 FROM s)
|
|
NULL
|
|
SELECT (SELECT * FROM u) IN (SELECT 1, 2);
|
|
(SELECT * FROM u) IN (SELECT 1, 2)
|
|
NULL
|
|
DROP TABLE s, u;
|
|
set @@optimizer_switch=@old_opt_switch;
|
|
SET sql_mode = default;
|
|
set optimizer_switch=default;
|