3890 Roy Lyseng 2012-03-21
WL#5561 - Enable semi-join transformation with outer join queries
Second worklog commit, after Guilhem's review.
mysql-test/include/subquery_sj.inc
Added a test case to demonstrate impact of table extension when
setting up duplicate weedout for tables involved in outer join.
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_bka.result
mysql-test/r/subquery_all_bka_nixbnl.result
Changed plan due to modifications to nested join advance and backout.
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
New results for the added test case.
sql/item_func.cc
Change a return to an old-style assert in Item_func::fix_fields.
sql/sql_executor.cc
Added a const modifier in do_sj_dups_weedout().
Changed a comment in evaluate_null_complemented_join_record().
sql/sql_optimizer.cc
In optimize_semijoin_nests_for_materialization(), updated code
according to review comments.
sql/sql_planner.cc
Optimize_table_order::check_interleaving_with_nj() and
Optimize_table_order::backout_nj_sj_state() are updated and simplified
according to review comments.
sql/sql_select.cc
Table extension code in duplicate weedout changed according to
review comments (setup_semijoin_dups_elimination()).
Comment added in setup_join_buffering(), according to review
comments.
sql/table.h
Changed comments of new members according to review comments.
Removed is_fully_covered() function, as it was barely used.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_bka.result
mysql-test/r/subquery_all_bka_nixbnl.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
sql/item_func.cc
sql/sql_executor.cc
sql/sql_optimizer.cc
sql/sql_planner.cc
sql/sql_select.cc
sql/table.h
3889 Guilhem Bichot 2012-03-19
Fix for BUG#13685026 ASSERTION CUR_SJ_INNER_TABLES == 0 IN
OPTIMIZE_TABLE_ORDER::CHOOSE_TABLE_ORDER:
cur_sj_inner_tables was not properly computed when
the search depth is less than the number of tables.
@ sql/sql_planner.cc
Scenario of the bug follows.
Search depth is 4, we have 5 tables (one outer "outr", and 4 sj-inner
"aliasN").
When search depth is >=5, we explore plans by traversing a tree of
plans (of depth 5, because we have 5 tables). As we go from the root (no
tables in the plan) to nodes (add more tables), we modify
cur_sj_inner_tables, to represent the open semijoin nests in this
partial plan; when we reach leaves, we have no open nests. When we go
back towards the root, we enter and leave open nests, so we modify
cur_sj_inner_tables; when we're back to the root (have explored all
plans), we have no open nests, cur_sj_inner_tables is back to 0.
With search depth =4, things work differently.
We walk down the tree with depth 4. We select the best 4-table plan,
which is alias1/4/5/3. We thus decide that alias1 will be the first
table in the complete 5-table not-yet-determined plan.
So we put alias1 as first table (search code for comment "select the
first table in the optimal extension as most promising").
And we then explore, with a depth of 4, the sub-tree rooted at
alias1. We explore plan alias1/alias4/5/3/outr and choose
it, then we explore plan alias1/alias5 and reject it for cost
reasons. This has the following problems:
- cur_sj_inner_tables is 0 when we start exploration of the sub-tree
(this is the first oddity: alias1 is part of the plan, so the semijoin
nest is open, but cur_sj_inner_tables is 0!)
- we add alias5 ("push" it): this adds bits to cur_sj_inner_tables
- we discard the plan, so we "pop" alias5: backout_nj_sj_state()
does *not* rollback cur_sj_inner_tables to 0 because it does so only
when popping the *first* table of the nest (here, alias1); as alias1
is not popped in this algorithm (as it's "forever glued to the plan's
first cell"), cur_sj_inner_tables remains non-zero (second oddity)
- we come to the end of choose_plan(), and the assertion fails.
The chosen solution is:
- notice similarity between POSITION::dups_producing_tables and
cur_sj_inner_tables
- notice that POSITION::dups_producing_tables has no problem because
it is attached to POSITION, and thus needs no "backing out" and is
preserved when we stick alias1 into the first cell of the final plan:
both oddities disappear.
- thus, delete cur_sj_inner_tables and use dups_producing_tables
instead. It makes one less state member to maintain.
This required a few small changes:
- dups_producing_tables was left uninitialized (i.e. random) if the
query had no semijoin; as this patch uses this variable more (for
example in the call to Loose_scan_opt.init(), which runs even if we
have no semijoin nest), we need to have it always set, so we
initialize it to 0 in the constructor of POSITION.
- we now set sj_strategy to SJ_OPT_NONE in this same constructor,
which allows removing three "else join->positions[idx].sj_strategy=
SJ_OPT_NONE" branches from sql_planner.cc
- the assertion on cur_sj_inner_tables==0 is now on
dups_producing_tables of the last POSITION of the plan; it shouldn't
be tested if the plan is partial (where it's legal to have inner
tables not yet handled with some strategy as we haven't seen all
tables).
I wondered if cur_embedding_map has the same bug. The answer is that
it used to have it, but that was fixed, see BUG#38795 (search for "Update
the interleaving state after extending the current partial plan with a
new table" in code). Thus, an assertion on this variable is added to
consider_complete_plan().
Now that cur_sj_inner_tables is gone, backout_nj_sj_state() does not
do anything related to semijoin, so is renamed to backout_nj_state().
And Loose_scan_opt::init(), which does not really need the map of inner
tables but only needs to know if there are any, has his
cur_sj_inner_tables argument changed to bool.
Actually, dups_producing_tables and cur_sj_inner_tables are not
exactly the same thing. When we have an inside-out plan (possibly
suitable for loosescan, semijoin materialization scan, duplicates
weedout), have passed the inner tables but not yet the outer tables,
cur_sj_inner_tables is 0 (we're in no nest) but
dups_producing_tables!=0 (because the passed inner tables are not yet
handled with some strategy, as we need to see outer tables before
making a decision).
In advance_sj_state(), Firstmatch or Loosescan start a potential range
if they see that cur_sj_inner_tables==0. Let's consider the following
example of inside-out scenario: a query with two nests:
select * from ot1,ot2 where ot1 SJ it1 and (ot1,ot2) SJ it2,
and this order of tables:
it2
ot1
it1
ot2
it2 is before ot1 and ot2 so this plan starts "inside-out".
Let's assume that advance_sj_state() is at it1.
Firstmatch is chosen for it1 (cur_sj_inner_tables==0). Then
advance_sj_state() comes to ot2, and, in order to handle it2, chooses
some inside-out strategy, for example duplicates weedout. So we have a
dups-weedout range from it2 to ot2, and inside it, a firstmatch range
(it1).
Then fix_semijoin_strategies(), taking
tables from last to first, will see that ot2 has dups-weedout, and
will clear any strategy in the range [it2,ot2]: firstmatch will be
wiped out (see end of fix_semijoin_strategies()) (I have verified this
with a debugger and a real query). So the end result is
that this firstmatch choice in advance_sj_state() was useless:
implementation is not ready for overlapping ranges of
tables. The same is true of setup_semijoin_dups_elimination() (see how
it "leaps" by lengths of pos->n_sj_tables, i.e. leaps from range's start
to range's end, not setting up any strategy for any contained
subrange).
After this patch, Firstmatch will not be chosen for it1 in
advance_sj_state(), because firstmatch will test
dups_producing_tables==0 instead of cur_sj_inner_tables==0, and
dups_producing_tables!=0 between inner and outer tables. The useless
choice will not be made. It is a very good thing, because before this
patch, the fact that firstmatch declared that it would handle it1,
whereas it would be cancelled later in fix_semijoin_strategies(), was
sort of "lying" to the further calls to advance_sj_state() for ot2.
During debugging, it was observed that the optimizer trace gives
confusing output when search depth is not infinite. Indeed, searching
is done like this:
a) explore 4-table plans (shown in trace)
b) pick first table of best 4-table plan (not shown in trace)
c) explore 4-table plans rooted at this first table: shown in trace,
but the fact that they start with the first table is not shown.
Thus the output gets confusing: (a) could show plan t1-t2-t3-t4, then
(c) could show plan t2-t3-t4-t5, giving the impression that this plan
was chosen over t1-t2-t3-t4, while in fact it's t1-t2-t3-t4-t5 which
is chosen. This confusion is fortunately removed by printing the "plan
prefix" when we add a table to the partial plan; this will be done in
a separate patch (bug 13799348).
@ sql/sql_planner.h
removed cur_sj_inner_tables
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
sql/sql_planner.cc
sql/sql_planner.h
sql/sql_select.h
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2012-03-19 10:20:57 +0000
+++ b/mysql-test/include/subquery_sj.inc 2012-03-21 11:22:13 +0000
@@ -1486,6 +1486,43 @@ FROM ot1
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+ a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+
+CREATE TABLE t2 (
+ a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+
+CREATE TABLE t4 (
+ a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+
+CREATE TABLE v (
+ a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+
+let $query=
+SELECT *
+FROM t AS t1
+ LEFT JOIN
+ (t2
+ LEFT JOIN t AS t3
+ ON t3.a IN (SELECT a FROM t AS it)
+ JOIN t4
+ ON t4.a=100
+ )
+ ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+eval explain $query;
+eval $query;
+
+DROP TABLE t,t2,t4,v;
+
--echo # End of WL#5561
--echo #
=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result 2012-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_all.result 2012-03-21 11:22:13 +0000
@@ -6864,14 +6864,14 @@ AND sq2_alias1.col_varchar_key > alias1.
)
;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
-1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x4)
-1 PRIMARY sq2_alias2 index col_int_key col_int_key 4 NULL 5 80.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
+1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 80.00 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY sq2_alias2 index col_int_key col_int_key 4 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
Warnings:
Note 1276 Field or reference 'alias1.col_int_key' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'alias1.col_varchar_key' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`test`.`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `test`.`t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `test`.`t1`.`col_varchar_key`))
+Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`test`.`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `test`.`t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `test`.`t1`.`col_varchar_key`))
SELECT alias2.col_varchar_nokey
FROM v1 AS alias1
RIGHT JOIN t1 AS alias2 ON 1
@@ -6908,14 +6908,14 @@ AND sq2_alias1.col_varchar_key > alias1.
;
EXPLAIN EXTENDED SELECT * FROM v2;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
-1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x4)
-1 PRIMARY sq2_alias2 index col_int_key col_int_key 4 NULL 5 80.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
+1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 80.00 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY sq2_alias2 index col_int_key col_int_key 4 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
Warnings:
Note 1276 Field or reference 'alias1.col_int_key' of SELECT #3 was resolved in SELECT #1
Note 1276 Field or reference 'alias1.col_varchar_key' of SELECT #3 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
+Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
SELECT * FROM v2;
col_varchar_nokey
d
@@ -6939,14 +6939,14 @@ WHERE sq2_alias1.pk != alias1.col_int_ke
AND sq2_alias1.col_varchar_key > alias1.col_varchar_key
);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
-1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x4)
-1 PRIMARY sq2_alias2 index col_int_key col_int_key 4 NULL 5 80.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
+1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 80.00 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY sq2_alias2 index col_int_key col_int_key 4 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
Warnings:
Note 1276 Field or reference 'alias1.col_int_key' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'alias1.col_varchar_key' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
+Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
SELECT alias2.col_varchar_nokey
FROM t1 AS alias2
LEFT JOIN v1 AS alias1 ON 1
=== modified file 'mysql-test/r/subquery_all_bka.result'
--- a/mysql-test/r/subquery_all_bka.result 2012-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_all_bka.result 2012-03-21 11:22:13 +0000
@@ -6865,14 +6865,14 @@ AND sq2_alias1.col_varchar_key > alias1.
)
;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
-1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x4)
-1 PRIMARY sq2_alias2 index col_int_key col_int_key 4 NULL 5 80.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
+1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 80.00 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY sq2_alias2 index col_int_key col_int_key 4 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
Warnings:
Note 1276 Field or reference 'alias1.col_int_key' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'alias1.col_varchar_key' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`test`.`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `test`.`t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `test`.`t1`.`col_varchar_key`))
+Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`test`.`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `test`.`t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `test`.`t1`.`col_varchar_key`))
SELECT alias2.col_varchar_nokey
FROM v1 AS alias1
RIGHT JOIN t1 AS alias2 ON 1
@@ -6909,14 +6909,14 @@ AND sq2_alias1.col_varchar_key > alias1.
;
EXPLAIN EXTENDED SELECT * FROM v2;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
-1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x4)
-1 PRIMARY sq2_alias2 index col_int_key col_int_key 4 NULL 5 80.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
+1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 80.00 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY sq2_alias2 index col_int_key col_int_key 4 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
Warnings:
Note 1276 Field or reference 'alias1.col_int_key' of SELECT #3 was resolved in SELECT #1
Note 1276 Field or reference 'alias1.col_varchar_key' of SELECT #3 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
+Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
SELECT * FROM v2;
col_varchar_nokey
d
@@ -6940,14 +6940,14 @@ WHERE sq2_alias1.pk != alias1.col_int_ke
AND sq2_alias1.col_varchar_key > alias1.col_varchar_key
);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
-1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; Using join buffer (Block Nested Loop)
-1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x4)
-1 PRIMARY sq2_alias2 index col_int_key col_int_key 4 NULL 5 80.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
+1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 80.00 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY sq2_alias2 index col_int_key col_int_key 4 NULL 5 80.00 Using where; Using index; Using join buffer (Block Nested Loop)
+1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; End temporary; Using join buffer (Block Nested Loop)
Warnings:
Note 1276 Field or reference 'alias1.col_int_key' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'alias1.col_varchar_key' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
+Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
SELECT alias2.col_varchar_nokey
FROM t1 AS alias2
LEFT JOIN v1 AS alias1 ON 1
=== modified file 'mysql-test/r/subquery_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_all_bka_nixbnl.result 2012-02-13 11:57:09 +0000
+++ b/mysql-test/r/subquery_all_bka_nixbnl.result 2012-03-21 11:22:13 +0000
@@ -6865,14 +6865,14 @@ AND sq2_alias1.col_varchar_key > alias1.
)
;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 100.00
-1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index
-1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x4)
-1 PRIMARY sq2_alias2 ref col_int_key col_int_key 4 func 2 100.00 Using where; Using index; FirstMatch(t1)
+1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
+1 PRIMARY alias2 ref col_varchar_key col_varchar_key 3 test.t1.col_varchar_nokey 1 100.00 Using join buffer (Batched Key Access)
+1 PRIMARY sq2_alias2 ref col_int_key col_int_key 4 func 2 100.00 Using where; Using index
+1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; End temporary
Warnings:
Note 1276 Field or reference 'alias1.col_int_key' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'alias1.col_varchar_key' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`test`.`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `test`.`t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `test`.`t1`.`col_varchar_key`))
+Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`test`.`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `test`.`t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `test`.`t1`.`col_varchar_key`))
SELECT alias2.col_varchar_nokey
FROM v1 AS alias1
RIGHT JOIN t1 AS alias2 ON 1
@@ -6909,14 +6909,14 @@ AND sq2_alias1.col_varchar_key > alias1.
;
EXPLAIN EXTENDED SELECT * FROM v2;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 100.00
-1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index
-1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x4)
-1 PRIMARY sq2_alias2 ref col_int_key col_int_key 4 func 2 100.00 Using where; Using index; FirstMatch(t1)
+1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
+1 PRIMARY alias2 ref col_varchar_key col_varchar_key 3 test.t1.col_varchar_nokey 1 100.00 Using join buffer (Batched Key Access)
+1 PRIMARY sq2_alias2 ref col_int_key col_int_key 4 func 2 100.00 Using where; Using index
+1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; End temporary
Warnings:
Note 1276 Field or reference 'alias1.col_int_key' of SELECT #3 was resolved in SELECT #1
Note 1276 Field or reference 'alias1.col_varchar_key' of SELECT #3 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
+Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
SELECT * FROM v2;
col_varchar_nokey
d
@@ -6940,14 +6940,14 @@ WHERE sq2_alias1.pk != alias1.col_int_ke
AND sq2_alias1.col_varchar_key > alias1.col_varchar_key
);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY alias2 ALL col_varchar_key NULL NULL NULL 5 100.00
-1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index
-1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Range checked for each record (index map: 0x4)
-1 PRIMARY sq2_alias2 ref col_int_key col_int_key 4 func 2 100.00 Using where; Using index; FirstMatch(t1)
+1 PRIMARY t1 ALL col_varchar_key NULL NULL NULL 5 100.00 Start temporary
+1 PRIMARY alias2 ref col_varchar_key col_varchar_key 3 test.t1.col_varchar_nokey 1 100.00 Using join buffer (Batched Key Access)
+1 PRIMARY sq2_alias2 ref col_int_key col_int_key 4 func 2 100.00 Using where; Using index
+1 PRIMARY t1 index NULL col_varchar_key 7 NULL 5 100.00 Using where; Using index; End temporary
Warnings:
Note 1276 Field or reference 'alias1.col_int_key' of SELECT #2 was resolved in SELECT #1
Note 1276 Field or reference 'alias1.col_varchar_key' of SELECT #2 was resolved in SELECT #1
-Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`t1`.`col_varchar_nokey` = `test`.`alias2`.`col_varchar_key`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
+Note 1003 /* select#1 */ select `test`.`alias2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t1` `alias2` semi join (`test`.`t1` left join `test`.`t1` `sq2_alias2` on((`sq2_alias2`.`col_int_key` = `test`.`t1`.`pk`))) left join (`test`.`t1`) on(1) where ((`test`.`alias2`.`col_varchar_key` = `test`.`t1`.`col_varchar_nokey`) and (`test`.`t1`.`pk` <> `t1`.`col_int_key`) and (`test`.`t1`.`col_varchar_key` > `t1`.`col_varchar_key`))
SELECT alias2.col_varchar_nokey
FROM t1 AS alias2
LEFT JOIN v1 AS alias1 ON 1
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2012-03-21 11:22:13 +0000
@@ -4553,6 +4553,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t3)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t3)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t3)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-03-21 11:22:13 +0000
@@ -4555,6 +4555,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t3)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-03-21 11:22:13 +0000
@@ -4552,6 +4552,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-03-21 11:22:13 +0000
@@ -4553,6 +4553,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-03-21 11:22:13 +0000
@@ -4553,6 +4553,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-03-21 11:22:13 +0000
@@ -4553,6 +4553,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t3)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t3)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t3)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-03-21 11:22:13 +0000
@@ -4555,6 +4555,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; FirstMatch(t3)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-03-21 11:22:13 +0000
@@ -4553,6 +4553,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-03-21 11:22:13 +0000
@@ -4555,6 +4555,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2012-03-21 11:22:13 +0000
@@ -4553,6 +4553,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-03-21 11:22:13 +0000
@@ -4554,6 +4554,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-03-21 11:22:13 +0000
@@ -4555,6 +4555,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY it ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-03-21 11:22:13 +0000
@@ -4622,6 +4622,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+3 SUBQUERY it2 ALL NULL NULL NULL NULL 2
+2 SUBQUERY it ALL NULL NULL NULL NULL 1
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2012-03-21 11:22:13 +0000
@@ -4564,6 +4564,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY it ALL NULL NULL NULL NULL 1 Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2012-03-21 11:22:13 +0000
@@ -4565,6 +4565,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY it ALL NULL NULL NULL NULL 1 Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-03-21 11:22:13 +0000
@@ -4565,6 +4565,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY it ALL NULL NULL NULL NULL 1 Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result 2012-03-19 10:20:57 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-03-21 11:22:13 +0000
@@ -4566,6 +4566,54 @@ a a a a
6 6 6 6
7 NULL NULL NULL
DROP TABLE ot1,ot2,ot3,it1,it2,it3;
+CREATE TABLE t (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t VALUES (1);
+CREATE TABLE t2 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1),(1);
+CREATE TABLE t4 (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO t4 VALUES (1),(1);
+CREATE TABLE v (
+a INTEGER DEFAULT NULL
+) ENGINE=InnoDB;
+INSERT INTO v VALUES (1),(1);
+explain SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY t4 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (Block Nested Loop)
+3 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY it ALL NULL NULL NULL NULL 1 Using where
+SELECT *
+FROM t AS t1
+LEFT JOIN
+(t2
+LEFT JOIN t AS t3
+ON t3.a IN (SELECT a FROM t AS it)
+JOIN t4
+ON t4.a=100
+)
+ON TRUE
+WHERE t1.a IN (SELECT * FROM v AS it2);
+a a a a
+1 NULL NULL NULL
+DROP TABLE t,t2,t4,v;
# End of WL#5561
#
# Bug#48868: Left outer join in subquery causes segmentation fault in
=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc 2012-02-13 11:57:09 +0000
+++ b/sql/item_func.cc 2012-03-21 11:22:13 +0000
@@ -172,8 +172,7 @@ Item_func::Item_func(THD *thd, Item_func
bool
Item_func::fix_fields(THD *thd, Item **ref)
{
- if (fixed)
- return false;
+ DBUG_ASSERT(fixed == 0 || basic_const_item());
Item **arg,**arg_end;
uchar buff[STACK_BUFF_ALLOC]; // Max argument in function
=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc 2012-02-13 11:57:09 +0000
+++ b/sql/sql_executor.cc 2012-03-21 11:22:13 +0000
@@ -2155,7 +2155,7 @@ int do_sj_dups_weedout(THD *thd, SJ_TMP_
}
// 2. Zero the null bytes
- uchar *nulls_ptr= ptr;
+ uchar *const nulls_ptr= ptr;
if (sjtbl->null_bytes)
{
memset(ptr, 0, sjtbl->null_bytes);
@@ -2482,7 +2482,7 @@ evaluate_null_complemented_join_record(J
mark_as_null_row(join_tab->table); // For group by without error
if (join_tab->flush_weedout_table && join_tab > first_inner_tab)
{
- // This qualifies as a new scan over the table
+ // sub_select() has not performed a reset for this table.
do_sj_reset(join_tab->flush_weedout_table);
}
/* Check all attached conditions for inner table rows. */
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc 2012-03-14 12:03:54 +0000
+++ b/sql/sql_optimizer.cc 2012-03-21 11:22:13 +0000
@@ -2608,18 +2608,17 @@ static bool record_semijoin_nests(st_sel
/**
Assign each nested join structure a bit in nested_join_map.
- Assign each nested join structure (except "confluent" ones - those that
- embed only one element) a bit in nested_join_map.
-
- @param join Join being processed
@param join_list List of tables
@param first_unused Number of first unused bit in nested_join_map before the
call
@note
This function is called after simplify_joins(), when there are no
- redundant nested joins, #non_confluent_nested_joins <= #tables_in_join so
- we will not run out of bits in nested_join_map.
+ redundant nested joins.
+ We cannot have more nested joins in a query block than there are tables,
+ so as long as the number of bits in nested_join_map is not less than the
+ maximum number of tables in a query block, nested_join_map can never
+ overflow.
@return
First unused bit in nested_join_map after the call.
@@ -2645,16 +2644,25 @@ static uint build_bitmap_for_nested_join
*/
if (table->join_cond())
{
+ DBUG_ASSERT(first_unused < sizeof(nested_join_map)*8);
nested_join->nj_map= (nested_join_map) 1 << first_unused++;
nested_join->nj_total= nested_join->join_list.elements;
}
else if (table->sj_on_expr)
{
- NESTED_JOIN* outer_nest= table->embedding ?
- table->embedding->nested_join : NULL;
+ NESTED_JOIN *const outer_nest=
+ table->embedding ? table->embedding->nested_join : NULL;
+ /*
+ The semi-join nest has already been counted into the table count
+ for the outer join nest as one table, so subtract 1 from the
+ table count.
+ */
if (outer_nest)
- outer_nest->nj_total+= (nested_join->join_list.elements-1);
+ outer_nest->nj_total+= (nested_join->join_list.elements - 1);
}
+ else
+ DBUG_ASSERT(false);
+
first_unused= build_bitmap_for_nested_joins(&nested_join->join_list,
first_unused);
}
@@ -3411,9 +3419,8 @@ error:
@param[inout] sj_nest Semi-join nest containing information about correlated
expressions. Set nested_join->sjm.scan_allowed to TRUE if
- MaterializeScan strategy allowed.
-
- @return TRUE if materialization is allowed, FALSE otherwise
+ MaterializeScan strategy allowed. Set nested_join->sjm.lookup_allowed
+ to TRUE if MaterializeLookup strategy allowed
@details
This is a temporary fix for BUG#36752.
@@ -3488,6 +3495,43 @@ void semijoin_types_allow_materializatio
sj_nest->nested_join->sjm.scan_allowed= all_are_fields;
sj_nest->nested_join->sjm.lookup_allowed= !blobs_involved;
DBUG_PRINT("info",("semijoin_types_allow_materialization: ok, allowed"));
+
+#if defined(UNUSED)
+ // @todo: When sub_select_sjm() can handle outer joins,
+ // enable the lines marked SJM-DISABLED and disable the lines
+ // marked SJM-ENABLED.
+ // Note also next todo a few lines below.
+ //if (sj_nest->nested_join->sjm.scan_allowed) SJM-DISABLED
+ if (sj_nest->nested_join->sjm.scan_allowed || // SJM-ENABLED
+ sj_nest->nested_join->sjm.lookup_allowed) // SJM-ENABLED
+ {
+ /*
+ If the semi-join contains dependencies to outer tables,
+ materialize-scan strategy cannot be used.
+ */
+ JOIN_TAB *jt;
+ for (jt= join->join_tab + join->const_tables;
+ jt < join->join_tab + join->tables;
+ jt++)
+ {
+ if ((jt->table->map & sj_nest->sj_inner_tables) &&
+ (jt->dependent & ~sj_nest->sj_inner_tables))
+ break;
+ }
+ //if (jt < join->join_tab + join->tables) SJM-DISABLED
+ // sj_nest->nested_join->sjm.scan_allowed= false; SJM-DISABLED
+ if (jt < join->join_tab + join->tables) // SJM-ENABLED
+ { // SJM-ENABLED
+ sj_nest->nested_join->sjm.scan_allowed= false; // SJM-ENABLED
+ sj_nest->nested_join->sjm.lookup_allowed= false; // SJM-ENABLED
+ } // SJM-ENABLED
+ }
+
+ DBUG_PRINT("info",("semijoin_types_allow_materialization: %s",
+ sj_nest->nested_join->sjm.scan_allowed ||
+ sj_nest->nested_join->sjm.lookup_allowed ?
+ "ok, allowed" : "not allowed"));
+#endif
DBUG_VOID_RETURN;
}
@@ -3902,35 +3946,24 @@ static bool optimize_semijoin_nests_for_
*/
semijoin_types_allow_materialization(sj_nest);
- // @todo: When sub_select_sjm() can handle outer joins,
- // enable the lines marked SJM-DISABLED and disable the lines
- // marked SJM-ENABLED.
- // Note also next todo a few lines below.
- //if (sj_nest->nested_join->sjm.scan_allowed) SJM-DISABLED
- if (sj_nest->nested_join->sjm.scan_allowed || // SJM-ENABLED
- sj_nest->nested_join->sjm.lookup_allowed) // SJM-ENABLED
+ if (sj_nest->embedding)
{
+ DBUG_ASSERT(sj_nest->embedding->join_cond());
/*
- If the semi-join contains dependencies to outer tables,
- materialize-scan strategy cannot be used.
+ There are two issues that prevent materialization strategy
+ from being used when a semi-join nest is on the inner side of
+ an outer join:
+ 1. If the semi-join contains dependencies to outer tables,
+ materialize-scan strategy cannot be used.
+ 2. sub_select_sjm() lacks support for setup of first_unmatched
+ and evaluation of triggered conditions, as in
+ evaluate_null_complemented_join_record().
+ Handle this by disabling materialization strategies:
*/
- JOIN_TAB *jt;
- for (jt= join->join_tab + join->const_tables;
- jt < join->join_tab + join->tables;
- jt++)
- {
- if ((jt->table->map & sj_nest->sj_inner_tables) &&
- (jt->dependent & ~sj_nest->sj_inner_tables))
- break;
- }
- //if (jt < join->join_tab + join->tables) SJM-DISABLED
- // sj_nest->nested_join->sjm.scan_allowed= false; SJM-DISABLED
- if (jt < join->join_tab + join->tables) // SJM-ENABLED
- { // SJM-ENABLED
- sj_nest->nested_join->sjm.scan_allowed= false; // SJM-ENABLED
- sj_nest->nested_join->sjm.lookup_allowed= false; // SJM-ENABLED
- } // SJM-ENABLED
+ sj_nest->nested_join->sjm.scan_allowed= false;
+ sj_nest->nested_join->sjm.lookup_allowed= false;
}
+
if (sj_nest->nested_join->sjm.scan_allowed ||
sj_nest->nested_join->sjm.lookup_allowed)
{
=== modified file 'sql/sql_planner.cc'
--- a/sql/sql_planner.cc 2012-03-19 10:20:57 +0000
+++ b/sql/sql_planner.cc 2012-03-21 11:22:13 +0000
@@ -2753,20 +2753,12 @@ bool Optimize_table_order::check_interle
*/
for (;next_emb; next_emb= next_emb->embedding)
{
+ // Ignore join nests that are not outer joins.
if (!next_emb->nested_join->nj_map)
continue;
- DBUG_ASSERT(next_emb->nested_join->nj_counter <
- next_emb->nested_join->nj_total);
+
next_emb->nested_join->nj_counter++;
- if (next_emb->nested_join->nj_counter == 1)
- {
- /*
- next_emb is the first table inside a nested join we've "entered". In
- the picture above, we're looking at the 'X' bracket. Don't exit yet as
- X bracket might have Y pair bracket.
- */
- cur_embedding_map |= next_emb->nested_join->nj_map;
- }
+ cur_embedding_map |= next_emb->nested_join->nj_map;
if (next_emb->nested_join->nj_total != next_emb->nested_join->nj_counter)
break;
@@ -3727,21 +3719,22 @@ void Optimize_table_order::backout_nj_st
for (; last_emb != emb_sjm_nest; last_emb= last_emb->embedding)
{
- if (last_emb->nested_join->nj_map)
- {
- NESTED_JOIN *nest= last_emb->nested_join;
- DBUG_ASSERT(nest->nj_counter > 0);
+ NESTED_JOIN *const nest= last_emb->nested_join;
- bool was_fully_covered= nest->is_fully_covered();
+ // Ignore join nests that are not outer joins.
+ if (!nest->nj_map)
+ continue;
- if (--nest->nj_counter == 0)
- cur_embedding_map&= ~nest->nj_map;
+ DBUG_ASSERT(nest->nj_counter > 0);
- if (!was_fully_covered)
- break;
+ cur_embedding_map|= nest->nj_map;
+ bool was_fully_covered= nest->nj_total == nest->nj_counter;
- cur_embedding_map|= nest->nj_map;
- }
+ if (--nest->nj_counter == 0)
+ cur_embedding_map&= ~nest->nj_map;
+
+ if (!was_fully_covered)
+ break;
}
}
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2012-02-13 11:57:09 +0000
+++ b/sql/sql_select.cc 2012-03-21 11:22:13 +0000
@@ -534,15 +534,8 @@ static bool setup_semijoin_dups_eliminat
}
}
- TABLE_LIST *first_embedding=
- (join->join_tab+first_table)->table->pos_in_table_list->embedding;
- if (first_embedding && first_embedding->sj_on_expr)
- first_embedding= first_embedding->embedding;
- TABLE_LIST *last_embedding=
- last_sj_tab->table->pos_in_table_list->embedding;
- if (last_embedding && last_embedding->sj_on_expr)
- last_embedding= last_embedding->embedding;
- if (first_embedding != last_embedding)
+ JOIN_TAB *const first_sj_tab= join->join_tab+first_table;
+ if (first_sj_tab->first_inner != last_sj_tab->first_inner)
{
/*
The first duplicate weedout table is an outer table of an outer join
@@ -550,26 +543,22 @@ static bool setup_semijoin_dups_eliminat
the outer join.
In this case, we must assure that all the inner tables of the
outer join is part of the duplicate weedout operation.
+ This is to assure that NULL-extension for inner tables of an
+ outer join is performed before duplicate elimination is performed,
+ otherwise we will have extra NULL-extended rows being output, which
+ should have been eliminated as duplicates.
*/
- for (JOIN_TAB *tab= last_sj_tab + 1;
- tab < join->join_tab + join->tables; tab++)
- {
- TABLE_LIST *embedding= tab->table->pos_in_table_list->embedding;
- if (embedding && embedding->sj_on_expr)
- embedding= embedding->embedding;
- // We are past the range if the join nest immediately containing
- // this table contains the first table as well:
- if (embedding == first_embedding)
- break;
- while (embedding && embedding != first_embedding)
- embedding= embedding->embedding;
- // We are past the range if we never reached the join nest
- // containing the first table:
- if (embedding != first_embedding)
- break;
- // Extend the duplicate weedout range with this table:
- last_sj_tab= tab;
- }
+ JOIN_TAB *tab= last_sj_tab->first_inner;
+ /*
+ First, locate the table that is the first inner table of the
+ outer join operation that first_sj_tab is outer for.
+ */
+ while (tab->first_upper != NULL &&
+ tab->first_upper != first_sj_tab->first_inner)
+ tab= tab->first_upper;
+ // Then, extend the range with all inner tables of the join nest:
+ if (tab->first_inner->last_inner > last_sj_tab)
+ last_sj_tab= tab->first_inner->last_inner;
}
SJ_TMP_TABLE::TAB sjtabs[MAX_TABLES];
@@ -2288,8 +2277,22 @@ static bool setup_join_buffering(JOIN_TA
tab->first_sj_inner_tab != (tab-1)->first_sj_inner_tab) // 3
prev_cache= NULL;
- // @todo The following code eliminates use of join buffering when there is an
- // outer join operation and first match semi-join strategy is used.
+ /*
+ The following code eliminates use of join buffering when there is an
+ outer join operation and first match semi-join strategy is used, because:
+
+ Outer join needs a "match flag" to track what a row should be
+ NULL-complemented, such flag being attached to first inner table's cache
+ (tracks whether the cached row from outer table got a match, in which case
+ no NULL-complemented row is needed).
+
+ FirstMatch also needs a "match flag", such flag is attached to sj inner
+ table's cache (tracks whether the cached row from outer table already got
+ a first match in the sj-inner table, in which case we don't need to join
+ this cached row again)
+ - but a row in a cache has only one "match flag"
+ - so if "sj inner table"=="first inner", there is a problem.
+ */
if (tab_sj_strategy == SJ_OPT_FIRST_MATCH &&
tab->is_inner_table_of_outer_join())
goto no_join_cache;
=== modified file 'sql/table.h'
--- a/sql/table.h 2012-02-13 11:57:09 +0000
+++ b/sql/table.h 2012-03-21 11:22:13 +0000
@@ -2099,6 +2099,14 @@ struct Semijoin_mat_optimize
Cost_estimate scan_cost;
};
+/**
+ Struct st_nested_join is used to represent how tables are connected through
+ outer join operations and semi-join operations to form a query block.
+ Out of the parser, inner joins are also represented by st_nested_join
+ structs, but these are later flattened out by simplify_joins().
+ Some outer join nests are also flattened, when it can be determined that
+ they can be processed as inner joins instead of outer joins.
+*/
typedef struct st_nested_join
{
List<TABLE_LIST> join_list; /* list of elements in the nested join */
@@ -2109,13 +2117,14 @@ typedef struct st_nested_join
join nest. It is used exclusively within make_outerjoin_info().
*/
struct st_join_table *first_nested;
- /*
- Number of tables administered by this nested join object for the sake
- of cost analysis. Includes direct member tables as well as tables
- included through semi-join nests.
+ /**
+ Number of tables and outer join nests administered by this nested join
+ object for the sake of cost analysis. Includes direct member tables as
+ well as tables included through semi-join nests, but notice that semi-join
+ nests themselves are not counted.
*/
uint nj_total;
- /*
+ /**
Used to count tables in the nested join in 2 isolated places:
1. In make_outerjoin_info().
2. check_interleaving_with_nj/restore_prev_nj_state (these are called
@@ -2123,9 +2132,9 @@ typedef struct st_nested_join
Before each use the counters are zeroed by reset_nj_counters.
*/
uint nj_counter;
- /*
+ /**
Bit identifying this nested join. Only nested joins representing the
- outer join structure needs this, other nests have bit set to zero.
+ outer join structure need this, other nests have bit set to zero.
*/
nested_join_map nj_map;
/*
@@ -2141,15 +2150,6 @@ typedef struct st_nested_join
*/
List<Item> sj_outer_exprs, sj_inner_exprs;
Semijoin_mat_optimize sjm;
- /**
- True if this join nest node is completely covered by the query execution
- plan. This means two things.
-
- 1. All tables on its @c join_list are covered by the plan.
-
- 2. All child join nest nodes are fully covered.
- */
- bool is_fully_covered() const { return nj_total == nj_counter; }
} NESTED_JOIN;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3889 to 3890) WL#5561 | Roy Lyseng | 21 Mar |
