[Commits] Rev 3851: Merge 5.3->5.5. in file:///home/igor/maria/maria-5.5-merge/

Igor Babaev igor at askmonty.org
Mon Aug 19 05:59:54 EEST 2013


At file:///home/igor/maria/maria-5.5-merge/

------------------------------------------------------------
revno: 3851 [merge]
revision-id: igor at askmonty.org-20130819025923-kxklic9911lvdgi9
parent: bar at mariadb.org-20130812133308-xugyz8mncfwny89b
parent: igor at askmonty.org-20130817050147-k5dvrilf0u0vz8cr
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.5-merge
timestamp: Sun 2013-08-18 19:59:23 -0700
message:
  Merge 5.3->5.5.
  In particular:
  Merged the patch for bug mdev-4418 from 5.3 into 5.5.
  Fixed a bug in the patch that should be backported to 5.3.
modified:
  mysql-test/r/join_outer.result sp1f-join_outer.result-20001228015633-vk2jshiracfus3ze2d2bim2csnnrc5us
  mysql-test/r/join_outer_jcl6.result join_outer_jcl6.resu-20091221012858-uiftww98yhc31z02-1
  mysql-test/r/range.result      sp1f-range.result-20001228015634-6hpoyn74lnc7irf4gop2jbowgpazbbae
  mysql-test/r/range_mrr_icp.result range_mrr_icp.result-20110708144530-oix2pzfbpykksv5t-1
  mysql-test/r/select.result     sp1f-select.result-20010103001548-znkoalxem6wchsbxizfosjhpfmhfyxuk
  mysql-test/r/select_jcl6.result select_jcl6.result-20091221012908-0kl039gl68crw8rz-1
  mysql-test/r/select_pkeycache.result select_pkeycache.res-20100216163255-sd54514jjhw0yi81-3
  mysql-test/r/subselect.result  subselect_scache.res-20110713142423-t6lrgrizahbpk2co-2
  mysql-test/r/subselect4.result subselect4.result-20090903150316-1sul3u8k29ooxm3r-2
  mysql-test/r/subselect_no_mat.result subselect_no_mat.res-20100117143924-hut18sl9k2c7qdj8-1
  mysql-test/r/subselect_no_opts.result subselect_no_opts.re-20100117143925-pabg7o8iyokjlu93-1
  mysql-test/r/subselect_no_scache.result sp1f-subselect.result-20020512204640-zgegcsgavnfd7t7eyrf7ibuqomsw7uzo
  mysql-test/r/subselect_no_semijoin.result subselect_no_semijoi-20100117143925-9yfygtcm7fwsuq2p-1
  mysql-test/r/subselect_sj2_mat.result subselect_sj2_mat.re-20110203133247-60oo2ydzq1r2g7ib-1
  mysql-test/r/table_elim.result table_elim.result-20090603125022-nge13y0ohk1g2tt2-1
  mysql-test/r/view.result       sp1f-view.result-20040715221517-nqk3l34grrhprjiitidhfjyjqlgh6a5v
  mysql-test/suite/vcol/r/vcol_misc.result vcol_misc.result-20100703032010-vsczyu8ezoekgraf-1
  mysql-test/suite/vcol/t/vcol_misc.test vcol_misc.test-20100703031956-dahu2qm6h9xlm1t9-1
  mysql-test/t/range.test        sp1f-range.test-20001228015636-xfak6bsaw5p3ek36np7bznadjb3boh2q
  mysql-test/t/select.test       sp1f-select.test-20010103001548-tbl2ff7qehzh43qnsmf4ejhjqe66f46n
  sql/item_cmpfunc.cc            sp1f-item_cmpfunc.cc-19700101030959-hrk7pi2n6qpwxauufnkizirsoucdcx2e
  sql/item_cmpfunc.h             sp1f-item_cmpfunc.h-19700101030959-pcvbjplo4e4ng7ibynfhcd6pjyem57gr
  sql/spatial.cc                 sp1f-spatial.cc-20020222112439-yecm64geoygpvzrlc2mg7hdtbvtlmhoe
  sql/spatial.h                  sp1f-spatial.h-20020222112440-72iyz2ld6nl3qbitdm4dorewo3urjci7
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
  sql/table.cc                   sp1f-table.cc-19700101030959-nsxtem2adyqzwe6nz4cgrpcmts3o54v7
-------------- next part --------------
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2013-07-31 13:24:52 +0000
+++ b/mysql-test/r/join_outer.result	2013-08-19 02:59:23 +0000
@@ -1930,7 +1930,7 @@
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select NULL AS `a` from `test`.`t2` where 1
+Note	1003	select NULL AS `a` from `test`.`t2`
 DROP TABLE t1,t2,t3;
 #
 # LP bug #817384 Wrong result with outer join + subquery in ON

=== modified file 'mysql-test/r/join_outer_jcl6.result'
--- a/mysql-test/r/join_outer_jcl6.result	2013-07-31 13:24:52 +0000
+++ b/mysql-test/r/join_outer_jcl6.result	2013-08-19 02:59:23 +0000
@@ -1941,7 +1941,7 @@
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	100.00	
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select NULL AS `a` from `test`.`t2` where 1
+Note	1003	select NULL AS `a` from `test`.`t2`
 DROP TABLE t1,t2,t3;
 #
 # LP bug #817384 Wrong result with outer join + subquery in ON

=== modified file 'mysql-test/r/range.result'
--- a/mysql-test/r/range.result	2013-07-31 13:24:52 +0000
+++ b/mysql-test/r/range.result	2013-08-19 02:59:23 +0000
@@ -712,14 +712,14 @@
 'd8c4177d09f8b11f5.52725522');
 EXPLAIN
 SELECT s.oxid FROM t1 v, t1 s 
-WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+WHERE 
 v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
 s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	v	ref	OXLEFT,OXRIGHT,OXROOTID	OXROOTID	34	const	5	Using index condition
 1	SIMPLE	s	ALL	OXLEFT	NULL	NULL	NULL	12	Range checked for each record (index map: 0x4)
 SELECT s.oxid FROM t1 v, t1 s 
-WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+WHERE
 v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
 s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 oxid
@@ -728,6 +728,11 @@
 d8c4177d225791924.30714720
 d8c4177d2380fc201.39666693
 d8c4177d24ccef970.14957924
