3530 Roy Lyseng 2012-01-20
Bug#13596176: Missing row on select with nested in clause when matr=on
and bnl=off + MyISAM
This is a case where Materialize-scan semi-join strategy is selected,
and ref access is used to look up a row in the outer table.
The referenced field in the outer table belongs in a multiple equality
together with three fields from the inner tables.
setup_sj_materialization() sets up objects to copy fields from the
materialized tables back to original field objects. For a multiple
equality, a "best" field is picked for this, the remaining fields
are not copied. This best field coincides with fields used by regular
conditions, but not necessarily with fields used for ref access.
The reason for this is that before fields are substituted within
regular conditions (see substitute_for_best_equal_field()), multiple
equality objects are sorted so that fields come in table order.
This sorting has not been done when calling set_access_methods(),
which sets up ref access.
The solution to the problem is to execute set_access_methods() after
the multiple equalities have been sorted, and use the same algorithm
as setup_sj_materialization() uses to pick the proper field for
ref access.
A pleasant side effect of this fix is also that
make_cond_for_table_from_pred() now does a better job in replacing
equality predicates that are also covered by ref accesses.
mysql-test/include/subquery_sj.inc
Added test case for bug#13596176.
mysql-test/r/derived.result
mysql-test/r/greedy_optimizer.result
mysql-test/r/join_cache_bka.result
mysql-test/r/join_cache_bka_nixbnl.result
mysql-test/r/join_cache_bkaunique.result
mysql-test/r/join_cache_bnl.result
mysql-test/r/join_cache_nojb.result
mysql-test/r/join_nested.result
mysql-test/r/join_nested_bka.result
mysql-test/r/join_nested_bka_nixbnl.result
mysql-test/suite/opt_trace/r/general2_no_prot.result
mysql-test/suite/opt_trace/r/general2_ps_prot.result
Updated plans. Mostly these are removed "Using where" and
"Using index condition", which means that a condition has been
correctly identified as ref access and removed.
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_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#13596176, plus some updated plans.
sql/sql_optimizer.cc
Added function get_best_field() that picks the "best" field from
a multiple equality.
Calls set_access_methods() after multiple equalities have been sorted.
Calls drop_unused_derived_keys() after ref access has been set up.
Calls update_depend_map() after ref access has been set up.
Calls initialize_tables() after ref access has been set up.
sql/sql_optimizer.h
Added definition for function get_best_field().
sql/sql_select.cc
Calls get_best_field() in create_ref_for_key() and
setup_sj_materialization() to get the "best" field.
Call to set_access_methods() and update_depend_map() moved to a more
appropriate place.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/derived.result
mysql-test/r/greedy_optimizer.result
mysql-test/r/join_cache_bka.result
mysql-test/r/join_cache_bka_nixbnl.result
mysql-test/r/join_cache_bkaunique.result
mysql-test/r/join_cache_bnl.result
mysql-test/r/join_cache_nojb.result
mysql-test/r/join_nested.result
mysql-test/r/join_nested_bka.result
mysql-test/r/join_nested_bka_nixbnl.result
mysql-test/r/subquery_mat.result
mysql-test/r/subquery_mat_all.result
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
mysql-test/suite/opt_trace/r/general2_no_prot.result
mysql-test/suite/opt_trace/r/general2_ps_prot.result
sql/sql_optimizer.cc
sql/sql_optimizer.h
sql/sql_select.cc
3529 Guilhem Bichot 2012-01-19
Fix for Bug#13596330 - EXTRA ROW ON SELECT WITH NESTED IN CLAUSE
+ IS NULL WHEN SEMIJOIN + BNL IS ON :
table->status must say "there is a record" when we process
records from the join buffer.
@ mysql-test/r/join_cache_bnl.result
without the code fix, SELECT would return 'e'
@ sql/sql_join_buffer.cc
Scenario of the bug follows (see query in join_cache.inc).
Records are read from PARENT1 (the first record having
GRANDPARENT1.col_varchar_key='e'), and buffered into the join buffer
of t1. When the end of PARENT1 is reached (table->status
set to "no record"), buffered records are "flushed"
(JOIN_CACHE::join_records()),
which triggers execution of the most inner subquery (with IN->EXISTS
and indexsubquery_engine); the first step of that is JOIN::optimize();
IS NULL is first converted to "=the_zero_datetime" (this is a "feature"
of DATETIME...) and then convert_constant_item() tries to optimize
this equality to a comparison of longlongs. This last function does
this:
a) backup the value of the left member (the DATETIME Field)
b) store the right member (an Item, the zero datetime) into the left
member
c) if this doesn't cause truncation, decide that the comparison can be
done as longlongs
d) restore the value saved in (a)
But (a)(d) are done only if there was a value to preserve, i.e. if there
was a record (into which Field::ptr points); the existence of a record
is determined by testing TABLE::status (per the fix for Bug 37870;
search for save_field_value in convert_constant_item()).
In our scenario, given that PARENT1 has 'status' with STATUS_NOT_FOUND,
(a)(d) doesn't happen. So, the record gets modified, ending up with "0"
in PARENT1.col_datetime_nokey, thus the WHERE clause of the (most
inner) subquery is satisfied, and 'e' is put in the result set.
Wrongly.
The fix: when we bring a record from the join buffer, we put it
in the table's record[0], and thus, table->status must say that
there is a record in record[0]; so it must not have STATUS_NOT_FOUND
and STATUS_GARBAGE. The status of the last real-read-from-table does
not matter for the join buffering code, as this read was superseded by a
read-from-cache. So we save/restore the status.
Note that the fix for BUG 11766522 removed the save/restore logic
because:
- it was absurd
- it was buggy (not re-entrant).
Well, you live and learn... it was not absurd. The new implementation
puts back the save/restore logic, making it re-entrant by using a
per-join-buffer backup of TABLE::status, local to
join_records() instead of join_matching_records().
Below some notes of investigations made.
* Why do we restore the status afterwards? Based on the scenario of
BUG 11766522 (see commit comments of that bugfix), we could imagine this
in a t1-t2 scenario:
read_from_t1, EOF # turns on STATUS_NOT_FOUND
call sub_select_cache() # for t2, to flush buffered records; sets status of t1&t2 to 0
if (t1 has STATUS_NOT_FOUND) etc; # i.e. this uses status to remember that it hit EOF above
If we didn't restore the status, the final if() would not be entered.
* Do we need a per-buffered-record status?
a) Certain flags of TABLE::status (STATUS_GARBAGE, STATUS_NOT_FOUND)
describe the presence of a record; those simply have to be restored
at end as we do. Indeed:
- if there was no record in table->record[0] when we started flushing
of inner table's buffer: given that we restore this "no record"
information at end of flushing, it's correct
- if there was a record when flushing started: after flushing of
buffered records, we have in table->record[0] what there was before
flushing started (we simply made an excursion through older records
but ended with the current one).
b) On the other hand, other flags describe the record: STATUS_NOT_NULL,
STATUS_UPDATED/DELETED. In theory, we could need to store their value
per buffered record (like we do for table->null_row, see
add_flag_field_to_join_cache()). But in fact, there is no need, because:
- STATUS_UPDATED/DELETED serve only for multi-update/delete, which
never use join buffering (btw see BUG 13602655)
- STATUS_NULL_ROW is not used while evaluating records (so,
not inside join_records()), only in join_read_key2(): this function
needs the status of the last read-from-index record; and as said in
point (a) above, the last-record information is properly restored.
@ sql/sql_join_buffer.h
Changed alloc_buffer() to bool.
@ sql/table.h
Reduce 'status' from uint to uint8; it's ok, I checked all read/writes
of this member.
Move copy_blobs into the my_bool section.
Move status into the my_bool section.
All this gives better padding, I verified that it saves 8 bytes on
sizeof(TABLE).
modified:
mysql-test/include/join_cache.inc
mysql-test/r/join_cache_bka.result
mysql-test/r/join_cache_bka_nixbnl.result
mysql-test/r/join_cache_bkaunique.result
mysql-test/r/join_cache_bnl.result
mysql-test/r/join_cache_nojb.result
sql/sql_join_buffer.cc
sql/sql_join_buffer.h
sql/structs.h
sql/table.h
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2012-01-16 12:51:06 +0000
+++ b/mysql-test/include/subquery_sj.inc 2012-01-20 15:30:14 +0000
@@ -4819,4 +4819,48 @@ eval $query;
DROP TABLE t1,t2,t3;
+--echo #
+--echo # Bug #13596176: Missing row on select with nested in clause when
+--echo # matr=on and bnl=off + MyISAM
+--echo #
+
+CREATE TABLE t1 (
+ int_key int DEFAULT NULL,
+ vc_key varchar(1) DEFAULT NULL,
+ vc_nokey varchar(1) DEFAULT NULL,
+ KEY int_key (int_key),
+ KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES
+ (8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+ (9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+ (2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+ (0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+ (NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+
+CREATE TABLE t2 (
+ int_key int DEFAULT NULL,
+ vc_key varchar(1) DEFAULT NULL,
+ KEY int_key (int_key),
+ KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES (8,'g');
+
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+ (SELECT vc_nokey, vc_nokey
+ FROM t1 middle
+ WHERE vc_nokey IN
+ (SELECT child1.vc_key
+ FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+ )
+ );
+
+DROP TABLE t1, t2;
+
+--echo # End of test for bug#13596176.
+
--echo # End of 5.6 tests
=== modified file 'mysql-test/r/derived.result'
--- a/mysql-test/r/derived.result 2012-01-18 13:53:00 +0000
+++ b/mysql-test/r/derived.result 2012-01-20 15:30:14 +0000
@@ -845,8 +845,8 @@ EXPLAIN EXTENDED
SELECT * FROM t1,v3 AS v31,v3 WHERE t1.f1=v31.f1 and t1.f1=v3.f1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where
-1 PRIMARY <derived2> ref auto_key0 auto_key0 5 test.t1.f1 3 100.00 Using where
-1 PRIMARY <derived3> ref auto_key0 auto_key0 5 v31.f1 3 100.00 Using where
+1 PRIMARY <derived2> ref auto_key0 auto_key0 5 test.t1.f1 3 100.00
+1 PRIMARY <derived3> ref auto_key0 auto_key0 5 test.t1.f1 3 100.00
3 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00
3 DERIVED t11 ALL NULL NULL NULL NULL 6 100.00 Using join buffer (Block Nested Loop)
2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00
=== modified file 'mysql-test/r/greedy_optimizer.result'
--- a/mysql-test/r/greedy_optimizer.result 2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/greedy_optimizer.result 2012-01-20 15:30:14 +0000
@@ -1068,7 +1068,7 @@ AND t10000.K=t10.I;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
-1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t100.K 1 Using where; Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t100,t10000
WHERE t100.K=t10.I
AND t10000.K=t10.I;
@@ -1094,7 +1094,7 @@ AND t10000.K=t10.I;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
-1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t100.K 1 Using where; Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
SELECT COUNT(*) FROM t10,t100,t10000
WHERE t100.K=t10.I
AND t10000.K=t10.I;
@@ -1107,7 +1107,7 @@ AND t10000.K=t10.I;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 ALL NULL NULL NULL NULL 10 Using where
1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
-1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t100.K 1 Using where; Using index
+1 SIMPLE t10000 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
SELECT COUNT(*) FROM t10,t10000,t100
WHERE t100.K=t10.I
AND t10000.K=t10.I;
@@ -1429,7 +1429,7 @@ AND t10000.I=t10.I;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 index IX IX 5 NULL 10 Using where; Using index
1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
-1 SIMPLE t10000 ref IX IX 5 test.t100.K 1000 Using where; Using index
+1 SIMPLE t10000 ref IX IX 5 test.t10.I 1000 Using index
SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t100,t10000
WHERE t100.K=t10.I
AND t10000.I=t10.I;
@@ -1442,7 +1442,7 @@ AND t10000.I=t10.I;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 index IX IX 5 NULL 10 Using where; Using index
1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
-1 SIMPLE t10000 ref IX IX 5 test.t100.K 1000 Using where; Using index
+1 SIMPLE t10000 ref IX IX 5 test.t10.I 1000 Using index
SELECT COUNT(*) FROM t10,t100,t10000
WHERE t100.K=t10.I
AND t10000.I=t10.I;
@@ -1455,7 +1455,7 @@ AND t10000.I=t10.I;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 index IX IX 5 NULL 10 Using where; Using index
1 SIMPLE t100 eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
-1 SIMPLE t10000 ref IX IX 5 test.t100.K 1000 Using where; Using index
+1 SIMPLE t10000 ref IX IX 5 test.t10.I 1000 Using index
SELECT COUNT(*) FROM t10,t10000,t100
WHERE t100.K=t10.I
AND t10000.I=t10.I;
@@ -1501,7 +1501,7 @@ AND Y.I=t10.I;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 index IX IX 5 NULL 10 Using where; Using index
1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
-1 SIMPLE Y ref IX IX 5 test.X.K 1000 Using where; Using index
+1 SIMPLE Y ref IX IX 5 test.t10.I 1000 Using index
SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
WHERE X.K=t10.I
AND Y.I=t10.I;
@@ -1514,7 +1514,7 @@ AND Y.I=t10.I;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 index IX IX 5 NULL 10 Using where; Using index
1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
-1 SIMPLE Y ref IX IX 5 test.X.K 1000 Using where; Using index
+1 SIMPLE Y ref IX IX 5 test.t10.I 1000 Using index
SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
WHERE X.K=t10.I
AND Y.I=t10.I;
@@ -1527,7 +1527,7 @@ AND Y.I=t10.I;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 index IX IX 5 NULL 10 Using where; Using index
1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
-1 SIMPLE Y ref IX IX 5 test.X.K 1000 Using where; Using index
+1 SIMPLE Y ref IX IX 5 test.t10.I 1000 Using index
SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
WHERE X.K=t10.I
AND Y.I=t10.I;
@@ -1540,7 +1540,7 @@ AND Y.I=X.K;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 index IX IX 5 NULL 10 Using where; Using index
1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
-1 SIMPLE Y ref IX IX 5 test.X.K 1000 Using where; Using index
+1 SIMPLE Y ref IX IX 5 test.t10.I 1000 Using index
SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
WHERE X.K=t10.I
AND Y.I=X.K;
@@ -1553,7 +1553,7 @@ AND Y.I=X.K;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 index IX IX 5 NULL 10 Using where; Using index
1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
-1 SIMPLE Y ref IX IX 5 test.X.K 1000 Using where; Using index
+1 SIMPLE Y ref IX IX 5 test.t10.I 1000 Using index
SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
WHERE X.K=t10.I
AND Y.I=X.K;
@@ -1566,7 +1566,7 @@ AND Y.I=X.K;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t10 index IX IX 5 NULL 10 Using where; Using index
1 SIMPLE X eq_ref PRIMARY PRIMARY 4 test.t10.I 1 Using index
-1 SIMPLE Y ref IX IX 5 test.X.K 1000 Using where; Using index
+1 SIMPLE Y ref IX IX 5 test.t10.I 1000 Using index
SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
WHERE X.K=t10.I
AND Y.I=X.K;
=== modified file 'mysql-test/r/join_cache_bka.result'
--- a/mysql-test/r/join_cache_bka.result 2012-01-19 09:57:18 +0000
+++ b/mysql-test/r/join_cache_bka.result 2012-01-20 15:30:14 +0000
@@ -477,7 +477,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (Batched Key Access)
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition; Using where; Using join buffer (Batched Key Access)
+1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (Batched Key Access)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -784,7 +784,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (Batched Key Access)
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition; Using where; Using join buffer (Batched Key Access)
+1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (Batched Key Access)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -1308,7 +1308,7 @@ id select_type table type possible_keys
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using join buffer (Batched Key Access)
1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Using join buffer (Batched Key Access)
-1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t7.metaid 2 Using index condition; Using join buffer (Batched Key Access)
+1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t1.metaid 2 Using join buffer (Batched Key Access)
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1 Using join buffer (Batched Key Access)
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 Using join buffer (Batched Key Access)
1 SIMPLE t3 ALL t3_metaid,t3_formatid,t3_metaidformatid NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
=== modified file 'mysql-test/r/join_cache_bka_nixbnl.result'
--- a/mysql-test/r/join_cache_bka_nixbnl.result 2012-01-19 09:57:18 +0000
+++ b/mysql-test/r/join_cache_bka_nixbnl.result 2012-01-20 15:30:14 +0000
@@ -477,7 +477,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (Batched Key Access)
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition; Using where; Using join buffer (Batched Key Access)
+1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (Batched Key Access)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -784,7 +784,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (Batched Key Access)
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition; Using where; Using join buffer (Batched Key Access)
+1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (Batched Key Access)
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -1308,7 +1308,7 @@ id select_type table type possible_keys
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using join buffer (Batched Key Access)
1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Using join buffer (Batched Key Access)
-1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t7.metaid 2 Using index condition; Using join buffer (Batched Key Access)
+1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t1.metaid 2 Using join buffer (Batched Key Access)
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1 Using join buffer (Batched Key Access)
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 Using join buffer (Batched Key Access)
1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where; Using join buffer (Batched Key Access)
=== modified file 'mysql-test/r/join_cache_bkaunique.result'
--- a/mysql-test/r/join_cache_bkaunique.result 2012-01-19 09:57:18 +0000
+++ b/mysql-test/r/join_cache_bkaunique.result 2012-01-20 15:30:14 +0000
@@ -478,7 +478,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (Batched Key Access (unique))
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition; Using where; Using join buffer (Batched Key Access (unique))
+1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (Batched Key Access (unique))
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -785,7 +785,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer (Batched Key Access (unique))
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition; Using where; Using join buffer (Batched Key Access (unique))
+1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where; Using join buffer (Batched Key Access (unique))
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -1309,7 +1309,7 @@ id select_type table type possible_keys
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using join buffer (Batched Key Access (unique))
1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Using join buffer (Batched Key Access (unique))
-1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t7.metaid 2 Using index condition; Using join buffer (Batched Key Access (unique))
+1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t1.metaid 2 Using join buffer (Batched Key Access (unique))
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1 Using join buffer (Batched Key Access (unique))
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 Using join buffer (Batched Key Access (unique))
1 SIMPLE t3 ALL t3_metaid,t3_formatid,t3_metaidformatid NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
=== modified file 'mysql-test/r/join_cache_bnl.result'
--- a/mysql-test/r/join_cache_bnl.result 2012-01-19 09:57:18 +0000
+++ b/mysql-test/r/join_cache_bnl.result 2012-01-20 15:30:14 +0000
@@ -478,7 +478,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition; Using where
+1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -785,7 +785,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition; Using where
+1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -1309,7 +1309,7 @@ id select_type table type possible_keys
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1
1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1
-1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t7.metaid 2 Using index condition
+1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t1.metaid 2
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1
1 SIMPLE t3 ALL t3_metaid,t3_formatid,t3_metaidformatid NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
=== modified file 'mysql-test/r/join_cache_nojb.result'
--- a/mysql-test/r/join_cache_nojb.result 2012-01-19 09:57:18 +0000
+++ b/mysql-test/r/join_cache_nojb.result 2012-01-20 15:30:14 +0000
@@ -478,7 +478,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition; Using where
+1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -785,7 +785,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition; Using where
+1 SIMPLE City ref Country Country 3 world.CountryLanguage.Country 18 Using where
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -1309,7 +1309,7 @@ id select_type table type possible_keys
1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1
1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1
-1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t7.metaid 2 Using index condition
+1 SIMPLE t9 ref PRIMARY,t9_subgenreid,t9_metaid t9_metaid 4 test.t1.metaid 2
1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1
1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1
1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where
=== modified file 'mysql-test/r/join_nested.result'
--- a/mysql-test/r/join_nested.result 2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/join_nested.result 2012-01-20 15:30:14 +0000
@@ -1729,9 +1729,9 @@ LEFT JOIN
(t5 JOIN t4 ON t5.carrier_id = t4.id)
ON t4.carrier = t1.carrier;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 36
-1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using where; Using index
-1 SIMPLE t2 ref package_id package_id 5 test.t3.package_id 5 Using where; Using index
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 36 Using where
+1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using index
+1 SIMPLE t2 ref package_id package_id 5 test.t1.id 5 Using index
1 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1
1 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index
SELECT COUNT(*)
=== modified file 'mysql-test/r/join_nested_bka.result'
--- a/mysql-test/r/join_nested_bka.result 2011-09-07 10:21:50 +0000
+++ b/mysql-test/r/join_nested_bka.result 2012-01-20 15:30:14 +0000
@@ -1730,9 +1730,9 @@ LEFT JOIN
(t5 JOIN t4 ON t5.carrier_id = t4.id)
ON t4.carrier = t1.carrier;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 36
-1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using where; Using index
-1 SIMPLE t2 ref package_id package_id 5 test.t3.package_id 5 Using where; Using index
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 36 Using where
+1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using index
+1 SIMPLE t2 ref package_id package_id 5 test.t1.id 5 Using index
1 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1
1 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index
SELECT COUNT(*)
=== modified file 'mysql-test/r/join_nested_bka_nixbnl.result'
--- a/mysql-test/r/join_nested_bka_nixbnl.result 2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/join_nested_bka_nixbnl.result 2012-01-20 15:30:14 +0000
@@ -1734,7 +1734,7 @@ id select_type table type possible_keys
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.package_id 1 Using join buffer (Batched Key Access)
1 SIMPLE t4 eq_ref PRIMARY,id PRIMARY 2 test.t1.carrier 1
1 SIMPLE t5 ref carrier_id carrier_id 5 test.t4.id 22 Using index
-1 SIMPLE t3 ref package_id package_id 5 test.t1.id 1 Using where; Using index
+1 SIMPLE t3 ref package_id package_id 5 test.t2.package_id 1 Using index
SELECT COUNT(*)
FROM ((t2 JOIN t1 ON t2.package_id = t1.id)
JOIN t3 ON t3.package_id = t1.id)
=== modified file 'mysql-test/r/subquery_mat.result'
--- a/mysql-test/r/subquery_mat.result 2012-01-20 09:07:08 +0000
+++ b/mysql-test/r/subquery_mat.result 2012-01-20 15:30:14 +0000
@@ -1276,7 +1276,7 @@ HAVING COUNT(*) > 0
);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 system NULL NULL NULL NULL 1
-1 PRIMARY table2 eq_ref PRIMARY PRIMARY 4 const 1 Using where
+1 PRIMARY table2 const PRIMARY PRIMARY 4 const 1 Using where
2 SUBQUERY innr ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
SELECT table1.pk, table2.pk
FROM t2 AS table1 LEFT JOIN t2 AS table2
=== modified file 'mysql-test/r/subquery_mat_all.result'
--- a/mysql-test/r/subquery_mat_all.result 2012-01-20 09:07:08 +0000
+++ b/mysql-test/r/subquery_mat_all.result 2012-01-20 15:30:14 +0000
@@ -1277,7 +1277,7 @@ HAVING COUNT(*) > 0
);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY table1 system NULL NULL NULL NULL 1
-1 PRIMARY table2 eq_ref PRIMARY PRIMARY 4 const 1 Using where
+1 PRIMARY table2 const PRIMARY PRIMARY 4 const 1 Using where
2 SUBQUERY innr ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort
SELECT table1.pk, table2.pk
FROM t2 AS table1 LEFT JOIN t2 AS table2
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2012-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2012-01-20 15:30:14 +0000
@@ -5333,7 +5333,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using index condition; Using MRR; Materialize; Scan
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using index condition; Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5876,7 +5876,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using index condition; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7626,5 +7626,43 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using index condition; Using MRR; Materialize; Scan
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; Using join buffer (Batched Key Access)
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using index condition; Using where; Using join buffer (Batched Key Access)
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where; Using join buffer (Batched Key Access)
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using index condition; End temporary; Using join buffer (Batched Key Access)
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary; Using join buffer (Batched Key Access)
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using index condition; Using MRR; Materialize; Scan
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; Using join buffer (Batched Key Access)
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using index condition; Using where; Using join buffer (Batched Key Access)
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where; Using join buffer (Batched Key Access)
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using index condition; End temporary; Using join buffer (Batched Key Access)
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary; Using join buffer (Batched Key Access)
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-01-20 15:30:14 +0000
@@ -5335,7 +5335,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using index condition; Using MRR; Materialize; Scan
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; Using join buffer (Batched Key Access (unique))
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using index condition; Using where; Using join buffer (Batched Key Access (unique))
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where; Using join buffer (Batched Key Access (unique))
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5878,7 +5878,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using index condition; End temporary; Using join buffer (Batched Key Access (unique))
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary; Using join buffer (Batched Key Access (unique))
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7628,6 +7628,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-01-20 15:30:14 +0000
@@ -5332,7 +5332,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Start temporary
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5875,7 +5875,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7625,5 +7625,43 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-01-20 15:30:14 +0000
@@ -5333,7 +5333,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Start temporary
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5876,7 +5876,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7626,6 +7626,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-01-20 15:30:14 +0000
@@ -5333,7 +5333,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Start temporary
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5876,7 +5876,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7626,6 +7626,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Start temporary
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-01-20 15:30:14 +0000
@@ -5333,7 +5333,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Start temporary
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5876,7 +5876,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7626,6 +7626,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Start temporary
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Start temporary
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-01-20 15:30:14 +0000
@@ -5335,7 +5335,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Start temporary
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5878,7 +5878,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7628,6 +7628,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2012-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-01-20 15:30:14 +0000
@@ -5333,7 +5333,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Start temporary
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5876,7 +5876,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7626,5 +7626,43 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Start temporary
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Start temporary
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-01-20 15:30:14 +0000
@@ -5335,7 +5335,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Start temporary
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5878,7 +5878,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7628,6 +7628,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2012-01-20 15:30:14 +0000
@@ -5333,7 +5333,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Materialize; Scan
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5876,7 +5876,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7626,5 +7626,43 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Materialize; Scan
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Materialize; Scan
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-01-20 15:30:14 +0000
@@ -5335,7 +5335,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using where; Materialize; Scan
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using where
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using where
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
@@ -5878,7 +5878,7 @@ and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref uid uid 5 const 4 Using where; Start temporary
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
-1 PRIMARY t1 ref uid uid 5 test.t4.uid 2 Using where; End temporary
+1 PRIMARY t1 ref uid uid 5 test.t3.fid 2 End temporary
1 PRIMARY t2 ALL PRIMARY NULL NULL NULL 9 Using where; Using join buffer (Block Nested Loop)
select name from t2, t1
where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7628,6 +7628,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-01-20 15:30:14 +0000
@@ -7702,5 +7702,43 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2012-01-20 15:30:14 +0000
@@ -3402,7 +3402,7 @@ id select_type table type possible_keys
1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY t1 index a a 5 NULL 10 Using where; Using index
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.a 1 Using index
-2 DEPENDENT SUBQUERY t3 ref a a 5 test.t2.a 1 Using where; Using index
+2 DEPENDENT SUBQUERY t3 ref a a 5 test.t1.a 1 Using index
drop table t0, t1,t2,t3;
Test that neither MaterializeLookup strategy for semijoin,
@@ -7637,5 +7637,43 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2012-01-20 15:30:14 +0000
@@ -3403,7 +3403,7 @@ id select_type table type possible_keys
1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY t1 index a a 5 NULL 10 Using where; Using index
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.a 1 Using index
-2 DEPENDENT SUBQUERY t3 ref a a 5 test.t2.a 1 Using where; Using index
+2 DEPENDENT SUBQUERY t3 ref a a 5 test.t1.a 1 Using index
drop table t0, t1,t2,t3;
Test that neither MaterializeLookup strategy for semijoin,
@@ -7638,6 +7638,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-01-20 15:30:14 +0000
@@ -3403,7 +3403,7 @@ id select_type table type possible_keys
1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY t1 index a a 5 NULL 10 Using where; Using index
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.a 1 Using index
-2 DEPENDENT SUBQUERY t3 ref a a 5 test.t2.a 1 Using where; Using index
+2 DEPENDENT SUBQUERY t3 ref a a 5 test.t1.a 1 Using index
drop table t0, t1,t2,t3;
Test that neither MaterializeLookup strategy for semijoin,
@@ -7638,6 +7638,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# 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-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-01-20 15:30:14 +0000
@@ -3404,7 +3404,7 @@ id select_type table type possible_keys
1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY t1 index a a 5 NULL 10 Using where; Using index
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.a 1 Using index
-2 DEPENDENT SUBQUERY t3 ref a a 5 test.t2.a 1 Using where; Using index
+2 DEPENDENT SUBQUERY t3 ref a a 5 test.t1.a 1 Using index
drop table t0, t1,t2,t3;
Test that neither MaterializeLookup strategy for semijoin,
@@ -7639,6 +7639,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
id
200
DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+# matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/suite/opt_trace/r/general2_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_no_prot.result 2012-01-10 18:58:10 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_no_prot.result 2012-01-20 15:30:14 +0000
@@ -2622,7 +2622,7 @@ id select_type table type possible_keys
1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY t1 index a a 5 NULL 10 Using where; Using index
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.a 1 Using index
-2 DEPENDENT SUBQUERY t3 ref a a 5 test.t2.a 1 Using where; Using index
+2 DEPENDENT SUBQUERY t3 ref a a 5 test.t1.a 1 Using index
select * from information_schema.optimizer_trace;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain select *
@@ -3271,7 +3271,7 @@ from t0 where a in
{
"database": "test",
"table": "t3",
- "attached": "(`test`.`t3`.`a` = `test`.`t1`.`a`)"
+ "attached": null
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
=== modified file 'mysql-test/suite/opt_trace/r/general2_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2012-01-10 18:58:10 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_ps_prot.result 2012-01-20 15:30:14 +0000
@@ -2675,7 +2675,7 @@ id select_type table type possible_keys
1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY t1 index a a 5 NULL 10 Using where; Using index
2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.a 1 Using index
-2 DEPENDENT SUBQUERY t3 ref a a 5 test.t2.a 1 Using where; Using index
+2 DEPENDENT SUBQUERY t3 ref a a 5 test.t1.a 1 Using index
select * from information_schema.optimizer_trace;
QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES
explain select *
@@ -3324,7 +3324,7 @@ from t0 where a in
{
"database": "test",
"table": "t3",
- "attached": "(`test`.`t3`.`a` = `test`.`t1`.`a`)"
+ "attached": null
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc 2012-01-20 09:07:08 +0000
+++ b/sql/sql_optimizer.cc 2012-01-20 15:30:14 +0000
@@ -362,8 +362,6 @@ JOIN::optimize()
DBUG_RETURN(1);
}
- drop_unused_derived_keys();
-
if (rollup.state != ROLLUP::STATE_NONE)
{
if (rollup_process_const_fields())
@@ -378,12 +376,6 @@ JOIN::optimize()
select_distinct= select_distinct && (const_tables != tables);
}
- THD_STAGE_INFO(thd, stage_preparing);
- if (result->initialize_tables(this))
- {
- DBUG_PRINT("error",("Error: initialize_tables() failed"));
- DBUG_RETURN(1); // error == -1
- }
if (const_table_map != found_const_table_map &&
!(select_options & SELECT_DESCRIBE) &&
(!conds ||
@@ -438,8 +430,7 @@ JOIN::optimize()
}
/*
- Permorm the the optimization on fields evaluation mentioned above
- for all on expressions.
+ Perform the same optimization on field evaluation for all join conditions.
*/
for (JOIN_TAB *tab= join_tab + const_tables; tab < join_tab + tables ; tab++)
{
@@ -464,6 +455,26 @@ JOIN::optimize()
conds=new Item_int((longlong) 0,1); // Always false
}
+ if (set_access_methods())
+ {
+ error= 1;
+ DBUG_PRINT("error",("Error from set_access_methods"));
+ DBUG_RETURN(1);
+ }
+
+ // We need all derived keys until access methods have been set.
+ drop_unused_derived_keys();
+
+ // Update table dependencies after assigning ref access fields
+ update_depend_map(this);
+
+ THD_STAGE_INFO(thd, stage_preparing);
+ if (result->initialize_tables(this))
+ {
+ DBUG_PRINT("error",("Error: initialize_tables() failed"));
+ DBUG_RETURN(1); // error == -1
+ }
+
if (make_join_select(this, conds))
{
zero_result_cause=
@@ -984,7 +995,33 @@ finish:
return item;
}
-
+
+/**
+ Get the best field substitution for a given field.
+
+ If the field is member of a multiple equality, look up that equality
+ and return the most appropriate field. Usually this is the equivalenced
+ field belonging to the outer-most table in the join order, but
+ @see Item_field::get_subst_item() for details.
+ Otherwise, return the same field.
+
+ @param item_field The field that we are seeking a substitution for.
+ @param cond_equal multiple equalities to search in
+
+ @return The substituted field.
+*/
+
+Item_field *get_best_field(Item_field *item_field, COND_EQUAL *cond_equal)
+{
+ bool dummy;
+ Item_equal *item_eq= find_item_equal(cond_equal, item_field->field, &dummy);
+ if (!item_eq)
+ return item_field;
+
+ return item_eq->get_subst_item(item_field);
+}
+
+
/**
Check whether an equality can be used to build multiple equalities.
@@ -2593,8 +2630,8 @@ void update_depend_map(JOIN *join)
uint i;
for (i=0 ; i < ref->key_parts ; i++,item++)
depend_map|=(*item)->used_tables();
- ref->depend_map=depend_map & ~OUTER_REF_TABLE_BIT;
- depend_map&= ~OUTER_REF_TABLE_BIT;
+ depend_map&= ~PSEUDO_TABLE_BITS;
+ ref->depend_map= depend_map;
for (JOIN_TAB **tab=join->map2table;
depend_map ;
tab++,depend_map>>=1 )
=== modified file 'sql/sql_optimizer.h'
--- a/sql/sql_optimizer.h 2011-12-15 09:00:42 +0000
+++ b/sql/sql_optimizer.h 2012-01-20 15:30:14 +0000
@@ -509,6 +509,7 @@ Item *remove_eq_conds(THD *thd, Item *co
bool is_indexed_agg_distinct(JOIN *join, List<Item_field> *out_args);
Item_equal *find_item_equal(COND_EQUAL *cond_equal, Field *field,
bool *inherited_fl);
+Item_field *get_best_field(Item_field *item_field, COND_EQUAL *cond_equal);
Item *
make_cond_for_table(Item *cond, table_map tables, table_map used_table,
bool exclude_expensive_cond);
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2012-01-17 09:27:34 +0000
+++ b/sql/sql_select.cc 2012-01-20 15:30:14 +0000
@@ -1224,11 +1224,6 @@ bool JOIN::get_best_combination()
set_semijoin_info();
- if (set_access_methods())
- DBUG_RETURN(true);
-
- update_depend_map(this);
-
DBUG_RETURN(false);
}
@@ -1242,6 +1237,11 @@ bool JOIN::get_best_combination()
- There is no key selected (use JT_ALL)
- Loose scan semi-join strategy is selected (use JT_ALL)
- A ref key can be used (use JT_REF, JT_REF_OR_NULL, JT_EQ_REF or JT_FT)
+
+ @note that we cannot setup fields used for ref access before we have sorted
+ the items within multiple equalities according to the final order of
+ the tables involved in the join operation. Currently, this occurs in
+ @see substitute_for_best_equal_field().
*/
bool JOIN::set_access_methods()
{
@@ -1360,8 +1360,14 @@ void JOIN::set_semijoin_info()
@details
This function will set up a ref access using the best key found
during access path analysis and cost analysis.
-*/
+ @note that we cannot setup fields used for ref access before we have sorted
+ the items within multiple equalities according to the final order of
+ the tables involved in the join operation. Currently, this occurs in
+ @see substitute_for_best_equal_field().
+ The exception is ref access for const tables, which are fixed
+ before the greedy search planner is invoked.
+*/
bool create_ref_for_key(JOIN *join, JOIN_TAB *j, Key_use *org_keyuse,
table_map used_tables)
@@ -1452,6 +1458,14 @@ bool create_ref_for_key(JOIN *join, JOIN
keyuse++; // Skip other parts
uint maybe_null= test(keyinfo->key_part[part_no].null_bit);
+
+ if (keyuse->val->type() == Item::FIELD_ITEM)
+ {
+ // Look up the most appropriate field to base the ref access on.
+ keyuse->val= get_best_field(static_cast<Item_field *>(keyuse->val),
+ join->cond_equal);
+ keyuse->used_tables= keyuse->val->used_tables();
+ }
j->ref.items[part_no]=keyuse->val; // Save for cond removal
j->ref.cond_guards[part_no]= keyuse->cond_guard;
if (keyuse->null_rejecting)
@@ -2579,14 +2593,11 @@ bool setup_sj_materialization(JOIN_TAB *
it.rewind();
for (uint i=0; i < sjm->table_cols.elements; i++)
{
- bool dummy;
- Item_equal *item_eq;
Item *item= (it++)->real_item();
DBUG_ASSERT(item->type() == Item::FIELD_ITEM);
- Field *copy_to= ((Item_field*)item)->field;
/*
- Tricks with Item_equal are due to the following: suppose we have a
- query:
+ The trick with get_best_field() is due to the following;
+ suppose we have a query:
... WHERE cond(ot.col) AND ot.col IN (SELECT it2.col FROM it1,it2
WHERE it1.col= it2.col)
@@ -2609,21 +2620,8 @@ bool setup_sj_materialization(JOIN_TAB *
element equality propagation member that refers to table that is
within the subquery.
*/
- item_eq= find_item_equal(tab->join->cond_equal, copy_to, &dummy);
-
- if (item_eq)
- {
- List_iterator<Item_field> it(item_eq->fields);
- Item_field *item;
- while ((item= it++))
- {
- if (!(item->used_tables() & ~emb_sj_nest->sj_inner_tables))
- {
- copy_to= item->field;
- break;
- }
- }
- }
+ Field *copy_to= get_best_field(static_cast<Item_field *>(item),
+ tab->join->cond_equal)->field;
sjm->copy_field[i].set(copy_to, sjm->table->field[i], FALSE);
/* The write_set for source tables must be set up to allow the copying */
bitmap_set_bit(copy_to->table->write_set, copy_to->field_index);
@@ -2634,8 +2632,6 @@ bool setup_sj_materialization(JOIN_TAB *
}
-
-
/**
Plan refinement stage: do various setup things for the executor
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3529 to 3530) Bug#13596176 | Roy Lyseng | 21 Jan |
