From: Jorgen Loland
Date: June 22 2011 9:07am
Subject: bzr push into mysql-trunk branch (jorgen.loland:3386 to 3387) Bug#12616131
List-Archive: http://lists.mysql.com/commits/139663
X-Bug: 12616131
Message-Id: <20110622090725.B40B9C26@atum21.norway.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 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
 3386 Roy Lyseng	2011-06-21
      Bug#12603200: Assert in QUICK_INDEX_MERGE_SELECT::need_sorted_output
      
      The problematic query is semi-join transformed and a LooseScan
      strategy is selected. setup_semijoin_dups_elimination() inspects
      the provided quick select object and attempts to set it to require
      ordering of output rows. However, the quick select object was not
      selected in the first place (see
      Loose_scan_opt::check_ref_access_part1()), hence there is a missing
      check that the index covered by the quick select matches the index
      selected for the loose scan access. Will also delete the quick select
      object when it is clear it will not be used later.
      
      Fixed by adding this check, and also deleting the quick select object
      if it was not chosen for accessing this table.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#12603200.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
        Added test case result for bug#12603200.
      
      sql/sql_select.cc
        Fixed setup_semijoin_dups_elimination() as described above.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
      sql/sql_select.cc
=== modified file 'mysql-test/include/join_cache.inc'
--- a/mysql-test/include/join_cache.inc	2011-06-08 13:24:56 +0000
+++ b/mysql-test/include/join_cache.inc	2011-06-22 09:07:01 +0000
@@ -1474,16 +1474,18 @@ INSERT INTO t1 VALUES (NULL),(3);
 CREATE TABLE t2 (a int, b int, KEY (b));
 INSERT INTO t2 VALUES (100,NULL),(150,200);
 
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+let $query= SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
+--eval EXPLAIN $query
 --sorted_result
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+--eval $query
 
 # test crash when no key is worth collecting by BKA for t2's ref
 delete from t1;
 INSERT INTO t1 VALUES (NULL),(NULL);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+
+--eval EXPLAIN $query
 --sorted_result
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+--eval $query
 
 DROP TABLE t1,t2;
 
@@ -1646,4 +1648,45 @@ left join t8 on t3.col582 <=  1;
 
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
 
+--echo #
+--echo # Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 
+--echo #                RETURNED WHEN JCL>=7
+--echo #
+
+CREATE TABLE t1 (t1a int, t1b int);
+INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0);
+
+CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL);
+
+let $query1= SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+let $query2= SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+
+--echo
+--echo # t2b is NULL-able
+--echo
+--eval EXPLAIN $query1
+--eval $query1
+--echo
+--eval EXPLAIN $query2
+--eval $query2
+--echo
+
+DROP TABLE t2;
+
+CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0);
+
+--echo
+--echo # t2b is NOT NULL
+--echo
+--eval EXPLAIN $query1
+--eval $query1
+--echo
+--eval EXPLAIN $query2
+--eval $query2
+--echo
+
+DROP TABLE t1,t2;
+
 set @@join_buffer_size=default;

=== modified file 'mysql-test/r/join_cache_jcl0.result'
--- a/mysql-test/r/join_cache_jcl0.result	2011-06-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl0.result	2011-06-22 09:07:01 +0000
@@ -2133,21 +2133,21 @@ CREATE TABLE t1 (b int);
 INSERT INTO t1 VALUES (NULL),(3);
 CREATE TABLE t2 (a int, b int, KEY (b));
 INSERT INTO t2 VALUES (100,NULL),(150,200);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
 delete from t1;
 INSERT INTO t1 VALUES (NULL),(NULL);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
@@ -2303,6 +2303,68 @@ left join t8 on t3.col582 <=  1;
 count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
+#
+# Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 
+#                RETURNED WHEN JCL>=7
+#
+CREATE TABLE t1 (t1a int, t1b int);
+INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0);
+CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL);
+
+# t2b is NULL-able
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using where
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	999	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+DROP TABLE t2;
+CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0);
+
+# t2b is NOT NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using where
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+DROP TABLE t1,t2;
 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-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl1.result	2011-06-22 09:07:01 +0000
@@ -2133,21 +2133,21 @@ CREATE TABLE t1 (b int);
 INSERT INTO t1 VALUES (NULL),(3);
 CREATE TABLE t2 (a int, b int, KEY (b));
 INSERT INTO t2 VALUES (100,NULL),(150,200);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
 delete from t1;
 INSERT INTO t1 VALUES (NULL),(NULL);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
@@ -2303,6 +2303,68 @@ left join t8 on t3.col582 <=  1;
 count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
