#At file:///home/rl136806/mysql/repo/mysql-work5/ based on revid:jorgen.loland@stripped
3342 Roy Lyseng 2011-02-14
Bug#11766669: materialization=on/off leads to different result set when using IN
Used to be bug#59833.
The offending query is subject to subquery materialization, and the
subquery predicate is attached to the join object representing
"t1 JOIN t2". Table t1 is subject to const table optimization,
so it is not part of the join order. It also makes the subquery
predicate become "const". Unlike a regular subquery, this
materialized subquery is considered "expensive", ie is_expensive()
returns true.
Inside make_join_select(), we first consider const conditions, but
since the subquery predicate is expensive, it is assumed to be
handled in the execution phase. make_cond_for_table_from_pred() has
a test to make sure that such const and expensive predicates are
handled with the first table in the join order, in:
!((used_table & 1) && cond->is_expensive())),
but as can be seen by the comment just above the test, this test is
already considered to be dubious. As the first table here is number 2
(table number 1 is const), the test will never be true, and the
subquery will thus never be evaluated.
The approach taken to fix the problem relies on some refactoring.
We want to add such expressions to the first table in the join order.
We also know that argument 'used_table' of make_cond_for_table()
represents the current table, and 'tables' represents the aggregation
of handled tables. Thus, an easy fix is to check that
tables=used_table. But it is not that easy. The list of tables also
comprise the set of const tables (const_table_map), and the
RAND_TABLE_BIT and OUTER_REF_TABLE_BIT bits. We add const_table_map
and OUTER_REF_TABLE_BIT together with the first table (we know the
values of the const tables and outer references are available),
and add RAND_TABLE_BIT with the last table (so random expressions are
evaluated for each resulting row), and we make sure that
tables=used_table for the first table.
mysql-test/include/subquery_mat.inc
Added test case for bug#59833.
mysql-test/r/innodb_icp.result
mysql-test/r/innodb_icp_all.result
mysql-test/r/innodb_icp_none.result
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_jcl6.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_jcl6.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_jcl6.result
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_innodb_none.result
mysql-test/r/subquery_sj_innodb_none_jcl6.result
mysql-test/r/subquery_sj_innodb_none_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
Some plans are changed. They are always improved, as the predicates
are now often evaluated on an earlier table in the join order.
Some explanations are given below:
mysql_test/r/subquery_all.result
Inside a scalar subquery execution, the table conditions were evaluated
both for the first and the second table in the join order. The reason
is that the test !(cond->used_tables() & used_table) is false for
both tables (the condition has used_tables=OUTER+t1, first table has
used_table=t1, second table has used_table=OUTER+t2).
With the new solution, the first table has used_table=OUTER+t1,
so the condition will no longer be applied to the second table.
Inside a derived table execution, SQL_SELECT::test_quick_select() decided
upon the plan "Range checked for each record (index map: 0x2)" because
the argument prev_tables was different because of this change.
mysql-test/r/subquery_sj_mat_nosj.result
The IN subquery predicate is moved from the last table in the outer
select to the first table, which is good for performance.
mysql-test/r/innodb_icp_none.result
The predicate containing the inner subquery is moved from the
second table to the first table of the outer subquery.
mysql-test/r/subquery_mat.result
mysql-test/r/subquery_mat_all.result
mysql-test/r/subquery_mat_none.result
Added test case results for bug#59833.
sql/sql_select.cc
In make_join_select(), make sure that 'used_tables' is always a
strict aggregation of the previously handled 'current_map'.
Also reflect better that outer references and const tables are
available when processing the first table in the join order.
In make_cond_for_table_from_pred(), change the test that attempts
to add a const but expensive predicate to the first table in the
join order.
In pushdown_on_conditions(), update 'current_map' and 'used_tables'
the way it's done in make_join_select().
modified:
mysql-test/include/subquery_mat.inc
mysql-test/r/innodb_icp.result
mysql-test/r/innodb_icp_all.result
mysql-test/r/innodb_icp_none.result
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_jcl6.result
mysql-test/r/subquery_mat.result
mysql-test/r/subquery_mat_all.result
mysql-test/r/subquery_mat_none.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_jcl6.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_jcl6.result
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_innodb_none.result
mysql-test/r/subquery_sj_innodb_none_jcl6.result
mysql-test/r/subquery_sj_innodb_none_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_mat.inc'
--- a/mysql-test/include/subquery_mat.inc 2010-11-29 13:04:34 +0000
+++ b/mysql-test/include/subquery_mat.inc 2011-02-14 11:21:26 +0000
@@ -817,3 +817,33 @@ DROP TABLE t1, t2;
--echo # End BUG#56367
+--echo #
+--echo # Bug#59833 - materialization=on/off leads to different result set
+--echo # when using IN
+--echo #
+
+CREATE TABLE t1 (
+ pk int NOT NULL,
+ f1 int DEFAULT NULL,
+ PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+
+CREATE TABLE t2 (
+ pk int NOT NULL,
+ f1 int DEFAULT NULL,
+ PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES (10,0);
+INSERT INTO t2 VALUES (10,0),(11,0);
+
+let $query=
+SELECT * FROM t1 JOIN t2 USING (f1)
+WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1);
+
+eval explain $query;
+eval $query;
+
+DROP TABLE t1, t2;
+
+--echo # End Bug#59833
=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result 2011-01-31 11:56:15 +0000
+++ b/mysql-test/r/innodb_icp.result 2011-02-14 11:21:26 +0000
@@ -577,7 +577,7 @@ WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT
FROM t3));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where
-2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using where
2 DEPENDENT SUBQUERY t2 ref c1 c1 3 test.t3.c1 1 Using where
3 SUBQUERY t3 ALL NULL NULL NULL NULL 1
SELECT i1
=== modified file 'mysql-test/r/innodb_icp_all.result'
--- a/mysql-test/r/innodb_icp_all.result 2011-02-10 10:19:19 +0000
+++ b/mysql-test/r/innodb_icp_all.result 2011-02-14 11:21:26 +0000
@@ -577,7 +577,7 @@ WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT
FROM t3));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where
-2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using where
2 DEPENDENT SUBQUERY t2 ref c1 c1 3 test.t3.c1 1 Using where
3 SUBQUERY t3 ALL NULL NULL NULL NULL 1
SELECT i1
=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result 2011-01-31 11:56:15 +0000
+++ b/mysql-test/r/innodb_icp_none.result 2011-02-14 11:21:26 +0000
@@ -576,7 +576,7 @@ WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT
FROM t3));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where
-2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 1
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using where
2 DEPENDENT SUBQUERY t2 ref c1 c1 3 test.t3.c1 1 Using where
3 SUBQUERY t3 ALL NULL NULL NULL NULL 1
SELECT i1
=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result 2011-02-02 15:05:14 +0000
+++ b/mysql-test/r/subquery_all.result 2011-02-14 11:21:26 +0000
@@ -4454,7 +4454,7 @@ from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using join buffer (BNL, incremental buffers)
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
@@ -5126,7 +5126,7 @@ FROM t3 WHERE 1 = 0 GROUP BY 1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using index
-2 DEPENDENT SUBQUERY t2 index b b 5 NULL 2 Using where; Using index; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 ALL b NULL NULL NULL 2 Range checked for each record (index map: 0x2)
# should return 0 rows
SELECT
(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b)
=== modified file 'mysql-test/r/subquery_all_jcl6.result'
--- a/mysql-test/r/subquery_all_jcl6.result 2011-02-02 15:05:14 +0000
+++ b/mysql-test/r/subquery_all_jcl6.result 2011-02-14 11:21:26 +0000
@@ -4458,7 +4458,7 @@ from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using join buffer (BNL, incremental buffers)
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
@@ -5130,7 +5130,7 @@ FROM t3 WHERE 1 = 0 GROUP BY 1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using index
-2 DEPENDENT SUBQUERY t2 index b b 5 NULL 2 Using where; Using index; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 ALL b NULL NULL NULL 2 Range checked for each record (index map: 0x2)
# should return 0 rows
SELECT
(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b)
=== modified file 'mysql-test/r/subquery_mat.result'
--- a/mysql-test/r/subquery_mat.result 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/subquery_mat.result 2011-02-14 11:21:26 +0000
@@ -1097,4 +1097,31 @@ SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a
a
DROP TABLE t1, t2;
# End BUG#56367
+#
+# Bug#59833 - materialization=on/off leads to different result set
+# when using IN
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+f1 int DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL,
+f1 int DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (10,0);
+INSERT INTO t2 VALUES (10,0),(11,0);
+explain SELECT * FROM t1 JOIN t2 USING (f1)
+WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 SUBQUERY t1 system NULL NULL NULL NULL 1
+SELECT * FROM t1 JOIN t2 USING (f1)
+WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1);
+f1 pk pk
+DROP TABLE t1, t2;
+# End Bug#59833
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_mat_all.result'
--- a/mysql-test/r/subquery_mat_all.result 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/subquery_mat_all.result 2011-02-14 11:21:26 +0000
@@ -1096,4 +1096,31 @@ SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a
a
DROP TABLE t1, t2;
# End BUG#56367
+#
+# Bug#59833 - materialization=on/off leads to different result set
+# when using IN
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+f1 int DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL,
+f1 int DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (10,0);
+INSERT INTO t2 VALUES (10,0),(11,0);
+explain SELECT * FROM t1 JOIN t2 USING (f1)
+WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 SUBQUERY t1 system NULL NULL NULL NULL 1
+SELECT * FROM t1 JOIN t2 USING (f1)
+WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1);
+f1 pk pk
+DROP TABLE t1, t2;
+# End Bug#59833
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_mat_none.result'
--- a/mysql-test/r/subquery_mat_none.result 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/subquery_mat_none.result 2011-02-14 11:21:26 +0000
@@ -1096,4 +1096,30 @@ SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a
a
DROP TABLE t1, t2;
# End BUG#56367
+#
+# Bug#59833 - materialization=on/off leads to different result set
+# when using IN
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+f1 int DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+CREATE TABLE t2 (
+pk int NOT NULL,
+f1 int DEFAULT NULL,
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (10,0);
+INSERT INTO t2 VALUES (10,0),(11,0);
+explain SELECT * FROM t1 JOIN t2 USING (f1)
+WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY t1 system PRIMARY NULL NULL NULL 1
+SELECT * FROM t1 JOIN t2 USING (f1)
+WHERE t1.f1 IN (SELECT t1.pk FROM t1 ORDER BY t1.f1);
+f1 pk pk
+DROP TABLE t1, t2;
+# End Bug#59833
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result 2011-02-02 09:04:55 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result 2011-02-14 11:21:26 +0000
@@ -4454,7 +4454,7 @@ from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using join buffer (BNL, incremental buffers)
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
@@ -5126,7 +5126,7 @@ FROM t3 WHERE 1 = 0 GROUP BY 1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using index
-2 DEPENDENT SUBQUERY t2 index b b 5 NULL 2 Using where; Using index; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 ALL b NULL NULL NULL 2 Range checked for each record (index map: 0x2)
# should return 0 rows
SELECT
(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b)
=== modified file 'mysql-test/r/subquery_nomat_nosj_jcl6.result'
--- a/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-02-02 15:05:14 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-02-14 11:21:26 +0000
@@ -4458,7 +4458,7 @@ from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using join buffer (BNL, incremental buffers)
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
@@ -5130,7 +5130,7 @@ FROM t3 WHERE 1 = 0 GROUP BY 1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using index
-2 DEPENDENT SUBQUERY t2 index b b 5 NULL 2 Using where; Using index; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 ALL b NULL NULL NULL 2 Range checked for each record (index map: 0x2)
# should return 0 rows
SELECT
(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b)
=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result 2011-02-02 09:04:55 +0000
+++ b/mysql-test/r/subquery_none.result 2011-02-14 11:21:26 +0000
@@ -4453,7 +4453,7 @@ from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using join buffer (BNL, incremental buffers)
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
@@ -5125,7 +5125,7 @@ FROM t3 WHERE 1 = 0 GROUP BY 1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using index
-2 DEPENDENT SUBQUERY t2 index b b 5 NULL 2 Using where; Using index; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 ALL b NULL NULL NULL 2 Range checked for each record (index map: 0x2)
# should return 0 rows
SELECT
(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b)
=== modified file 'mysql-test/r/subquery_none_jcl6.result'
--- a/mysql-test/r/subquery_none_jcl6.result 2011-02-02 15:05:14 +0000
+++ b/mysql-test/r/subquery_none_jcl6.result 2011-02-14 11:21:26 +0000
@@ -4457,7 +4457,7 @@ from t2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 100.00 Using join buffer (BNL, incremental buffers)
Warnings:
Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a`,`test`.`t1`.`b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2`
@@ -5129,7 +5129,7 @@ FROM t3 WHERE 1 = 0 GROUP BY 1;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using index
-2 DEPENDENT SUBQUERY t2 index b b 5 NULL 2 Using where; Using index; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 ALL b NULL NULL NULL 2 Range checked for each record (index map: 0x2)
# should return 0 rows
SELECT
(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b)
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2011-02-14 11:21:26 +0000
@@ -267,7 +267,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result 2011-02-14 11:21:26 +0000
@@ -271,7 +271,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result 2011-02-14 11:21:26 +0000
@@ -271,7 +271,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2011-02-14 11:21:26 +0000
@@ -266,7 +266,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
@@ -2358,19 +2358,19 @@ explain select * from t2 where a in (sel
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
create table t0 (a int, b int);
insert into t0 values(1,1);
explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2011-02-14 11:21:26 +0000
@@ -270,7 +270,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
@@ -2362,19 +2362,19 @@ explain select * from t2 where a in (sel
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
create table t0 (a int, b int);
insert into t0 values(1,1);
explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2011-02-14 11:21:26 +0000
@@ -270,7 +270,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
@@ -2362,19 +2362,19 @@ explain select * from t2 where a in (sel
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
create table t0 (a int, b int);
insert into t0 values(1,1);
explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2011-02-14 11:21:26 +0000
@@ -267,7 +267,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
@@ -2359,19 +2359,19 @@ explain select * from t2 where a in (sel
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
create table t0 (a int, b int);
insert into t0 values(1,1);
explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2011-02-14 11:21:26 +0000
@@ -271,7 +271,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
@@ -2363,19 +2363,19 @@ explain select * from t2 where a in (sel
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
create table t0 (a int, b int);
insert into t0 values(1,1);
explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2011-02-14 11:21:26 +0000
@@ -271,7 +271,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
@@ -2363,19 +2363,19 @@ explain select * from t2 where a in (sel
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
create table t0 (a int, b int);
insert into t0 values(1,1);
explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
=== modified file 'mysql-test/r/subquery_sj_innodb_none.result'
--- a/mysql-test/r/subquery_sj_innodb_none.result 2011-01-24 11:56:54 +0000
+++ b/mysql-test/r/subquery_sj_innodb_none.result 2011-02-14 11:21:26 +0000
@@ -138,8 +138,8 @@ c1 in (select convert(c6,char(1)) from t
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1
-2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using join buffer (BNL, incremental buffers)
drop table t2, t3;
#
# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
=== modified file 'mysql-test/r/subquery_sj_innodb_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_innodb_none_jcl6.result 2011-01-24 11:56:54 +0000
+++ b/mysql-test/r/subquery_sj_innodb_none_jcl6.result 2011-02-14 11:21:26 +0000
@@ -142,8 +142,8 @@ c1 in (select convert(c6,char(1)) from t
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1
-2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using join buffer (BNL, incremental buffers)
drop table t2, t3;
#
# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
=== modified file 'mysql-test/r/subquery_sj_innodb_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_innodb_none_jcl7.result 2011-01-24 11:56:54 +0000
+++ b/mysql-test/r/subquery_sj_innodb_none_jcl7.result 2011-02-14 11:21:26 +0000
@@ -142,8 +142,8 @@ c1 in (select convert(c6,char(1)) from t
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1
-2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using join buffer (BNL, regular buffers)
drop table t2, t3;
#
# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2011-02-14 11:21:26 +0000
@@ -267,7 +267,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
@@ -2359,19 +2359,19 @@ explain select * from t2 where a in (sel
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
create table t0 (a int, b int);
insert into t0 values(1,1);
explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2011-02-14 11:21:26 +0000
@@ -271,7 +271,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
@@ -2363,19 +2363,19 @@ explain select * from t2 where a in (sel
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
create table t0 (a int, b int);
insert into t0 values(1,1);
explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2011-02-14 11:21:26 +0000
@@ -271,7 +271,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
@@ -2363,19 +2363,19 @@ explain select * from t2 where a in (sel
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
create table t0 (a int, b int);
insert into t0 values(1,1);
explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2011-02-14 11:21:26 +0000
@@ -267,7 +267,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2011-02-14 11:21:26 +0000
@@ -271,7 +271,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2011-02-14 11:21:26 +0000
@@ -271,7 +271,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2011-02-14 11:21:26 +0000
@@ -5297,7 +5297,7 @@ where t1.uid in (select t4.uid from t4,
and t2.uid=t1.fid;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where
-1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.fid 1 Using where
+1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.fid 1
2 SUBQUERY t3 ref uid uid 5 const 4 Using where
2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t3.fid 1 Using index
select name from t2, t1
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2011-02-14 11:21:26 +0000
@@ -268,7 +268,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
@@ -2504,19 +2504,19 @@ explain select * from t2 where a in (sel
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
create table t0 (a int, b int);
insert into t0 values(1,1);
explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result 2011-02-14 11:21:26 +0000
@@ -272,7 +272,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
@@ -2508,19 +2508,19 @@ explain select * from t2 where a in (sel
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, incremental buffers)
create table t0 (a int, b int);
insert into t0 values(1,1);
explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result 2011-01-27 11:38:22 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result 2011-02-14 11:21:26 +0000
@@ -272,7 +272,7 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
-2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, regular buffers)
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
@@ -2508,19 +2508,19 @@ explain select * from t2 where a in (sel
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
explain select straight_join * from t2 X, t2 Y
where X.a in (select straight_join A.a from t1 A, t1 B);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
2 DEPENDENT SUBQUERY A ALL NULL NULL NULL NULL 10 Using where
-2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer (BNL, regular buffers)
create table t0 (a int, b int);
insert into t0 values(1,1);
explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-02-10 09:42:51 +0000
+++ b/sql/sql_select.cc 2011-02-14 11:21:26 +0000
@@ -9505,12 +9505,20 @@ static bool pushdown_on_conditions(JOIN*
*/
Item *on_expr= *first_inner_tab->on_expr_ref;
- table_map used_tables= (join->const_table_map |
- OUTER_REF_TABLE_BIT | RAND_TABLE_BIT);
+ table_map used_tables= 0;
+
for (JOIN_TAB *join_tab= join->join_tab+join->const_tables;
join_tab <= last_tab ; join_tab++)
{
+ /*
+ For explanation on how these bitmasks are built, see
+ make_join_select(), Step #2.
+ */
table_map current_map= join_tab->table->map;
+ if (join_tab == join->join_tab + join->const_tables)
+ current_map|= join->const_table_map | OUTER_REF_TABLE_BIT;
+ if (join_tab == last_tab)
+ current_map|= RAND_TABLE_BIT;
used_tables|= current_map;
Item *tmp_cond= make_cond_for_table(on_expr, used_tables, current_map, 0);
if (!tmp_cond)
@@ -9567,7 +9575,6 @@ static bool make_join_select(JOIN *join,
DBUG_ENTER("make_join_select");
{
add_not_null_conds(join);
- table_map used_tables;
/*
Step #1: Extract constant condition
- Extract and check the constant part of the WHERE
@@ -9634,22 +9641,34 @@ static bool make_join_select(JOIN *join,
/*
Step #2: Extract WHERE/ON parts
*/
+ table_map used_tables= 0;
table_map save_used_tables= 0;
- used_tables= join->const_table_map | OUTER_REF_TABLE_BIT | RAND_TABLE_BIT;
- JOIN_TAB *tab;
- table_map current_map;
for (uint i=join->const_tables ; i < join->tables ; i++)
{
- tab= join->join_tab+i;
+ JOIN_TAB *tab= join->join_tab + i;
/*
first_inner is the X in queries like:
SELECT * FROM t1 LEFT OUTER JOIN (t2 JOIN t3) ON X
*/
JOIN_TAB *first_inner_tab= tab->first_inner;
- current_map= tab->table->map;
bool use_quick_range=0;
Item *tmp;
+ /*
+ Calculate used table information added at this stage.
+ The current table is always added. Const tables are assumed to be
+ available together with the first table in the join order.
+ All outer references are available, so these may be evaluated together
+ with the first table.
+ Random expressions must be added to the last table's condition.
+ It solves problem with queries like SELECT * FROM t1 WHERE rand() > 0.5
+ */
+ table_map current_map= tab->table->map;
+ if (i == join->const_tables)
+ current_map|= join->const_table_map | OUTER_REF_TABLE_BIT;
+ if (i == join->tables - 1)
+ current_map|= RAND_TABLE_BIT;
+
/*
Tables that are within SJ-Materialization nests cannot have their
conditions referring to preceding non-const tables.
@@ -9660,17 +9679,10 @@ static bool make_join_select(JOIN *join,
!(used_tables & tab->emb_sj_nest->sj_inner_tables))
{
save_used_tables= used_tables;
- used_tables= join->const_table_map | OUTER_REF_TABLE_BIT |
- RAND_TABLE_BIT;
+ used_tables= join->const_table_map | OUTER_REF_TABLE_BIT;
}
- /*
- Following force including random expression in last table condition.
- It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5
- */
- if (i == join->tables-1)
- current_map|= OUTER_REF_TABLE_BIT | RAND_TABLE_BIT;
- used_tables|=current_map;
+ used_tables|= current_map;
if (tab->type == JT_REF && tab->quick &&
(uint) tab->ref.key == tab->quick->index &&
@@ -19131,7 +19143,7 @@ static bool replace_subcondition(JOIN *j
@param cond Condition to analyze
@param tables Tables for which "current field values" are available
- @param used_table Table that we're extracting the condition for (may
+ @param used_table Table(s) that we are extracting the condition for (may
also include PSEUDO_TABLE_BITS, and may be zero)
@param exclude_expensive_cond Do not push expensive conditions
@@ -19139,15 +19151,32 @@ static bool replace_subcondition(JOIN *j
@retval = NULL Already checked, OR error
@details
- Extract the condition that can be checked after reading the table
- specified in 'used_table', given that current-field values for tables
- specified in 'tables' bitmap are available.
- If 'used_table' is 0, extract conditions for all tables in 'tables'.
-
- The function assumes that
- - Constant parts of the condition has already been checked.
- - Condition that could be checked for tables in 'tables' has already
- been checked.
+ Extract the condition that can be checked after reading the table(s)
+ specified in @c used_table, given that current-field values for tables
+ specified in @c tables bitmap are available.
+ If @c used_table is 0, extract conditions for all tables in @c tables.
+
+ This function can be used to extract conditions relevant for a table
+ in a join order. Together with its caller, it will ensure that all
+ conditions are attached to the first table in the join order where all
+ necessary fields are available, and it will also ensure that a given
+ condition is attached to only one table.
+ To accomplish this, first initialize @c tables to the empty
+ set. Then, loop over all tables in the join order, set @c used_table to
+ the bit representing the current table, accumulate @c used_table into the
+ @c tables set, and call this function. To ensure correct handling of
+ const expressions and outer references, add the const table map and
+ OUTER_REF_TABLE_BIT to @c used_table for the first table. To ensure
+ that random expressions are evaluated for the final table, add
+ RAND_TABLE_BIT to @c used_table for the final table.
+
+ The function assumes that constant, inexpensive parts of the condition
+ have already been checked. Constant, expensive parts will be attached
+ to the first table in the join order, provided that the above call
+ sequence is followed.
+
+ The call order will ensure that conditions covering tables in @c tables
+ minus those in @c used_table, have already been checked.
The function takes into account that some parts of the condition are
guaranteed to be true by employed 'ref' access methods (the code that
@@ -19175,21 +19204,14 @@ make_cond_for_table_from_pred(Item *root
/*
Ignore this condition if
1. We are extracting conditions for a specific table, and
- 2. that table is not referenced by the condition, and
- 3. exclude constant conditions not checked at optimization time if
- the table we are pushing conditions to is the first one.
- As a result, such conditions are not considered as already checked
- and will be checked at execution time, attached to the first table.
+ 2. that table is not referenced by the condition, but not if
+ 3. this is a constant condition not checked at optimization time and
+ this is the first table we are extracting conditions for.
+ (Assuming that used_table == tables for the first table.)
*/
if (used_table && // 1
!(cond->used_tables() & used_table) && // 2
- /*
- psergey: TODO: "used_table & 1" doesn't make sense in nearly any
- context. Look at setup_table_map(), table bits reflect the order
- the tables were encountered by the parser. Check what we should
- replace this condition with.
- */
- !((used_table & 1) && cond->is_expensive())) // 3
+ !(cond->is_expensive() && used_table == tables)) // 3
return NULL;
if (cond->type() == Item::COND_ITEM)
Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20110214112126-1bwlsqxamrgbqwfi.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (roy.lyseng:3342) Bug#11766669 | Roy Lyseng | 14 Feb |
