#At file:///export/home/jl208045/mysql/mysql-next-mr-opt-backporting-12619399/ based on revid:jorgen.loland@stripped
3388 Jorgen Loland 2011-06-23
Bug#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN
JCL>=5
Consider a query containing a predicate of the form
left_expr IN/SOME/ANY/ALL (subquery)
If any part of left_expr is NULL, then this predicate shall
evaluate to:
- NULL if the subquery returns rows after disabling subquery
conditions relying on the NULL outer values
- NULL if the subquery evaluates to NULL
- false otherwise
For normal subqueries, this is handled by disabling any subquery
predicates relying on NULL values in left_expr and then evaluate
the subquery. If the subquery returns at least one row, or if the
subquery evaluates to NULL, the predicate evaluates to NULL.
However, when BKA is used to resolve the subquery, the values in
left_expr are used to lookup rows that are join matches. BKA
cannot disable parts of the key when doing this lookup and can
therefore not be used in such cases.
The problem was that BKA was used in cases where left_expr had
NULL values. The fix is to detect and disable BKA in such cases.
@ mysql-test/include/join_cache.inc
Added test for BUG#12619399
@ mysql-test/r/join_cache_jcl0.result
Added test for BUG#12619399
@ mysql-test/r/join_cache_jcl1.result
Added test for BUG#12619399
@ mysql-test/r/join_cache_jcl2.result
Added test for BUG#12619399
@ mysql-test/r/join_cache_jcl3.result
Added test for BUG#12619399
@ mysql-test/r/join_cache_jcl4.result
Added test for BUG#12619399
@ mysql-test/r/join_cache_jcl5.result
Added test for BUG#12619399
@ mysql-test/r/join_cache_jcl6.result
Added test for BUG#12619399
@ mysql-test/r/join_cache_jcl7.result
Added test for BUG#12619399
@ mysql-test/r/join_cache_jcl8.result
Added test for BUG#12619399
@ mysql-test/r/subquery_all_jcl6.result
BUG#12619399 disabled BKA for some queries
@ mysql-test/r/subquery_nomat_nosj_jcl6.result
BUG#12619399 disabled BKA for some queries
@ sql/sql_select.cc
In setup_join_buffering(): Do not use BKA for IN subqueries if left_expr is nullable and the predicate is not a top level item.
modified:
mysql-test/include/join_cache.inc
mysql-test/r/join_cache_jcl0.result
mysql-test/r/join_cache_jcl1.result
mysql-test/r/join_cache_jcl2.result
mysql-test/r/join_cache_jcl3.result
mysql-test/r/join_cache_jcl4.result
mysql-test/r/join_cache_jcl5.result
mysql-test/r/join_cache_jcl6.result
mysql-test/r/join_cache_jcl7.result
mysql-test/r/join_cache_jcl8.result
mysql-test/r/subquery_all_jcl6.result
mysql-test/r/subquery_nomat_nosj_jcl6.result
sql/sql_select.cc
=== modified file 'mysql-test/include/join_cache.inc'
--- a/mysql-test/include/join_cache.inc 2011-06-22 09:07:01 +0000
+++ b/mysql-test/include/join_cache.inc 2011-06-23 09:06:41 +0000
@@ -1689,4 +1689,66 @@ INSERT INTO t2 VALUES (100,0),(150,200),
DROP TABLE t1,t2;
+--echo #
+--echo # BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5
+--echo #
+
+CREATE TABLE t1 (
+ c1 INTEGER NOT NULL,
+ c2_key INTEGER NOT NULL,
+ KEY col_int_key (c2_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (24,204);
+
+CREATE TABLE t2 (
+ pk INTEGER NOT NULL,
+ PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES (10);
+
+CREATE TABLE t3 (
+ c1 INTEGER,
+ KEY k1 (c1)
+) ENGINE=InnoDB;
+
+INSERT INTO t3 VALUES (NULL), (NULL);
+
+--echo
+
+let query_in=
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+
+let query_any=
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+
+let query_some=
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+
+let query_all=
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+
+eval explain $query_some;
+eval explain $query_any;
+eval explain $query_all;
+eval explain $query_in;
+eval $query_some;
+eval $query_any;
+eval $query_all;
+eval $query_in;
+
+select @@optimizer_join_cache_level;
+
+--echo
+DROP TABLE t1, t2, t3;
+
set @@join_buffer_size=default;
=== modified file 'mysql-test/r/join_cache_jcl0.result'
--- a/mysql-test/r/join_cache_jcl0.result 2011-06-22 09:07:01 +0000
+++ b/mysql-test/r/join_cache_jcl0.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@ t1a t1b t2a t2b
99 0 999 0
DROP TABLE t1,t2;
+#
+# BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5
+#
+CREATE TABLE t1 (
+c1 INTEGER NOT NULL,
+c2_key INTEGER NOT NULL,
+KEY col_int_key (c2_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (24,204);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (10);
+CREATE TABLE t3 (
+c1 INTEGER,
+KEY k1 (c1)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (NULL), (NULL);
+
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+select @@optimizer_join_cache_level;
+@@optimizer_join_cache_level
+0
+
+DROP TABLE t1, t2, t3;
set @@join_buffer_size=default;
set optimizer_join_cache_level = default;
set optimizer_switch = default;
=== modified file 'mysql-test/r/join_cache_jcl1.result'
--- a/mysql-test/r/join_cache_jcl1.result 2011-06-22 09:07:01 +0000
+++ b/mysql-test/r/join_cache_jcl1.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@ t1a t1b t2a t2b
99 0 999 0
DROP TABLE t1,t2;
+#
+# BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5
+#
+CREATE TABLE t1 (
+c1 INTEGER NOT NULL,
+c2_key INTEGER NOT NULL,
+KEY col_int_key (c2_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (24,204);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (10);
+CREATE TABLE t3 (
+c1 INTEGER,
+KEY k1 (c1)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (NULL), (NULL);
+
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, regular buffers)
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+select @@optimizer_join_cache_level;
+@@optimizer_join_cache_level
+1
+
+DROP TABLE t1, t2, t3;
set @@join_buffer_size=default;
set optimizer_join_cache_level = default;
set optimizer_switch = default;
=== modified file 'mysql-test/r/join_cache_jcl2.result'
--- a/mysql-test/r/join_cache_jcl2.result 2011-06-22 09:07:01 +0000
+++ b/mysql-test/r/join_cache_jcl2.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@ t1a t1b t2a t2b
99 0 999 0
DROP TABLE t1,t2;
+#
+# BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5
+#
+CREATE TABLE t1 (
+c1 INTEGER NOT NULL,
+c2_key INTEGER NOT NULL,
+KEY col_int_key (c2_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (24,204);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (10);
+CREATE TABLE t3 (
+c1 INTEGER,
+KEY k1 (c1)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (NULL), (NULL);
+
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+select @@optimizer_join_cache_level;
+@@optimizer_join_cache_level
+2
+
+DROP TABLE t1, t2, t3;
set @@join_buffer_size=default;
set optimizer_join_cache_level = default;
set optimizer_switch = default;
=== modified file 'mysql-test/r/join_cache_jcl3.result'
--- a/mysql-test/r/join_cache_jcl3.result 2011-06-22 09:07:01 +0000
+++ b/mysql-test/r/join_cache_jcl3.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@ t1a t1b t2a t2b
99 0 999 0
DROP TABLE t1,t2;
+#
+# BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5
+#
+CREATE TABLE t1 (
+c1 INTEGER NOT NULL,
+c2_key INTEGER NOT NULL,
+KEY col_int_key (c2_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (24,204);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (10);
+CREATE TABLE t3 (
+c1 INTEGER,
+KEY k1 (c1)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (NULL), (NULL);
+
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, regular buffers)
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+select @@optimizer_join_cache_level;
+@@optimizer_join_cache_level
+3
+
+DROP TABLE t1, t2, t3;
set @@join_buffer_size=default;
set optimizer_join_cache_level = default;
set optimizer_switch = default;
=== modified file 'mysql-test/r/join_cache_jcl4.result'
--- a/mysql-test/r/join_cache_jcl4.result 2011-06-22 09:07:01 +0000
+++ b/mysql-test/r/join_cache_jcl4.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@ t1a t1b t2a t2b
99 0 999 0
DROP TABLE t1,t2;
+#
+# BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5
+#
+CREATE TABLE t1 (
+c1 INTEGER NOT NULL,
+c2_key INTEGER NOT NULL,
+KEY col_int_key (c2_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (24,204);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (10);
+CREATE TABLE t3 (
+c1 INTEGER,
+KEY k1 (c1)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (NULL), (NULL);
+
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+select @@optimizer_join_cache_level;
+@@optimizer_join_cache_level
+4
+
+DROP TABLE t1, t2, t3;
set @@join_buffer_size=default;
set optimizer_join_cache_level = default;
set optimizer_switch = default;
=== modified file 'mysql-test/r/join_cache_jcl5.result'
--- a/mysql-test/r/join_cache_jcl5.result 2011-06-22 09:07:01 +0000
+++ b/mysql-test/r/join_cache_jcl5.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@ t1a t1b t2a t2b
99 3 NULL NULL
DROP TABLE t1,t2;
+#
+# BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5
+#
+CREATE TABLE t1 (
+c1 INTEGER NOT NULL,
+c2_key INTEGER NOT NULL,
+KEY col_int_key (c2_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (24,204);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (10);
+CREATE TABLE t3 (
+c1 INTEGER,
+KEY k1 (c1)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (NULL), (NULL);
+
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, regular buffers)
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+select @@optimizer_join_cache_level;
+@@optimizer_join_cache_level
+5
+
+DROP TABLE t1, t2, t3;
set @@join_buffer_size=default;
set optimizer_join_cache_level = default;
set optimizer_switch = default;
=== modified file 'mysql-test/r/join_cache_jcl6.result'
--- a/mysql-test/r/join_cache_jcl6.result 2011-06-22 09:07:01 +0000
+++ b/mysql-test/r/join_cache_jcl6.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@ t1a t1b t2a t2b
99 3 NULL NULL
DROP TABLE t1,t2;
+#
+# BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5
+#
+CREATE TABLE t1 (
+c1 INTEGER NOT NULL,
+c2_key INTEGER NOT NULL,
+KEY col_int_key (c2_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (24,204);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (10);
+CREATE TABLE t3 (
+c1 INTEGER,
+KEY k1 (c1)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (NULL), (NULL);
+
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+select @@optimizer_join_cache_level;
+@@optimizer_join_cache_level
+6
+
+DROP TABLE t1, t2, t3;
set @@join_buffer_size=default;
set optimizer_join_cache_level = default;
set optimizer_switch = default;
=== modified file 'mysql-test/r/join_cache_jcl7.result'
--- a/mysql-test/r/join_cache_jcl7.result 2011-06-22 09:07:01 +0000
+++ b/mysql-test/r/join_cache_jcl7.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@ t1a t1b t2a t2b
99 3 NULL NULL
DROP TABLE t1,t2;
+#
+# BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5
+#
+CREATE TABLE t1 (
+c1 INTEGER NOT NULL,
+c2_key INTEGER NOT NULL,
+KEY col_int_key (c2_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (24,204);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (10);
+CREATE TABLE t3 (
+c1 INTEGER,
+KEY k1 (c1)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (NULL), (NULL);
+
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, regular buffers)
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+select @@optimizer_join_cache_level;
+@@optimizer_join_cache_level
+7
+
+DROP TABLE t1, t2, t3;
set @@join_buffer_size=default;
set optimizer_join_cache_level = default;
set optimizer_switch = default;
=== modified file 'mysql-test/r/join_cache_jcl8.result'
--- a/mysql-test/r/join_cache_jcl8.result 2011-06-22 09:07:01 +0000
+++ b/mysql-test/r/join_cache_jcl8.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@ t1a t1b t2a t2b
99 3 NULL NULL
DROP TABLE t1,t2;
+#
+# BUG#12619399 - JCL: NO ROWS VS 3X NULL QUERY OUTPUT WHEN JCL>=5
+#
+CREATE TABLE t1 (
+c1 INTEGER NOT NULL,
+c2_key INTEGER NOT NULL,
+KEY col_int_key (c2_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (24,204);
+CREATE TABLE t2 (
+pk INTEGER NOT NULL,
+PRIMARY KEY (pk)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (10);
+CREATE TABLE t3 (
+c1 INTEGER,
+KEY k1 (c1)
+) ENGINE=InnoDB;
+INSERT INTO t3 VALUES (NULL), (NULL);
+
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (BNL, incremental buffers)
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t3 index NULL k1 5 NULL 2 Using where; Using index
+2 DEPENDENT SUBQUERY t2 index PRIMARY PRIMARY 4 NULL 1 Using index
+2 DEPENDENT SUBQUERY t1 ref col_int_key col_int_key 4 func 1 Using where; Full scan on NULL key
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = SOME (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ANY (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+select @@optimizer_join_cache_level;
+@@optimizer_join_cache_level
+8
+
+DROP TABLE t1, t2, t3;
set @@join_buffer_size=default;
set optimizer_join_cache_level = default;
set optimizer_switch = default;
=== modified file 'mysql-test/r/subquery_all_jcl6.result'
--- a/mysql-test/r/subquery_all_jcl6.result 2011-05-05 07:41:53 +0000
+++ b/mysql-test/r/subquery_all_jcl6.result 2011-06-23 09:06:41 +0000
@@ -4357,7 +4357,7 @@ from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where; Using join buffer (BKA, incremental buffers)
+2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
@@ -4385,7 +4385,7 @@ from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where; Using join buffer (BKA, incremental buffers)
+2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
@@ -4927,7 +4927,7 @@ WHERE t3.name='xxx' AND t2.id=t3.id);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index; Full scan on NULL key
-2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key; Using join buffer (BKA, incremental buffers)
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key
SELECT * FROM t1
WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
WHERE t3.name='xxx' AND t2.id=t3.id);
=== modified file 'mysql-test/r/subquery_nomat_nosj_jcl6.result'
--- a/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-05-05 07:41:53 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-06-23 09:06:41 +0000
@@ -4357,7 +4357,7 @@ from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where; Using join buffer (BKA, incremental buffers)
+2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3`
@@ -4385,7 +4385,7 @@ from t3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 100.00
2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 100.00 Using where; Full scan on NULL key
-2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where; Using join buffer (BKA, incremental buffers)
+2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1
Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3`
@@ -4927,7 +4927,7 @@ WHERE t3.name='xxx' AND t2.id=t3.id);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index; Full scan on NULL key
-2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key; Using join buffer (BKA, incremental buffers)
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key
SELECT * FROM t1
WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
WHERE t3.name='xxx' AND t2.id=t3.id);
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-06-21 09:29:01 +0000
+++ b/sql/sql_select.cc 2011-06-23 09:06:41 +0000
@@ -10944,6 +10944,44 @@ static bool setup_join_buffering(JOIN_TA
DBUG_ASSERT(tab->use_join_cache == JOIN_CACHE::ALG_NONE);
goto no_join_cache;
}
+
+ /*
+ Can't use BKA for subquery if dealing with
+
+ left_expr IN/SOME/ANY/ALL (subquery)
+
+ if left_expr is nullable and the subquery is not a top level
+ item. Reason: if left value is NULL, the IN/ALL/ANY/SOME
+ expression shall have value:
+
+ - NULL if the subquery returns rows after disabling subquery
+ conditions relying on the NULL outer values
+ - NULL if the subquery evaluates to NULL
+ - false otherwise
+ (- don't care whether it's false or NULL if the IN/ALL/ANY/SOME
+ is a top level item)
+
+ The disabling of predicates is done in
+ Item_in_optimizer::val_int(), but it does not work for BKA
+ because the value of left_expr is used by BKA when looking up
+ join matches in the subquery. Hence, BKA will return 0 rows even
+ if these predicates are disabled, making it impossible to
+ distinguish between false and NULL.
+ */
+ if (join->select_lex->master_unit()->item &&
+ (join->select_lex->master_unit()->item->substype() ==
+ Item_subselect::IN_SUBS))
+ {
+ Item_in_subselect *item_sub=
+ static_cast<Item_in_subselect *>(join->select_lex->master_unit()->item);
+
+ if (!item_sub->is_top_level_item() && item_sub->left_expr->maybe_null)
+ {
+ DBUG_ASSERT(tab->use_join_cache == JOIN_CACHE::ALG_NONE);
+ goto no_join_cache;
+ }
+ }
+
flags= HA_MRR_NO_NULL_ENDPOINTS;
if (tab->table->covering_keys.is_set(tab->ref.key))
flags|= HA_MRR_INDEX_ONLY;
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110623090641-duf9250zx0wn6s0e.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (jorgen.loland:3388) Bug#12619399 | Jorgen Loland | 23 Jun |