+#
+# Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 
+#                RETURNED WHEN JCL>=7
+#
+CREATE TABLE t1 (t1a int, t1b int);
+INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0);
+CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL);
+
+# t2b is NULL-able
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using where
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	999	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+DROP TABLE t2;
+CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0);
+
+# t2b is NOT NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using where
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+DROP TABLE t1,t2;
 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-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl2.result	2011-06-22 09:07:01 +0000
@@ -2133,21 +2133,21 @@ CREATE TABLE t1 (b int);
 INSERT INTO t1 VALUES (NULL),(3);
 CREATE TABLE t2 (a int, b int, KEY (b));
 INSERT INTO t2 VALUES (100,NULL),(150,200);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
 delete from t1;
 INSERT INTO t1 VALUES (NULL),(NULL);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
 1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
@@ -2303,6 +2303,68 @@ left join t8 on t3.col582 <=  1;
 count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
+#
+# Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 
+#                RETURNED WHEN JCL>=7
+#
+CREATE TABLE t1 (t1a int, t1b int);
+INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0);
+CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL);
+
+# t2b is NULL-able
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using where
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	999	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+DROP TABLE t2;
+CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0);
+
+# t2b is NOT NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using where
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+DROP TABLE t1,t2;
 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-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl3.result	2011-06-22 09:07:01 +0000
@@ -2133,21 +2133,21 @@ CREATE TABLE t1 (b int);
 INSERT INTO t1 VALUES (NULL),(3);
 CREATE TABLE t2 (a int, b int, KEY (b));
 INSERT INTO t2 VALUES (100,NULL),(150,200);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
 delete from t1;
 INSERT INTO t1 VALUES (NULL),(NULL);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
@@ -2303,6 +2303,68 @@ left join t8 on t3.col582 <=  1;
 count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
+#
+# Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 
+#                RETURNED WHEN JCL>=7
+#
+CREATE TABLE t1 (t1a int, t1b int);
+INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0);
+CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL);
+
+# t2b is NULL-able
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using where
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	999	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+DROP TABLE t2;
+CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0);
+
+# t2b is NOT NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using where
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+DROP TABLE t1,t2;
 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-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl4.result	2011-06-22 09:07:01 +0000
@@ -2133,21 +2133,21 @@ CREATE TABLE t1 (b int);
 INSERT INTO t1 VALUES (NULL),(3);
 CREATE TABLE t2 (a int, b int, KEY (b));
 INSERT INTO t2 VALUES (100,NULL),(150,200);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
 delete from t1;
 INSERT INTO t1 VALUES (NULL),(NULL);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
@@ -2303,6 +2303,68 @@ left join t8 on t3.col582 <=  1;
 count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
+#
+# Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 
+#                RETURNED WHEN JCL>=7
+#
+CREATE TABLE t1 (t1a int, t1b int);
+INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0);
+CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL);
+
+# t2b is NULL-able
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using where
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	999	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+DROP TABLE t2;
+CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0);
+
+# t2b is NOT NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using where
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+99	0	100	0
+99	0	999	0
+
+DROP TABLE t1,t2;
 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-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl5.result	2011-06-22 09:07:01 +0000
@@ -2133,21 +2133,21 @@ CREATE TABLE t1 (b int);
 INSERT INTO t1 VALUES (NULL),(3);
 CREATE TABLE t2 (a int, b int, KEY (b));
 INSERT INTO t2 VALUES (100,NULL),(150,200);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA, regular buffers)
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
 delete from t1;
 INSERT INTO t1 VALUES (NULL),(NULL);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA, regular buffers)
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
@@ -2303,6 +2303,68 @@ left join t8 on t3.col582 <=  1;
 count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
+#
+# Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 
+#                RETURNED WHEN JCL>=7
+#
+CREATE TABLE t1 (t1a int, t1b int);
+INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0);
+CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL);
+
+# t2b is NULL-able
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using join buffer (BKA, regular buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using where; Using join buffer (BKA, regular buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	999	NULL
+99	3	NULL	NULL
+
+DROP TABLE t2;
+CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0);
+
+# t2b is NOT NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using join buffer (BKA, regular buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using where; Using join buffer (BKA, regular buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+
+DROP TABLE t1,t2;
 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-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl6.result	2011-06-22 09:07:01 +0000
@@ -2133,21 +2133,21 @@ CREATE TABLE t1 (b int);
 INSERT INTO t1 VALUES (NULL),(3);
 CREATE TABLE t2 (a int, b int, KEY (b));
 INSERT INTO t2 VALUES (100,NULL),(150,200);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA, incremental buffers)
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
 delete from t1;
 INSERT INTO t1 VALUES (NULL),(NULL);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA, incremental buffers)
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
@@ -2303,6 +2303,68 @@ left join t8 on t3.col582 <=  1;
 count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
