Fixed bug mdev-4418.

After single row substitutions there might appear new equalities.
They should be properly propagated to all AND/OR levels the WHERE
condition. It's done now with an additional call of remove_eq_conds(). 

13 files changed, 81 insertions, 10 deletions

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 723b2a88382..684bfb3836e 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1649,7 +1649,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(6,<exists>(select `test`.`t3`.`a` from `test`.`t3` where (6 = `test`.`t3`.`a`)))) where 1
+Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(6,<exists>(select `test`.`t3`.`a` from `test`.`t3` where (6 = `test`.`t3`.`a`))))
 DROP TABLE t1,t2,t3;
 #
 # LP bug #817384 Wrong result with outer join + subquery in ON
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index d063720836d..4f4c16199e9 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1660,7 +1660,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(6,<exists>(select `test`.`t3`.`a` from `test`.`t3` where (6 = `test`.`t3`.`a`)))) where 1
+Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t1` left join `test`.`t2` on(<in_optimizer>(6,<exists>(select `test`.`t3`.`a` from `test`.`t3` where (6 = `test`.`t3`.`a`))))
 DROP TABLE t1,t2,t3;
 #
 # LP bug #817384 Wrong result with outer join + subquery in ON
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index dfc5236f794..56a4a8d2bdc 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -5217,4 +5217,22 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
 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` join `test`.`t2` 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;
 End of 5.3 tests
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 6eb0d65a72f..1a735299e0e 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -5228,6 +5228,24 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
 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` join `test`.`t2` 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;
 End of 5.3 tests
 set join_cache_level=default;
 show variables like 'join_cache_level';
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index dfc5236f794..56a4a8d2bdc 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -5217,4 +5217,22 @@ Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1`
 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` join `test`.`t2` 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;
 End of 5.3 tests
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 8503164dde2..e4448d96657 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1322,7 +1322,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 INSERT INTO t1 (pseudo) VALUES ('test1');
 SELECT 0 IN (SELECT 1 FROM t1 a);
 0 IN (SELECT 1 FROM t1 a)
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index d0c9555d976..df6b884fbe0 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -1329,7 +1329,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 INSERT INTO t1 (pseudo) VALUES ('test1');
 SELECT 0 IN (SELECT 1 FROM t1 a);
 0 IN (SELECT 1 FROM t1 a)
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 47f6d3a0ed4..f9df463f6aa 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -1325,7 +1325,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 INSERT INTO t1 (pseudo) VALUES ('test1');
 SELECT 0 IN (SELECT 1 FROM t1 a);
 0 IN (SELECT 1 FROM t1 a)
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index f3a01618eff..70de4b26743 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -1328,7 +1328,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 INSERT INTO t1 (pseudo) VALUES ('test1');
 SELECT 0 IN (SELECT 1 FROM t1 a);
 0 IN (SELECT 1 FROM t1 a)
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 964704e0695..91071c98777 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -1325,7 +1325,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
 INSERT INTO t1 (pseudo) VALUES ('test1');
 SELECT 0 IN (SELECT 1 FROM t1 a);
 0 IN (SELECT 1 FROM t1 a)
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index 35acd30d76d..86e5bbe0060 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -17,7 +17,7 @@ explain extended select t1.a from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
+Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1`
 select t1.a from t1 left join t2 on t2.a=t1.a;
 a
 0
@@ -62,7 +62,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	4	100.00	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on((`test`.`t1`.`a` = `test`.`t0`.`a`)) where 1
+Note	1003	select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on((`test`.`t1`.`a` = `test`.`t0`.`a`))
 # Elimination with aggregate functions
 explain select count(*) from t1 left join t2 on t2.a=t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 0f666f0e708..2d89c66a226 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4300,7 +4300,7 @@ WHERE f1<>0 OR f2<>0 AND f4='v' AND (f2<>0 OR f3<>0 AND f5<>0 OR f4 LIKE '%b%');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select 'r' AS `f4` from `test`.`t1` where ((20 <> 0) or 0)
+Note	1003	select 'r' AS `f4` from `test`.`t1` where (20 <> 0)
 DROP VIEW v1;
 DROP TABLE t1;
 #
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 94e169821e9..c0e22cb8f4e 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -4372,4 +4372,21 @@ SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1);
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug mdev-4418: impossible multiple equality in OR formula 
+--echo #                after row substitution
+--echo #
+
+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));
+SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
+
+DROP TABLE t1,t2;
+
 --echo End of 5.3 tests