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).
| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:3386 to 3387) Bug#12616131 | Jorgen Loland | 22 Jun |
