Fixed LP bug #943543.

This bug appeared after the patch for bug 939009 that in the
function merge_key_fields forgot to reset a proper value for
the val field in the result of the merge operation of the key
field created for a regular key access and  the key field
created to look for a NULL key.

Adjusted the results of the test case for bug 939009 that
actually were incorrect. 

5 files changed, 235 insertions, 4 deletions

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 60defea291b..5624d9467af 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1656,4 +1656,88 @@ b	b	a	b
 DEALLOCATE PREPARE stmt;
 SET SESSION join_cache_level=default;
 DROP TABLE t1,t2,t3;
+#
+# LP bug #943543: LEFT JOIN converted to JOIN with 
+#                 ORed IS NULL(primary key) in WHERE clause                
+#
+CREATE TABLE t1 (
+a int, b int NOT NULL, pk int NOT NULL,
+PRIMARY KEY (pk), INDEX idx(b)
+);
+INSERT INTO t1 VALUES
+(NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4),
+(1,9,6), (8,5,7), (NULL,8,8), (8,1,5);
+CREATE TABLE t2 (pk int PRIMARY KEY);
+INSERT INTO t2 VALUES (3), (8), (5);
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where (1) order by 5
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+pk
+5
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	const	PRIMARY,idx	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+pk
+5
+DROP TABLE t2;
+CREATE TABLE t2 (c int, d int, KEY (c));
+INSERT INTO t2 VALUES
+(3,30), (8,88), (5,50), (8,81),
+(4,40), (9,90), (7,70), (9,90),
+(13,130), (18,188), (15,150), (18,181),
+(14,140), (19,190), (17,170), (19,190);
+INSERT INTO t1 VALUES (8,5,9);
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	idx	idx	4	const	2	100.00	Using where
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b`
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+b	c	d
+5	8	88
+5	8	81
+5	8	88
+5	8	81
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	PRIMARY,idx	idx	4	const	2	100.00	Using where; Using filesort
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b`
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+b	c	d
+5	8	88
+5	8	81
+5	8	88
+5	8	81
+DROP TABLE t1,t2;
 SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 767d0544f1b..d80b838e4e7 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1667,6 +1667,90 @@ b	b	a	b
 DEALLOCATE PREPARE stmt;
 SET SESSION join_cache_level=default;
 DROP TABLE t1,t2,t3;
+#
+# LP bug #943543: LEFT JOIN converted to JOIN with 
+#                 ORed IS NULL(primary key) in WHERE clause                
+#
+CREATE TABLE t1 (
+a int, b int NOT NULL, pk int NOT NULL,
+PRIMARY KEY (pk), INDEX idx(b)
+);
+INSERT INTO t1 VALUES
+(NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4),
+(1,9,6), (8,5,7), (NULL,8,8), (8,1,5);
+CREATE TABLE t2 (pk int PRIMARY KEY);
+INSERT INTO t2 VALUES (3), (8), (5);
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where (1) order by 5
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+pk
+5
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	const	PRIMARY,idx	PRIMARY	4	const	1	100.00	
+1	SIMPLE	t2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index
+Warnings:
+Note	1003	select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+pk
+5
+DROP TABLE t2;
+CREATE TABLE t2 (c int, d int, KEY (c));
+INSERT INTO t2 VALUES
+(3,30), (8,88), (5,50), (8,81),
+(4,40), (9,90), (7,70), (9,90),
+(13,130), (18,188), (15,150), (18,181),
+(14,140), (19,190), (17,170), (19,190);
+INSERT INTO t1 VALUES (8,5,9);
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	idx	idx	4	const	2	100.00	Using where
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b`
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+b	c	d
+5	8	88
+5	8	81
+5	8	88
+5	8	81
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ref	PRIMARY,idx	idx	4	const	2	100.00	Using where; Using filesort
+1	SIMPLE	t2	ref	c	c	5	test.t1.a	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b`
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+b	c	d
+5	8	88
+5	8	81
+5	8	88
+5	8	81
+DROP TABLE t1,t2;
 SET optimizer_switch=@save_optimizer_switch;
 set join_cache_level=default;
 show variables like 'join_cache_level';
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index b7cc7e8a3ed..97463447e1d 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1892,7 +1892,7 @@ EXPLAIN EXTENDED
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	Using where
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
@@ -1900,11 +1900,12 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from  <materializ
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
 a	b
+7	7
 EXPLAIN
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	Using where
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT * FROM t1
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 9a238eaaccd..db5a27a5d84 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -1930,7 +1930,7 @@ EXPLAIN EXTENDED
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	Using where
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	100.00	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 Warnings:
@@ -1938,11 +1938,12 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from  <materializ
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2) AND b=7 AND (a IS NULL OR a=b);
 a	b
+7	7
 EXPLAIN
 SELECT * FROM t1
 WHERE a IN (SELECT MAX(c) FROM t2 WHERE c < 4) AND b=7 AND (a IS NULL OR a=b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	Using where
+1	PRIMARY	<subquery2>	const	distinct_key	distinct_key	4	const	1	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
 SELECT * FROM t1
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 04816402205..3fae21ac738 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1221,4 +1221,65 @@ SET SESSION join_cache_level=default;
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # LP bug #943543: LEFT JOIN converted to JOIN with 
+--echo #                 ORed IS NULL(primary key) in WHERE clause                
+--echo #
+
+CREATE TABLE t1 (
+  a int, b int NOT NULL, pk int NOT NULL,
+  PRIMARY KEY (pk), INDEX idx(b)
+);
+INSERT INTO t1 VALUES
+  (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4),
+  (1,9,6), (8,5,7), (NULL,8,8), (8,1,5);
+
+CREATE TABLE t2 (pk int PRIMARY KEY);
+INSERT INTO t2 VALUES (3), (8), (5);
+
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+ WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+SELECT t1.pk FROM t2 JOIN t1 ON t2.pk = t1.a
+ WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+
+EXPLAIN EXTENDED
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+ WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a
+ WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5
+ORDER BY t1.pk;
+
+DROP TABLE t2;
+
+CREATE TABLE t2 (c int, d int, KEY (c));
+INSERT INTO t2 VALUES
+  (3,30), (8,88), (5,50), (8,81),
+  (4,40), (9,90), (7,70), (9,90),
+  (13,130), (18,188), (15,150), (18,181),
+  (14,140), (19,190), (17,170), (19,190);
+
+INSERT INTO t1 VALUES (8,5,9);
+
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+  WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+SELECT t1.b, t2.c, t2.d FROM t2 JOIN t1 ON t2.c = t1.a
+  WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+
+EXPLAIN EXTENDED
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+  WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a
+  WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5
+ORDER BY t1.b;
+
+DROP TABLE t1,t2;
+
 SET optimizer_switch=@save_optimizer_switch;