List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:June 23 2011 2:04pm
Subject:Re: Patch for BUG#12619399
View as plain text  
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
Thread
Re: Patch for BUG#12619399Roy Lyseng23 Jun
  • Re: Patch for BUG#12619399Jorgen Loland24 Jun