3515 Roy Lyseng 2012-01-11
Bug#13541406: Wrong result with loosescan on select .. where .. in
To explain this problem, let's look at the query in the attached test:
SELECT ot1.col_int_key AS field1
FROM t2 AS ot1, t2 AS ot2
WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
SELECT it2.col_varchar_nokey, it1.col_varchar_key
FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
The selected plan for this query is (abbreviated):
ot2 ALL
it1 index col_varchar_key ... LooseScan
it2 ref(it1.pk) col_int_key FirstMatch(it1)
ot1 ref(it2.col_varchar_nokey) col_varchar_key Using index
In English, this means:
- The outermost loop is over the outer table ot2.
- For each row of ot2, we perform a loose scan over it1.
(Notice that we could have used a ref access using index
col_varchar_key, but loosescan and ref access cannot be combined yet).
- We use FirstMatch on table it2 to weed out duplicate rows from the
subquery.
- We use a ref access to look up rows in ot1, based on values from it2.
However, this means that there is a dependency from it2 to ot1 and
FirstMatch is only able to handle dependencies to outer tables that
are earlier in the join prefix. Hence, LooseScan (combined with FirstMatch)
is not a valid strategy for this query.
The source of the problem is that get_bound_sj_equalities() sets the
zeroth bit in bound_sj_equalities, even though the Loose_scan_opt
function add_keyuse() performs a complete analysis of bound_sj_equalities
and handled_sj_equalities later. Setting the zeroth bit has fatal
consequences in few cases, because it is later overwritten or ignored,
but it did have consequences in this case.
Thus, the problem is solved by removing get_bound_sj_equalities() and
initializing bound_sj_equalities to the empty set. As for the question
on equality propagation inside the function, I do not think that is a
problem because separate keyuse objects are set up for individual
fields of a multiple equality.
mysql-test/include/subquery_sj.inc
Added test case for bug#13541406.
Make sure that LooseScan is not selected.
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
Added test case results for bug#13541406.
sql/sql_planner.cc
Removed the function get_bound_sj_equalities() and initialized
bound_sj_equalities to an empty set.
Comment updates.
sql/sql_select.h
Comment updates in allowed table patterns for semi-join strategies.
sql/sql_select.h
Fixed a minor documentation inconsistency.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
sql/sql_planner.cc
sql/sql_select.cc
sql/sql_select.h
3514 Guilhem Bichot 2012-01-11
fix for pb2 after the last push: ORDER BY <alias> is now printed with alias.
modified:
mysql-test/r/archive_gis.result
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2012-01-05 09:37:44 +0000
+++ b/mysql-test/include/subquery_sj.inc 2012-01-11 13:29:29 +0000
@@ -4626,8 +4626,6 @@ DROP TABLE t1, t2, t3;
--echo # End of test for bug#13545215.
---echo # End of 5.6 tests
-
--echo #
--echo # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
--echo # MATERIALIZATION + SEMIJOIN ON
@@ -4668,3 +4666,54 @@ eval EXPLAIN $query;
eval $query;
DROP TABLE t1;
+
+--echo #
+--echo # Bug#13541406: Wrong result with loosescan on select .. where .. in
+--echo #
+
+CREATE TABLE t1 (
+ col_int_key INT NOT NULL,
+ col_varchar_nokey VARCHAR(1) NOT NULL,
+ KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+ (7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+ (1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+ (9,'e'), (5,'i'), (0,'y'), (3,'w');
+
+CREATE TABLE t2 (
+ pk INT NOT NULL,
+ col_int_key INT NOT NULL,
+ col_varchar_key VARCHAR(1) NOT NULL,
+ col_varchar_nokey VARCHAR(1) NOT NULL,
+ PRIMARY KEY (pk),
+ KEY col_int_key (col_int_key),
+ KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES
+ (1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+ (5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+ (9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+ (13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+ (17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+
+let $query=
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+ SELECT it2.col_varchar_nokey, it1.col_varchar_key
+ FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+
+--echo # This query should never use a LooseScan strategy
+
+eval explain $query;
+--sorted_result
+eval $query;
+
+DROP TABLE t1, t2;
+
+--echo # End of test for bug#13541406.
+
+--echo # End of 5.6 tests
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2012-01-11 13:29:29 +0000
@@ -7331,7 +7331,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7395,4 +7394,67 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index; Start temporary
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1 Using join buffer (Batched Key Access)
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1 Using join buffer (Batched Key Access)
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-01-11 13:29:29 +0000
@@ -7333,7 +7333,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7397,5 +7396,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1 Using join buffer (Batched Key Access (unique))
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-01-11 13:29:29 +0000
@@ -7330,7 +7330,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7394,4 +7393,67 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index; Start temporary
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-01-11 13:29:29 +0000
@@ -7331,7 +7331,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7395,5 +7394,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-01-11 13:29:29 +0000
@@ -7331,7 +7331,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7395,5 +7394,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-01-11 13:29:29 +0000
@@ -7331,7 +7331,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7396,6 +7395,69 @@ v v v
v v v
DROP TABLE t1;
#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index; Start temporary
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7397,6 +7396,69 @@ v v v
v v v
DROP TABLE t1;
#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7397,6 +7396,69 @@ v v v
v v v
DROP TABLE t1;
#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-01-11 13:29:29 +0000
@@ -7333,7 +7333,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7398,6 +7397,69 @@ v v v
v v v
DROP TABLE t1;
#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
+#
# 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 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-01-11 13:29:29 +0000
@@ -7331,7 +7331,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7395,4 +7394,67 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index; Start temporary
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-01-11 13:29:29 +0000
@@ -7333,7 +7333,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7397,5 +7396,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2012-01-11 13:29:29 +0000
@@ -7331,7 +7331,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7395,4 +7394,67 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index; Start temporary
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-01-11 13:29:29 +0000
@@ -7333,7 +7333,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7397,5 +7396,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Start temporary
+1 PRIMARY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 test.ot2.col_varchar_nokey 1 Using index
+1 PRIMARY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+1 PRIMARY ot1 ref col_varchar_key col_varchar_key 3 test.it2.col_varchar_nokey 1 Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-01-11 13:29:29 +0000
@@ -7407,7 +7407,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7471,4 +7470,67 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 index NULL col_varchar_key 7 NULL 20 Using index
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (Block Nested Loop)
+2 SUBQUERY it1 index PRIMARY col_varchar_key 7 NULL 20 Using index
+2 SUBQUERY it2 ref col_int_key col_int_key 4 test.it1.pk 1
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2012-01-11 13:29:29 +0000
@@ -7342,7 +7342,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7406,4 +7405,67 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 index NULL col_varchar_key 7 NULL 20 Using index
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 func 1 Using index
+2 DEPENDENT SUBQUERY it2 ref col_int_key col_int_key 4 test.it1.pk 1 Using where
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2012-01-11 13:29:29 +0000
@@ -7343,7 +7343,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7407,5 +7406,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 index NULL col_varchar_key 7 NULL 20 Using index
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 func 1 Using index
+2 DEPENDENT SUBQUERY it2 ref col_int_key col_int_key 4 test.it1.pk 1 Using where
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-01-11 13:29:29 +0000
@@ -7343,7 +7343,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7407,5 +7406,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 index NULL col_varchar_key 7 NULL 20 Using index
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Using where
+2 DEPENDENT SUBQUERY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 func 1 Using index
+2 DEPENDENT SUBQUERY it2 ref col_int_key col_int_key 4 test.it1.pk 1 Using where
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result 2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-01-11 13:29:29 +0000
@@ -7344,7 +7344,6 @@ x
x
DROP TABLE t1, t2, t3;
# End of test for bug#13545215.
-# End of 5.6 tests
#
# BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
# MATERIALIZATION + SEMIJOIN ON
@@ -7408,5 +7407,68 @@ v v v
v v v
v v v
DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 index NULL col_varchar_key 7 NULL 20 Using index
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY it1 ref PRIMARY,col_varchar_key col_varchar_key 3 func 1 Using index
+2 DEPENDENT SUBQUERY it2 ref col_int_key col_int_key 4 test.it1.pk 1 Using where
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'sql/sql_planner.cc'
--- a/sql/sql_planner.cc 2011-12-16 14:39:02 +0000
+++ b/sql/sql_planner.cc 2012-01-11 13:29:29 +0000
@@ -138,47 +138,6 @@ join_tab_cmp_embedded_first(const void *
/*
- Given a semi-join nest, find out which of the IN-equalities are bound
-
- SYNOPSIS
- get_bound_sj_equalities()
- sj_nest Semi-join nest
- remaining_tables Tables that are not yet bound
-
- DESCRIPTION
- Given a semi-join nest, find out which of the IN-equalities have their
- left part expression bound (i.e. the said expression doesn't refer to
- any of remaining_tables and can be evaluated).
-
- RETURN
- Bitmap of bound IN-equalities.
-*/
-
-static ulonglong get_bound_sj_equalities(TABLE_LIST *sj_nest,
- table_map remaining_tables)
-{
- List_iterator<Item> li(sj_nest->nested_join->sj_outer_exprs);
- Item *item;
- uint i= 0;
- ulonglong res= 0;
- while ((item= li++))
- {
- /*
- Q: should this take into account equality propagation and how?
- A: If e->outer_side is an Item_field, walk over the equality
- class and see if there is an element that is bound?
- (this is an optional feature)
- */
- if (!(item->used_tables() & remaining_tables))
- {
- res |= 1ULL << i;
- }
- }
- return res;
-}
-
-
-/*
This is a class for considering possible loose index scan optimizations.
It's usage pattern is as follows:
best_access_path()
@@ -265,36 +224,35 @@ public:
table_map cur_sj_inner_tables, bool is_sjm_nest)
{
/*
- Discover the bound equalities. We need to do this if
+ We may consider the LooseScan strategy if
1. The next table is an SJ-inner table, and
- 2. It is the first table from that semijoin, and
- 3. We're not within a semi-join range (i.e. all semi-joins either have
+ 2, We have no more than 64 IN expressions (must fit in bitmap), and
+ 3. It is the first table from that semijoin, and
+ 4. We're not within a semi-join range (i.e. all semi-joins either have
all or none of their tables in join_table_map), except
- s->emb_sj_nest (which we've just entered, see #2).
- 4. All non-IN-equality correlation references from this sj-nest are
- bound
- 5. But some of the IN-equalities aren't (so this can't be handled by
- FirstMatch strategy)
- 6. Not a derived table/view. (a temporary restriction)
+ s->emb_sj_nest (which we've just entered, see #2), and
+ 5. All non-IN-equality correlation references from this sj-nest are
+ bound, and
+ 6. But some of the IN-equalities aren't (so this can't be handled by
+ FirstMatch strategy), and
+ 7. LooseScan is not disabled, and
+ 8. Not a derived table/view. (a temporary restriction)
*/
best_loose_scan_cost= DBL_MAX;
if (s->emb_sj_nest && !is_sjm_nest && // (1)
- s->emb_sj_nest->nested_join->sj_inner_exprs.elements < 64 &&
- ((remaining_tables & s->emb_sj_nest->sj_inner_tables) == // (2)
- s->emb_sj_nest->sj_inner_tables) && // (2)
- cur_sj_inner_tables == 0 && // (3)
+ s->emb_sj_nest->nested_join->sj_inner_exprs.elements <= 64 && // (2)
+ ((remaining_tables & s->emb_sj_nest->sj_inner_tables) == // (3)
+ s->emb_sj_nest->sj_inner_tables) && // (3)
+ cur_sj_inner_tables == 0 && // (4)
!(remaining_tables &
- s->emb_sj_nest->nested_join->sj_corr_tables) && // (4)
- (remaining_tables & s->emb_sj_nest->nested_join->sj_depends_on) &&// (5)
- s->join->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_LOOSE_SCAN) &&
- !s->table->pos_in_table_list->uses_materialization())
- {
- /* This table is an LooseScan scan candidate */
- bound_sj_equalities= get_bound_sj_equalities(s->emb_sj_nest,
- remaining_tables);
- try_loosescan= TRUE;
- DBUG_PRINT("info", ("Will try LooseScan scan, bound_map=%llx",
- (longlong)bound_sj_equalities));
+ s->emb_sj_nest->nested_join->sj_corr_tables) && // (5)
+ (remaining_tables & s->emb_sj_nest->nested_join->sj_depends_on) && //(6)
+ s->join->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_LOOSE_SCAN) &&//(7)
+ !s->table->pos_in_table_list->uses_materialization()) // (8)
+ {
+ try_loosescan= true; // This table is a LooseScan scan candidate
+ bound_sj_equalities= 0; // These equalities are populated later
+ DBUG_PRINT("info", ("Will try LooseScan scan"));
}
}
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2012-01-05 14:20:14 +0000
+++ b/sql/sql_select.cc 2012-01-11 13:29:29 +0000
@@ -328,10 +328,15 @@ static bool might_do_join_buffering(uint
that the correlation is not just through the IN-equality).
(2) - Inner table for which the LooseScan scan is performed.
+ Notice that special requirements for existence of certain indexes
+ apply to this table, @see class Loose_scan_opt.
(3) - The remainder of the duplicate-generating range. It is served by
- application of FirstMatch strategy, with the exception that
- outer IN-correlated tables are considered to be non-correlated.
+ application of FirstMatch strategy. Outer IN-correlated tables
+ must be correlated to the LooseScan table but not to the inner
+ tables in this range. (Currently, there can be no outer tables
+ in this range because of implementation restrictions,
+ @see Optimize_table_order::advance_sj_state()).
(4) - The suffix of outer correlated and non-correlated tables.
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2011-12-15 15:15:37 +0000
+++ b/sql/sql_select.h 2012-01-11 13:29:29 +0000
@@ -101,7 +101,7 @@ public:
*/
bool *cond_guard;
/**
- 0..64 <=> This was created from semi-join IN-equality # sj_pred_no.
+ 0..63 <=> This was created from semi-join IN-equality # sj_pred_no.
UINT_MAX Otherwise
Not used if the index is fulltext (such index cannot be used for
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3514 to 3515) Bug#13541406 | Roy Lyseng | 11 Jan |
