List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:June 20 2011 2:13pm
Subject:bzr commit into mysql-trunk branch (jorgen.loland:3385) Bug#12616131
View as plain text  
#At file:///export/home/jl208045/mysql/mysql-next-mr-opt-backporting/ based on revid:jorgen.loland@stripped

 3385 Jorgen Loland	2011-06-20
      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.
      
      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
        For BKA_UNIQUE: 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.

    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-08 13:24:56 +0000
+++ b/mysql-test/include/join_cache.inc	2011-06-20 14:12:56 +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 # Bug312616131 - 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-20 14:12:56 +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;
+#
+# Bug312616131 - 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-20 14:12:56 +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;
+#
+# Bug312616131 - 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-20 14:12:56 +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;
+#
+# Bug312616131 - 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-20 14:12:56 +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;
+#
+# Bug312616131 - 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-20 14:12:56 +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;
+#
+# Bug312616131 - 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-20 14:12:56 +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;
+#
+# Bug312616131 - 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-20 14:12:56 +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;
+#
+# Bug312616131 - 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-20 14:12:56 +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;
+#
+# Bug312616131 - 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-20 14:12:56 +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;
+#
+# Bug312616131 - 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-20 14:12:56 +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.


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20110620141256-g2auznma0gkomdeq.bundle
Thread
bzr commit into mysql-trunk branch (jorgen.loland:3385) Bug#12616131Jorgen Loland20 Jun
  • Re: bzr commit into mysql-trunk branch (jorgen.loland:3385) Bug#12616131Øystein Grøvlen21 Jun