List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:June 24 2011 9:40am
Subject:bzr push into mysql-trunk branch (jorgen.loland:3387 to 3388)
View as plain text  
 3388 Jorgen Loland	2011-06-24
      Consider a query containing a predicate of the form
      
        left_expr IN/SOME/ANY/ALL (subquery)
      
      If any part of left_expr is UNKNOWN, then this predicate shall
      evaluate to: 
      
        - UNKNOWN if the subquery returns rows after disabling 
          subquery conditions relying on the NULL outer values
        - false otherwise
      
      For normal subqueries, this is handled by disabling any subquery
      predicates relying on UNKNOWN values in left_expr and then
      evaluate the subquery. If the subquery returns at least one row
      the predicate evaluates to UNKNOWN.
      
      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 might
      have UNKNOWN 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 queries with "Full scan on NULL key"
     @ mysql-test/r/subquery_nomat_nosj_jcl6.result
        BUG#12619399 disabled BKA for queries with "Full scan on NULL key"
     @ sql/item_subselect.cc
        Added comment to subselect_single_select_engine::exec(): Do not
        use BKA for tables with "Full scan on NULL key"
     @ sql/sql_select.cc
        In setup_join_buffering(): Do not use BKA for tables with
        "Full scan on NULL key"

    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/item_subselect.cc
      sql/sql_select.cc
 3387 Jorgen Loland	2011-06-22
      Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS RETURNED WHEN JCL>=7
      
      Consider a query of the form
      
      SELECT * FROM t1 LEFT JOIN t2 on t1_col = t2_col
      
      If the BKA UNIQUE algorithm is used on table t2, rows from t1 are read
      into the join buffer and each row's "key" (i.e., the columns used in
      the join) are then inserted into a hash table. When the buffer is
      either full or there are no more rows in t1, join matches are found in
      t2 using the key values in the hash table.
      
      The key value that is inserted into the hash table is created by
      calling cp_buffer_from_ref(). This effectively formats the key
      as a t2_col and copies the necessary info from JOIN_REF.
      
      The problem in this bug was that in the case where t1_col is nullable
      but t2_col is not, the information about NULL-values in t1_col was
      lost in cp_buffer_from_ref(). Thus, if t1_col had a value NULL, key
      would not be NULL but 0 (not necessarily reliably). The result was
      that t1 rows with a t1_col value of NULL would be joined with rows in
      t2 with t2_col=0.
      
      The fix is to not add NULL-valued keys into the hash table if the join
      condition is '='. This also saves work since these keys cannot result
      in join matches anyway.
      
      Note 1: The information about NULL values in t1_col is not lost as
      long as t2_col is also nullable.
      
      Note 2: If the join condition is <=>, the join correctness is handled
      by a triggered condition added in pushdown_on_conditions()
     @ mysql-test/include/join_cache.inc
        Test case for BUG#52636 no longer functioned as a regression test due to changed execution plan. Fixed
        Added test for BUG#12616131
     @ mysql-test/r/join_cache_jcl0.result
        Test case for BUG#52636 no longer functioned as a regression test due to changed execution plan. Fixed
        Added test for BUG#12616131
     @ mysql-test/r/join_cache_jcl1.result
        Test case for BUG#52636 no longer functioned as a regression test due to changed execution plan. Fixed
        Added test for BUG#12616131
     @ mysql-test/r/join_cache_jcl2.result
        Test case for BUG#52636 no longer functioned as a regression test due to changed execution plan. Fixed
        Added test for BUG#12616131
     @ mysql-test/r/join_cache_jcl3.result
        Test case for BUG#52636 no longer functioned as a regression test due to changed execution plan. Fixed
        Added test for BUG#12616131
     @ mysql-test/r/join_cache_jcl4.result
        Test case for BUG#52636 no longer functioned as a regression test due to changed execution plan. Fixed
        Added test for BUG#12616131
     @ mysql-test/r/join_cache_jcl5.result
        Test case for BUG#52636 no longer functioned as a regression test due to changed execution plan. Fixed
        Added test for BUG#12616131
     @ mysql-test/r/join_cache_jcl6.result
        Test case for BUG#52636 no longer functioned as a regression test due to changed execution plan. Fixed
        Added test for BUG#12616131
     @ mysql-test/r/join_cache_jcl7.result
        Test case for BUG#52636 no longer functioned as a regression test due to changed execution plan. Fixed
        Added test for BUG#12616131
     @ mysql-test/r/join_cache_jcl8.result
        Test case for BUG#52636 no longer functioned as a regression test due to changed execution plan. Fixed
        Added test for BUG#12616131
     @ sql/sql_join_cache.cc
        JOIN_CACHE_BKA_UNIQUE::put_record(): Do not add join 
        keys with NULL values into the hash table if join 
        condition is '='. In this case, it is impossible to 
        find a join match to the key. 
        
        The first part of JOIN_CACHE_BKA_UNIQUE::put_record() 
        is equal to JOIN_CACHE::put_record(). Duplicate code 
        is now replaced with call to that method.
        
        JOIN_CACHE_BKA_UNIQUE::get_next_key(): NULL-handling
        no longer needed since keys with NULL values are not
        inserted into the hash table.

    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
      sql/sql_join_cache.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-24 09:39:44 +0000
