| List: | Commits | « Previous MessageNext Message » | |
| From: | Timour Katchaounov | Date: | November 20 2008 4:34pm |
| Subject: | bzr commit into mysql-6.0-opt branch (timour:2701) Bug#36752 | ||
| View as plain text | |||
#At file:///home/tkatchaounov/mysql/bzr/6.0-opt/
2701 Timour Katchaounov 2008-11-20 [merge]
BUG#36752: subquery materialization produces wrong results when comparing different
types
Manual merge with mysql-6.0-opt tree that fixes missing "drop table" statements in
subselect_mat.test.
modified:
mysql-test/r/subselect.result
mysql-test/r/subselect_mat.result
mysql-test/r/subselect_no_mat.result
mysql-test/r/subselect_no_opts.result
mysql-test/r/subselect_no_semijoin.result
mysql-test/t/subselect_mat.test
sql/item_subselect.cc
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result 2008-10-20 09:16:47 +0000
+++ b/mysql-test/r/subselect.result 2008-11-19 15:27:23 +0000
@@ -4367,13 +4367,13 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ),
<primary_index_lookup>(1 in <temporary table> on distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ),
<primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 =
`materialized subselect`.`1`)))))
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY
a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using
filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3)
group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on
distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3)
group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on
distinct_key where ((1 = `materialized subselect`.`1`)))))
DROP TABLE t1;
End of 5.0 tests.
create table t_out (subcase char(3),
=== modified file 'mysql-test/r/subselect_mat.result'
--- a/mysql-test/r/subselect_mat.result 2008-10-17 09:46:06 +0000
+++ b/mysql-test/r/subselect_mat.result 2008-11-20 15:34:15 +0000
@@ -41,7 +41,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select
`test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select
`test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key
where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`)))))
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
a1 a2
1 - 01 2 - 01
@@ -52,7 +52,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using
filesort
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select
`test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by
`test`.`t2`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary
table> on distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>(`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (select
`test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by
`test`.`t2`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary
table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`)))))
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -63,7 +63,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using
filesort
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from
`test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2`
), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from
`test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2`
), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on
distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and
(`test`.`t1`.`a2` = `materialized subselect`.`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
@@ -74,7 +74,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using
filesort
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,min(`test`.`t2`.`b2`) AS
`min(b2)` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ),
<primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,min(`test`.`t2`.`b2`) AS
`min(b2)` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ),
<primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key
where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` =
`materialized subselect`.`min(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
@@ -85,7 +85,7 @@ id select_type table type possible_keys
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
2 SUBQUERY t2i index it2i1,it2i3 it2i1 9 NULL 5 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where <in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize>
(select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where <in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize>
(select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key
where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`)))))
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
a1 a2
1 - 01 2 - 01
@@ -96,7 +96,7 @@ id select_type table type possible_keys
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
2 SUBQUERY t2i range it2i1,it2i3 it2i1 9 NULL 3 100.00 Using where; Using index for
group-by
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where <in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize>
(select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')
group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in
<temporary table> on distinct_key)))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where <in_optimizer>(`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( <materialize>
(select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0')
group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1i`.`a1` in
<temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized
subselect`.`b1`)))))
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -107,7 +107,7 @@ id select_type table type possible_keys
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
2 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where
<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where
<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key
where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` =
`materialized subselect`.`b2`)))))
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
a1 a2
1 - 01 2 - 01
@@ -118,7 +118,7 @@ id select_type table type possible_keys
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for
group-by
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where
<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by
`test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), <primary_index_lookup>(`test`.`t1i`.`a1` in
<temporary table> on distinct_key)))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where
<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by
`test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), <primary_index_lookup>(`test`.`t1i`.`a1` in
<temporary table> on distinct_key where ((`test`.`t1i`.`a1` = `materialized
subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`)))))
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
@@ -129,7 +129,7 @@ id select_type table type possible_keys
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for
group-by
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where
<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS
`min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`
), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where
<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS
`min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`
), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on
distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and
(`test`.`t1i`.`a2` = `materialized subselect`.`min(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
@@ -140,7 +140,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2i range NULL it2i3 9 NULL 3 100.00 Using index for group-by
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,max(`test`.`t2i`.`b2`) AS
`max(b2)` from `test`.`t2i` group by `test`.`t2i`.`b1` ),
<primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,max(`test`.`t2i`.`b2`) AS
`max(b2)` from `test`.`t2i` group by `test`.`t2i`.`b1` ),
<primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key
where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` =
`materialized subselect`.`max(b2)`)))))
select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
a1 a2
1 - 01 2 - 01
@@ -169,7 +169,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for
group-by
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS
`min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`
), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS
`min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`
), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on
distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and
(`test`.`t1`.`a2` = `materialized subselect`.`min(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
@@ -209,7 +209,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from
`test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2` ),
<primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from
`test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2` ),
<primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key
where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` =
`materialized subselect`.`b2`)))))
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
a1 a2
1 - 01 2 - 01
@@ -220,7 +220,7 @@ id select_type table type possible_keys
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
2 SUBQUERY t2i index NULL it2i3 18 NULL 5 100.00 Using index
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where
<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ),
<primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where
<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ),
<primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key
where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` =
`materialized subselect`.`b2`)))))
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
a1 a2
1 - 01 2 - 01
@@ -275,7 +275,7 @@ id select_type table type possible_keys
4 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from
`test`.`t2` where (`test`.`t2`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on
distinct_key))) and
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from
`test`.`t3` where
<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),
<primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on
distinct_key))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary
table> on distinct_key))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from
`test`.`t2` where (`test`.`t2`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key
where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` =
`materialized subselect`.`b2`))))) and
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from
`test`.`t3` where
<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),
<primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key
wh
ere ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` =
`materialized subselect`.`b2`))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in
<temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized
subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`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
@@ -294,7 +294,7 @@ id select_type table type possible_keys
4 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
2 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where
(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on
distinct_key))) and
<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from
`test`.`t3i` where
<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),
<primary_index_lookup>(`test`.`t3i`.`c1` in <temporary table> on
distinct_key))) ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary
table> on distinct_key))))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i`
where
(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key
where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` =
`materialized subselect`.`b2`))))) and
<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from
`test`.`t3i` where
<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),
<primary_index_lookup>(`test`.`t3i`.`c1` in <temporary
table> on distinct_key where ((`test`.`t3i`.`c1` = `materialized subselect`.`b1`) and
(`test`.`t3i`.`c2` = `materialized subselect`.`b2`))))) ),
<primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key
where ((`test`.`t1i`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1i`.`a2` =
`materialized subselect`.`c2`))))))
select * from t1i
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
(a1, a2) in (select c1, c2 from t3i
@@ -317,7 +317,7 @@ id select_type table type possible_keys
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from
`test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in (
<materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where
(`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in
<temporary table> on distinct_key))) or
<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select
`test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ),
<primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on
distinct_key)))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary
table> on distinct_key))) and
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`
.`t3`.`c2` AS `c2` from `test`.`t3` where
<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),
<primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on
distinct_key))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary
table> on distinct_key))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from
`test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in (
<materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where
(`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in
<temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized
subselect`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in (
<materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where
(`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in
<temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized
subselect`.`c2`)))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary
table> on distinct_key where ((`test`.`t1`.`a1` = `materi
alized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) and
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from
`test`.`t3` where
<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),
<primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key
where ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` =
`materialized subselect`.`b2`))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in
<temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized
subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`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
@@ -342,7 +342,7 @@ id select_type table type possible_keys
3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
-Note 1003 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
`test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where
((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` 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`,`test`.`t2`.`b2` in ( <materialize> (select
`test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ),
<primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on
distinct_key)))) 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`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from
`test`.`t3` `t3c` where <in_optimizer>(
(`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),(`test`.`t3c`.`c1`,`test`.`t3c`.`c2`) in (
<materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),
<primary_index_lookup>(`test`.`t3c`.`c1` in <temporary table> on
distinct_key))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary
table> on distinct_key))))
+Note 1003 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
`test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where
((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` 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`,`test`.`t2`.`b2` in ( <materialize> (select
`test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ),
<primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key
where ((`test`.`t2`.`b2` = `materialized subselect`.`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`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c
`.`c2` AS `c2` from `test`.`t3` `t3c` where
<in_optimizer>((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),(`test`.`t3c`.`c1`,`test`.`t3c`.`c2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),
<primary_index_lookup>(`test`.`t3c`.`c1` in <temporary table> on distinct_key
where ((`test`.`t3c`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3c`.`c2` =
`materialized subselect`.`b2`))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in
<temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized
subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`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
@@ -378,7 +378,7 @@ id select_type table type possible_keys
8 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index
NULL UNION RESULT <union1,7> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1`
where
(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from
`test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in (
<materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where
(`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in
<temporary table> on distinct_key))) or
<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select
`test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ),
<primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on
distinct_key)))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ),
<primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on
distinct_key))) and
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <material
ize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where
<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),
<primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on
distinct_key))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary
table> on distinct_key))))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2`
AS `a2` from `test`.`t1i` where
(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on
distinct_key))) and
<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `t
est`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from `test`.`t3i` where
<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),
<primary_index_lookup>(`test`.`t3i`.`c1` in <temporary table> on
distinct_key))) ), <primary_index_lookup>(`test`.`t1i`.`a1` in <temporary
table> on distinct_key)))))
+Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1`
where
(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from
`test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in (
<materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where
(`test`.`t3`.`c2` like '%02') ), <primary_index_lookup>(`test`.`t2`.`b2` in
<temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized
subselect`.`c2`))))) or <in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in (
<materialize> (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where
(`test`.`t3`.`c2` like '%03') ), <primary_index_lookup>(`test`.`t2`.`b2` in
<temporary table> on distinct_key where ((`test`.`t2`.`b2` = `materialized
subselect`.`c2`)))))) group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ),
<primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on dis
tinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2`
= `materialized subselect`.`b2`))))) and
<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from
`test`.`t3` where
<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),
<primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key
where ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` =
`materialized subselect`.`b2`))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in
<temporary table> on distinct_key where ((`test`.`t1`.`a1` = `materialized
subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`))))))) union
(select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` A
S `a2` from `test`.`t1i` where
(<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key
where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` =
`materialized subselect`.`b2`))))) and
<in_optimizer>((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`)
in ( <materialize> (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from
`test`.`t3i` where
<in_optimizer>((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ),
<primary_index_lookup>(`test`.`t3i`.`c1` in <temporary table> on distinct_key
where ((`test`.`t3i`.`c1` = `materiali
zed subselect`.`b1`) and (`test`.`t3i`.`c2` = `materialized subselect`.`b2`))))) ),
<primary_index_lookup>(`test`.`t1i`.`a1` in <temporary table> on distinct_key
where ((`test`.`t1i`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1i`.`a2` =
`materialized subselect`.`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
@@ -407,7 +407,7 @@ id select_type table type possible_keys
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 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
`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` 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 `test`.`t2`.`b1` AS
`b1`,`test`.`t2`.`b2` AS `b2` 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`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from
`test`.`t3` where
<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <p
rimary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key))) ),
<primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on
distinct_key))))
+Note 1003 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
`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` 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 `test`.`t2`.`b1` AS
`b1`,`test`.`t2`.`b2` AS `b2` 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`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`)
in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from
`test`.`t3` where
<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`)
in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from
`test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), <p
rimary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key where
((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized
subselect`.`b2`))))) ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary
table> on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and
(`test`.`t1`.`a2` = `materialized subselect`.`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
@@ -430,7 +430,7 @@ id select_type table type possible_keys
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where
NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
-Note 1003 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
`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` 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 `test`.`t2`.`b1` AS
`b1`,`test`.`t2`.`b2` AS `b2` 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`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`)
in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from
`test`.`t3` where
<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`)
in ( <materialize> (selec
t `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where
(`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in
<temporary table> on distinct_key))) ),
<primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on
distinct_key))))
+Note 1003 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
`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` 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 `test`.`t2`.`b1` AS
`b1`,`test`.`t2`.`b2` AS `b2` 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`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`)
in ( <materialize> (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from
`test`.`t3` where
<in_optimizer>((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`)
in ( <materialize> (selec
t `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where
(`test`.`t2i`.`b2` > '0') ), <primary_index_lookup>(`test`.`t3`.`c1` in
<temporary table> on distinct_key where ((`test`.`t3`.`c1` = `materialized
subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`))))) ),
<primary_index_lookup>(`test`.`t3`.`c1` in <temporary table> on distinct_key
where ((`test`.`t3`.`c1` = `materialized subselect`.`c1`) and (`test`.`t3`.`c2` =
`materialized subselect`.`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
@@ -476,7 +476,7 @@ id select_type table type possible_keys
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 `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
(<in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),<exists>(select
`test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where
((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` 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`,`test`.`t2`.`b2` in ( <materialize> (select
`test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ),
<primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on
distinct_key)))) 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
`test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` 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`.`t3c`.`c1`) = `test`.`t2i`.`b1`) and
(<cache>(`test`.`t3c`.`c2`) = `test`.`t2i`.`b2`))))) and
(<cache>(`test`.`t1`.`a1`) = `test`.`t3c`.`c1`) and
(<cache>(`test`.`t1`.`a2`) = `test`.`t3c`.`c2`)))))
+Note 1003 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
`test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where
((<in_optimizer>(`test`.`t2`.`b2`,<exists>(select 1 AS `Not_used` 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`,`test`.`t2`.`b2` in ( <materialize> (select
`test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ),
<primary_index_lookup>(`test`.`t2`.`b2` in <temporary table> on distinct_key
where ((`test`.`t2`.`b2` = `materialized subselect`.`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
`test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from `test`.`t3` `t3c` where (<i
n_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`.`t3c`.`c1`) = `test`.`t2i`.`b1`) and
(<cache>(`test`.`t3c`.`c2`) = `test`.`t2i`.`b2`))))) and
(<cache>(`test`.`t1`.`a1`) = `test`.`t3c`.`c1`) and
(<cache>(`test`.`t1`.`a2`) = `test`.`t3c`.`c2`)))))
explain extended
select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');
id select_type table type possible_keys key key_len ref rows filtered Extra
@@ -611,7 +611,7 @@ id select_type table type possible_keys
1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
-Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize>
(select substr(`test`.`t2_16`.`b1`,1,16) AS `substring(b1,1,16)` from `test`.`t2_16`
where (`test`.`t2_16`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1_16`.`a1`
in <temporary table> on distinct_key)))
+Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize>
(select substr(`test`.`t2_16`.`b1`,1,16) AS `substring(b1,1,16)` from `test`.`t2_16`
where (`test`.`t2_16`.`b1` > '0') ), <primary_index_lookup>(`test`.`t1_16`.`a1`
in <temporary table> on distinct_key where ((`test`.`t1_16`.`a1` = `materialized
subselect`.`substring(b1,1,16)`)))))
select left(a1,7), left(a2,7)
from t1_16
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -640,7 +640,7 @@ id select_type table type possible_keys
1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_16` group by `test`.`t2_16`.`b2` ),
<primary_index_lookup>(`test`.`t1_16`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_16` group by `test`.`t2_16`.`b2` ),
<primary_index_lookup>(`test`.`t1_16`.`a1` in <temporary table> on
distinct_key where ((`test`.`t1_16`.`a1` = `materialized
subselect`.`group_concat(b1)`)))))
select left(a1,7), left(a2,7)
from t1_16
where a1 in (select group_concat(b1) from t2_16 group by b2);
@@ -662,7 +662,7 @@ id select_type table type possible_keys
3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>(concat(`test`.`t1`.`a1`,'x'),concat(`test`.`t1`.`a1`,'x') in (
<materialize> (select left(`test`.`t1_16`.`a1`,8) AS `left(a1,8)` from
`test`.`t1_16` where
<in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`)
in (select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from `test`.`t2_16`
join `test`.`t2` where ((`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and
<in_optimizer>(`test`.`t2`.`b1`,`test`.`t2`.`b1` in ( <materialize> (select
`test`.`t3`.`c1` AS `c1` from `test`.`t3` where (`test`.`t3`.`c2` > '0') ),
<primary_index_lookup>(`test`.`t2`.`b1` in <temporary table> on
distinct_key))) and (<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and
(<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`)))) ),
<primary_index_lookup>(concat(`test`.`t1`.`a1`,'x') in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where
<in_optimizer>(concat(`test`.`t1`.`a1`,'x'),concat(`test`.`t1`.`a1`,'x') in (
<materialize> (select left(`test`.`t1_16`.`a1`,8) AS `left(a1,8)` from
`test`.`t1_16` where
<in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`)
in (select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from `test`.`t2_16`
join `test`.`t2` where ((`test`.`t2`.`b2` = substr(`test`.`t2_16`.`b2`,1,6)) and
<in_optimizer>(`test`.`t2`.`b1`,`test`.`t2`.`b1` in ( <materialize> (select
`test`.`t3`.`c1` AS `c1` from `test`.`t3` where (`test`.`t3`.`c2` > '0') ),
<primary_index_lookup>(`test`.`t2`.`b1` in <temporary table> on distinct_key
where ((`test`.`t2`.`b1` = `materialized subselect`.`c1`))))) and
(<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`) and
(<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`)))) ),
<primary_index_lookup>(concat(`test`.`t1`.`a1`,'x')
in <temporary table> on distinct_key where ((concat(`test`.`t1`.`a1`,'x') =
`materialized subselect`.`left(a1,8)`)))))
drop table t1_16, t2_16, t3_16;
set @blob_len = 512;
set @suffix_len = @blob_len - @prefix_len;
@@ -724,7 +724,7 @@ id select_type table type possible_keys
1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
-Note 1003 select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize>
(select substr(`test`.`t2_512`.`b1`,1,512) AS `substring(b1,1,512)` from `test`.`t2_512`
where (`test`.`t2_512`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize>
(select substr(`test`.`t2_512`.`b1`,1,512) AS `substring(b1,1,512)` from `test`.`t2_512`
where (`test`.`t2_512`.`b1` > '0') ),
<primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on
distinct_key where ((`test`.`t1_512`.`a1` = `materialized
subselect`.`substring(b1,1,512)`)))))
select left(a1,7), left(a2,7)
from t1_512
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -738,13 +738,11 @@ id select_type table type possible_keys
1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_512` group by `test`.`t2_512`.`b2` ),
<primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_512` group by `test`.`t2_512`.`b2` ),
<primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on
distinct_key where ((`test`.`t1_512`.`a1` = `materialized
subselect`.`group_concat(b1)`)))))
select left(a1,7), left(a2,7)
from t1_512
where a1 in (select group_concat(b1) from t2_512 group by b2);
left(a1,7) left(a2,7)
-1 - 01x 2 - 01x
-1 - 02x 2 - 02x
set @@group_concat_max_len = 256;
explain extended select left(a1,7), left(a2,7)
from t1_512
@@ -753,13 +751,11 @@ id select_type table type possible_keys
1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_512` group by `test`.`t2_512`.`b2` ),
<primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_512` group by `test`.`t2_512`.`b2` ),
<primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on
distinct_key where ((`test`.`t1_512`.`a1` = `materialized
subselect`.`group_concat(b1)`)))))
select left(a1,7), left(a2,7)
from t1_512
where a1 in (select group_concat(b1) from t2_512 group by b2);
left(a1,7) left(a2,7)
-1 - 01x 2 - 01x
-1 - 02x 2 - 02x
drop table t1_512, t2_512, t3_512;
set @blob_len = 1024;
set @suffix_len = @blob_len - @prefix_len;
@@ -835,13 +831,11 @@ id select_type table type possible_keys
1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1024` group by `test`.`t2_1024`.`b2` ),
<primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1024` group by `test`.`t2_1024`.`b2` ),
<primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on
distinct_key where ((`test`.`t1_1024`.`a1` = `materialized
subselect`.`group_concat(b1)`)))))
select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select group_concat(b1) from t2_1024 group by b2);
left(a1,7) left(a2,7)
-1 - 01x 2 - 01x
-1 - 02x 2 - 02x
set @@group_concat_max_len = 256;
explain extended select left(a1,7), left(a2,7)
from t1_1024
@@ -850,13 +844,11 @@ id select_type table type possible_keys
1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1024` group by `test`.`t2_1024`.`b2` ),
<primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1024` group by `test`.`t2_1024`.`b2` ),
<primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on
distinct_key where ((`test`.`t1_1024`.`a1` = `materialized
subselect`.`group_concat(b1)`)))))
select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select group_concat(b1) from t2_1024 group by b2);
left(a1,7) left(a2,7)
-1 - 01x 2 - 01x
-1 - 02x 2 - 02x
drop table t1_1024, t2_1024, t3_1024;
set @blob_len = 1025;
set @suffix_len = @blob_len - @prefix_len;
@@ -932,13 +924,11 @@ id select_type table type possible_keys
1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1025` group by `test`.`t2_1025`.`b2` ),
<primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1025` group by `test`.`t2_1025`.`b2` ),
<primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on
distinct_key where ((`test`.`t1_1025`.`a1` = `materialized
subselect`.`group_concat(b1)`)))))
select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select group_concat(b1) from t2_1025 group by b2);
left(a1,7) left(a2,7)
-1 - 01x 2 - 01x
-1 - 02x 2 - 02x
set @@group_concat_max_len = 256;
explain extended select left(a1,7), left(a2,7)
from t1_1025
@@ -947,13 +937,11 @@ id select_type table type possible_keys
1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using filesort
Warnings:
-Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1025` group by `test`.`t2_1025`.`b2` ),
<primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on
distinct_key)))
+Note 1003 select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1025` group by `test`.`t2_1025`.`b2` ),
<primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on
distinct_key where ((`test`.`t1_1025`.`a1` = `materialized
subselect`.`group_concat(b1)`)))))
select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select group_concat(b1) from t2_1025 group by b2);
left(a1,7) left(a2,7)
-1 - 01x 2 - 01x
-1 - 02x 2 - 02x
drop table t1_1025, t2_1025, t3_1025;
create table t1bit (a1 bit(3), a2 bit(3));
create table t2bit (b1 bit(3), b2 bit(3));
@@ -971,7 +959,7 @@ id select_type table type possible_keys
1 PRIMARY t1bit ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2bit ALL NULL NULL NULL NULL 3 100.00
Warnings:
-Note 1003 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`),(`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`)
in ( <materialize> (select `test`.`t2bit`.`b1` AS `b1`,`test`.`t2bit`.`b2` AS `b2`
from `test`.`t2bit` ), <primary_index_lookup>(`test`.`t1bit`.`a1` in <temporary
table> on distinct_key)))
+Note 1003 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`),(`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`)
in ( <materialize> (select `test`.`t2bit`.`b1` AS `b1`,`test`.`t2bit`.`b2` AS `b2`
from `test`.`t2bit` ), <primary_index_lookup>(`test`.`t1bit`.`a1` in <temporary
table> on distinct_key where ((`test`.`t1bit`.`a1` = `materialized subselect`.`b1`)
and (`test`.`t1bit`.`a2` = `materialized subselect`.`b2`)))))
select bin(a1), bin(a2)
from t1bit
where (a1, a2) in (select b1, b2 from t2bit);
@@ -1042,7 +1030,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where
<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select
`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ),
<primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where
<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select
`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ),
<primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key
where ((`test`.`t1`.`a` = `materialized subselect`.`c`)))))
select a from t1 where a in (select c from t2 where d >= 20);
a
2
@@ -1056,7 +1044,7 @@ id select_type table type possible_keys
1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using where; Using index
2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where
<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select
`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ),
<primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where
<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select
`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ),
<primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key
where ((`test`.`t1`.`a` = `materialized subselect`.`c`)))))
select a from t1 where a in (select c from t2 where d >= 20);
a
2
@@ -1070,7 +1058,7 @@ id select_type table type possible_keys
1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using where; Using index
2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where
<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select
`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ),
<primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where
<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select
`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ),
<primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key
where ((`test`.`t1`.`a` = `materialized subselect`.`c`)))))
select a from t1 where a in (select c from t2 where d >= 20);
a
2
@@ -1083,7 +1071,7 @@ id select_type table type possible_keys
1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index
2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having
<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select
`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ),
<primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having
<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select
`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ),
<primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key
where ((`test`.`t1`.`a` = `materialized subselect`.`c`)))))
select a from t1 group by a having a in (select c from t2 where d >= 20);
a
2
@@ -1095,7 +1083,7 @@ id select_type table type possible_keys
1 PRIMARY t1 index NULL it1a 4 NULL 7 100.00 Using index
2 SUBQUERY t2 ALL NULL NULL NULL NULL 7 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having
<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select
`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ),
<primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` having
<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select
`test`.`t2`.`c` AS `c` from `test`.`t2` where (`test`.`t2`.`d` >= 20) ),
<primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key
where ((`test`.`t1`.`a` = `materialized subselect`.`c`)))))
select a from t1 group by a having a in (select c from t2 where d >= 20);
a
2
@@ -1175,3 +1163,20 @@ id select_type table type possible_keys
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');
+set @@optimizer_switch='no_semijoin';
+explain select a,b from t1 where b in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 2
+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;
=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result 2008-08-27 16:48:24 +0000
+++ b/mysql-test/r/subselect_no_mat.result 2008-11-19 15:27:23 +0000
@@ -4371,13 +4371,13 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ),
<primary_index_lookup>(1 in <temporary table> on distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ),
<primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 =
`materialized subselect`.`1`)))))
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY
a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using
filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3)
group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on
distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3)
group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on
distinct_key where ((1 = `materialized subselect`.`1`)))))
DROP TABLE t1;
End of 5.0 tests.
create table t_out (subcase char(3),
=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result 2008-08-27 16:48:24 +0000
+++ b/mysql-test/r/subselect_no_opts.result 2008-11-19 15:27:23 +0000
@@ -4371,13 +4371,13 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ),
<primary_index_lookup>(1 in <temporary table> on distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ),
<primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 =
`materialized subselect`.`1`)))))
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY
a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using
filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3)
group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on
distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3)
group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on
distinct_key where ((1 = `materialized subselect`.`1`)))))
DROP TABLE t1;
End of 5.0 tests.
create table t_out (subcase char(3),
=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result 2008-08-27 16:48:24 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result 2008-11-19 15:27:23 +0000
@@ -1300,7 +1300,7 @@ id select_type table type possible_keys
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
2 SUBQUERY t1 index NULL PRIMARY 4 NULL 4 100.00 Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a`
in <temporary table> on distinct_key)))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a`
in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized
subselect`.`a`)))))
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
@@ -1310,7 +1310,7 @@ id select_type table type possible_keys
1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index
2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ),
<primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ),
<primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key
where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
@@ -1321,7 +1321,7 @@ id select_type table type possible_keys
2 SUBQUERY t3 index PRIMARY PRIMARY 4 NULL 3 100.00 Using index
2 SUBQUERY t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` =
`test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary
table> on distinct_key)))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` =
`test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary
table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))
drop table t1, t2, t3;
create table t1 (a int, b int, index a (a,b));
create table t2 (a int, index a (a));
@@ -1343,7 +1343,7 @@ id select_type table type possible_keys
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a`
in <temporary table> on distinct_key)))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` ), <primary_index_lookup>(`test`.`t2`.`a`
in <temporary table> on distinct_key where ((`test`.`t2`.`a` = `materialized
subselect`.`a`)))))
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
2
@@ -1353,7 +1353,7 @@ id select_type table type possible_keys
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ),
<primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ),
<primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key
where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))
select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
a
2
@@ -1364,7 +1364,7 @@ id select_type table type possible_keys
2 SUBQUERY t3 index a a 5 NULL 3 100.00 Using index
2 SUBQUERY t1 index NULL a 10 NULL 10004 100.00 Using where; Using index; Using join
buffer
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` =
`test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary
table> on distinct_key)))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t3` where (`test`.`t1`.`b` =
`test`.`t3`.`a`) ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary
table> on distinct_key where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))
insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
@@ -1380,7 +1380,7 @@ id select_type table type possible_keys
1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index
2 SUBQUERY t1 index NULL a 10 NULL 10005 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ),
<primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on
distinct_key)))
+Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where
<in_optimizer>(`test`.`t2`.`a`,`test`.`t2`.`a` in ( <materialize> (select
`test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`b` <> 30) ),
<primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key
where ((`test`.`t2`.`a` = `materialized subselect`.`a`)))))
drop table t0, t1, t2, t3;
create table t1 (a int, b int);
create table t2 (a int, b int);
@@ -4371,13 +4371,13 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ),
<primary_index_lookup>(1 in <temporary table> on distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ),
<primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 =
`materialized subselect`.`1`)))))
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY
a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using
filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3)
group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on
distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,1 in (
<materialize> (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3)
group by `test`.`t1`.`a` ), <primary_index_lookup>(1 in <temporary table> on
distinct_key where ((1 = `materialized subselect`.`1`)))))
DROP TABLE t1;
End of 5.0 tests.
create table t_out (subcase char(3),
=== modified file 'mysql-test/t/subselect_mat.test'
--- a/mysql-test/t/subselect_mat.test 2008-10-17 09:46:06 +0000
+++ b/mysql-test/t/subselect_mat.test 2008-11-20 15:34:15 +0000
@@ -831,4 +831,19 @@ select min(a1) from t1 where 7 in (selec
set @@optimizer_switch='no_materialization';
explain select min(a1) from t1 where 7 in (select b1 from t2);
select min(a1) from t1 where 7 in (select b1 from t2);
+drop table t1,t2;
+#
+# BUG#36752 "subquery materialization produces wrong results when comparing different
types"
+#
+create table t1 (a char(2), b varchar(10));
+insert into t1 values ('a', 'aaa');
+insert into t1 values ('aa', 'aaaa');
+
+set @@optimizer_switch='no_semijoin';
+explain select a,b from t1 where b in (select a from t1);
+select a,b from t1 where b in (select a from t1);
+prepare st1 from "select a,b from t1 where b in (select a from t1)";
+execute st1;
+execute st1;
+drop table t1;
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc 2008-11-19 12:55:52 +0000
+++ b/sql/item_subselect.cc 2008-11-20 15:34:15 +0000
@@ -3114,11 +3114,54 @@ bool subselect_hash_sj_engine::init_perm
KEY_PART_INFO *cur_key_part= tmp_key->key_part;
store_key **ref_key= tab->ref.key_copy;
uchar *cur_ref_buff= tab->ref.key_buff;
+
+ /*
+ Create an artificial condition to post-filter those rows matched by index
+ lookups that cannot be distinguished by the index lookup procedure, e.g.
+ because of truncation. Prepared statements execution requires that
+ fix_fields is called for every execution. In order to call fix_fields we
+ need to create a Name_resolution_context and a corresponding TABLE_LIST
+ for the temporary table for the subquery, so that all column references
+ to the materialized subquery table can be resolved correctly.
+ */
+ DBUG_ASSERT(cond == NULL);
+ if (!(cond= new Item_cond_and))
+ DBUG_RETURN(TRUE);
+ /*
+ Table reference for tmp_table that is used to resolve column references
+ (Item_fields) to columns in tmp_table.
+ */
+ TABLE_LIST *tmp_table_ref;
+ if (!(tmp_table_ref= (TABLE_LIST*) thd->calloc(sizeof(TABLE_LIST))))
+ DBUG_RETURN(TRUE);
+
+ tmp_table_ref->init_one_table(NULL, 0, "materialized subselect", 22,
+ "materialized subselect", TL_READ);
+ tmp_table_ref->table= tmp_table;
+
+ /* Name resolution context for all tmp_table columns created below. */
+ Name_resolution_context *context= new Name_resolution_context;
+ context->init();
+ context->first_name_resolution_table=
+ context->last_name_resolution_table= tmp_table_ref;
for (uint i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++)
{
- tab->ref.items[i]= item_in->left_expr->element_index(i);
+ Item_func_eq *eq_cond; /* New equi-join condition for the current column. */
+ /* Item for the corresponding field from the materialized temp table. */
+ Item_field *right_col_item;
int null_count= test(cur_key_part->field->real_maybe_null());
+ tab->ref.items[i]= item_in->left_expr->element_index(i);
+
+ if (!(right_col_item= new Item_field(thd, context, cur_key_part->field)) ||
+ !(eq_cond= new Item_func_eq(tab->ref.items[i], right_col_item)) ||
+ ((Item_cond_and*)cond)->add(eq_cond))
+ {
+ delete cond;
+ cond= NULL;
+ DBUG_RETURN(TRUE);
+ }
+
*ref_key= new store_key_item(thd, cur_key_part->field,
/* TODO:
the NULL byte is taken into account in
@@ -3135,6 +3178,9 @@ bool subselect_hash_sj_engine::init_perm
tab->ref.key_err= 1;
tab->ref.key_parts= tmp_key_parts;
+ if (cond->fix_fields(thd, &cond))
+ DBUG_RETURN(TRUE);
+
DBUG_RETURN(FALSE);
}
@@ -3154,6 +3200,12 @@ bool subselect_hash_sj_engine::init_runt
the subquery if not yet created.
*/
materialize_engine->prepare();
+ /*
+ Repeat name resolution for 'cond' since cond is not part of any
+ clause of the query, and it is not 'fixed' during JOIN::prepare.
+ */
+ if (cond && !cond->fixed && cond->fix_fields(thd, &cond))
+ return TRUE;
/* Let our engine reuse this query plan for materialization. */
materialize_join= materialize_engine->join;
materialize_join->change_result(result);
| Thread | ||
|---|---|---|
| • bzr commit into mysql-6.0-opt branch (timour:2701) Bug#36752 | Timour Katchaounov | 20 Nov |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.016 sec. using MySQL 5.1.14-beta-log