5.5 merge and fixes for compiler/test errors

1 files changed, 113 insertions, 1 deletions

diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index 806afb1c843..55b2ac7bc3a 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -5345,7 +5345,7 @@ a	b	c
 8	8	8
 DROP TABLE t1,t2;
 #
-# Bug mdev-4413: another manifestations of bug mdev-2474
+# Bug mdev-4413: another manifestations of bug mdev-4274
 #                (valgrind complains)
 #
 CREATE TABLE t1 (a int, b int) ENGINE=MyISAM;
@@ -5357,6 +5357,118 @@ WHERE c = a AND
 ( 0 OR ( b BETWEEN 45 AND 300 OR a > 45 AND a < 100 ) AND b = c );
 a	b	c
 DROP TABLE t1, t2;
+#
+# Bug mdev-4355: equalities from the result of simplification of OR
+#                are not propagated to lower AND levels
+#
+CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,101),(2,102),(3,103),(4,104),(5,11);
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1))
+SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1);
+a	b
+5	11
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1))
+SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5);
+a	b
+5	11
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 5) and (`test`.`t1`.`b` <> 1))
+SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1);
+a	b
+5	11
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where 0
+SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1);
+a	b
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = 5) and (`test`.`t1`.`a` = 5))
+SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1);
+a	b
+DROP TABLE t1;
+#
+# Bug mdev-4418: impossible multiple equality in OR formula 
+#                after row substitution
+#
+CREATE TABLE t1 (a int, b varchar(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (0,'j'), (8,'v');
+CREATE TABLE t2 (c varchar(1), d varchar(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('k','k');
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,'k' AS `c`,'k' AS `d` from `test`.`t1` where ((`test`.`t1`.`b` = 'k') and (`test`.`t1`.`a` = 136))
+SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
+a	b	c	d
+DROP TABLE t1,t2;
+#
+# Bug mdev-4944: range conditition in OR formula with fields
+#                belonging to multiple equalities 
+#
+CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,8);
+CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8), (9);
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2 
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Note	1003	select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t2` where 0
+SELECT * FROM t1, t2 
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+i1	j1	i2
+DROP TABLE t1,t2;
+#
+# Bug mdev-4971: equality propagation after merging degenerate 
+#                disjunction into embedding AND level 
+#
+CREATE TABLE t1 (pk1 int, a1 int, b1 int, PRIMARY KEY(pk1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,10,100), (2,20,200) ;
+CREATE TABLE t2 (pk2 int, a2 int, PRIMARY KEY(pk2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,1);
+SELECT * FROM t1, t2
+WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL );
+pk1	a1	b1	pk2	a2
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	system	PRIMARY	NULL	NULL	NULL	1	100.00	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,1 AS `pk2`,1 AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1` = 1) and ((`test`.`t1`.`b1` = 6) or 0))
+INSERT INTO t1 VALUES (3,1,6);
+SELECT * FROM t1, t2
+WHERE a1 = pk2 AND ( ( b1 = 6 OR a2 > 4 ) AND pk2 = a2 OR pk1 IS NULL );
+pk1	a1	b1	pk2	a2
+3	1	6	1	1
+DROP TABLE t1,t2;
 End of 5.3 tests
 #
 # mysql BUG#1271 Undefined variable in PASSWORD()