@@ -1689,4 +1689,71 @@ 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;
+
+#BKA is OK for this query
+let query_in_toplevel=
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+
+let query_notin=
+SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+
+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;
+
+eval explain $query_some;
+eval explain $query_any;
+eval explain $query_in;
+eval explain $query_notin;
+eval explain $query_in_toplevel;
+eval $query_some;
+eval $query_any;
+eval $query_in;
+eval $query_notin;
+eval $query_in_toplevel;
+
+
+--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-24 09:39:44 +0000
@@ -2365,6 +2365,79 @@ 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 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
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+
+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-24 09:39:44 +0000
@@ -2365,6 +2365,79 @@ 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 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
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+
+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-24 09:39:44 +0000
@@ -2365,6 +2365,79 @@ 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 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
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+
+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-24 09:39:44 +0000
@@ -2365,6 +2365,79 @@ 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 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
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+
+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-24 09:39:44 +0000
@@ -2365,6 +2365,79 @@ 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 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
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+
+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-24 09:39:44 +0000
@@ -2365,6 +2365,79 @@ 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 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
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+
+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-24 09:39:44 +0000
@@ -2365,6 +2365,79 @@ 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 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
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+
+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-24 09:39:44 +0000
@@ -2365,6 +2365,79 @@ 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 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
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+
+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-24 09:39:44 +0000
@@ -2365,6 +2365,79 @@ 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 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
+explain SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	1	Using index; Start temporary
+1	PRIMARY	t1	ALL	col_int_key	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ref	k1	k1	5	test.t1.c2_key	1	Using index; End temporary
+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 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
+XOR TRUE;
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 NOT IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+SELECT t3.c1 FROM t3
+WHERE t3.c1 IN (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1);
+c1
+
+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-24 09:39:44 +0000
@@ -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-24 09:39:44 +0000
@@ -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/item_subselect.cc'
--- a/sql/item_subselect.cc	2011-06-15 10:50:48 +0000
+++ b/sql/item_subselect.cc	2011-06-24 09:39:44 +0000
@@ -2373,6 +2373,18 @@ bool subselect_single_select_engine::exe
             bool *cond_guard= tab->ref.cond_guards[i];
             if (cond_guard && !*cond_guard)
             {
+              /*
+                Can't use BKA if switching from ref to "full scan on
+                NULL key" 
+
+                @see Item_in_optimizer::val_int()
+                @see TABLE_REF::cond_guards
+                @see push_index_cond()
+                @see setup_join_buffering()
+              */
+              DBUG_ASSERT(tab->use_join_cache != JOIN_CACHE::ALG_BKA);
+              DBUG_ASSERT(tab->use_join_cache != JOIN_CACHE::ALG_BKA_UNIQUE);
+
               /* Change the access method to full table scan */
               tab->save_read_first_record= tab->read_first_record;
               tab->save_read_record= tab->read_record.read_record;

=== 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-24 09:39:44 +0000
@@ -10450,6 +10450,10 @@ static void push_index_cond(JOIN_TAB *ta
     4. The JOIN_TAB is not part of a subquery that has guarded conditions
        that can be turned on or off during execution of a 'Full scan on NULL 
        key'.
+       @see Item_in_optimizer::val_int()
+       @see subselect_single_select_engine::exec()
+       @see TABLE_REF::cond_guards
+       @see setup_join_buffering
   */
   if (tab->condition() &&
       tab->table->file->index_flags(keyno, 0, 1) &
@@ -10944,6 +10948,28 @@ 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 a subquery that can
+      turn a ref access into a "full scan on NULL key" table scan.
+
+      @see Item_in_optimizer::val_int()
+      @see subselect_single_select_engine::exec()
+      @see TABLE_REF::cond_guards
+      @see push_index_cond()
+
+      @todo: This choice to not use BKA should be done before making
+      cost estimates, e.g. in set_join_buffer_properties(). That
+      happens before cond guards are set up, so instead of doing the
+      check below, BKA should be disabled if
+       - We are in an IN subquery, and
+       - The IN predicate is not a top_level_item, and
+       - The left_expr of the IN predicate may contain NULL values 
+         (left_expr->maybe_null)
+    */
+    if (tab->has_guarded_conds())
+      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;

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (jorgen.loland:3387 to 3388) Jorgen Loland25 Jun