#At file:///home/rl136806/mysql/repo/mysql-work5/ based on revid:tor.didriksen@stripped
3260 Roy Lyseng 2010-10-07
Bug#45174: Incorrectly applied equality propagation caused wrong result
on a query with a materialized semi-join.
Bug#50019: Wrong result for IN-query with materialization.
Bug#52068: Optimizer generates invalid semijoin materialization plan
When a subquery is subject to a semijoin optimization, it's tables
are merged to the outer query and later are treated as regular tables.
This allows a bunch of optimizations to be applied, equality
propagation is among them. Equality propagation is done after query
execution plan is chosen. It substitutes fields from tables being
retrieved later for fields from tables being retrieved earlier.
However, it can't be applied as is to any semijoin table.
The semijoin materialization strategy differs from other semijoin
strategies that the data from materialized semijoin tables isn't used
directly but saved to a temporary table first.
The materialization isn't isolated in a separate step, it is done
inline within the nested loop execution.
When it comes to fetching rows from the first table in the block of
materialized semijoin tables, sub_select() function is called to
materialize the result of the subquery and save it in the
materialized table. Later, data from the materialized table is used
as they were regular table rows.
Due to this we can't substitute fields that belong to the semi-join
for fields from outer query and vice versa.
Example: suppose we have a join order:
ot1 ot2 SJ-Mat(it1 it2 it3) ot3
and equality ot2.col = it1.col = it2.col
If we're looking for best substitute for 'it2.col', we should pick
it1.col and not ot2.col.
For a field that is not in a materialized semijoin we can use any field,
even those that are embedded in a materialized semijoin. This is because
such fields are "copied back" to their original join-tab structures when
the materialized temporary table is being read.
Now we have added another Item_equal::get_first() function that accepts
as a parameter a field being substituted and checks whether it belongs
to a materialized semijoin.
The field to substitute will be from the same materialized semijoin nest
(if supplied field is within such nest), otherwise it will be the first
field in the multiple equality.
The new checks rely on the first_sj_inner_tab and first_sj_inner_tab
fields of the join-tab. These fields are therefore set as soon as
possible after the join strategy is fixed.
Also fixed problem appearing in Bug#52068: When MaterializeScan
semijoin strategy was used and there were one or more outer dependent
tables before the semijoin tables, the scan over the materialized
table was not properly reset for each row of the prefix outer tables.
Also fixed problems with pushdown of SJM-aware predicates during
make_join_select(): wrong predicates were sometimes generated,
make_cond_after_sjm() was called at the wrong position, and
make_cond_after_sjm() was never actually considering the pushed-down
SJM predicates.
mysql-test/include/subquery_sj.inc
Bug#45174: Incorrectly applied equality propagation caused wrong result
on a query with a materialized semi-join.
Bug#50019: Wrong result for IN-query with materialization.
Bug#52068: Optimizer generates invalid semijoin materialization plan
Added new tests.
mysql-test/r/subquery_mat_all.result
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
Bug#45174: Incorrectly applied equality propagation caused wrong result
on a query with a materialized semi-join.
Bug#50019: Wrong result for IN-query with materialization.
Bug#52068: Optimizer generates invalid semijoin materialization plan
Results for three new tests added.
Some tests using semijoin materialization show that where clause
has moved from the outer query into the materialized inner query.
This is caused by the changed call to get_first() in
eliminate_item_equal().
Ex: select * from ot where a in(select b from it where b>0);
The clause "b>0" is now evaluated on the inner query materialization.
Performance-wise this is never worse when using MaterializeScan and
usually better for MaterializeLookup. For the latter strategy, the
best possible solution is probably to evaluate the clause in both
queries, this can be subject for a later feature development.
Another test that applies the same condition to both the outer and
the inner query is added, to show the plan for such types of queries.
Earlier wrong semijoin materialization test results are corrected.
sql/item.cc
Bug#45174: Incorrectly applied equality propagation caused wrong result
on a query with a materialized semi-join.
Calling new get_first() function instead of old.
sql/item_cmpfunc.cc
Bug#45174: Incorrectly applied equality propagation caused wrong result
on a query with a materialized semi-join.
New function Item_equal::get_first() that accepts as argument
a field being substituted.
sql/item_cmpfunc.h
Bug#45174: Incorrectly applied equality propagation caused wrong result
on a query with a materialized semi-join.
New function Item_equal::get_first() that accepts as argument
a field being substituted.
sql/sql_select.cc
Bug#45174: Incorrectly applied equality propagation caused wrong result
on a query with a materialized semi-join.
Bug#50019: Wrong result for IN-query with materialization.
Bug#52068: Optimizer generates invalid semijoin materialization plan
Setting fields first_sj_inner_tab and last_sj_inner_tab moved from
setup_semijoin_dups_elimination() to get_best_combination(), so they
are set as early as possible after join order optimization.
In make_join_select(), the test that determined when to pushdown
SJM-specific predicates was wrong, in addition to approving the
comments.
The logic of eliminate_item_equal() has been simplified and
adjusted so that it generates equalities that are useful also
when the semijoin materialization strategy is being used.
Some simplification was possible by taking advantage of the new
Item_equal::get_first() function.
In sub_select_sjm(), moved code that initializes the scan over the
materialized table so that it is now performed for each scan of
table, instead of only for the first scan.
In make_cond_for_table_from_pred(), a number of comments has been
added, and TAB characters are replaced by spaces.
In make_cond_after_sjm(), make sure that it handles equalities
generated for semijoin materialization (with marker=3).
Otherwise, removed marker optimizations for this function, as
it will only be called once per materialized semijoin nest
in a query. Added comments and removed TAB characters.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_mat_all.result
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
sql/item.cc
sql/item_cmpfunc.cc
sql/item_cmpfunc.h
sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2010-09-20 14:06:02 +0000
+++ b/mysql-test/include/subquery_sj.inc 2010-10-07 15:15:10 +0000
@@ -393,6 +393,9 @@ insert into t3 select A.a + 10*B.a from
explain select * from t3 where a in (select kp1 from t1 where kp1<20);
select * from t3 where a in (select kp1 from t1 where kp1<20);
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+
create table t4 (pk int primary key);
insert into t4 select a from t3;
@@ -637,11 +640,9 @@ drop table t0, t1,t2,t3;
--echo
---echo Test that MaterializeLookup strategy for semijoin,
+--echo Test that neither MaterializeLookup strategy for semijoin,
--echo nor subquery materialization is used when BLOBs are involved
--echo (except when arguments of some functions).
---echo Note: Due to Bug#52068, wrong may occur below if MaterializeScan
---echo strategy is used instead,
--echo
set @prefix_len = 6;
@@ -3326,3 +3327,89 @@ eval explain $query;
eval $query;
DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # Bug#45174: Incorrectly applied equality propagation caused wrong
+--echo # result on a query with a materialized semi-join.
+--echo #
+
+CREATE TABLE t1 (
+ varchar_nokey varchar(1) NOT NULL
+);
+
+INSERT INTO t1 VALUES
+ ('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+ ('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+
+CREATE TABLE t2 (
+ pk int NOT NULL,
+ varchar_key varchar(1) NOT NULL,
+ varchar_nokey varchar(1) NOT NULL,
+ PRIMARY KEY(pk),
+ KEY varchar_key(varchar_key)
+);
+
+INSERT INTO t2 VALUES
+ (11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+ (16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+ (23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+
+let $query=
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+ FROM t2
+ WHERE varchar_nokey < 'n' XOR pk);
+eval $query;
+eval explain $query;
+
+DROP TABLE t1, t2;
+
+--echo # End of the test for bug#45174.
+
+--echo #
+--echo # Bug#50019: Wrong result for IN-query with materialization
+--echo #
+
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+
+let $query=
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+ FROM t2 JOIN t3
+ WHERE t2.i + t3.i = 5);
+eval $query;
+eval explain $query;
+
+DROP TABLE t1,t2,t3;
+
+--echo # End of the test for bug#50019.
+
+--echo #
+--echo # Bug#52068: Optimizer generates invalid semijoin materialization plan
+--echo #
+
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+
+let $query=
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+ FROM it2,it3);
+
+eval $query;
+eval explain $query;
+
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+
+--echo # End of the test for bug#52068.
=== modified file 'mysql-test/r/subquery_mat_all.result'
--- a/mysql-test/r/subquery_mat_all.result 2010-09-08 14:39:38 +0000
+++ b/mysql-test/r/subquery_mat_all.result 2010-10-07 15:15:10 +0000
@@ -262,12 +262,12 @@ where (a1, a2) in (select b1, b2 from t2
(a1, a2) in (select c1, c2 from t3i
where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1i index it1i1,it1i2,it1i3 it1i3 18 NULL 3 100.00 Using where; Using index
-1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 100.00 Using index; Materialize
-1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 100.00 Using index; Start materialize
+1 PRIMARY t1i index it1i1,it1i2,it1i3 it1i3 18 NULL 3 100.00 Using index
+1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index; Materialize
+1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index; Start materialize
1 PRIMARY t3i index it3i1,it3i2,it3i3 it3i3 18 NULL 4 75.00 Using where; Using index; End materialize
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t1i`.`a1` > '0') and (`test`.`t1i`.`a2` > '0'))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`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
@@ -345,13 +345,13 @@ id select_type table type possible_keys
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
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
-7 UNION t1i index it1i1,it1i2,it1i3 it1i3 18 NULL 3 100.00 Using where; Using index
-7 UNION t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 100.00 Using index; Materialize
-7 UNION t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 100.00 Using index; Start materialize
+7 UNION t1i index it1i1,it1i2,it1i3 it1i3 18 NULL 3 100.00 Using index
+7 UNION t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index; Materialize
+7 UNION t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index; Start materialize
7 UNION t3i index it3i1,it3i2,it3i3 it3i3 18 NULL 4 75.00 Using where; Using index; End materialize
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` semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`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` 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 distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) and (`test`.`t1`.`a2` > '0'))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t1i`.`a1` > '0') and (`test`.`t1i`.`a2` > '0')))
+Note 1003 (select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` semi join (`test`.`t2i` join `test`.`t3`) where ((`test`.`t2i`.`b2` = `test`.`t1`.`a2`) and (`test`.`t3`.`c2` = `test`.`t1`.`a2`) and (`test`.`t2i`.`b1` = `test`.`t1`.`a1`) and (`test`.`t3`.`c1` = `test`.`t1`.`a1`) and <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2`.`b1`,`test`.`t2`.`b2` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( <materialize> (select `test`.`t3`.`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` 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 distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) and (`test`.`t1`.`a2` > '0'))) union (select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) semi join (`test`.`t2i` join `test`.`t3i`) where ((`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t2i`.`b2` = `test`.`t1i`.`a2`) and (`test`.`t3i`.`c2` = `test`.`t2i`.`b2`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t2i`.`b1` = `test`.`t1i`.`a1`) and (`test`.`t3i`.`c1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0') and (`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
@@ -397,7 +397,7 @@ a1 = c1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (BNL, regular buffers)
-1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where; Start materialize
1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 18 NULL 5 80.00 Using where; Using index; End materialize
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
3 DEPENDENT UNION t2 ALL NULL NULL NULL NULL 5 100.00 Using where
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2010-10-07 15:15:10 +0000
@@ -2200,6 +2200,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (BNL, regular buffers)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2580,14 +2606,12 @@ id select_type table type possible_keys
1 PRIMARY t0 ALL NULL NULL NULL NULL 10
1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start materialize
1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
-1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End materialize
+1 PRIMARY t3 ref a a 5 test.t1.a 1 Using index; End materialize
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -4902,7 +4926,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4911,8 +4935,7 @@ Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
pk
-1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4932,7 +4955,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4942,7 +4965,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4952,7 +4975,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4972,7 +4995,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4982,7 +5005,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
pk
1
-1
+2
DROP TABLE t1, t2;
# End of Bug#48213
#
@@ -5148,4 +5171,102 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18
+1 PRIMARY t2 ALL varchar_key NULL NULL NULL 15 Using where; Materialize
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End materialize
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Start materialize; Scan
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 End materialize
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result 2010-10-07 15:15:10 +0000
@@ -2204,6 +2204,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (BNL, incremental buffers)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2584,14 +2610,12 @@ id select_type table type possible_keys
1 PRIMARY t0 ALL NULL NULL NULL NULL 10
1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start materialize
1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
-1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End materialize
+1 PRIMARY t3 ref a a 5 test.t1.a 1 Using index; End materialize
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -4906,7 +4930,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4915,8 +4939,7 @@ Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
pk
-1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4936,7 +4959,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4946,7 +4969,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4956,7 +4979,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4976,7 +4999,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4986,7 +5009,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
pk
1
-1
+2
DROP TABLE t1, t2;
# End of Bug#48213
#
@@ -5152,6 +5175,104 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18
+1 PRIMARY t2 ALL varchar_key NULL NULL NULL 15 Using where; Materialize
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End materialize
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Start materialize; Scan
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 End materialize
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result 2010-10-07 15:15:10 +0000
@@ -2204,6 +2204,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (BNL, regular buffers)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2584,14 +2610,12 @@ id select_type table type possible_keys
1 PRIMARY t0 ALL NULL NULL NULL NULL 10
1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start materialize
1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
-1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End materialize
+1 PRIMARY t3 ref a a 5 test.t1.a 1 Using index; End materialize
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -4906,7 +4930,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4915,8 +4939,7 @@ Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
pk
-1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4936,7 +4959,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4946,7 +4969,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4956,7 +4979,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4976,7 +4999,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4986,7 +5009,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
pk
1
-1
+2
DROP TABLE t1, t2;
# End of Bug#48213
#
@@ -5152,6 +5175,104 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18
+1 PRIMARY t2 ALL varchar_key NULL NULL NULL 15 Using where; Materialize
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End materialize
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Start materialize; Scan
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 End materialize
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2010-10-07 15:15:10 +0000
@@ -2199,6 +2199,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; Start temporary; End temporary
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2582,11 +2608,9 @@ id select_type table type possible_keys
1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -5146,4 +5170,102 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18
+1 PRIMARY t2 ref varchar_key varchar_key 3 test.t1.varchar_nokey 2 Using where; Start temporary; End temporary
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+4
+3
+2
+1
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-10-07 15:15:10 +0000
@@ -2203,6 +2203,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Start temporary
+1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; End temporary
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2586,11 +2612,9 @@ id select_type table type possible_keys
1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -5150,6 +5174,104 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Start temporary
+1 PRIMARY t2 ref varchar_key varchar_key 3 test.t1.varchar_nokey 2 Using where; End temporary
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+4
+3
+2
+1
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, incremental buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-10-07 15:15:10 +0000
@@ -2203,6 +2203,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Start temporary
+1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; End temporary
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2586,11 +2612,9 @@ id select_type table type possible_keys
1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -5150,6 +5174,104 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Start temporary
+1 PRIMARY t2 ref varchar_key varchar_key 3 test.t1.varchar_nokey 2 Using where; End temporary
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+4
+3
+2
+1
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2010-10-07 15:15:10 +0000
@@ -2200,6 +2200,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2583,11 +2609,9 @@ id select_type table type possible_keys
1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; FirstMatch(t0)
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -5148,6 +5172,104 @@ a
1
DROP TABLE t1,t2,t3;
#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18
+1 PRIMARY t2 ref varchar_key varchar_key 3 test.t1.varchar_nokey 2 Using where; FirstMatch(t1)
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; FirstMatch(t1)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-10-07 15:15:10 +0000
@@ -2204,6 +2204,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2587,11 +2613,9 @@ id select_type table type possible_keys
1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; FirstMatch(t0)
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -5152,6 +5176,104 @@ a
1
DROP TABLE t1,t2,t3;
#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18
+1 PRIMARY t2 ref varchar_key varchar_key 3 test.t1.varchar_nokey 2 Using where; FirstMatch(t1)
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; FirstMatch(t1)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, incremental buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-10-07 15:15:10 +0000
@@ -2204,6 +2204,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; FirstMatch(t3)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2587,11 +2613,9 @@ id select_type table type possible_keys
1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; FirstMatch(t0)
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -5152,6 +5176,104 @@ a
1
DROP TABLE t1,t2,t3;
#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18
+1 PRIMARY t2 ref varchar_key varchar_key 3 test.t1.varchar_nokey 2 Using where; FirstMatch(t1)
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; FirstMatch(t1)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2010-10-07 15:15:10 +0000
@@ -2200,6 +2200,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (BNL, regular buffers)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2583,11 +2609,9 @@ id select_type table type possible_keys
1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -5151,4 +5175,102 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18
+1 PRIMARY t2 ref varchar_key varchar_key 3 test.t1.varchar_nokey 2 Using where; Start temporary; End temporary
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+4
+3
+2
+1
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-10-07 15:15:10 +0000
@@ -2204,6 +2204,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (BNL, incremental buffers)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2587,11 +2613,9 @@ id select_type table type possible_keys
1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -5155,6 +5179,104 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Start temporary
+1 PRIMARY t2 ref varchar_key varchar_key 3 test.t1.varchar_nokey 2 Using where; End temporary
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+4
+3
+2
+1
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, incremental buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-10-07 15:15:10 +0000
@@ -2204,6 +2204,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer (BNL, regular buffers)
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2587,11 +2613,9 @@ id select_type table type possible_keys
1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -5155,6 +5179,104 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Start temporary
+1 PRIMARY t2 ref varchar_key varchar_key 3 test.t1.varchar_nokey 2 Using where; End temporary
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+4
+3
+2
+1
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2010-10-07 15:15:10 +0000
@@ -2176,7 +2176,7 @@ create table t3 (a int);
insert into t3 select A.a + 10*B.a from t0 A, t0 B;
explain select * from t3 where a in (select kp1 from t1 where kp1<20);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100
1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; Materialize
select * from t3 where a in (select kp1 from t1 where kp1<20);
a
@@ -2200,12 +2200,38 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; Materialize
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
(select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100
1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Start materialize
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; End materialize
select * from t3 where a in
@@ -2580,14 +2606,12 @@ id select_type table type possible_keys
1 PRIMARY t0 ALL NULL NULL NULL NULL 10
1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start materialize
1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
-1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End materialize
+1 PRIMARY t3 ref a a 5 test.t1.a 1 Using index; End materialize
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -2619,32 +2643,30 @@ explain extended select left(a1,7), left
from t1_16
where a1 in (select b1 from t2_16 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+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` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2_16`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_16
where a1 in (select b1 from t2_16 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_16
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+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` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2_16`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_16
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_16
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -2749,32 +2771,30 @@ explain extended select left(a1,7), left
from t1_512
where a1 in (select b1 from t2_512 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+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` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t2_512`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_512
where a1 in (select b1 from t2_512 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_512
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+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` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t2_512`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_512
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_512
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -2844,32 +2864,30 @@ explain extended select left(a1,7), left
from t1_513
where a1 in (select b1 from t2_513 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_513 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_513 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_513 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_513 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
Warnings:
-Note 1003 select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t1_513`.`a1` > '0'))
+Note 1003 select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t2_513`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_513
where a1 in (select b1 from t2_513 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_513
where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_513 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_513 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_513 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_513 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
Warnings:
-Note 1003 select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b2` = `test`.`t1_513`.`a2`) and (`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t1_513`.`a1` > '0'))
+Note 1003 select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b2` = `test`.`t1_513`.`a2`) and (`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t2_513`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_513
where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_513
where a1 in (select substring(b1,1,513) from t2_513 where b1 > '0');
@@ -2926,32 +2944,30 @@ explain extended select left(a1,7), left
from t1_1024
where a1 in (select b1 from t2_1024 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+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` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t2_1024`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select b1 from t2_1024 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1024
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+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` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t2_1024`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_1024
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -3021,32 +3037,30 @@ explain extended select left(a1,7), left
from t1_1025
where a1 in (select b1 from t2_1025 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+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` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t2_1025`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select b1 from t2_1025 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1025
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+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` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t2_1025`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_1025
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -4912,7 +4926,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4921,8 +4935,7 @@ Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
pk
-1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Start temporary
@@ -4942,7 +4955,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4952,7 +4965,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4962,7 +4975,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Start temporary
@@ -4982,7 +4995,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4992,7 +5005,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
pk
1
-1
+2
DROP TABLE t1, t2;
# End of Bug#48213
#
@@ -5158,4 +5171,102 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18
+1 PRIMARY t2 ALL varchar_key NULL NULL NULL 15 Using where; Materialize
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End materialize
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Start materialize; Scan
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 End materialize
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2010-10-07 15:15:10 +0000
@@ -2180,7 +2180,7 @@ create table t3 (a int);
insert into t3 select A.a + 10*B.a from t0 A, t0 B;
explain select * from t3 where a in (select kp1 from t1 where kp1<20);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100
1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; Materialize
select * from t3 where a in (select kp1 from t1 where kp1<20);
a
@@ -2204,12 +2204,38 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; Materialize
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
(select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100
1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Start materialize
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; End materialize
select * from t3 where a in
@@ -2584,14 +2610,12 @@ id select_type table type possible_keys
1 PRIMARY t0 ALL NULL NULL NULL NULL 10
1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start materialize
1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
-1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End materialize
+1 PRIMARY t3 ref a a 5 test.t1.a 1 Using index; End materialize
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -2623,32 +2647,30 @@ explain extended select left(a1,7), left
from t1_16
where a1 in (select b1 from t2_16 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+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` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2_16`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_16
where a1 in (select b1 from t2_16 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_16
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+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` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2_16`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_16
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_16
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -2753,32 +2775,30 @@ explain extended select left(a1,7), left
from t1_512
where a1 in (select b1 from t2_512 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+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` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t2_512`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_512
where a1 in (select b1 from t2_512 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_512
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+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` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t2_512`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_512
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_512
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -2848,32 +2868,30 @@ explain extended select left(a1,7), left
from t1_513
where a1 in (select b1 from t2_513 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_513 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_513 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_513 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_513 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
Warnings:
-Note 1003 select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t1_513`.`a1` > '0'))
+Note 1003 select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t2_513`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_513
where a1 in (select b1 from t2_513 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_513
where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_513 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_513 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_513 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_513 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
Warnings:
-Note 1003 select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b2` = `test`.`t1_513`.`a2`) and (`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t1_513`.`a1` > '0'))
+Note 1003 select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b2` = `test`.`t1_513`.`a2`) and (`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t2_513`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_513
where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_513
where a1 in (select substring(b1,1,513) from t2_513 where b1 > '0');
@@ -2930,32 +2948,30 @@ explain extended select left(a1,7), left
from t1_1024
where a1 in (select b1 from t2_1024 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+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` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t2_1024`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select b1 from t2_1024 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1024
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+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` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t2_1024`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_1024
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -3025,32 +3041,30 @@ explain extended select left(a1,7), left
from t1_1025
where a1 in (select b1 from t2_1025 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+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` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t2_1025`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select b1 from t2_1025 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1025
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+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` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t2_1025`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_1025
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -4916,7 +4930,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4925,8 +4939,7 @@ Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
pk
-1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Start temporary
@@ -4946,7 +4959,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4956,7 +4969,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4966,7 +4979,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Start temporary
@@ -4986,7 +4999,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4996,7 +5009,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
pk
1
-1
+2
DROP TABLE t1, t2;
# End of Bug#48213
#
@@ -5162,6 +5175,104 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18
+1 PRIMARY t2 ALL varchar_key NULL NULL NULL 15 Using where; Materialize
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End materialize
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Start materialize; Scan
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 End materialize
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2010-10-07 15:15:10 +0000
@@ -2180,7 +2180,7 @@ create table t3 (a int);
insert into t3 select A.a + 10*B.a from t0 A, t0 B;
explain select * from t3 where a in (select kp1 from t1 where kp1<20);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100
1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; Materialize
select * from t3 where a in (select kp1 from t1 where kp1<20);
a
@@ -2204,12 +2204,38 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; Materialize
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
(select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100
1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Start materialize
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; End materialize
select * from t3 where a in
@@ -2584,14 +2610,12 @@ id select_type table type possible_keys
1 PRIMARY t0 ALL NULL NULL NULL NULL 10
1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start materialize
1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
-1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End materialize
+1 PRIMARY t3 ref a a 5 test.t1.a 1 Using index; End materialize
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -2623,32 +2647,30 @@ explain extended select left(a1,7), left
from t1_16
where a1 in (select b1 from t2_16 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+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` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2_16`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_16
where a1 in (select b1 from t2_16 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_16
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_16 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_16 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t1_16`.`a1` > '0'))
+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` semi join (`test`.`t2_16`) where ((`test`.`t2_16`.`b2` = `test`.`t1_16`.`a2`) and (`test`.`t2_16`.`b1` = `test`.`t1_16`.`a1`) and (`test`.`t2_16`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_16
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_16
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -2753,32 +2775,30 @@ explain extended select left(a1,7), left
from t1_512
where a1 in (select b1 from t2_512 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+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` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t2_512`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_512
where a1 in (select b1 from t2_512 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_512
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_512 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_512 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t1_512`.`a1` > '0'))
+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` semi join (`test`.`t2_512`) where ((`test`.`t2_512`.`b2` = `test`.`t1_512`.`a2`) and (`test`.`t2_512`.`b1` = `test`.`t1_512`.`a1`) and (`test`.`t2_512`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_512
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_512
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -2848,32 +2868,30 @@ explain extended select left(a1,7), left
from t1_513
where a1 in (select b1 from t2_513 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_513 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_513 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_513 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_513 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
Warnings:
-Note 1003 select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t1_513`.`a1` > '0'))
+Note 1003 select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t2_513`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_513
where a1 in (select b1 from t2_513 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_513
where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_513 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_513 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_513 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_513 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
Warnings:
-Note 1003 select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b2` = `test`.`t1_513`.`a2`) and (`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t1_513`.`a1` > '0'))
+Note 1003 select left(`test`.`t1_513`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_513`.`a2`,7) AS `left(a2,7)` from `test`.`t1_513` semi join (`test`.`t2_513`) where ((`test`.`t2_513`.`b2` = `test`.`t1_513`.`a2`) and (`test`.`t2_513`.`b1` = `test`.`t1_513`.`a1`) and (`test`.`t2_513`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_513
where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_513
where a1 in (select substring(b1,1,513) from t2_513 where b1 > '0');
@@ -2930,32 +2948,30 @@ explain extended select left(a1,7), left
from t1_1024
where a1 in (select b1 from t2_1024 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+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` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t2_1024`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select b1 from t2_1024 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1024
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_1024 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1024 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t1_1024`.`a1` > '0'))
+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` semi join (`test`.`t2_1024`) where ((`test`.`t2_1024`.`b2` = `test`.`t1_1024`.`a2`) and (`test`.`t2_1024`.`b1` = `test`.`t1_1024`.`a1`) and (`test`.`t2_1024`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_1024
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -3025,32 +3041,30 @@ explain extended select left(a1,7), left
from t1_1025
where a1 in (select b1 from t2_1025 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+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` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t2_1025`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select b1 from t2_1025 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1025
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00 Using where
-1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Materialize; Scan
+1 PRIMARY t1_1025 ALL NULL NULL NULL NULL 3 100.00
+1 PRIMARY t2_1025 ALL NULL NULL NULL NULL 3 100.00 Using where; Materialize; Scan
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` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t1_1025`.`a1` > '0'))
+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` semi join (`test`.`t2_1025`) where ((`test`.`t2_1025`.`b2` = `test`.`t1_1025`.`a2`) and (`test`.`t2_1025`.`b1` = `test`.`t1_1025`.`a1`) and (`test`.`t2_1025`.`b1` > '0'))
select left(a1,7), left(a2,7)
from t1_1025
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
left(a1,7) left(a2,7)
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
-1 - 00x 2 - 00x
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -4916,7 +4930,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4925,8 +4939,7 @@ Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
pk
-1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Start temporary
@@ -4946,7 +4959,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4956,7 +4969,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4966,7 +4979,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Start temporary
@@ -4986,7 +4999,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
pk
1
-1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4996,7 +5009,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
pk
1
-1
+2
DROP TABLE t1, t2;
# End of Bug#48213
#
@@ -5162,6 +5175,104 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18
+1 PRIMARY t2 ALL varchar_key NULL NULL NULL 15 Using where; Materialize
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End materialize
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Start materialize; Scan
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 End materialize
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2010-10-07 15:15:10 +0000
@@ -2415,6 +2415,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+2 SUBQUERY t1 range kp1 kp1 5 NULL 48 Using where; Using index
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2798,11 +2824,9 @@ id select_type table type possible_keys
2 SUBQUERY t3 ref a a 5 test.t1.a 1 Using index
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -5369,4 +5393,102 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Using where
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 15 Using where
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 10
+2 SUBQUERY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
+2 SUBQUERY it2 ALL NULL NULL NULL NULL 4
+2 SUBQUERY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2010-10-07 15:15:10 +0000
@@ -2348,6 +2348,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+2 DEPENDENT SUBQUERY t1 index_subquery kp1 kp1 5 func 1 Using index; Using where
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2731,11 +2757,9 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY t3 ref a a 5 test.t2.a 1 Using where; Using index
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -5295,4 +5319,102 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Using where
+2 DEPENDENT SUBQUERY t2 index_subquery varchar_key varchar_key 3 func 2 Using where
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY it3 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result 2010-10-07 15:15:10 +0000
@@ -2352,6 +2352,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+2 DEPENDENT SUBQUERY t1 index_subquery kp1 kp1 5 func 1 Using index; Using where
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2735,11 +2761,9 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY t3 ref a a 5 test.t2.a 1 Using where; Using index
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -5299,6 +5323,104 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Using where
+2 DEPENDENT SUBQUERY t2 index_subquery varchar_key varchar_key 3 func 2 Using where
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY it3 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, incremental buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result 2010-09-20 14:06:02 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result 2010-10-07 15:15:10 +0000
@@ -2352,6 +2352,32 @@ a
17
18
19
+explain select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
+2 DEPENDENT SUBQUERY t1 index_subquery kp1 kp1 5 func 1 Using index; Using where
+select * from t3 where a in (select kp1 from t1 where kp1<20) and a<20;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
create table t4 (pk int primary key);
insert into t4 select a from t3;
explain select * from t3 where a in
@@ -2735,11 +2761,9 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY t3 ref a a 5 test.t2.a 1 Using where; Using index
drop table t0, t1,t2,t3;
-Test that MaterializeLookup strategy for semijoin,
+Test that neither MaterializeLookup strategy for semijoin,
nor subquery materialization is used when BLOBs are involved
(except when arguments of some functions).
-Note: Due to Bug#52068, wrong may occur below if MaterializeScan
-strategy is used instead,
set @prefix_len = 6;
set @blob_len = 16;
@@ -5299,6 +5323,104 @@ a
1
1
DROP TABLE t1,t2,t3;
+#
+# Bug#45174: Incorrectly applied equality propagation caused wrong
+# result on a query with a materialized semi-join.
+#
+CREATE TABLE t1 (
+varchar_nokey varchar(1) NOT NULL
+);
+INSERT INTO t1 VALUES
+('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
+('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
+CREATE TABLE t2 (
+pk int NOT NULL,
+varchar_key varchar(1) NOT NULL,
+varchar_nokey varchar(1) NOT NULL,
+PRIMARY KEY(pk),
+KEY varchar_key(varchar_key)
+);
+INSERT INTO t2 VALUES
+(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
+(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
+(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
+SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+varchar_nokey
+explain SELECT varchar_nokey
+FROM t1
+WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
+FROM t2
+WHERE varchar_nokey < 'n' XOR pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Using where
+2 DEPENDENT SUBQUERY t2 index_subquery varchar_key varchar_key 3 func 2 Using where
+DROP TABLE t1, t2;
+# End of the test for bug#45174.
+#
+# Bug#50019: Wrong result for IN-query with materialization
+#
+CREATE TABLE t1(i INT);
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t2(i INT);
+INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+CREATE TABLE t3(i INT);
+INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+i
+1
+2
+3
+4
+explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
+FROM t2 JOIN t3
+WHERE t2.i + t3.i = 5);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE t1,t2,t3;
+# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY it3 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2010-09-28 15:17:29 +0000
+++ b/sql/item.cc 2010-10-07 15:15:10 +0000
@@ -5021,8 +5021,8 @@ Item *Item_field::replace_equal_field(uc
return this;
return const_item;
}
- Item_field *subst= item_equal->get_first();
- if (subst && field->table != subst->field->table && !field->eq(subst->field))
+ Item_field *subst= item_equal->get_first(this);
+ if (field->table != subst->field->table && !field->eq(subst->field))
return subst;
}
return this;
=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc 2010-09-28 15:17:29 +0000
+++ b/sql/item_cmpfunc.cc 2010-10-07 15:15:10 +0000
@@ -5816,3 +5816,101 @@ void Item_equal::print(String *str, enum
str->append(')');
}
+
+/**
+ Get the first field of multiple equality, use for semantic checking.
+
+ @retval First field in the multiple equality.
+*/
+
+Item_field* Item_equal::get_first()
+{
+ return fields.head();
+}
+
+/**
+ Get the first equal field of multiple equality, use when execution order
+ must be considered (see details below).
+
+ @param field field to get substitution field for, which must be
+ present within the multiple equality itself.
+
+ @retval Found first field in the multiple equality.
+
+ @details Get the first field of multiple equality that is equal to the
+ given field. In order to make semijoin materialization strategy work
+ correctly we can't propagate equal fields between a materialized semijoin
+ and the outer query (or any other semijoin) unconditionally.
+ Thus the field is returned according to following rules:
+
+ 1) If the given field belongs to a materialized semijoin then the
+ first field in multiple equality which belong to the same semijoin
+ is returned.
+ 2) If the given field doesn't belong to a materialized semijoin then
+ the first field in the multiple equality is returned.
+*/
+
+Item_field* Item_equal::get_first(Item_field *field)
+{
+ DBUG_ASSERT(field != NULL);
+
+ const JOIN_TAB *field_tab= field->field->table->reginfo.join_tab;
+
+ if (sj_is_materialize_strategy(field_tab->get_sj_strategy()))
+ {
+ /*
+ It's a field from a materialized semijoin. We can substitute it only
+ with a field from the same semijoin.
+
+ Example: suppose we have a join order:
+
+ ot1 ot2 SJM(it1 it2 it3) ot3
+
+ and equality ot2.col = it1.col = it2.col
+
+ If we're looking for best substitute for 'it2.col', we must pick it1.col
+ and not ot2.col. it2.col is evaluated while performing materialization,
+ when the outer tables are not available in the execution.
+ */
+ List_iterator<Item_field> it(fields);
+ Item_field *item;
+ const JOIN_TAB *first= field_tab->first_sj_inner_tab;
+ const JOIN_TAB *last= field_tab->last_sj_inner_tab;
+
+ while ((item= it++))
+ {
+ if (item->field->table->reginfo.join_tab >= first &&
+ item->field->table->reginfo.join_tab <= last)
+ {
+ return item;
+ }
+ }
+ }
+ else
+ {
+ /*
+ The field is not in a materialized semijoin nest. We can return
+ the first field in the multiple equality.
+
+ Example: suppose we have a join order with MaterializeLookup:
+
+ ot1 ot2 SJM-Lookup(it1 it2)
+
+ Here we should always pick the first field in the multiple equality,
+ as this will be present before all other dependent fields.
+
+ Example: suppose we have a join order with MaterializeScan:
+
+ SJM-Scan(it1 it2) ot1 ot2
+
+ and equality ot2.col = ot1.col = it2.col.
+
+ When looking for best substitute for 'ot2.col', we can pick it2.col,
+ because when we run the scan, column values from the inner materialized
+ tables will be copied back to the column buffers for it1 and it2.
+ */
+ return fields.head();
+ }
+ DBUG_ASSERT(FALSE); // Should never get here.
+ return NULL;
+}
=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h 2010-08-12 00:26:10 +0000
+++ b/sql/item_cmpfunc.h 2010-10-07 15:15:10 +0000
@@ -1634,7 +1634,8 @@ public:
void add(Item_field *f);
uint members();
bool contains(Field *field);
- Item_field* get_first() { return fields.head(); }
+ Item_field* get_first();
+ Item_field* get_first(Item_field *field);
void merge(Item_equal *item);
void update_const();
enum Functype functype() const { return MULT_EQUAL_FUNC; }
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-09-30 14:53:11 +0000
+++ b/sql/sql_select.cc 2010-10-07 15:15:10 +0000
@@ -1414,13 +1414,16 @@ bool might_do_join_buffering(uint join_c
setup_sj_materialization() (todo: can't we move that to here also?)
*/
-int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
- uint no_jbuf_after)
+bool setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
+ uint no_jbuf_after)
{
uint tableno;
THD *thd= join->thd;
DBUG_ENTER("setup_semijoin_dups_elimination");
+ if (join->select_lex->sj_nests.is_empty())
+ DBUG_RETURN(FALSE);
+
for (tableno= join->const_tables ; tableno < join->tables; )
{
JOIN_TAB *tab=join->join_tab + tableno;
@@ -1661,21 +1664,6 @@ int setup_semijoin_dups_elimination(JOIN
break;
}
}
- /*
- Remember the first and last semijoin inner tables; this serves to tell
- a JOIN_TAB's semijoin strategy (like in check_join_cache_usage()).
- */
- JOIN_TAB *last_sj_inner=
- (pos->sj_strategy == SJ_OPT_DUPS_WEEDOUT) ?
- /* Range may end with non-inner table so cannot set last_sj_inner_tab */
- NULL : last_sj_tab;
- for (JOIN_TAB *tab_in_range= tab;
- tab_in_range <= last_sj_tab;
- tab_in_range++)
- {
- tab_in_range->first_sj_inner_tab= tab;
- tab_in_range->last_sj_inner_tab= last_sj_inner;
- }
}
DBUG_RETURN(FALSE);
}
@@ -4248,14 +4236,18 @@ bool find_eq_ref_candidate(TABLE *table,
using Materialization or LooseScan to execute it.
RETURN
- 0 - OK
- 1 - Out of memory error
+ FALSE - OK
+ TRUE - Out of memory error
*/
-int pull_out_semijoin_tables(JOIN *join)
+bool pull_out_semijoin_tables(JOIN *join)
{
TABLE_LIST *sj_nest;
DBUG_ENTER("pull_out_semijoin_tables");
+
+ if (join->select_lex->sj_nests.is_empty())
+ DBUG_RETURN(FALSE);
+
List_iterator<TABLE_LIST> sj_list_it(join->select_lex->sj_nests);
/* Try pulling out of the each of the semi-joins */
@@ -4338,7 +4330,8 @@ int pull_out_semijoin_tables(JOIN *join)
pointers.
*/
child_li.remove();
- upper_join_list->push_back(tbl);
+ if (upper_join_list->push_back(tbl))
+ DBUG_RETURN(TRUE);
tbl->join_list= upper_join_list;
tbl->embedding= sj_nest->embedding;
}
@@ -4359,7 +4352,7 @@ int pull_out_semijoin_tables(JOIN *join)
join->thd->restore_active_arena(arena, &backup);
}
}
- DBUG_RETURN(0);
+ DBUG_RETURN(FALSE);
}
@@ -8415,15 +8408,18 @@ prev_record_reads(JOIN *join, uint idx,
static bool fix_semijoin_strategies_for_picked_join_order(JOIN *join)
{
- uint tablenr;
+ uint tableno;
table_map remaining_tables= 0;
table_map handled_tabs= 0;
DBUG_ENTER("fix_semijoin_strategies_for_picked_join_order");
- for (tablenr= join->tables - 1 ; tablenr != join->const_tables - 1; tablenr--)
+ if (join->select_lex->sj_nests.is_empty())
+ DBUG_RETURN(FALSE);
+
+ for (tableno= join->tables - 1; tableno != join->const_tables - 1; tableno--)
{
- POSITION *pos= join->best_positions + tablenr;
+ POSITION *pos= join->best_positions + tableno;
JOIN_TAB *s= pos->table;
TABLE_LIST *emb_sj_nest= s->emb_sj_nest;
uint first;
@@ -8460,7 +8456,7 @@ static bool fix_semijoin_strategies_for_
*/
memcpy(pos - table_count + 1, emb_sj_nest->nested_join->sjm.positions,
sizeof(POSITION) * table_count);
- first= tablenr - table_count + 1;
+ first= tableno - table_count + 1;
join->best_positions[first].n_sj_tables= table_count;
join->best_positions[first].sj_strategy= SJ_OPT_MATERIALIZE_LOOKUP;
@@ -8498,12 +8494,12 @@ static bool fix_semijoin_strategies_for_
uint i;
table_map rem_tables= remaining_tables;
- for (i= tablenr; i != (first + table_count - 1); i--)
+ for (i= tableno; i != (first + table_count - 1); i--)
rem_tables |= join->best_positions[i].table->table->map;
POSITION dummy;
join->cur_sj_inner_tables= 0;
- for (i= first + table_count; i <= tablenr; i++)
+ for (i= first + table_count; i <= tableno; i++)
{
best_access_path(join, join->best_positions[i].table, rem_tables, i, FALSE,
prefix_rec_count, join->best_positions + i, &dummy);
@@ -8517,14 +8513,14 @@ static bool fix_semijoin_strategies_for_
{
first= pos->first_firstmatch_table;
join->best_positions[first].sj_strategy= SJ_OPT_FIRST_MATCH;
- join->best_positions[first].n_sj_tables= tablenr - first + 1;
+ join->best_positions[first].n_sj_tables= tableno - first + 1;
POSITION dummy; // For loose scan paths
double record_count= (first== join->const_tables)? 1.0:
- join->best_positions[tablenr - 1].prefix_record_count;
+ join->best_positions[tableno - 1].prefix_record_count;
table_map rem_tables= remaining_tables;
uint idx;
- for (idx= first; idx <= tablenr; idx++)
+ for (idx= first; idx <= tableno; idx++)
{
rem_tables |= join->best_positions[idx].table->table->map;
}
@@ -8533,7 +8529,7 @@ static bool fix_semijoin_strategies_for_
join buffering
*/
join->cur_sj_inner_tables= 0;
- for (idx= first; idx <= tablenr; idx++)
+ for (idx= first; idx <= tableno; idx++)
{
if (join->best_positions[idx].use_join_buffer)
{
@@ -8551,18 +8547,18 @@ static bool fix_semijoin_strategies_for_
POSITION *first_pos= join->best_positions + first;
POSITION loose_scan_pos; // For loose scan paths
double record_count= (first== join->const_tables)? 1.0:
- join->best_positions[tablenr - 1].prefix_record_count;
+ join->best_positions[tableno - 1].prefix_record_count;
table_map rem_tables= remaining_tables;
uint idx;
- for (idx= first; idx <= tablenr; idx++)
+ for (idx= first; idx <= tableno; idx++)
rem_tables |= join->best_positions[idx].table->table->map;
/*
Re-run best_access_path to produce best access methods that do not use
join buffering
*/
join->cur_sj_inner_tables= 0;
- for (idx= first; idx <= tablenr; idx++)
+ for (idx= first; idx <= tableno; idx++)
{
if (join->best_positions[idx].use_join_buffer || (idx == first))
{
@@ -8587,7 +8583,7 @@ static bool fix_semijoin_strategies_for_
*/
first= pos->first_dupsweedout_table;
join->best_positions[first].sj_strategy= SJ_OPT_DUPS_WEEDOUT;
- join->best_positions[first].n_sj_tables= tablenr - first + 1;
+ join->best_positions[first].n_sj_tables= tableno - first + 1;
}
uint i_end= first + join->best_positions[first].n_sj_tables;
@@ -8602,7 +8598,7 @@ static bool fix_semijoin_strategies_for_
handled_tabs |= join->best_positions[i].table->table->map;
}
- if (tablenr != first)
+ if (tableno != first)
pos->sj_strategy= SJ_OPT_NONE;
remaining_tables |= s->table->map;
}
@@ -8639,16 +8635,13 @@ static bool fix_semijoin_strategies_for_
static bool get_best_combination(JOIN *join)
{
- uint i,tablenr;
table_map used_tables;
- JOIN_TAB *join_tab,*j;
KEYUSE *keyuse;
- uint table_count;
+ uint table_count= join->tables;
THD *thd=join->thd;
DBUG_ENTER("get_best_combination");
- table_count=join->tables;
- if (!(join->join_tab= join_tab= new (thd->mem_root) JOIN_TAB[table_count]))
+ if (!(join->join_tab= new (thd->mem_root) JOIN_TAB[table_count]))
DBUG_RETURN(TRUE);
join->full_join=0;
@@ -8658,11 +8651,12 @@ static bool get_best_combination(JOIN *j
if (fix_semijoin_strategies_for_picked_join_order(join))
DBUG_RETURN(TRUE);
- for (j=join_tab, tablenr=0 ; tablenr < table_count ; tablenr++,j++)
+ for (uint tableno= 0; tableno < table_count; tableno++)
{
+ JOIN_TAB *j= join->join_tab + tableno;
TABLE *form;
- *j= *join->best_positions[tablenr].table;
- form=join->all_tables[tablenr]=j->table;
+ *j= *join->best_positions[tableno].table;
+ form=join->all_tables[tableno]= j->table;
used_tables|= form->map;
form->reginfo.join_tab=j;
if (!*j->on_expr_ref)
@@ -8672,31 +8666,69 @@ static bool get_best_combination(JOIN *j
if (j->type == JT_CONST)
continue; // Handled in make_join_stat..
-
j->loosescan_match_tab= NULL; //non-nulls will be set later
j->ref.key = -1;
j->ref.key_parts=0;
-
if (j->type == JT_SYSTEM)
continue;
- if (j->keys.is_clear_all() || !(keyuse= join->best_positions[tablenr].key) ||
- (join->best_positions[tablenr].sj_strategy == SJ_OPT_LOOSE_SCAN))
+ if (j->keys.is_clear_all() ||
+ !(keyuse= join->best_positions[tableno].key) ||
+ (join->best_positions[tableno].sj_strategy == SJ_OPT_LOOSE_SCAN))
{
j->type=JT_ALL;
- j->index= join->best_positions[tablenr].loosescan_key;
- if (tablenr != join->const_tables)
+ j->index= join->best_positions[tableno].loosescan_key;
+ if (tableno != join->const_tables)
join->full_join=1;
}
else if (create_ref_for_key(join, j, keyuse, used_tables))
DBUG_RETURN(TRUE); // Something went wrong
}
- for (i=0 ; i < table_count ; i++)
- join->map2table[join->join_tab[i].table->tablenr]=join->join_tab+i;
+ for (uint tableno= 0; tableno < table_count; tableno++)
+ join->map2table[join->join_tab[tableno].table->tablenr]=
+ join->join_tab + tableno;
+
update_depend_map(join);
- DBUG_RETURN(0);
+
+ for (uint tableno= join->const_tables; tableno < table_count; )
+ {
+ JOIN_TAB *tab= join->join_tab + tableno;
+ const POSITION *pos= join->best_positions + tableno;
+
+ switch (pos->sj_strategy)
+ {
+ case SJ_OPT_NONE:
+ tableno++;
+ break;
+ case SJ_OPT_MATERIALIZE_LOOKUP:
+ case SJ_OPT_MATERIALIZE_SCAN:
+ case SJ_OPT_LOOSE_SCAN:
+ case SJ_OPT_DUPS_WEEDOUT:
+ case SJ_OPT_FIRST_MATCH:
+ /*
+ Remember the first and last semijoin inner tables; this serves to tell
+ a JOIN_TAB's semijoin strategy (like in check_join_cache_usage()).
+ */
+ JOIN_TAB *last_sj_tab= tab + pos->n_sj_tables - 1;
+ JOIN_TAB *last_sj_inner=
+ (pos->sj_strategy == SJ_OPT_DUPS_WEEDOUT) ?
+ /* Range may end with non-inner table so cannot set last_sj_inner_tab */
+ NULL : last_sj_tab;
+ for (JOIN_TAB *tab_in_range= tab;
+ tab_in_range <= last_sj_tab;
+ tab_in_range++)
+ {
+ tab_in_range->first_sj_inner_tab= tab;
+ tab_in_range->last_sj_inner_tab= last_sj_inner;
+ }
+ tableno+= pos->n_sj_tables;
+ break;
+ }
+ }
+
+ DBUG_RETURN(FALSE);
}
@@ -9678,32 +9710,32 @@ static bool make_join_select(JOIN *join,
DBUG_ASSERT(save_used_tables ? tab->emb_sj_nest != NULL : TRUE);
- if (save_used_tables && !(used_tables &
- ~(tab->emb_sj_nest->sj_inner_tables |
- join->const_table_map | PSEUDO_TABLE_BITS)))
+ /*
+ 1. We are inside a materialized semijoin nest, and
+ 2. All inner tables of the nest are covered.
+ */
+ if (save_used_tables && // 1
+ !(tab->emb_sj_nest->sj_inner_tables & ~used_tables)) // 2
{
/*
- We have reached the end of semi join nest. That is, the join order
- looks like this:
+ The join order now looks like this:
+
+ ot1 ... otI SJM(it1 ... itN) otI+1 ... otM
+ ^
+ \-we're here
+ At this point, we have generated a condition that can be checked
+ when we have all of the sj-inner tables (it1 ... itN).
+ This will be used while doing materialization.
- outer_tbl1 SJ-Materialize(inner_tbl1 ... inner_tblN) outer_tbl ...
- ^
- \-we're here
- At this point, we need to produce two conditions
- - A condition that can be checked when we have all of the sj-inner
- tables (inner_tbl1 ... inner_tblN). This will be used while doing
- materialization.
- - A condition that can be checked when we have all of the tables
- in the prefix (both inner and outer).
+ In addition, we need a condition that can be checked when we have
+ all of the tables in the prefix (both inner and outer):
*/
tab->emb_sj_nest->sj_mat_exec->join_cond=
- cond ?
- make_cond_after_sjm(cond, cond, save_used_tables, used_tables):
- NULL;
+ make_cond_after_sjm(cond, cond, save_used_tables, used_tables);
+
used_tables= save_used_tables | used_tables;
save_used_tables= 0;
}
-
}
}
DBUG_RETURN(0);
@@ -10866,8 +10898,7 @@ make_join_readinfo(JOIN *join, ulonglong
uint last_sjm_table= MAX_TABLES;
DBUG_ENTER("make_join_readinfo");
- if (!join->select_lex->sj_nests.is_empty() &&
- setup_semijoin_dups_elimination(join, options, no_jbuf_after))
+ if (setup_semijoin_dups_elimination(join, options, no_jbuf_after))
DBUG_RETURN(TRUE); /* purecov: inspected */
for (i=join->const_tables ; i < join->tables ; i++)
@@ -12516,17 +12547,18 @@ Item *eliminate_item_equal(Item *cond, C
Item_equal *item_equal)
{
List<Item> eq_list;
- Item_func_eq *eq_item= 0;
+ Item_func_eq *eq_item= NULL;
if (((Item *) item_equal)->const_item() && !item_equal->val_int())
return new Item_int((longlong) 0,1);
Item *item_const= item_equal->get_const();
Item_equal_iterator it(*item_equal);
Item *head;
- if (item_const)
- head= item_const;
- else
+ if (!item_const)
{
- head= item_equal->get_first();
+ /*
+ If there is a const item, match all field items with the const item,
+ otherwise match the second and subsequent field items with the first one:
+ */
it++;
}
Item_field *item_field;
@@ -12537,7 +12569,7 @@ Item *eliminate_item_equal(Item *cond, C
if (upper)
{
if (item_const && upper->get_const())
- item= 0;
+ item= NULL;
else
{
Item_equal_iterator li(*item_equal);
@@ -12552,57 +12584,55 @@ Item *eliminate_item_equal(Item *cond, C
{
if (eq_item)
eq_list.push_back(eq_item);
+
/*
- item_field might refer to a table that is within a semi-join
- materialization nest. In that case, the join order looks like this:
+ item_field may refer to a table that is within a semijoin
+ materialization nest. In that case, the join order may look like:
- outer_tbl1 outer_tbl2 SJM (inner_tbl1 inner_tbl2) outer_tbl3
+ ot1 ot2 SJM (it1 it2) ot3
- We must not construct equalities like
+ We must not construct general equalities like
- outer_tbl1.col = inner_tbl1.col
+ ot1.col = it1.col
because they would get attached to inner_tbl1 and will get evaluated
during materialization phase, when we don't have current value of
- outer_tbl1.col.
+ ot1.col. When such equalities are generated, add a special marker to
+ the condition so that it will be evaluated after the inner tables
+ have been materialized, and the materialized table is joined with the
+ outer tables.
+ */
+
+ bool need_marker= FALSE;
+ /*
+ If there is a const item, match against this one.
+ Otherwise, match against the first field item in the multiple equality,
+ unless the item is within a materialized semijoin nest, where we match
+ against the first item within the SJM nest (if the item is not the first
+ item within the SJM nest), or match against the first item in the
+ list (if the item is the first one in the SJM list).
+ The latter equalities are the ones that must be tagged with a marker,
+ to prevent them from being evaluated at the wrong place.
*/
- TABLE_LIST *emb_nest=
- item_field->field->table->pos_in_table_list->embedding;
- if (!item_const && emb_nest && emb_nest->sj_mat_exec)
+ head= item_const ? item_const : item_equal->get_first(item);
+ if (head == item)
{
- /*
- Find the first equal expression that refers to a table that is
- within the semijoin nest. If we can't find it, do nothing
- */
- List_iterator<Item_field> fit(item_equal->fields);
- Item_field *head_in_sjm;
- bool found= FALSE;
- while ((head_in_sjm= fit++))
- {
- if (head_in_sjm->used_tables() & emb_nest->sj_inner_tables)
- {
- if (head_in_sjm == item_field)
- {
- /* This is the first table inside the semi-join*/
- eq_item= new Item_func_eq(item_field, head);
- /* Tell make_cond_for_table don't use this. */
- eq_item->marker=3;
- }
- else
- {
- eq_item= new Item_func_eq(item_field, head_in_sjm);
- found= TRUE;
- }
- break;
- }
- }
- if (!found)
- continue;
+ head= item_equal->get_first();
+ need_marker= TRUE;
}
- else
- eq_item= new Item_func_eq(item_field, head);
+ eq_item= new Item_func_eq(item_field, head);
if (!eq_item)
return 0;
+ if (need_marker)
+ {
+ /*
+ Setting 'marker= 3' means that make_cond_for_table() will ignore this
+ condition, but make_cond_after_sjm() will pick it up.
+ The design is not pretty, but the alternative is to have a hairy
+ check for SJM-related conditions inside make_cond_for_table().
+ */
+ eq_item->marker= 3;
+ }
eq_item->set_cmp_func();
eq_item->quick_fix_field();
}
@@ -16897,21 +16927,8 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
Ok, materialization finished. Initialize the access to the temptable
*/
sjm->materialized= TRUE;
+
join_tab->read_record.read_record= join_no_more_records;
- if (sjm->is_scan)
- {
- /* Initialize full scan */
- JOIN_TAB *last_tab= join_tab + (sjm->table_count - 1);
- init_read_record(&last_tab->read_record, join->thd,
- sjm->table, NULL, TRUE, TRUE, FALSE);
-
- DBUG_ASSERT(last_tab->read_record.read_record == rr_sequential);
- last_tab->read_first_record= join_read_record_no_init;
- last_tab->read_record.copy_field= sjm->copy_field;
- last_tab->read_record.copy_field_end= sjm->copy_field +
- sjm->table_cols.elements;
- last_tab->read_record.read_record= rr_sequential_and_unpack;
- }
}
if (sjm->is_scan)
@@ -16919,6 +16936,15 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
/* Do full scan of the materialized table */
JOIN_TAB *last_tab= join_tab + (sjm->table_count - 1);
+ init_read_record(&last_tab->read_record, join->thd,
+ sjm->table, NULL, TRUE, TRUE, FALSE);
+
+ last_tab->read_first_record= join_read_record_no_init;
+ last_tab->read_record.copy_field= sjm->copy_field;
+ last_tab->read_record.copy_field_end= sjm->copy_field +
+ sjm->table_cols.elements;
+ last_tab->read_record.read_record= rr_sequential_and_unpack;
+
Item *save_cond= last_tab->select_cond;
last_tab->set_select_cond(sjm->join_cond, __LINE__);
rc= sub_select(join, last_tab, end_of_records);
@@ -18837,13 +18863,14 @@ static bool replace_subcondition(JOIN *j
cond Condition to analyze
tables Tables for which "current field values" are available
used_table Table that we're extracting the condition for (may
- also include PSEUDO_TABLE_BITS
+ also include PSEUDO_TABLE_BITS, and may be zero)
exclude_expensive_cond Do not push expensive conditions
DESCRIPTION
Extract the condition that can be checked after reading the table
specified in 'used_table', given that current-field values for tables
specified in 'tables' bitmap are available.
+ If 'used_table' is 0 (zero), extract conditions for all tables in 'tables'.
The function assumes that
- Constant parts of the condition has already been checked.
@@ -18869,26 +18896,32 @@ make_cond_for_table(Item *cond, table_ma
return make_cond_for_table_from_pred(cond, cond, tables, used_table,
exclude_expensive_cond);
}
-
+
static Item *
make_cond_for_table_from_pred(Item *root_cond, Item *cond,
table_map tables, table_map used_table,
bool exclude_expensive_cond)
{
- if (used_table && !(cond->used_tables() & used_table) &&
- /*
- Exclude constant conditions not checked at optimization time if
+ /*
+ Ignore this condition if
+ 1. We are extracting conditions for a specific table, and
+ 2. that table is not referenced by the condition, and
+ 3. exclude constant conditions not checked at optimization time if
the table we are pushing conditions to is the first one.
As a result, such conditions are not considered as already checked
and will be checked at execution time, attached to the first table.
-
+ */
+ if (used_table && // 1
+ !(cond->used_tables() & used_table) && // 2
+ /*
psergey: TODO: "used_table & 1" doesn't make sense in nearly any
context. Look at setup_table_map(), table bits reflect the order
the tables were encountered by the parser. Check what we should
replace this condition with.
*/
- !((used_table & 1) && cond->is_expensive()))
- return (Item*) 0; // Already checked
+ !((used_table & 1) && cond->is_expensive())) // 3
+ return (Item*) NULL; // Already checked
+
if (cond->type() == Item::COND_ITEM)
{
if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
@@ -18896,48 +18929,48 @@ make_cond_for_table_from_pred(Item *root
/* Create new top level AND item */
Item_cond_and *new_cond=new Item_cond_and;
if (!new_cond)
- return (Item*) 0; // OOM /* purecov: inspected */
+ return (Item*) NULL;
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
Item *item;
while ((item=li++))
{
- Item *fix=make_cond_for_table_from_pred(root_cond, item,
+ Item *fix=make_cond_for_table_from_pred(root_cond, item,
tables, used_table,
exclude_expensive_cond);
- if (fix)
- new_cond->argument_list()->push_back(fix);
+ if (fix)
+ new_cond->argument_list()->push_back(fix);
}
switch (new_cond->argument_list()->elements) {
case 0:
- return (Item*) 0; // Always true
+ return (Item*) NULL; // Always true
case 1:
- return new_cond->argument_list()->head();
+ return new_cond->argument_list()->head();
default:
- /*
- Item_cond_and do not need fix_fields for execution, its parameters
- are fixed or do not need fix_fields, too
- */
- new_cond->quick_fix_field();
- new_cond->used_tables_cache=
- ((Item_cond_and*) cond)->used_tables_cache &
- tables;
- return new_cond;
+ /*
+ Item_cond_and do not need fix_fields for execution, its parameters
+ are fixed or do not need fix_fields, too
+ */
+ new_cond->quick_fix_field();
+ new_cond->used_tables_cache=
+ ((Item_cond_and*) cond)->used_tables_cache &
+ tables;
+ return new_cond;
}
}
else
- { // Or list
+ { // Or list
Item_cond_or *new_cond=new Item_cond_or;
if (!new_cond)
- return (Item*) 0; // OOM /* purecov: inspected */
+ return (Item*) NULL;
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
Item *item;
while ((item=li++))
{
- Item *fix=make_cond_for_table_from_pred(root_cond, item,
+ Item *fix=make_cond_for_table_from_pred(root_cond, item,
tables, 0L,
exclude_expensive_cond);
if (!fix)
- return (Item*) 0; // Always true
+ return (Item*) NULL; // Always true
new_cond->argument_list()->push_back(fix);
}
/*
@@ -18952,20 +18985,25 @@ make_cond_for_table_from_pred(Item *root
}
/*
- Because the following test takes a while and it can be done
- table_count times, we mark each item that we have examined with the result
- of the test
- */
+ Omit this condition if
+ 1. It has been marked as omittable before, or
+ 2. All tables referred by the condition are not available, or
+ 3. We are extracting constant conditions, the condition is
+ considered 'expensive', and we want to delay evaluation of such
+ conditions to the execution phase.
+ */
+ if (cond->marker == 3 || // 1
+ (cond->used_tables() & ~tables) || // 2
+ (!used_table && exclude_expensive_cond && cond->is_expensive())) // 3
+ return (Item*) NULL;
- if (cond->marker == 3 || (cond->used_tables() & ~tables) ||
- /*
- When extracting constant conditions, treat expensive conditions as
- non-constant, so that they are not evaluated at optimization time.
- */
- (!used_table && exclude_expensive_cond && cond->is_expensive()))
- return (Item*) 0; // Can't check this yet
+ /*
+ Extract this condition if
+ 1. It has already been marked as applicable, or
+ 2. It is not a <comparison predicate> (=, <, >, <=, >=, <=>)
+ */
if (cond->marker == 2 || cond->eq_cmp_result() == Item::COND_OK)
- return cond; // Not boolean op
+ return cond;
/*
Remove equalities that are guaranteed to be true by use of 'ref' access
@@ -18993,19 +19031,19 @@ make_cond_for_table_from_pred(Item *root
Item *left_item= ((Item_func*) cond)->arguments()[0]->real_item();
Item *right_item= ((Item_func*) cond)->arguments()[1]->real_item();
if (left_item->type() == Item::FIELD_ITEM &&
- test_if_ref(root_cond, (Item_field*) left_item,right_item))
+ test_if_ref(root_cond, (Item_field*) left_item,right_item))
{
- cond->marker=3; // Checked when read
- return (Item*) 0;
+ cond->marker=3; // Condition can be omitted
+ return (Item*) NULL;
}
if (right_item->type() == Item::FIELD_ITEM &&
- test_if_ref(root_cond, (Item_field*) right_item,left_item))
+ test_if_ref(root_cond, (Item_field*) right_item,left_item))
{
- cond->marker=3; // Checked when read
- return (Item*) 0;
+ cond->marker=3; // Condition can be omitted
+ return (Item*) NULL;
}
}
- cond->marker=2;
+ cond->marker=2; // Mark condition as applicable
return cond;
}
@@ -19034,9 +19072,11 @@ static Item *
make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables,
table_map sjm_tables)
{
+ if (!cond)
+ return (Item*) NULL;
if ((!(cond->used_tables() & ~tables) ||
!(cond->used_tables() & ~sjm_tables)))
- return (Item*) 0; // Already checked
+ return (Item*) NULL; // Already checked
if (cond->type() == Item::COND_ITEM)
{
if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
@@ -19044,49 +19084,49 @@ make_cond_after_sjm(Item *root_cond, Ite
/* Create new top level AND item */
Item_cond_and *new_cond=new Item_cond_and;
if (!new_cond)
- return (Item*) 0; // OOM /* purecov: inspected */
+ return (Item*) NULL;
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
Item *item;
while ((item=li++))
{
- Item *fix=make_cond_after_sjm(root_cond, item, tables, sjm_tables);
- if (fix)
- new_cond->argument_list()->push_back(fix);
+ Item *fix=make_cond_after_sjm(root_cond, item, tables, sjm_tables);
+ if (fix)
+ new_cond->argument_list()->push_back(fix);
}
switch (new_cond->argument_list()->elements) {
case 0:
- return (Item*) 0; // Always true
+ return (Item*) NULL; // Always true
case 1:
- return new_cond->argument_list()->head();
+ return new_cond->argument_list()->head();
default:
/*
- Item_cond_and do not need fix_fields for execution, its parameters
- are fixed or do not need fix_fields, too
+ Item_cond_and do not need fix_fields for execution, its parameters
+ are fixed or do not need fix_fields, too
*/
- new_cond->quick_fix_field();
- new_cond->used_tables_cache=
- ((Item_cond_and*) cond)->used_tables_cache &
- tables;
- return new_cond;
+ new_cond->quick_fix_field();
+ new_cond->used_tables_cache=
+ ((Item_cond_and*) cond)->used_tables_cache &
+ tables;
+ return new_cond;
}
}
else
- { // Or list
+ { // Or list
Item_cond_or *new_cond=new Item_cond_or;
if (!new_cond)
- return (Item*) 0; // OOM /* purecov: inspected */
+ return (Item*) NULL;
List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
Item *item;
while ((item=li++))
{
- Item *fix= make_cond_after_sjm(root_cond, item, tables, 0L);
- if (!fix)
- return (Item*) 0; // Always true
- new_cond->argument_list()->push_back(fix);
+ Item *fix= make_cond_after_sjm(root_cond, item, tables, 0L);
+ if (!fix)
+ return (Item*) NULL; // Always true
+ new_cond->argument_list()->push_back(fix);
}
/*
- Item_cond_and do not need fix_fields for execution, its parameters
- are fixed or do not need fix_fields, too
+ Item_cond_and do not need fix_fields for execution, its parameters
+ are fixed or do not need fix_fields, too
*/
new_cond->quick_fix_field();
new_cond->used_tables_cache= ((Item_cond_or*) cond)->used_tables_cache;
@@ -19095,16 +19135,10 @@ make_cond_after_sjm(Item *root_cond, Ite
}
}
- /*
- Because the following test takes a while and it can be done
- table_count times, we mark each item that we have examined with the result
- of the test
- */
-
- if (cond->marker == 3 || (cond->used_tables() & ~(tables | sjm_tables)))
- return (Item*) 0; // Can't check this yet
- if (cond->marker == 2 || cond->eq_cmp_result() == Item::COND_OK)
- return cond; // Not boolean op
+ if (cond->used_tables() & ~(tables | sjm_tables))
+ return (Item*) NULL; // Can't check this yet
+ if (cond->eq_cmp_result() == Item::COND_OK)
+ return cond; // Not a <comparison predicate>
/*
Remove equalities that are guaranteed to be true by use of 'ref' access
@@ -19117,17 +19151,14 @@ make_cond_after_sjm(Item *root_cond, Ite
if (left_item->type() == Item::FIELD_ITEM &&
test_if_ref(root_cond, (Item_field*) left_item,right_item))
{
- cond->marker=3; // Checked when read
return (Item*) 0;
}
if (right_item->type() == Item::FIELD_ITEM &&
test_if_ref(root_cond, (Item_field*) right_item,left_item))
{
- cond->marker=3; // Checked when read
return (Item*) 0;
}
}
- cond->marker=2;
return cond;
}
Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20101007151510-tmsoxa2ywrw0frkr.bundle
