Hi Jørgen,
thank you for fixing this problem.
The bugfix is approved, but please consider changes in test cases and comments
as indicated.
I've also noted a reminder to myself on adding some guidelines for use of
use_join_cache flag...
On 23.06.11 11.27, Jorgen Loland wrote:
> See attachment.
>
> revno: 3388
> committer: Jorgen Loland <jorgen.loland@stripped>
> branch nick: mysql-next-mr-opt-backporting-12619399
> timestamp: Thu 2011-06-23 11:06:41 +0200
> message:
> 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:
Proposal: Consider replacing the NULL terms with UNKNOWN in order to align with
SQL standard terminology.
>
> - NULL if the subquery returns rows after disabling subquery
> conditions relying on the NULL outer values
> - NULL if the subquery evaluates to NULL
A table-valued subquery cannot evaluate to NULL (or UNKNOWN), its result is
either an empty row set or a non-empty row set. Also beware that the logic is
reverted when e.g a NOT IN operator is considered instead of IN.
> - 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.
>
Proposal: The problem was that BKA was used in cases where left_expr might have
NULL values. The fix is to detect and disable BKA in such cases.
# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: jorgen.loland@stripped\
# duf9250zx0wn6s0e
# target_branch: bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-\
# next-mr-opt-backporting/
# testament_sha1: a1d04d30a784145f23a86f3d02d523fdf906b94d
# timestamp: 2011-06-23 11:24:20 +0200
# base_revision_id: jorgen.loland@stripped\
# tbaprq34n8nseq0b
#
# Begin patch
=== modified file 'mysql-test/include/join_cache.inc'
--- mysql-test/include/join_cache.inc 2011-06-22 09:07:01 +0000
+++ mysql-test/include/join_cache.inc 2011-06-23 09:06:41 +0000
@@ -1689,4 +1689,66 @@
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;
Some is just an alias for any, you can safely delete this.
+
+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;
I think that you can simplify this test case by removing XOR. It should still be
"top level". You may also add in a NOT IN query for completeness.
+
+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'
--- mysql-test/r/join_cache_jcl0.result 2011-06-22 09:07:01 +0000
+++ mysql-test/r/join_cache_jcl0.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@
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'
--- mysql-test/r/join_cache_jcl1.result 2011-06-22 09:07:01 +0000
+++ mysql-test/r/join_cache_jcl1.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@
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'
--- mysql-test/r/join_cache_jcl2.result 2011-06-22 09:07:01 +0000
+++ mysql-test/r/join_cache_jcl2.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@
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'
--- mysql-test/r/join_cache_jcl3.result 2011-06-22 09:07:01 +0000
+++ mysql-test/r/join_cache_jcl3.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@
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'
--- mysql-test/r/join_cache_jcl4.result 2011-06-22 09:07:01 +0000
+++ mysql-test/r/join_cache_jcl4.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@
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'
--- mysql-test/r/join_cache_jcl5.result 2011-06-22 09:07:01 +0000
+++ mysql-test/r/join_cache_jcl5.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@
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'
--- mysql-test/r/join_cache_jcl6.result 2011-06-22 09:07:01 +0000
+++ mysql-test/r/join_cache_jcl6.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@
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'
--- mysql-test/r/join_cache_jcl7.result 2011-06-22 09:07:01 +0000
+++ mysql-test/r/join_cache_jcl7.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@
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'
--- mysql-test/r/join_cache_jcl8.result 2011-06-22 09:07:01 +0000
+++ mysql-test/r/join_cache_jcl8.result 2011-06-23 09:06:41 +0000
@@ -2365,6 +2365,75 @@
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'
--- mysql-test/r/subquery_all_jcl6.result 2011-05-05 07:41:53 +0000
+++ mysql-test/r/subquery_all_jcl6.result 2011-06-23 09:06:41 +0000
@@ -4357,7 +4357,7 @@
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 @@
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 @@
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'
--- mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-05-05 07:41:53 +0000
+++ mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-06-23 09:06:41 +0000
@@ -4357,7 +4357,7 @@
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 @@
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 @@
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'
--- sql/sql_select.cc 2011-06-21 09:29:01 +0000
+++ sql/sql_select.cc 2011-06-23 09:06:41 +0000
@@ -10944,6 +10944,44 @@
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;
Thank,
Roy
