Merge 5.3->5.5.

1 files changed, 84 insertions, 0 deletions

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 842ac2e81cd..163043c2cef 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1905,4 +1905,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;