+#
+# Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 
+#                RETURNED WHEN JCL>=7
+#
+CREATE TABLE t1 (t1a int, t1b int);
+INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0);
+CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL);
+
+# t2b is NULL-able
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using join buffer (BKA, incremental buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using where; Using join buffer (BKA, incremental buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	999	NULL
+99	3	NULL	NULL
+
+DROP TABLE t2;
+CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0);
+
+# t2b is NOT NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using join buffer (BKA, incremental buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using where; Using join buffer (BKA, incremental buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+
+DROP TABLE t1,t2;
 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-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl7.result	2011-06-22 09:07:01 +0000
@@ -2133,21 +2133,21 @@ CREATE TABLE t1 (b int);
 INSERT INTO t1 VALUES (NULL),(3);
 CREATE TABLE t2 (a int, b int, KEY (b));
 INSERT INTO t2 VALUES (100,NULL),(150,200);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
 delete from t1;
 INSERT INTO t1 VALUES (NULL),(NULL);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
@@ -2303,6 +2303,68 @@ left join t8 on t3.col582 <=  1;
 count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
+#
+# Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 
+#                RETURNED WHEN JCL>=7
+#
+CREATE TABLE t1 (t1a int, t1b int);
+INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0);
+CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL);
+
+# t2b is NULL-able
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using where; Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	999	NULL
+99	3	NULL	NULL
+
+DROP TABLE t2;
+CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0);
+
+# t2b is NOT NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using where; Using join buffer (BKA_UNIQUE, regular buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+
+DROP TABLE t1,t2;
 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-08 13:24:56 +0000
+++ b/mysql-test/r/join_cache_jcl8.result	2011-06-22 09:07:01 +0000
@@ -2133,21 +2133,21 @@ CREATE TABLE t1 (b int);
 INSERT INTO t1 VALUES (NULL),(3);
 CREATE TABLE t2 (a int, b int, KEY (b));
 INSERT INTO t2 VALUES (100,NULL),(150,200);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA_UNIQUE, incremental buffers)
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
 delete from t1;
 INSERT INTO t1 VALUES (NULL),(NULL);
-explain SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+EXPLAIN SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	SIMPLE	t2	ALL	b	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
-SELECT t2.a FROM t1 LEFT JOIN t2 ON t2.b  = t1.b;
+1	SIMPLE	t2	ref	b	b	5	test.t1.b	2	Using join buffer (BKA_UNIQUE, incremental buffers)
+SELECT t2.a FROM t1 LEFT JOIN t2 FORCE INDEX (b) ON t2.b  = t1.b;
 a
 NULL
 NULL
@@ -2303,6 +2303,68 @@ left join t8 on t3.col582 <=  1;
 count(*)
 32
 drop table t1,t2,t3,t4,t5,t6,t7,t8;
+#
+# Bug#12616131 - JCL: NULL VS DATE + TWICE AS MANY ROWS 
+#                RETURNED WHEN JCL>=7
+#
+CREATE TABLE t1 (t1a int, t1b int);
+INSERT INTO t1 VALUES (99, NULL),(99, 3),(99,0);
+CREATE TABLE t2 (t2a int, t2b int, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0),(999, NULL);
+
+# t2b is NULL-able
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using join buffer (BKA_UNIQUE, incremental buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.t1b	2	Using where; Using join buffer (BKA_UNIQUE, incremental buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	999	NULL
+99	3	NULL	NULL
+
+DROP TABLE t2;
+CREATE TABLE t2 (t2a int, t2b int NOT NULL, KEY idx (t2b));
+INSERT INTO t2 VALUES (100,0),(150,200),(999, 0);
+
+# t2b is NOT NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using join buffer (BKA_UNIQUE, incremental buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b = t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.t1b	2	Using where; Using join buffer (BKA_UNIQUE, incremental buffers)
+SELECT * FROM t1 LEFT JOIN t2 force index (idx) ON t2.t2b <=> t1.t1b;
+t1a	t1b	t2a	t2b
+99	0	100	0
+99	0	999	0
+99	NULL	NULL	NULL
+99	3	NULL	NULL
+
+DROP TABLE t1,t2;
 set @@join_buffer_size=default;
 set optimizer_join_cache_level = default;
 set optimizer_switch = default;

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2011-06-11 13:20:10 +0000
+++ b/sql/sql_join_cache.cc	2011-06-22 09:07:01 +0000
@@ -748,8 +748,8 @@ bool JOIN_CACHE_BKA::check_emb_key_usage
       return FALSE;
       /*
         If this is changed so that embedded keys may contain nullable
-        components, get_next_key() willhave to test ref->null_rejecting in the
-        "embedded keys" case too.
+        components, get_next_key() and put_record() will have to test
+        ref->null_rejecting in the "embedded keys" case too.
       */
     }
   }
