Merge 5.3->5.5

2 files changed, 29 insertions, 1 deletions

diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result
index e06d799845e..72ca9d33354 100644
--- a/mysql-test/r/subselect2.result
+++ b/mysql-test/r/subselect2.result
@@ -237,6 +237,34 @@ a	b
 DROP TABLE t1,t2,t3;
 set optimizer_switch=@tmp_mdev567;
 #
+# MDEV-4996: degenerate OR formula in WHERE of a subquery
+#
+CREATE TABLE t1 (a int, c1 varchar(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (2,'x'), (8,'d');
+CREATE TABLE t2 (m int, n int, c2 varchar(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (0, 5, 'x'), (1, 4,'p');
+SELECT * FROM t1 WHERE c1 NOT IN (
+SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c
+WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND
+( t2b.m != a OR t2b.m = t2a.m ));
+a	c1
+2	x
+8	d
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE c1 NOT IN (
+SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c
+WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND
+( t2b.m != a OR t2b.m = t2a.m ));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2a	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DEPENDENT SUBQUERY	t2b	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+2	DEPENDENT SUBQUERY	t2c	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (incremental, BNL join)
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t2a`.`c2` from `test`.`t2` `t2a` join `test`.`t2` `t2b` join `test`.`t2` `t2c` where (((`test`.`t2b`.`m` <> `test`.`t1`.`a`) or (`test`.`t2b`.`m` = `test`.`t2a`.`m`)) and trigcond(((<cache>(`test`.`t1`.`c1`) = `test`.`t2a`.`c2`) or isnull(`test`.`t2a`.`c2`))) and (`test`.`t2c`.`c2` = `test`.`t2b`.`c2`) and (`test`.`t2b`.`n` = `test`.`t2a`.`m`)) having trigcond(<is_not_null_test>(`test`.`t2a`.`c2`)))))))
+DROP TABLE t1,t2;
+#
 # MDEV-614, also MDEV-536, also LP:1050806:
 #  different result for a query using subquery between 5.5.25 and 5.5.27
 #
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index fbe6db4edda..74fea8957a9 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -1399,7 +1399,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 2	MATERIALIZED	t3	hash_ALL	NULL	#hash#$hj	5	test.t2.i2	3	100.00	Using where; Using join buffer (flat, BNLH join)
 Warnings:
-Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`i1` = `test`.`t2`.`i2`) and (`test`.`t2`.`i2` > 0))
+Note	1003	select `test`.`t1`.`i1` AS `i1` from `test`.`t1` semi join (`test`.`t2` join `test`.`t3`) where ((`test`.`t3`.`i3` = `test`.`t2`.`i2`) and (`test`.`t1`.`i1` = `test`.`t2`.`i2`) and (`test`.`t3`.`i3` > 0))
 SELECT * FROM t1 
 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 > 0 AND i3 = i2 OR 1=2);
 i1