#At file:///home/oysteing/mysql/mysql-6.0-codebase-bugfixing-2/ based on revid:tor.didriksen@stripped
3717 oystein.grovlen@stripped 2009-11-18
Bug#46692 Crash occurring on queries with nested FROM subqueries
using materialization
Subqueries with STRAIGHT_JOIN prevents pull-out of tables that would normally
be pulled out of the subquery. This created scenarioes which the plan search
was not prepared for, and one ended up without any valid plan. This caused
segmentation fault in optimize_semijoin_nests when trying to access the best
plan.
The problem may occur with either const tables or eq_ref relations that
does not have any matches. Bug#46692 reports the problem for FROM subqueries,
but ordinary IN subqueries will have the same issue.
The following changes are needed to handle this problem:
1. Do not include const tables in the set of tables to be considered
by optimize_semijon_nests(). This will make sure cost estimation is
not skipped for tables that depend on const tables. (This is
consistent with what is done for top level joins where const tables
are filtered out from the set out tables considered by
choose_plan.)
2. Currently, when the table that is added to the join prefix, is
empty, fanout will become zero since it is the product of the
number of records of all tables. A zero fanout will cause the
computed cost to become infinite. Hence, in order to get a valid
cost, we need to skip empty tables when computing the fanout.
The fanout issue (issue 2 above) is also present with the duplicate weedout
strategy, and this patch contains the necessary changes to handle fanout
in this scenario, too.
A number of bug reports have been marked as duplicate of this bug report
based on that this fix also fixes these issues. Those bug reports show
that this is not just an issue for STRAIGHT_JOIN, but also for both right
and left outer join. This patch also adds some test cases for these
duplicate reports as well as general testing of outer joins in subqueries
when constant tables are involved.
@ mysql-test/r/subselect3.result
Updated results for test for subqueries with STRAIGHT_JOIN and outer join for
different permutations of const and non-const tables.
@ mysql-test/r/subselect3_jcl6.result
Updated results for test for subqueries with STRAIGHT_JOIN and outer join for
different permutations of const and non-const tables.
@ mysql-test/r/subselect4.result
Updated with results for Bug#46692 test case and test cases for duplicate
bug reports.
@ mysql-test/t/subselect3.test
Add test cases to check that subqueries with STRAIGHT_JOIN or outer
join work for different permutations of const and non-const tables. Similar
checking is also done for the duplicate weedout strategy (by turning off FirstMatch and Materialization).
Note that due to a still existing bug, left outer join is not tested.
@ mysql-test/t/subselect4.test
Added a test case that is similar to the query reported in Bug#46692.
Also adds a few test cases for duplicate bug reports.
@ sql/sql_select.cc
1. Do not include const tables in the set of tables to be considered
by optimize_semijon_nests(). This will make sure cost estimation is
not skipped for tables that depend on const tables. (This is
consistent with what is done for top level joins where const tables
are filtered out from the set out tables considered by
choose_plan.)
2. Make sure fanout is always >= 1 when computing plan cost.
A zero fanout will cause the computed cost to become infinite.
Hence, in order to get a valid cost, we need to skip empty tables when
computing the fanout.
modified:
mysql-test/r/subselect3.result
mysql-test/r/subselect3_jcl6.result
mysql-test/r/subselect4.result
mysql-test/t/subselect3.test
mysql-test/t/subselect4.test
sql/sql_select.cc
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result 2009-11-18 06:14:20 +0000
+++ b/mysql-test/r/subselect3.result 2009-11-18 17:36:15 +0000
@@ -1306,6 +1306,327 @@ id select_type table type possible_keys
1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using join buffer
1 PRIMARY Z ALL NULL NULL NULL NULL 6 End materialize; Using join buffer
drop table t0,t1,t2;
+CREATE TABLE t1 (i INTEGER);
+CREATE TABLE t2 (i INTEGER);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (0);
+DROP TABLE t2;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (i INTEGER);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (0);
+DROP TABLE t2;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (i INTEGER);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (0);
+DROP TABLE t2;
+INSERT INTO t1 VALUES (0);
+DROP TABLE t1;
+set @@optimizer_switch='firstmatch=off,materialization=off';
+CREATE TABLE t1 (i INTEGER);
+CREATE TABLE t2 (i INTEGER);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (0);
+DROP TABLE t2;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (i INTEGER);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (0);
+DROP TABLE t2;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (i INTEGER);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (0);
+DROP TABLE t2;
+INSERT INTO t1 VALUES (0);
+DROP TABLE t1;
+set @@optimizer_switch=default;
BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307
=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result 2009-11-18 06:14:20 +0000
+++ b/mysql-test/r/subselect3_jcl6.result 2009-11-18 17:36:15 +0000
@@ -1311,6 +1311,327 @@ id select_type table type possible_keys
1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using join buffer
1 PRIMARY Z ALL NULL NULL NULL NULL 6 End materialize; Using join buffer
drop table t0,t1,t2;
+CREATE TABLE t1 (i INTEGER);
+CREATE TABLE t2 (i INTEGER);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (0);
+DROP TABLE t2;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (i INTEGER);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (0);
+DROP TABLE t2;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (i INTEGER);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN
+(SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+i
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (0);
+DROP TABLE t2;
+INSERT INTO t1 VALUES (0);
+DROP TABLE t1;
+set @@optimizer_switch='firstmatch=off,materialization=off';
+CREATE TABLE t1 (i INTEGER);
+CREATE TABLE t2 (i INTEGER);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (0);
+DROP TABLE t2;
+INSERT INTO t1 VALUES (2);
+CREATE TABLE t2 (i INTEGER);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (0);
+DROP TABLE t2;
+INSERT INTO t1 VALUES (1);
+CREATE TABLE t2 (i INTEGER);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (2);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (1);
+CREATE TABLE t3 (i INTEGER);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (2);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (1);
+SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+i
+INSERT INTO t3 VALUES (0);
+DROP TABLE t3;
+INSERT INTO t2 VALUES (0);
+DROP TABLE t2;
+INSERT INTO t1 VALUES (0);
+DROP TABLE t1;
+set @@optimizer_switch=default;
BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307
=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result 2009-11-03 09:42:49 +0000
+++ b/mysql-test/r/subselect4.result 2009-11-18 17:36:15 +0000
@@ -131,3 +131,82 @@ set @@session.optimizer_switch
@@session.optimizer_use_mrr = @old_optimizer_use_mrr,
@@session.engine_condition_pushdown = @old_engine_condition_pushdown;
DROP TABLE t1;
+#
+# Bug#46692 "Crash occurring on queries with nested FROM subqueries
+# using materialization."
+#
+CREATE TABLE t1 (
+pk INTEGER PRIMARY KEY,
+int_key INTEGER,
+KEY int_key(int_key)
+);
+INSERT INTO t1 VALUES (10,186),(11,NULL),(12,2),(13,3),(14,0),(15,133),(16,1);
+CREATE TABLE t2 (
+pk INTEGER PRIMARY KEY,
+int_key INTEGER,
+KEY int_key(int_key)
+);
+INSERT INTO t2 VALUES (1,7),(2,2);
+SELECT int_key FROM
+(SELECT * FROM t1 WHERE (140, 4) IN
+(SELECT t2.int_key, t2 .pk FROM t2 STRAIGHT_JOIN t1 ON t2.int_key)) table1;
+int_key
+DROP TABLE t1, t2;
+#
+# Bug#42353 "SELECT ... WHERE oe IN (SELECT w/ LEFT JOIN) query
+# causes crash."
+#
+CREATE TABLE t1 (
+pk INTEGER PRIMARY KEY,
+int_nokey INTEGER,
+int_key INTEGER,
+date_key DATE,
+datetime_nokey DATETIME,
+varchar_nokey VARCHAR(1)
+);
+CREATE TABLE t2 (
+date_nokey DATE
+);
+CREATE TABLE t3 (
+pk INTEGER PRIMARY KEY,
+int_nokey INTEGER,
+date_key date,
+varchar_key VARCHAR(1),
+varchar_nokey VARCHAR(1),
+KEY date_key (date_key)
+);
+SELECT DISTINCT date_key FROM t1
+WHERE (int_key, int_nokey)
+IN (SELECT t3.int_nokey, t3.pk
+FROM t2 LEFT JOIN t3 ON (t2.date_nokey < t3.date_key)
+WHERE t3.varchar_key <= t3.varchar_nokey OR t3.int_nokey <= t3.pk
+)
+AND (varchar_nokey <> 'f' OR NOT int_key < 7)
+HAVING date_key < '2001-11-14 00:43:55'
+ORDER BY datetime_nokey, pk;
+date_key
+#
+# Bug#45933 "Crash in optimize_semijoin_nests on JOIN in subquery
+# + AND in outer query".
+#
+INSERT INTO t1 VALUES (10,7,5,'2009-06-16','2002-04-10 14:25:30','w'),
+(11,7,0,'0000-00-00','0000-00-00 00:00:00','s'),
+(12,4,0,'2003-07-14','2006-09-14 04:01:02','y'),
+(13,0,4,'2002-07-25','0000-00-00 00:00:00','c'),
+(14,1,8,'2007-07-03','0000-00-00 00:00:00','q'),
+(15,6,5,'2001-11-12','0000-00-00 00:00:00',''),
+(16,2,9,'0000-00-00','0000-00-00 00:00:00','j'),
+(29,9,1,'0000-00-00','2003-08-11 00:00:00','m');
+INSERT INTO t3 VALUES (1,9,'0000-00-00','b','b'),
+(2,2,'2002-09-17','h','h');
+SELECT t1.varchar_nokey FROM t1 JOIN t3 ON t1.datetime_nokey
+WHERE t1.varchar_nokey
+IN (SELECT varchar_nokey FROM t1
+WHERE (pk)
+IN (SELECT t3.int_nokey
+FROM t3 LEFT JOIN t1 ON t1.varchar_nokey
+WHERE t3.date_key BETWEEN '2008-06-07' AND '2006-06-26'
+ )
+);
+varchar_nokey
+DROP TABLE t1, t2, t3;
=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test 2009-11-09 10:27:46 +0000
+++ b/mysql-test/t/subselect3.test 2009-11-18 17:36:15 +0000
@@ -1074,6 +1074,75 @@ explain select * from t1 where (a,b,c) i
drop table t0,t1,t2;
+
+#
+# Check that subqueries with outer joins or straight_join work for
+# different permutations of const and non-const tables. (Ref. Bug#46692)
+#
+CREATE TABLE t1 (i INTEGER);
+let $i=3;
+while ($i)
+{
+ CREATE TABLE t2 (i INTEGER);
+ let $j=3;
+ while ($j)
+ {
+ CREATE TABLE t3 (i INTEGER);
+ let $k=3;
+ while ($k)
+ {
+# TODO: LEFT JOIN queries are disabled due to Bug#48868
+# SELECT * FROM t1 WHERE (11) IN
+# (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
+ SELECT * FROM t1 WHERE (11) IN
+ (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
+ SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+ dec $k;
+ eval INSERT INTO t3 VALUES ($k);
+ }
+ DROP TABLE t3;
+ dec $j;
+ eval INSERT INTO t2 VALUES ($j);
+ }
+ DROP TABLE t2;
+ dec $i;
+ eval INSERT INTO t1 VALUES ($i);
+}
+DROP TABLE t1;
+
+#
+# Check that subqueries with straight_join work for different permutations
+# of const and non-const tables with duplicate weedout strategy
+# (by turning off FirstMatch and Materialization).
+#
+set @@optimizer_switch='firstmatch=off,materialization=off';
+CREATE TABLE t1 (i INTEGER);
+let $i=3;
+while ($i)
+{
+ CREATE TABLE t2 (i INTEGER);
+ let $j=3;
+ while ($j)
+ {
+ CREATE TABLE t3 (i INTEGER);
+ let $k=3;
+ while ($k)
+ {
+ SELECT * FROM t1 WHERE (11) IN (SELECT t3.i FROM t2 STRAIGHT_JOIN t3);
+ dec $k;
+ eval INSERT INTO t3 VALUES ($k);
+ }
+ DROP TABLE t3;
+ dec $j;
+ eval INSERT INTO t2 VALUES ($j);
+ }
+ DROP TABLE t2;
+ dec $i;
+ eval INSERT INTO t1 VALUES ($i);
+}
+DROP TABLE t1;
+set @@optimizer_switch=default;
+
--echo
--echo BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307
--echo
=== modified file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test 2009-10-25 13:41:27 +0000
+++ b/mysql-test/t/subselect4.test 2009-11-18 17:36:15 +0000
@@ -121,3 +121,90 @@ set @@session.optimizer_switch
@@session.engine_condition_pushdown = @old_engine_condition_pushdown;
DROP TABLE t1;
+
+--echo #
+--echo # Bug#46692 "Crash occurring on queries with nested FROM subqueries
+--echo # using materialization."
+--echo #
+CREATE TABLE t1 (
+ pk INTEGER PRIMARY KEY,
+ int_key INTEGER,
+ KEY int_key(int_key)
+);
+INSERT INTO t1 VALUES (10,186),(11,NULL),(12,2),(13,3),(14,0),(15,133),(16,1);
+
+CREATE TABLE t2 (
+ pk INTEGER PRIMARY KEY,
+ int_key INTEGER,
+ KEY int_key(int_key)
+);
+INSERT INTO t2 VALUES (1,7),(2,2);
+
+SELECT int_key FROM
+ (SELECT * FROM t1 WHERE (140, 4) IN
+ (SELECT t2.int_key, t2 .pk FROM t2 STRAIGHT_JOIN t1 ON t2.int_key)) table1;
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug#42353 "SELECT ... WHERE oe IN (SELECT w/ LEFT JOIN) query
+--echo # causes crash."
+--echo #
+CREATE TABLE t1 (
+ pk INTEGER PRIMARY KEY,
+ int_nokey INTEGER,
+ int_key INTEGER,
+ date_key DATE,
+ datetime_nokey DATETIME,
+ varchar_nokey VARCHAR(1)
+);
+
+CREATE TABLE t2 (
+ date_nokey DATE
+);
+
+CREATE TABLE t3 (
+ pk INTEGER PRIMARY KEY,
+ int_nokey INTEGER,
+ date_key date,
+ varchar_key VARCHAR(1),
+ varchar_nokey VARCHAR(1),
+ KEY date_key (date_key)
+);
+
+SELECT DISTINCT date_key FROM t1
+WHERE (int_key, int_nokey)
+ IN (SELECT t3.int_nokey, t3.pk
+ FROM t2 LEFT JOIN t3 ON (t2.date_nokey < t3.date_key)
+ WHERE t3.varchar_key <= t3.varchar_nokey OR t3.int_nokey <= t3.pk
+ )
+ AND (varchar_nokey <> 'f' OR NOT int_key < 7)
+HAVING date_key < '2001-11-14 00:43:55'
+ORDER BY datetime_nokey, pk;
+
+--echo #
+--echo # Bug#45933 "Crash in optimize_semijoin_nests on JOIN in subquery
+--echo # + AND in outer query".
+--echo #
+INSERT INTO t1 VALUES (10,7,5,'2009-06-16','2002-04-10 14:25:30','w'),
+ (11,7,0,'0000-00-00','0000-00-00 00:00:00','s'),
+ (12,4,0,'2003-07-14','2006-09-14 04:01:02','y'),
+ (13,0,4,'2002-07-25','0000-00-00 00:00:00','c'),
+ (14,1,8,'2007-07-03','0000-00-00 00:00:00','q'),
+ (15,6,5,'2001-11-12','0000-00-00 00:00:00',''),
+ (16,2,9,'0000-00-00','0000-00-00 00:00:00','j'),
+ (29,9,1,'0000-00-00','2003-08-11 00:00:00','m');
+INSERT INTO t3 VALUES (1,9,'0000-00-00','b','b'),
+ (2,2,'2002-09-17','h','h');
+
+SELECT t1.varchar_nokey FROM t1 JOIN t3 ON t1.datetime_nokey
+WHERE t1.varchar_nokey
+ IN (SELECT varchar_nokey FROM t1
+ WHERE (pk)
+ IN (SELECT t3.int_nokey
+ FROM t3 LEFT JOIN t1 ON t1.varchar_nokey
+ WHERE t3.date_key BETWEEN '2008-06-07' AND '2006-06-26'
+ )
+ );
+
+DROP TABLE t1, t2, t3;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-11-17 10:12:07 +0000
+++ b/sql/sql_select.cc 2009-11-18 17:36:15 +0000
@@ -4511,7 +4511,7 @@ make_join_statistics(JOIN *join, TABLE_L
if (join->const_tables != join->tables)
optimize_keyuse(join, keyuse_array);
- if (optimize_semijoin_nests(join, all_table_map))
+ if (optimize_semijoin_nests(join, all_table_map & ~join->const_table_map))
DBUG_RETURN(TRUE); /* purecov: inspected */
/* Find an optimal join order of the non-constant tables. */
@@ -13011,7 +13011,7 @@ void optimize_wo_join_buffering(JOIN *jo
rec_count *= pos.records_read;
cost += pos.read_time;
- if (rs->emb_sj_nest)
+ if (rs->emb_sj_nest && pos.records_read > 1) // fanout should always be >= 1
inner_fanout *= pos.records_read;
}
@@ -13473,12 +13473,14 @@ void advance_sj_state(JOIN *join, table_
dups_cost += p->read_time;
if (p->table->emb_sj_nest)
{
- sj_inner_fanout *= p->records_read;
+ // fanout should always be >= 1
+ sj_inner_fanout *= p->records_read > 1 ? p->records_read : 1;
dups_removed_fanout |= p->table->table->map;
}
else
{
- sj_outer_fanout *= p->records_read;
+ // fanout should always be >= 1
+ sj_outer_fanout *= p->records_read > 1 ? p->records_read : 1;
temptable_rec_size += p->table->table->file->ref_length;
}
}
Attachment: [text/bzr-bundle]