@@ -2652,27 +2652,35 @@ void JOIN_CACHE_BKA_UNIQUE::reset(bool f
 
 bool JOIN_CACHE_BKA_UNIQUE::put_record()
 {
-  bool is_full;
   uchar *key;
   uint key_len= key_length;
   uchar *key_ref_ptr;
-  uchar *link= 0;
   TABLE_REF *ref= &join_tab->ref;
   uchar *next_ref_ptr= pos;
-
   pos+= get_size_of_rec_offset();
-  /* Write the record into the join buffer */  
-  if (prev_cache)
-    link= prev_cache->get_curr_rec_link();
-  write_record_data(link, &is_full);
+
+  // Write record to join buffer
+  bool is_full= JOIN_CACHE::put_record();
 
   if (use_emb_key)
-    key= get_curr_emb_key();
+  {
+     key= get_curr_emb_key();
+    // Embedded is not used if one of the key columns is nullable
+  }
   else
   {
     /* Build the key over the fields read into the record buffers */ 
     cp_buffer_from_ref(join->thd, join_tab->table, ref);
     key= ref->key_buff;
+    /*
+      If the row just read into the buffer has a NULL-value for one of
+      the ref-columns and the join comparison function for that column
+      is '=' (in contrast to '<=>'), it's impossible with a join match
+      for this row. The key is therefore not inserted into the hash
+      table.
+    */
+    if (ref->impossible_null_ref())
+      return is_full;
   }
 
   /* Look for the key in the hash table */
@@ -3250,54 +3258,19 @@ bool JOIN_CACHE_BKA_UNIQUE::check_all_ma
 */
 
 uint JOIN_CACHE_BKA_UNIQUE::get_next_key(uchar ** key)
-{  
-
-  uint len= 0;
+{
+  if (curr_key_entry == last_key_entry)
+    return 0;
 
-  /* Read keys until find non-ignorable one or EOF */
-  while((curr_key_entry > last_key_entry) && (len == 0))
-  {
-    curr_key_entry-= key_entry_length;
+  curr_key_entry-= key_entry_length;
 
-    *key = use_emb_key ? get_emb_key(curr_key_entry) : curr_key_entry;
+  *key = use_emb_key ? get_emb_key(curr_key_entry) : curr_key_entry;
 
-    DBUG_ASSERT(*key >= buff && *key < hash_table);
+  DBUG_ASSERT(*key >= buff && *key < hash_table);
 
-    len= key_length;
-    DBUG_ASSERT(len != 0);
-    const TABLE_REF *ref= &join_tab->ref;
-    if (ref->null_rejecting != 0)
-    {
-      /*
-        Unlike JOIN_CACHE_BKA::get_next_key(), we have a key just read from
-        a buffer, not up-to-date fields pointed to by "ref". So we cannot use
-        ref->null_rejected(), must inspect the key.
-      */
-      const KEY *key_info= join_tab->table->key_info + ref->key;
-      const uchar *ptr= *key;
-#ifndef DBUG_OFF
-      const uchar *key_end= ptr + key_length;
-#endif
-      for (uint i= 0 ; i < ref->key_parts ; i++)
-      {
-        KEY_PART_INFO *key_part= key_info->key_part + i;
-        if (key_part->null_bit && ptr[0] && (ref->null_rejecting & 1 << i))
-        {
-          DBUG_PRINT("info", ("JOIN_CACHE_BKA_UNIQUE::get_next_key null_rejected"));
-          len= 0;
-          break;
-        }
-        ptr+= key_part->store_length;
-        DBUG_ASSERT(ptr <= key_end);
-      }
-      if (len != 0)
-        DBUG_ASSERT(ptr == key_end); /* should have read the full key */
-    }
-  }
-  return len;
+  return key_length;
 }
 
-
 /**
   Check matching to a partial join record from the join buffer, an
   implementation specialized for JOIN_CACHE_BKA_UNIQUE.

No bundle (reason: useless for push emails).