+d8c4177d151affab2.81582771
+d8c4177d206a333d2.74422678
+d8c4177d225791924.30714721
+d8c4177d2380fc201.39666694
+d8c4177d24ccef970.14957925
 DROP TABLE t1;
 create table t1 (
 c1  char(10), c2  char(10), c3  char(10), c4  char(10),
@@ -1887,6 +1892,46 @@
 AAAA	AAAA	AAAA
 AAAAA	AAAAA	AAAAA
 DROP TABLE t1;
+#
+# mdev-4894:  Poor performance with unnecessary 
+# (bug#70021) 'Range checked for each record'
+#
+create table t1( key1 int not null, INDEX i1(key1) );
+insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8);
+insert into t1 select key1+8 from t1;
+insert into t1 select key1+16 from t1;
+insert into t1 select key1+32 from t1;
+insert into t1 select key1+64 from t1;
+insert into t1 select key1+128 from t1;
+insert into t1 select key1+256 from t1;
+insert into t1 select key1+512 from t1;
+alter table t1 add key2 int not null, add index i2(key2);
+update t1 set key2=key1;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+create table t2 (a int);
+insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8);
+insert into t2 select a+16 from t2;
+insert into t2 select a+32 from t2;
+insert into t2 select a+64 from t2;
+explain
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	64	
+1	SIMPLE	t1	range	i1,i2	i1	4	NULL	78	Using where; Using join buffer (flat, BNL join)
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
+count(*)
+128
+explain
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	64	
+1	SIMPLE	t1	range	i1,i2	i1	4	NULL	78	Using where; Using join buffer (flat, BNL join)
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
+count(*)
+126
+drop table t1,t2;
 End of 5.1 tests
 #
 # LP Bug #533117: Wrong use_count in SEL_ARG trees

=== modified file 'mysql-test/r/range_mrr_icp.result'
--- a/mysql-test/r/range_mrr_icp.result	2013-07-31 13:24:52 +0000
+++ b/mysql-test/r/range_mrr_icp.result	2013-08-19 02:59:23 +0000
@@ -714,14 +714,14 @@
 'd8c4177d09f8b11f5.52725522');
 EXPLAIN
 SELECT s.oxid FROM t1 v, t1 s 
-WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+WHERE 
 v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
 s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	v	ref	OXLEFT,OXRIGHT,OXROOTID	OXROOTID	34	const	5	Using index condition
 1	SIMPLE	s	ALL	OXLEFT	NULL	NULL	NULL	12	Range checked for each record (index map: 0x4)
 SELECT s.oxid FROM t1 v, t1 s 
-WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+WHERE
 v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
 s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 oxid
@@ -730,6 +730,11 @@
 d8c4177d225791924.30714720
 d8c4177d2380fc201.39666693
 d8c4177d24ccef970.14957924
+d8c4177d151affab2.81582771
+d8c4177d206a333d2.74422678
+d8c4177d225791924.30714721
+d8c4177d2380fc201.39666694
+d8c4177d24ccef970.14957925
 DROP TABLE t1;
 create table t1 (
 c1  char(10), c2  char(10), c3  char(10), c4  char(10),
@@ -1889,6 +1894,46 @@
 AAAA	AAAA	AAAA
 AAAAA	AAAAA	AAAAA
 DROP TABLE t1;
+#
+# mdev-4894:  Poor performance with unnecessary 
+# (bug#70021) 'Range checked for each record'
+#
+create table t1( key1 int not null, INDEX i1(key1) );
+insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8);
+insert into t1 select key1+8 from t1;
+insert into t1 select key1+16 from t1;
+insert into t1 select key1+32 from t1;
+insert into t1 select key1+64 from t1;
+insert into t1 select key1+128 from t1;
+insert into t1 select key1+256 from t1;
+insert into t1 select key1+512 from t1;
+alter table t1 add key2 int not null, add index i2(key2);
+update t1 set key2=key1;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+create table t2 (a int);
+insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8);
+insert into t2 select a+16 from t2;
+insert into t2 select a+32 from t2;
+insert into t2 select a+64 from t2;
+explain
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	64	
+1	SIMPLE	t1	range	i1,i2	i1	4	NULL	78	Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
+count(*)
+128
+explain
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	64	
+1	SIMPLE	t1	range	i1,i2	i1	4	NULL	78	Using where; Rowid-ordered scan; Using join buffer (flat, BNL join)
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
+count(*)
+126
+drop table t1,t2;
 End of 5.1 tests
 #
 # LP Bug #533117: Wrong use_count in SEL_ARG trees

=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2013-05-08 09:36:17 +0000
+++ b/mysql-test/r/select.result	2013-08-19 02:59:23 +0000
@@ -4786,7 +4786,7 @@
 1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
 Warnings:
-Note	1003	select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1
+Note	1003	select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1`
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
@@ -5345,7 +5345,7 @@
 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,4 +5357,72 @@
 ( 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;
 End of 5.3 tests

=== modified file 'mysql-test/r/select_jcl6.result'
--- a/mysql-test/r/select_jcl6.result	2013-05-08 09:36:17 +0000
+++ b/mysql-test/r/select_jcl6.result	2013-08-19 02:59:23 +0000
@@ -4797,7 +4797,7 @@
 1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
 Warnings:
-Note	1003	select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1
+Note	1003	select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1`
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
@@ -5356,7 +5356,7 @@
 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;
@@ -5368,6 +5368,74 @@
 ( 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;
 End of 5.3 tests
 set join_cache_level=default;
 show variables like 'join_cache_level';

=== modified file 'mysql-test/r/select_pkeycache.result'
--- a/mysql-test/r/select_pkeycache.result	2013-05-08 09:36:17 +0000
+++ b/mysql-test/r/select_pkeycache.result	2013-08-19 02:59:23 +0000
@@ -4786,7 +4786,7 @@
 1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
 Warnings:
-Note	1003	select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1
+Note	1003	select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1`
 EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where
@@ -5345,7 +5345,7 @@
 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,4 +5357,72 @@
 ( 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;
 End of 5.3 tests

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2013-07-16 17:09:54 +0000
+++ b/mysql-test/r/subselect.result	2013-08-19 02:59:23 +0000
@@ -1319,7 +1319,7 @@
 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 dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from dual 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)

=== modified file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	2013-03-29 15:53:21 +0000
+++ b/mysql-test/r/subselect4.result	2013-08-19 02:59:23 +0000
@@ -1750,7 +1750,7 @@
 EXPLAIN SELECT * FROM t1 WHERE a1 IN (SELECT b1 FROM t2 WHERE b1 = b2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 SELECT * FROM t1 WHERE a1 IN (SELECT b1 FROM t2 WHERE b1 = b2);
 a1	a2
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2013-07-16 17:09:54 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2013-08-19 02:59:23 +0000
@@ -1326,7 +1326,7 @@
 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 dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from dual 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)

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2013-07-16 17:09:54 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2013-08-19 02:59:23 +0000
@@ -1322,7 +1322,7 @@
 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 dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from dual 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)

=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2013-07-16 17:09:54 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2013-08-19 02:59:23 +0000
@@ -1325,7 +1325,7 @@
 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 dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from dual 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)

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2013-07-16 17:09:54 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2013-08-19 02:59:23 +0000
@@ -1322,7 +1322,7 @@
 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 dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
+Note	1003	select <in_optimizer>(0,<exists>(select 1 from dual 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)

=== modified file 'mysql-test/r/subselect_sj2_mat.result'
--- a/mysql-test/r/subselect_sj2_mat.result	2013-07-16 11:57:27 +0000
+++ b/mysql-test/r/subselect_sj2_mat.result	2013-08-19 02:59:23 +0000
@@ -1399,7 +1399,7 @@
 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

=== modified file 'mysql-test/r/table_elim.result'
--- a/mysql-test/r/table_elim.result	2012-05-21 18:54:41 +0000
+++ b/mysql-test/r/table_elim.result	2013-08-19 02:59:23 +0000
@@ -17,7 +17,7 @@
 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 @@
 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
@@ -626,6 +626,6 @@
 1	PRIMARY	t2	index	NULL	b	5	NULL	2	100.00	Using where; Using index
 2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select `test`.`t2`.`b` AS `b` from `test`.`t2` where <expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select sum(1) from dual where 1 having (<cache>(`test`.`t2`.`b`) = <ref_null_helper>(sum(1))))))
+Note	1003	select `test`.`t2`.`b` AS `b` from `test`.`t2` where <expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select sum(1) from dual having (<cache>(`test`.`t2`.`b`) = <ref_null_helper>(sum(1))))))
 DROP TABLE t1,t2;
 SET optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2013-06-17 17:33:36 +0000
+++ b/mysql-test/r/view.result	2013-08-19 02:59:23 +0000
@@ -4417,7 +4417,7 @@
 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 dual where ((20 <> 0) or 0)
+Note	1003	select 'r' AS `f4` from dual where (20 <> 0)
 DROP VIEW v1;
 DROP TABLE t1;
 #

=== modified file 'mysql-test/suite/vcol/r/vcol_misc.result'
--- a/mysql-test/suite/vcol/r/vcol_misc.result	2013-07-08 12:49:42 +0000
+++ b/mysql-test/suite/vcol/r/vcol_misc.result	2013-08-19 02:59:23 +0000
@@ -197,6 +197,13 @@
 COUNT(*)
 2
 DROP TABLE t1;
+#
+# MDEV-4823 Server crashes in Item_func_not::fix_fields on
+# creating a table with a virtual column using NOT
+#
+CREATE TABLE t1 ( f1 INT, v4 INT AS ( NOT f1 ) VIRTUAL );
+drop table t1;
+# end of 5.2 tests
 create table t1 (a int, b int);
 insert into t1 values (3, 30), (4, 20), (1, 20);
 create table t2 (c int, d int, v int as (d+1), index idx(c));
@@ -309,3 +316,4 @@
 INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a');
 ERROR HY000: The value specified for computed column 'd' in table 't1' ignored
 drop table t1;
+# end of 5.3 tests

=== modified file 'mysql-test/suite/vcol/t/vcol_misc.test'
--- a/mysql-test/suite/vcol/t/vcol_misc.test	2013-01-22 05:29:19 +0000
+++ b/mysql-test/suite/vcol/t/vcol_misc.test	2013-08-15 21:04:20 +0000
@@ -197,6 +197,15 @@
 
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-4823 Server crashes in Item_func_not::fix_fields on
+--echo # creating a table with a virtual column using NOT
+--echo #
+CREATE TABLE t1 ( f1 INT, v4 INT AS ( NOT f1 ) VIRTUAL );
+drop table t1;
+
+--echo # end of 5.2 tests
+
 #
 #  SELECT that uses a virtual column and executed with BKA
 #
@@ -265,3 +274,5 @@
 --error ER_WARNING_NON_DEFAULT_VALUE_FOR_VIRTUAL_COLUMN
 INSERT INTO `test`.`t1`(`a`,`b`,`c`,`d`) VALUES ( '1','a',NULL,'a');
 drop table t1;
+
+--echo # end of 5.3 tests

=== modified file 'mysql-test/t/range.test'
--- a/mysql-test/t/range.test	2013-01-28 13:13:39 +0000
+++ b/mysql-test/t/range.test	2013-08-19 02:59:23 +0000
@@ -570,12 +570,12 @@
 
 EXPLAIN
 SELECT s.oxid FROM t1 v, t1 s 
-  WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+  WHERE 
         v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
         s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 
 SELECT s.oxid FROM t1 v, t1 s 
-  WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+  WHERE
         v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
         s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 
@@ -1481,6 +1481,40 @@
 
 DROP TABLE t1;
 
+--echo #
+--echo # mdev-4894:  Poor performance with unnecessary 
+--echo # (bug#70021) 'Range checked for each record'
+--echo #
+
+create table t1( key1 int not null, INDEX i1(key1) );
+insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8);
+insert into t1 select key1+8 from t1; 
+insert into t1 select key1+16 from t1; 
+insert into t1 select key1+32 from t1; 
+insert into t1 select key1+64 from t1; 
+insert into t1 select key1+128 from t1; 
+insert into t1 select key1+256 from t1; 
+insert into t1 select key1+512 from t1; 
+
+alter table t1 add key2 int not null, add index i2(key2);
+update t1 set key2=key1;
+analyze table t1;
+
+create table t2 (a int);
+insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8);
+insert into t2 select a+16 from t2;
+insert into t2 select a+32 from t2;
+insert into t2 select a+64 from t2;
+
+explain
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
+explain
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
+
+drop table t1,t2;
+
 --echo End of 5.1 tests
 
 --echo #

=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test	2013-05-08 09:36:17 +0000
+++ b/mysql-test/t/select.test	2013-08-19 02:59:23 +0000
@@ -4500,7 +4500,7 @@
 DROP TABLE t1,t2;
 
 --echo #
---echo # Bug mdev-4413: another manifestations of bug mdev-2474
+--echo # Bug mdev-4413: another manifestations of bug mdev-4274
 --echo #                (valgrind complains)
 --echo #
 
@@ -4516,5 +4516,52 @@
 
 DROP TABLE t1, t2;
 
+--echo #
+--echo # Bug mdev-4355: equalities from the result of simplification of OR
+--echo #                are not propagated to lower AND levels
+--echo #
+
+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);
+SELECT * FROM t1 WHERE (1 != 1 OR a = 5) AND (b != 1 OR a = 1);
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5);
+SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (1 != 1 OR a = 5);
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1);
+SELECT * FROM t1 WHERE (b != 1 OR a = 1) AND (a = 5 OR 1 != 1);
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1);
+SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1);
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1);
+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
 

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2013-08-08 07:48:49 +0000
+++ b/sql/item_cmpfunc.cc	2013-08-19 02:59:23 +0000
@@ -5738,10 +5738,12 @@
   @brief
   Merge members of another Item_equal object into this one
   
-  @param item    multiple equality whose members are to be merged
+  @param item         multiple equality whose members are to be merged
+  @param save_merged  keep the list of equalities in 'item' intact
+                      (e.g. for other merges)
 
   @details
-  If the Item_equal 'item' happened to have some elements of the list
+  If the Item_equal 'item' happens to have some elements of the list
   of equal items belonging to 'this' object then the function merges
   the equal items from 'item' into this list.
   If both lists contains constants and they are different then
@@ -5756,24 +5758,45 @@
   The method 'merge' just joins the list of equal items belonging to 'item'
   to the list of equal items belonging to this object assuming that the lists
   are disjoint. It would be more correct to call the method 'join'.
-  The method 'merge_with_check' really merges two lists of equal items if they
-  have common members.  
+  The method 'merge_into_with_check' really merges two lists of equal items if
+  they have common members.  
 */
   
-bool Item_equal::merge_with_check(Item_equal *item)
+bool Item_equal::merge_with_check(Item_equal *item, bool save_merged)
 {
   bool intersected= FALSE;
-  Item_equal_fields_iterator_slow fi(*this);
+  Item_equal_fields_iterator_slow fi(*item);
+  
   while (fi++)
   {
-    if (item->contains(fi.get_curr_field()))
+    if (contains(fi.get_curr_field()))
     {
-      fi.remove();
       intersected= TRUE;
+      if (!save_merged)
+        fi.remove();
     }
   }
   if (intersected)
-    item->merge(this);
+  {
+    if (!save_merged)
+      merge(item);
+    else
+    {
+      Item *c= item->get_const();
+      if (c)
+        add_const(c);
+      if (!cond_false)
+      {
+        Item *item;
+        fi.rewind();
+        while ((item= fi++))
+	{
+          if (!contains(fi.get_curr_field()))
+            add(item);
+        }
+      }
+    }         
+  }
   return intersected;
 }
 
@@ -5782,17 +5805,25 @@
   @brief
   Merge this object into a list of Item_equal objects 
   
-  @param list   the list of Item_equal objects to merge into
+  @param list                 the list of Item_equal objects to merge into
+  @param save_merged          keep the list of equalities in 'this' intact
+                              (e.g. for other merges)
+  @param only_intersected     do not merge if there are no common members
+                              in any of Item_equal objects from the list
+                              and this Item_equal
 
   @details
   If the list of equal items from 'this' object contains common members
   with the lists of equal items belonging to Item_equal objects from 'list'
   then all involved Item_equal objects e1,...,ek are merged into one 
-  Item equal that replaces e1,...,ek in the 'list'. Otherwise this
+  Item equal that replaces e1,...,ek in the 'list'. Otherwise, in the case
+  when the value of the parameter only_if_intersected is false, this
   Item_equal is joined to the 'list'.
 */
 
-void Item_equal::merge_into_list(List<Item_equal> *list)
+void Item_equal::merge_into_list(List<Item_equal> *list,
+                                 bool save_merged,
+                                 bool only_intersected)
 {
   Item_equal *item;
   List_iterator<Item_equal> it(*list);
@@ -5801,16 +5832,16 @@
   {
     if (!merge_into)
     {
-      if (merge_with_check(item))
+      if (item->merge_with_check(this, save_merged))
         merge_into= item;
     }
     else
     {
-      if (item->merge_with_check(merge_into))
+      if (merge_into->merge_with_check(item, false))
         it.remove();
     }
   }
-  if (!merge_into)
+  if (!only_intersected && !merge_into)
     list->push_back(this);
 }
 

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2013-07-16 17:09:54 +0000
+++ b/sql/item_cmpfunc.h	2013-08-19 02:59:23 +0000
@@ -1754,8 +1754,9 @@
   /** Get number of field items / references to field items in this object */   
   uint n_field_items() { return equal_items.elements-test(with_const); }
   void merge(Item_equal *item);
-  bool merge_with_check(Item_equal *equal_item);
-  void merge_into_list(List<Item_equal> *list);
+  bool merge_with_check(Item_equal *equal_item, bool save_merged);
+  void merge_into_list(List<Item_equal> *list, bool save_merged,
+                      bool only_intersected);
   void update_const();
   enum Functype functype() const { return MULT_EQUAL_FUNC; }
   longlong val_int(); 

=== modified file 'sql/spatial.cc'
--- a/sql/spatial.cc	2013-07-16 17:09:54 +0000
+++ b/sql/spatial.cc	2013-08-19 02:59:23 +0000
@@ -447,19 +447,18 @@
 const char *Geometry::get_mbr_for_points(MBR *mbr, const char *data,
 					 uint offset) const
 {
-  uint32 n_points;
+  uint32 points;
   /* read number of points */
   if (no_data(data, 4))
     return 0;
-  n_points= uint4korr(data);
+  points= uint4korr(data);
   data+= 4;
 
-  if (n_points > max_n_points ||
-      no_data(data, (POINT_DATA_SIZE + offset) * n_points))
+  if (not_enough_points(data, points, offset))
     return 0;
 
   /* Calculate MBR for points */
-  while (n_points--)
+  while (points--)
   {
     data+= offset;
     mbr->add_xy(data, data + SIZEOF_STORED_DOUBLE);
@@ -563,12 +562,16 @@
 
 uint32 Gis_line_string::get_data_size() const 
 {
-  uint32 n_points, size;
-  if (no_data(m_data, 4) ||
-      (n_points= uint4korr(m_data)) > max_n_points ||
-      no_data(m_data, (size= 4 + n_points * POINT_DATA_SIZE)))
-    return GET_SIZE_ERROR;
-  return size;
+  uint32 n_points;
+  if (no_data(m_data, 4))
+    return GET_SIZE_ERROR;
+
+  n_points= uint4korr(m_data);
+
+  if (not_enough_points(m_data + 4, n_points))
+    return GET_SIZE_ERROR;
+
+  return 4 + n_points * POINT_DATA_SIZE;
 }
 
 
@@ -607,9 +610,8 @@
   const char *wkb_end;
   Gis_point p;
 
-  if (len < 4 ||
-      (n_points= wkb_get_uint(wkb, bo)) < 1 ||
-      n_points > max_n_points)
+  if (len < 4 || (n_points= wkb_get_uint(wkb, bo)) < 1 ||
+      ((len - 4) / POINT_DATA_SIZE) < n_points)
     return 0;
   proper_length= 4 + n_points * POINT_DATA_SIZE;
 
@@ -638,8 +640,8 @@
   n_points= uint4korr(data);
   data += 4;
 
-  if (n_points < 1 || n_points > max_n_points ||
-      no_data(data, POINT_DATA_SIZE * n_points) ||
+  if (n_points < 1 ||
+      not_enough_points(data, n_points) ||
       txt->reserve(((MAX_DIGITS_IN_DOUBLE + 1)*2 + 1) * n_points))
     return 1;
 
@@ -676,8 +678,7 @@
     return 1;
   n_points= uint4korr(data);
   data+= 4;
-  if (n_points < 1 || n_points > max_n_points ||
-      no_data(data, POINT_DATA_SIZE * n_points))
+  if (n_points < 1 || not_enough_points(data, n_points))
     return 1;
 
   get_point(&prev_x, &prev_y, data);
@@ -725,8 +726,7 @@
     return 0;
   }
   data+= 4;
-  if (n_points == 0 || n_points > max_n_points ||
-      no_data(data, POINT_DATA_SIZE * n_points))
+  if (n_points == 0 || not_enough_points(data, n_points))
     return 1;
 
   /* Get first point */
@@ -761,8 +761,7 @@
   if (no_data(m_data, 4))
     return 1;
   n_points= uint4korr(m_data);
-  if (n_points == 0 || n_points > max_n_points ||
-      no_data(m_data, POINT_DATA_SIZE * n_points))
+  if (n_points == 0 || not_enough_points(m_data+4, n_points))
     return 1;
   return create_point(result, m_data + 4 + (n_points - 1) * POINT_DATA_SIZE);
 }
@@ -775,9 +774,7 @@
     return 1;
   num--;
   n_points= uint4korr(m_data);
-  if (num >= n_points ||
-      num > max_n_points || // means (num > n_points || num < 1)
-      no_data(m_data, num * POINT_DATA_SIZE))
+  if (num >= n_points || not_enough_points(m_data+4, n_points))
     return 1;
 
   return create_point(result, m_data + 4 + num*POINT_DATA_SIZE);
@@ -796,8 +793,7 @@
     return 1;
   n_points= uint4korr(data);
   data+= 4;
-  if (n_points < 1 ||  n_points > max_n_points ||
-      no_data(data, POINT_DATA_SIZE * n_points))
+  if (n_points < 1 || no_data(data, POINT_DATA_SIZE * n_points))
     return 1;
 
   trn->start_line();
@@ -840,7 +836,7 @@
   while (n_linear_rings--)
   {
     if (no_data(data, 4) ||
-        (n_points= uint4korr(data)) > max_n_points)
+        not_enough_points(data+4, n_points= uint4korr(data)))
       return GET_SIZE_ERROR;
     data+= 4 + n_points*POINT_DATA_SIZE;
   }
@@ -986,7 +982,7 @@
       return 1;
     n_points= uint4korr(data);
     data+= 4;
-    if (n_points > max_n_points || no_data(data, POINT_DATA_SIZE * n_points) ||
+    if (not_enough_points(data, n_points) ||
 	txt->reserve(2 + ((MAX_DIGITS_IN_DOUBLE + 1) * 2 + 1) * n_points))
       return 1;
     txt->qs_append('(');
@@ -1040,8 +1036,8 @@
     if (no_data(data, 4))
       return 1;
     n_points= uint4korr(data);
-    if (n_points == 0 || n_points > max_n_points ||
-        no_data(data, POINT_DATA_SIZE * n_points))
+    if (n_points == 0 ||
+        not_enough_points(data, n_points))
       return 1;
     get_point(&prev_x, &prev_y, data+4);
     data+= (4+POINT_DATA_SIZE);
@@ -1077,8 +1073,7 @@
   n_points= uint4korr(data);
   data+= 4;
   length= n_points * POINT_DATA_SIZE;
-  if (n_points > max_n_points ||
-      no_data(data, length) || result->reserve(1 + 4 + 4 + length))
+  if (not_enough_points(data, n_points) || result->reserve(1+4+4+ length))
     return 1;
 
   result->q_append((char) wkb_ndr);
@@ -1124,8 +1119,7 @@
   n_points= uint4korr(data);
   points_size= n_points * POINT_DATA_SIZE;
   data+= 4;
-  if (n_points > max_n_points ||
-      no_data(data, points_size) || result->reserve(1 + 4 + 4 + points_size))
+  if (not_enough_points(data, n_points) || result->reserve(1+4+4+ points_size))
     return 1;
 
   result->q_append((char) wkb_ndr);
@@ -1162,8 +1156,7 @@
       return 1;
     org_n_points= n_points= uint4korr(data);
     data+= 4;
-    if (n_points == 0 || n_points > max_n_points ||
-        no_data(data, POINT_DATA_SIZE * n_points))
+    if (n_points == 0 || not_enough_points(data, n_points))
       return 1;
     get_point(&prev_x, &prev_y, data);
     data+= POINT_DATA_SIZE;
@@ -1237,8 +1230,7 @@
       return 1;
     n_points= uint4korr(data);
     data+= 4;
-    if (!n_points || n_points > max_n_points ||
-        no_data(data, POINT_DATA_SIZE * n_points))
+    if (!n_points || no_data(data, POINT_DATA_SIZE * n_points))
       return 1;
 
     trn->start_ring();
@@ -1292,13 +1284,12 @@
 uint32 Gis_multi_point::get_data_size() const 
 {
   uint32 n_points;
-  uint32 size;
 
   if (no_data(m_data, 4) ||
-      (n_points= uint4korr(m_data)) > max_n_points ||
-      no_data(m_data, (size= 4 + n_points*(POINT_DATA_SIZE + WKB_HEADER_SIZE))))
+      not_enough_points(m_data+4, (n_points= uint4korr(m_data)),
+        WKB_HEADER_SIZE))
      return GET_SIZE_ERROR;
-  return size;
+  return  4 + n_points * (POINT_DATA_SIZE + WKB_HEADER_SIZE);
 }
 
 
@@ -1393,7 +1384,7 @@
 
   n_points= uint4korr(m_data);
   if (n_points > max_n_points ||
-      no_data(m_data+4, n_points * (POINT_DATA_SIZE + WKB_HEADER_SIZE)) ||
+      not_enough_points(m_data+4, n_points, WKB_HEADER_SIZE) ||
       txt->reserve(((MAX_DIGITS_IN_DOUBLE + 1) * 2 + 1) * n_points))
     return 1;
   *end= append_points(txt, n_points, m_data+4, WKB_HEADER_SIZE);
@@ -1485,7 +1476,8 @@
   while (n_line_strings--)
   {
     if (no_data(data, WKB_HEADER_SIZE + 4) ||
-        (n_points= uint4korr(data + WKB_HEADER_SIZE)) > max_n_points)
+        not_enough_points(data + WKB_HEADER_SIZE+4,
+                          (n_points= uint4korr(data + WKB_HEADER_SIZE))))
       return GET_SIZE_ERROR;
     data+= (WKB_HEADER_SIZE + 4 + n_points*POINT_DATA_SIZE);
   }
@@ -1614,7 +1606,7 @@
       return 1;
     n_points= uint4korr(data + WKB_HEADER_SIZE);
     data+= WKB_HEADER_SIZE + 4;
-    if (n_points > max_n_points || no_data(data, n_points * POINT_DATA_SIZE) ||
+    if (not_enough_points(data, n_points) ||
 	txt->reserve(2 + ((MAX_DIGITS_IN_DOUBLE + 1) * 2 + 1) * n_points))
       return 1;
     txt->qs_append('(');
@@ -1675,7 +1667,7 @@
       return 1;
     n_points= uint4korr(data + WKB_HEADER_SIZE);
     length= WKB_HEADER_SIZE + 4+ POINT_DATA_SIZE * n_points;
-    if (n_points > max_n_points || no_data(data, length))
+    if (not_enough_points(data+WKB_HEADER_SIZE+4, n_points))
       return 1;
     if (!--num)
       break;
@@ -1806,7 +1798,7 @@
     while (n_linear_rings--)
     {
       if (no_data(data, 4) ||
-          (n_points= uint4korr(data)) > max_n_points)
+          not_enough_points(data+4, (n_points= uint4korr(data))))
 	return GET_SIZE_ERROR;
       data+= 4 + n_points * POINT_DATA_SIZE;
     }
@@ -1940,8 +1932,7 @@
         return 1;
       uint32 n_points= uint4korr(data);
       data+= 4;
-      if (n_points > max_n_points ||
-          no_data(data, POINT_DATA_SIZE * n_points) ||
+      if (not_enough_points(data, n_points) ||
 	  txt->reserve(2 + ((MAX_DIGITS_IN_DOUBLE + 1) * 2 + 1) * n_points,
 		       512))
 	return 1;
@@ -2024,7 +2015,7 @@
       if (no_data(data, 4))
 	return 1;
       n_points= uint4korr(data);
-      if (n_points > max_n_points)
+      if (not_enough_points(data + 4, n_points))
         return 1;
       data+= 4 + POINT_DATA_SIZE * n_points;
     }

=== modified file 'sql/spatial.h'
--- a/sql/spatial.h	2013-07-16 17:09:54 +0000
+++ b/sql/spatial.h	2013-08-19 02:59:23 +0000
@@ -214,11 +214,6 @@
 class Geometry
 {
 public:
-  // Maximum number of points in feature that can fit into String
-  static const uint32 max_n_points=
-    (uint32) (INT_MAX32 - WKB_HEADER_SIZE - 4 /* n_points */) /
-    POINT_DATA_SIZE;
-
   Geometry() {}                               /* Remove gcc warning */
   virtual ~Geometry() {}                        /* Remove gcc warning */
   static void *operator new(size_t size, void *buffer)

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2013-07-18 08:16:18 +0000
+++ b/sql/sql_select.cc	2013-08-19 02:59:23 +0000
@@ -1261,6 +1261,20 @@
     /* Handle the case where we have an OUTER JOIN without a WHERE */
     conds=new Item_int((longlong) 1,1);	// Always true
   }
+
+  if (const_tables && conds)
+  {
+    conds= remove_eq_conds(thd, conds, &cond_value);
+    if (cond_value == Item::COND_FALSE)
+    {
+      zero_result_cause=
+        "Impossible WHERE noticed after reading const tables";
+      select_lex->mark_const_derived(zero_result_cause);
+      conds=new Item_int((longlong) 0,1);
+      goto setup_subq_exit;
+    }
+  }
+
   select= make_select(*table, const_table_map,
                       const_table_map, conds, 1, &error);
   if (error)
@@ -8827,19 +8841,18 @@
 	  else
 	  {
 	    sel->needed_reg=tab->needed_reg;
-	    sel->quick_keys.clear_all();
 	  }
+	  sel->quick_keys= tab->table->quick_keys;
 	  if (!sel->quick_keys.is_subset(tab->checked_keys) ||
               !sel->needed_reg.is_subset(tab->checked_keys))
 	  {
-	    tab->keys=sel->quick_keys;
-            tab->keys.merge(sel->needed_reg);
 	    tab->use_quick= (!sel->needed_reg.is_clear_all() &&
-			     (select->quick_keys.is_clear_all() ||
-			      (select->quick &&
-			       (select->quick->records >= 100L)))) ?
+			     (sel->quick_keys.is_clear_all() ||
+			      (sel->quick &&
+			       (sel->quick->records >= 100L)))) ?
 	      2 : 1;
 	    sel->read_tables= used_tables & ~current_map;
+            sel->quick_keys.clear_all();
 	  }
 	  if (i != join->const_tables && tab->use_quick != 2 &&
               !tab->first_inner)
@@ -13422,22 +13435,175 @@
 
 
 /**
-  Handles the recursive job  remove_eq_conds()
-
-  Remove const and eq items. Return new item, or NULL if no condition
-  cond_value is set to according:
-  COND_OK    query is possible (field = constant)
-  COND_TRUE  always true	( 1 = 1 )
-  COND_FALSE always false	( 1 = 2 )
-
-  SYNOPSIS
-    internal_remove_eq_conds()
-    thd 			THD environment
-    cond                        the condition to handle
-    cond_value                  the resulting value of the condition
-
-  RETURN
-    *COND with the simplified condition
+  @brief
+  Propagate multiple equalities to the sub-expressions of a condition
+
+  @param thd             thread handle
+  @param cond            the condition where equalities are to be propagated
+  @param *new_equalities the multiple equalities to be propagated
+  @param inherited        path to all inherited multiple equality items
+  @param[out] is_simplifiable_cond   'cond' may be simplified after the
+                                      propagation of the equalities
+ 
+  @details
+  The function recursively traverses the tree of the condition 'cond' and
+  for each its AND sub-level of any depth the function merges the multiple
+  equalities from the list 'new_equalities' into the multiple equalities
+  attached to the AND item created for this sub-level.
+  The function also [re]sets references to the equalities formed by the
+  merges of multiple equalities in all field items occurred in 'cond'
+  that are encountered in the equalities.
+  If the result of any merge of multiple equalities is an impossible
+  condition the function returns TRUE in the parameter is_simplifiable_cond.   
+*/
+
+void propagate_new_equalities(THD *thd, Item *cond,
+                              List<Item_equal> *new_equalities,
+                              COND_EQUAL *inherited,
+                              bool *is_simplifiable_cond)
+{
+  if (cond->type() == Item::COND_ITEM)
+  {
+    bool and_level= ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC;
+    if (and_level)
+    {
+      Item_cond_and *cond_and= (Item_cond_and *) cond; 
+      List<Item_equal> *cond_equalities= &cond_and->cond_equal.current_level;
+      inherited= cond_and->cond_equal.upper_levels;
+      if (!cond_equalities->is_empty() && cond_equalities != new_equalities)
+      {
+        Item_equal *equal_item;
+        List_iterator<Item_equal> it(*new_equalities);
+	while ((equal_item= it++))
+	{
+          equal_item->merge_into_list(cond_equalities, true, true);
+        }
+        List_iterator<Item_equal> ei(*cond_equalities);
+        while ((equal_item= ei++))
+	{
+          if (equal_item->const_item() && !equal_item->val_int())
+	  {
+            *is_simplifiable_cond= true;
+            return;
+          }
+        }
+      }
+    }
+
+    Item *item;
+    List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+    while ((item= li++))
+    {
+      propagate_new_equalities(thd, item, new_equalities, inherited,
+                               is_simplifiable_cond);
+    }
+  }
+  else if (cond->type() == Item::FUNC_ITEM && 
+           ((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC)
+  {
+    Item_equal *equal_item;
+    List_iterator<Item_equal> it(*new_equalities);
+    Item_equal *equality= (Item_equal *) cond;
+    while ((equal_item= it++))
+    {
+      equality->merge_with_check(equal_item, true);
+    }
+    if (equality->const_item() && !equality->val_int())
+      *is_simplifiable_cond= true;
+  }
+  else
+  {
+    uchar* is_subst_valid= (uchar *) Item::ANY_SUBST;
+    cond= cond->compile(&Item::subst_argument_checker,
+                        &is_subst_valid, 
+                        &Item::equal_fields_propagator,
+                        (uchar *) inherited);
+    cond->update_used_tables();
+  }          
+} 
+
+
+
+/**
+  @brief
+  Evaluate all constant boolean sub-expressions in a condition
+ 
+  @param thd        thread handle
+  @param cond       condition where where to evaluate constant sub-expressions
+  @param[out] cond_value : the returned value of the condition 
+                           (TRUE/FALSE/UNKNOWN:
+                           Item::COND_TRUE/Item::COND_FALSE/Item::COND_OK)
+  @return
+   the item that is the result of the substitution of all inexpensive constant
+   boolean sub-expressions into cond, or,
+   NULL if the condition is constant and is evaluated to FALSE.
+
+  @details
+  This function looks for all inexpensive constant boolean sub-expressions in
+  the given condition 'cond' and substitutes them for their values.
+  For example, the condition 2 > (5 + 1) or a < (10 / 2)
+  will be transformed to the condition a < (10 / 2).
+  Note that a constant sub-expression is evaluated only if it is constant and
+  inexpensive. A sub-expression with an uncorrelated subquery may be evaluated
+  only if the subquery is considered as inexpensive.
+  The function does not evaluate a constant sub-expression if it is not on one
+  of AND/OR levels of the condition 'cond'. For example, the subquery in the
+  condition a > (select max(b) from t1 where b > 5) will never be evaluated
+  by this function. 
+  If a constant boolean sub-expression is evaluated to TRUE then:
+    - when the sub-expression is a conjunct of an AND formula it is simply
+      removed from this formula
+    - when the sub-expression is a disjunct of an OR formula the whole OR
+      formula is converted to TRUE 
+  If a constant boolean sub-expression is evaluated to FALSE then:
+    - when the sub-expression is a disjunct of an OR formula it is simply
+      removed from this formula
+    - when the sub-expression is a conjuct of an AND formula the whole AND
+      formula is converted to FALSE
+  When a disjunct/conjunct is removed from an OR/AND formula it might happen
+  that there is only one conjunct/disjunct remaining. In this case this
+  remaining disjunct/conjunct must be merged into underlying AND/OR formula,
+  because AND/OR levels must alternate in the same way as they alternate
+  after fix_fields() is called for the original condition.
+  The specifics of merging a formula f into an AND formula A appears
+  when A contains multiple equalities and f contains multiple equalities.
+  In this case the multiple equalities from f and A have to be merged.
+  After this the resulting multiple equalities have to be propagated into
+  the all AND/OR levels of the formula A (see propagate_new_equalities()).
+  The propagation of multiple equalities might result in forming multiple
+  equalities that are always FALSE. This, in its turn, might trigger further
+  simplification of the condition.
+
+  @note
+  EXAMPLE 1:
+  SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5 OR 1 != 1);
+  First 1 != 1 will be removed from the second conjunct:
+  => SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5 AND a = 5);
+  Then (b = 5 AND a = 5) will be merged into the top level condition:
+  => SELECT * FROM t1 WHERE (b = 1 OR a = 1) AND (b = 5) AND (a = 5);
+  Then (b = 5), (a = 5)  will be propagated into the disjuncs of 
+  (b = 1 OR a = 1):
+  => SELECT * FROM t1 WHERE ((b = 1) AND (b = 5) AND (a = 5) OR
+                             (a = 1) AND (b = 5) AND (a = 5)) AND
+                            (b = 5) AND (a = 5)
+  => SELECT * FROM t1 WHERE ((FALSE AND (a = 5)) OR
+                             (FALSE AND (b = 5))) AND
+                             (b = 5) AND (a = 5)
+  After this an additional call of remove_eq_conds() converts it
+  to FALSE
+
+  EXAMPLE 2:  
+  SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5 OR 1 != 1);
+  => SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5 AND a = 5);
+  => SELECT * FROM t1 WHERE (b = 1 OR a = 5) AND (b = 5) AND (a = 5);
+  => SELECT * FROM t1 WHERE ((b = 1) AND (b = 5) AND (a = 5) OR
+                             (a = 5) AND (b = 5) AND (a = 5)) AND
+                            (b = 5) AND (a = 5)
+  => SELECT * FROM t1 WHERE ((FALSE AND (a = 5)) OR
+                             ((b = 5) AND (a = 5))) AND
+                             (b = 5) AND (a = 5)
+  After this an additional call of  remove_eq_conds() converts it to
+ =>  SELECT * FROM t1 WHERE (b = 5) AND (a = 5)                            
 */
 
 static COND *
@@ -13445,9 +13611,11 @@
 {
   if (cond->type() == Item::COND_ITEM)
   {
+    List<Item_equal> new_equalities;
     bool and_level= ((Item_cond*) cond)->functype()
       == Item_func::COND_AND_FUNC;
-    List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
+    List<Item> *cond_arg_list= ((Item_cond*) cond)->argument_list();
+    List_iterator<Item> li(*cond_arg_list);
     Item::cond_result tmp_cond_value;
     bool should_fix_fields=0;
 
@@ -13457,92 +13625,72 @@
     {
       Item *new_item=internal_remove_eq_conds(thd, item, &tmp_cond_value);
       if (!new_item)
+      {
+        /* This can happen only when item is converted to TRUE or FALSE */
 	li.remove();
+      }
       else if (item != new_item)
       {
-        if (and_level)
+        /* 
+          This can happen when:
+          - item was an OR formula converted to one disjunct
+          - item was an AND formula converted to one conjunct
+          In these cases the disjunct/conjunct must be merged into the
+          argument list of cond.
+	*/
+        if (new_item->type() == Item::COND_ITEM)
+        {
+          DBUG_ASSERT(((Item_cond *) cond)->functype() == 
+                      ((Item_cond *) new_item)->functype());          
+	  List<Item> *new_item_arg_list=
+            ((Item_cond *) new_item)->argument_list();
+          if (and_level)
+	  {
+            /*
+              If new_item is an AND formula then multiple equalities
+              of new_item_arg_list must merged into multiple equalities
+              of cond_arg_list. 
+	    */
+            List<Item_equal> *new_item_equalities=
+              &((Item_cond_and *) new_item)->cond_equal.current_level;
+            if (!new_item_equalities->is_empty())
+	    {
+              /*
+                Cut the multiple equalities from the new_item_arg_list and
+                append them on the list new_equalities. Later the equalities
+                from this list will be merged into the multiple equalities
+                of cond_arg_list all together.
+	      */
+              new_item_arg_list->disjoin((List<Item> *) new_item_equalities);
+              new_equalities.concat(new_item_equalities);
+            }
+          }
+          if (new_item_arg_list->is_empty())
+	    li.remove();
+	  else
+	  {
+            uint cnt= new_item_arg_list->elements;
+            li.replace(*new_item_arg_list);
+            /* Make iterator li ignore new items */
+            for (cnt--; cnt; cnt--)
+              li++;
+            should_fix_fields= 1;
+          }
+        }
+        else if (and_level && 
+                 new_item->type() == Item::FUNC_ITEM && 
+                 ((Item_cond*) new_item)->functype() ==
+                  Item_func::MULT_EQUAL_FUNC)
 	{
-          /*
-            Take a special care of multiple equality predicates
-            that may be part of 'cond' and 'new_item'.
-            Those multiple equalities that have common members
-            must be merged.
-	  */  
-          Item_cond_and *cond_and= (Item_cond_and *) cond;
-          List<Item_equal> *cond_equal_items=
-            &cond_and->cond_equal.current_level;
-          List<Item> *cond_and_list= cond_and->argument_list();
-
-          if (new_item->type() == Item::COND_ITEM && 
-              ((Item_cond*) new_item)->functype() == Item_func::COND_AND_FUNC)
-          {
-            Item_cond_and *new_item_and= (Item_cond_and *) new_item;
-            List<Item_equal> *new_item_equal_items=
-              &new_item_and->cond_equal.current_level;
-            List<Item> *new_item_and_list= new_item_and->argument_list();
-            cond_and_list->disjoin((List<Item>*) cond_equal_items);
-            new_item_and_list->disjoin((List<Item>*) new_item_equal_items);
-            Item_equal *equal_item;
-            List_iterator<Item_equal> it(*new_item_equal_items);
-	    while ((equal_item= it++))
-	    {
-              equal_item->merge_into_list(cond_equal_items);
-            }
-            if (new_item_and_list->is_empty())
-              li.remove();
-            else
-	    {
-              Item *list_item;
-              Item *new_list_item; 
-              uint cnt= new_item_and_list->elements;     
-              List_iterator<Item> it(*new_item_and_list);
-              while ((list_item= it++))
-	      {
-                uchar* is_subst_valid= (uchar *) Item::ANY_SUBST;
-                new_list_item= 
-                  list_item->compile(&Item::subst_argument_checker,
-                                         &is_subst_valid, 
-                                         &Item::equal_fields_propagator,
-                                         (uchar *) &cond_and->cond_equal);
-                if (new_list_item != list_item)
-                  it.replace(new_list_item);
-                new_list_item->update_used_tables();
-              }              
-              li.replace(*new_item_and_list);
-              for (cnt--; cnt; cnt--)
-                item= li++;  
-            }
-            cond_and_list->concat((List<Item>*) cond_equal_items); 
-          }
-          else if (new_item->type() == Item::FUNC_ITEM && 
-                   ((Item_cond*) new_item)->functype() ==
-                   Item_func::MULT_EQUAL_FUNC)
-	  {
-            cond_and_list->disjoin((List<Item>*) cond_equal_items);
-            ((Item_equal *) new_item)->merge_into_list(cond_equal_items);
-            li.remove();
-            cond_and_list->concat((List<Item>*) cond_equal_items); 
-          }
-          else
-            li.replace(new_item);
+          li.remove();
+          new_equalities.push_back((Item_equal *) new_item);
         }
         else
-	{ 
-          if (new_item->type() == Item::COND_ITEM &&
-              ((Item_cond*) new_item)->functype() == 
-              ((Item_cond*) cond)->functype())
-	  {
-            List<Item> *arg_list= ((Item_cond*) new_item)->argument_list();
-            uint cnt= arg_list->elements;
-            li.replace(*arg_list);
-            for ( cnt--; cnt; cnt--)
-              item= li++;
-          }
-	  else
-            li.replace(new_item);
+	{
+          li.replace(new_item);
+          should_fix_fields= 1;
         } 
-	should_fix_fields=1;
-      }
+      }   
       if (*cond_value == Item::COND_UNDEF)
 	*cond_value=tmp_cond_value;
       switch (tmp_cond_value) {
@@ -13568,6 +13716,53 @@
 	break; /* purecov: deadcode */
       }
     }
+    if (!new_equalities.is_empty())
+    {
+      DBUG_ASSERT(and_level);
+      /* 
+        Merge multiple equalities that were cut from the results of 
+        simplification of OR formulas converted into AND formulas.
+        These multiple equalities are to be merged into the
+        multiple equalities of  cond_arg_list.
+      */
+      COND_EQUAL *cond_equal= &((Item_cond_and *) cond)->cond_equal;
+      List<Item_equal> *cond_equalities= &cond_equal->current_level;
+      cond_arg_list->disjoin((List<Item> *) cond_equalities);
+      Item_equal *equality;
+      List_iterator_fast<Item_equal> it(new_equalities);
+      while ((equality= it++))
+      {
+        equality->merge_into_list(cond_equalities, false, false);
+        List_iterator_fast<Item_equal> ei(*cond_equalities);
+        while ((equality= ei++))
+	{
+          if (equality->const_item() && !equality->val_int())
+	  {
+            *cond_value= Item::COND_FALSE;
+            return (COND*) 0;
+          }
+        }
+      }
+      cond_arg_list->concat((List<Item> *) cond_equalities);
+      /* 
+        Propagate the newly formed multiple equalities to
+        the all AND/OR levels of cond 
+      */
+      bool is_simplifiable_cond= true;
+      propagate_new_equalities(thd, cond, cond_equalities,
+                               cond_equal->upper_levels,
+                               &is_simplifiable_cond);
+      /*
+        If the above propagation of multiple equalities brings us
+        to multiple equalities that are always FALSE then try to
+        simplify the condition with remove_eq_cond() again.
+      */ 
+      if (is_simplifiable_cond)
+      {
+        if (!(cond= remove_eq_conds(thd, cond, cond_value)))
+          return cond;
+      }          
+    }
     if (should_fix_fields)
       cond->update_used_tables();
 
@@ -13732,7 +13927,7 @@
 }
 
 
-/* 
+/**
   Check if equality can be used in removing components of GROUP BY/DISTINCT
   
   @param    l          the left comparison argument (a field if any)

=== modified file 'sql/table.cc'
--- a/sql/table.cc	2013-07-16 17:09:54 +0000
+++ b/sql/table.cc	2013-08-19 02:59:23 +0000
@@ -2070,7 +2070,7 @@
   Item* func_expr= vcol_info->expr_item;
   bool result= TRUE;
   TABLE_LIST tables;
-  int error;
+  int error= 0;
   const char *save_where;
   Field **ptr, *field;
   enum_mark_columns save_mark_used_columns= thd->mark_used_columns;
@@ -2083,7 +2083,11 @@
   thd->where= "virtual column function";
 
   /* Fix fields referenced to by the virtual column function */
-  error= func_expr->fix_fields(thd, (Item**)0);
+  if (!func_expr->fixed)
+    error= func_expr->fix_fields(thd, &vcol_info->expr_item);
+  /* fix_fields could change the expression */
+  func_expr= vcol_info->expr_item;
+  /* Number of columns will be checked later */
 
   if (unlikely(error))
   {



More information about the commits mailing list