Merge

18 files changed, 485 insertions, 47 deletions

diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
index 9ac85236b48..37776a7bd87 100644
--- a/mysql-test/r/join_cache.result
+++ b/mysql-test/r/join_cache.result
@@ -870,7 +870,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1180,7 +1180,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (incremental, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1490,7 +1490,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (flat, BKA join)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (flat, BKA join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1797,7 +1797,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (incremental, BKA join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2104,7 +2104,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (flat, BKAH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2411,7 +2411,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (incremental, BKAH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2722,7 +2722,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -2936,7 +2936,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BNLH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (incremental, BNLH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -3150,7 +3150,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (flat, BKA join)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (flat, BKA join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -3364,7 +3364,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKA join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (incremental, BKA join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -3578,7 +3578,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (flat, BKAH join)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (flat, BKAH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -3792,7 +3792,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (flat, BKAH join)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (incremental, BKAH join)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index 373472f73ce..9d60059ac28 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -1733,7 +1733,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.package_id	1	
 1	SIMPLE	t4	eq_ref	PRIMARY,id	PRIMARY	2	test.t1.carrier	1	Using where
 1	SIMPLE	t5	ref	carrier_id	carrier_id	5	test.t4.id	22	Using index
-1	SIMPLE	t3	ref	package_id	package_id	5	test.t1.id	1	Using where; Using index
+1	SIMPLE	t3	ref	package_id	package_id	5	test.t2.package_id	1	Using index
 SELECT COUNT(*) 
 FROM ((t2 JOIN t1 ON t2.package_id = t1.id) 
 JOIN t3 ON t3.package_id = t1.id)
diff --git a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result
index ea637486c40..e7199bd5cf0 100644
--- a/mysql-test/r/join_nested_jcl6.result
+++ b/mysql-test/r/join_nested_jcl6.result
@@ -1740,7 +1740,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.package_id	1	Using join buffer (flat, BKA join)
 1	SIMPLE	t4	eq_ref	PRIMARY,id	PRIMARY	2	test.t1.carrier	1	Using where
 1	SIMPLE	t5	ref	carrier_id	carrier_id	5	test.t4.id	22	Using index
-1	SIMPLE	t3	ref	package_id	package_id	5	test.t1.id	1	Using where; Using index
+1	SIMPLE	t3	ref	package_id	package_id	5	test.t2.package_id	1	Using index
 SELECT COUNT(*) 
 FROM ((t2 JOIN t1 ON t2.package_id = t1.id) 
 JOIN t3 ON t3.package_id = t1.id)
diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result
index e1eeb4e1a62..d0f0d12c28e 100644
--- a/mysql-test/r/myisam_mrr.result
+++ b/mysql-test/r/myisam_mrr.result
@@ -504,7 +504,7 @@ table3.col_varchar_key = table2.col_varchar_nokey AND
 table3.pk<>0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	table2	ALL	col_varchar_key	NULL	NULL	NULL	20	Using where
-1	SIMPLE	table3	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.table2.col_varchar_key	3	Using index condition(BKA); Using where; Using join buffer (flat, BKA join)
+1	SIMPLE	table3	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.table2.col_varchar_key	3	Using where; Using join buffer (flat, BKA join)
 set join_cache_level= @save_join_cache_level;
 set join_buffer_size= @save_join_buffer_size;
 drop table t1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 48fcc646d28..23e4fa75ffb 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -4383,7 +4383,7 @@ EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index condition; Using where; Using MRR
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` > 1)) limit 2
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2
 DROP TABLE t1;
 #
 # Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when 
@@ -4875,4 +4875,106 @@ f1	f1	f1	f1	f2	f1	f1
 18	9	NULL	NULL	NULL	5	7
 SET SESSION join_buffer_size = DEFAULT;
 DROP TABLE t1,t2,t3,t4,t5,t6;
+#
+# Bug #698882: best equality substitution not applied to ref 
+#
+CREATE TABLE t1 (a1 int NOT NULL, b1 char(10), INDEX idx (a1));
+CREATE TABLE t2 (a2 int NOT NULL, b2 char(10), INDEX idx (a2));
+CREATE TABLE t3 (a3 int NOT NULL, b3 char(10), INDEX idx (a3));
+INSERT INTO t1 VALUES (2,'xx'), (1,'xxx'),  (11,'xxxxxxx');
+INSERT INTO t2 VALUES
+(7,'yyyy'), (2,'y'), (3,'yyy'), (1,'yy'), (1,'yyyyy'),
+(3,'yy'),  (1,'y'), (4,'yyy'), (7,'y'),  (4,'yyyyy'), (7,'yyy'),
+(7,'yyyy'), (2,'yy'),  (3,'yyy'), (1,'yyyyyyyy'), (1,'yyyyy'),
+(3,'yy'), (1,'yyy'), (4,'yyy'), (7,'y'), (4,'yyyyy'), (7,'yyy');
+INSERT INTO t3 VALUES
+(9,'zzzzzzz'), (2,'zzzzz'), (1,'z'), (9,'zz'), (1,'zz'), (5,'zzzzzzz'),
+(4,'zz'), (3,'z'), (5,'zzzzzz'), (3,'zz'), (4,'zzzz'), (3,'z'),
+(9,'zzzzzzzz'), (2,'zz'), (1,'zz'), (9,'zzz'), (1,'zzz'), (5,'zzzzzzzz'),
+(4,'zzz'), (3,'zz'), (5,'zzzzzzz'), (3,'zzz'), (4,'zzzzz'), (3,'zz'),
+(9,'zzzzzz'), (2,'zzzz'), (1,'zzz'), (9,'z'), (1,'z'), (5,'zzzzzz'),
+(4,'z'), (3,'zzz'), (5,'zzzzz'), (3,'z'), (4,'zzz'), (3,'zzzz'),
+(9,'zzzzz'), (2,'zzz'), (1,'zzzz'), (9,'zzz'), (1,'zzzz'), (5,'zzzzz'),
+(4,'zzz'), (3,'zzzz'), (5,'zzzz'), (3,'zzz'), (4,'zz'), (3,'zzzzz');
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+EXPLAIN SELECT * from t1,t2,t3 WHERE t3.a3=t1.a1 AND t2.a2=t1.a1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	
+1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	
+EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t1.a1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	
+1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	
+EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t2.a2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	
+1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	
+SELECT * from t1,t2,t3
+WHERE t3.a3=t1.a1 AND t2.a2=t1.a1 AND
+LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7;
+a1	b1	a2	b2	a3	b3
+2	xx	2	y	2	zz
+2	xx	2	y	2	zzzz
+2	xx	2	y	2	zzz
+2	xx	2	yy	2	zz
+2	xx	2	yy	2	zzz
+1	xxx	1	yy	1	z
+1	xxx	1	yy	1	zz
+1	xxx	1	yy	1	zz
+1	xxx	1	yy	1	z
+1	xxx	1	y	1	z
+1	xxx	1	y	1	zz
+1	xxx	1	y	1	zz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	z
+1	xxx	1	yyy	1	z
+1	xxx	1	yyy	1	z
+SELECT * FROM t1,t2,t3
+WHERE t2.a2=t1.a1 AND t3.a3=t1.a1 AND 
+LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7;
+a1	b1	a2	b2	a3	b3
+2	xx	2	y	2	zz
+2	xx	2	y	2	zzzz
+2	xx	2	y	2	zzz
+2	xx	2	yy	2	zz
+2	xx	2	yy	2	zzz
+1	xxx	1	yy	1	z
+1	xxx	1	yy	1	zz
+1	xxx	1	yy	1	zz
+1	xxx	1	yy	1	z
+1	xxx	1	y	1	z
+1	xxx	1	y	1	zz
+1	xxx	1	y	1	zz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	z
+1	xxx	1	yyy	1	z
+1	xxx	1	yyy	1	z
+SELECT * FROM t1,t2,t3
+WHERE t2.a2=t1.a1 AND t3.a3=t2.a2 AND
+LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7;
+a1	b1	a2	b2	a3	b3
+2	xx	2	y	2	zz
+2	xx	2	y	2	zzzz
+2	xx	2	y	2	zzz
+2	xx	2	yy	2	zz
+2	xx	2	yy	2	zzz
+1	xxx	1	yy	1	z
+1	xxx	1	yy	1	zz
+1	xxx	1	yy	1	zz
+1	xxx	1	yy	1	z
+1	xxx	1	y	1	z
+1	xxx	1	y	1	zz
+1	xxx	1	y	1	zz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	z
+1	xxx	1	yyy	1	z
+1	xxx	1	yyy	1	z
+SET SESSION optimizer_switch=DEFAULT;
+DROP TABLE t1,t2,t3;
 End of 5.1 tests
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 4b32b27fe7e..5a52b083b8e 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -4390,7 +4390,7 @@ EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index condition; Using where; Using MRR
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` > 1)) limit 2
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2
 DROP TABLE t1;
 #
 # Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when 
@@ -4882,6 +4882,108 @@ f1	f1	f1	f1	f2	f1	f1
 18	9	NULL	NULL	NULL	5	7
 SET SESSION join_buffer_size = DEFAULT;
 DROP TABLE t1,t2,t3,t4,t5,t6;
+#
+# Bug #698882: best equality substitution not applied to ref 
+#
+CREATE TABLE t1 (a1 int NOT NULL, b1 char(10), INDEX idx (a1));
+CREATE TABLE t2 (a2 int NOT NULL, b2 char(10), INDEX idx (a2));
+CREATE TABLE t3 (a3 int NOT NULL, b3 char(10), INDEX idx (a3));
+INSERT INTO t1 VALUES (2,'xx'), (1,'xxx'),  (11,'xxxxxxx');
+INSERT INTO t2 VALUES
+(7,'yyyy'), (2,'y'), (3,'yyy'), (1,'yy'), (1,'yyyyy'),
+(3,'yy'),  (1,'y'), (4,'yyy'), (7,'y'),  (4,'yyyyy'), (7,'yyy'),
+(7,'yyyy'), (2,'yy'),  (3,'yyy'), (1,'yyyyyyyy'), (1,'yyyyy'),
+(3,'yy'), (1,'yyy'), (4,'yyy'), (7,'y'), (4,'yyyyy'), (7,'yyy');
+INSERT INTO t3 VALUES
+(9,'zzzzzzz'), (2,'zzzzz'), (1,'z'), (9,'zz'), (1,'zz'), (5,'zzzzzzz'),
+(4,'zz'), (3,'z'), (5,'zzzzzz'), (3,'zz'), (4,'zzzz'), (3,'z'),
+(9,'zzzzzzzz'), (2,'zz'), (1,'zz'), (9,'zzz'), (1,'zzz'), (5,'zzzzzzzz'),
+(4,'zzz'), (3,'zz'), (5,'zzzzzzz'), (3,'zzz'), (4,'zzzzz'), (3,'zz'),
+(9,'zzzzzz'), (2,'zzzz'), (1,'zzz'), (9,'z'), (1,'z'), (5,'zzzzzz'),
+(4,'z'), (3,'zzz'), (5,'zzzzz'), (3,'z'), (4,'zzz'), (3,'zzzz'),
+(9,'zzzzz'), (2,'zzz'), (1,'zzzz'), (9,'zzz'), (1,'zzzz'), (5,'zzzzz'),
+(4,'zzz'), (3,'zzzz'), (5,'zzzz'), (3,'zzz'), (4,'zz'), (3,'zzzzz');
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+EXPLAIN SELECT * from t1,t2,t3 WHERE t3.a3=t1.a1 AND t2.a2=t1.a1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	Using join buffer (flat, BKA join)
+1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	Using join buffer (incremental, BKA join)
+EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t1.a1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	Using join buffer (flat, BKA join)
+1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	Using join buffer (incremental, BKA join)
+EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t2.a2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	Using join buffer (flat, BKA join)
+1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	Using join buffer (incremental, BKA join)
+SELECT * from t1,t2,t3
+WHERE t3.a3=t1.a1 AND t2.a2=t1.a1 AND
+LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7;
+a1	b1	a2	b2	a3	b3
+1	xxx	1	yyy	1	z
+1	xxx	1	y	1	z
+1	xxx	1	yy	1	z
+1	xxx	1	y	1	zz
+1	xxx	1	yy	1	zz
+1	xxx	1	y	1	zz
+1	xxx	1	yy	1	zz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	zzz
+1	xxx	1	yyy	1	z
+1	xxx	1	y	1	z
+1	xxx	1	yy	1	z
+2	xx	2	y	2	zz
+2	xx	2	yy	2	zz
+2	xx	2	y	2	zzzz
+2	xx	2	y	2	zzz
+2	xx	2	yy	2	zzz
+SELECT * FROM t1,t2,t3
+WHERE t2.a2=t1.a1 AND t3.a3=t1.a1 AND 
+LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7;
+a1	b1	a2	b2	a3	b3
+1	xxx	1	yyy	1	z
+1	xxx	1	y	1	z
+1	xxx	1	yy	1	z
+1	xxx	1	y	1	zz
+1	xxx	1	yy	1	zz
+1	xxx	1	y	1	zz
+1	xxx	1	yy	1	zz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	zzz
+1	xxx	1	yyy	1	z
+1	xxx	1	y	1	z
+1	xxx	1	yy	1	z
+2	xx	2	y	2	zz
+2	xx	2	yy	2	zz
+2	xx	2	y	2	zzzz
+2	xx	2	y	2	zzz
+2	xx	2	yy	2	zzz
+SELECT * FROM t1,t2,t3
+WHERE t2.a2=t1.a1 AND t3.a3=t2.a2 AND
+LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7;
+a1	b1	a2	b2	a3	b3
+1	xxx	1	yyy	1	z
+1	xxx	1	y	1	z
+1	xxx	1	yy	1	z
+1	xxx	1	y	1	zz
+1	xxx	1	yy	1	zz
+1	xxx	1	y	1	zz
+1	xxx	1	yy	1	zz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	zzz
+1	xxx	1	yyy	1	z
+1	xxx	1	y	1	z
+1	xxx	1	yy	1	z
+2	xx	2	y	2	zz
+2	xx	2	yy	2	zz
+2	xx	2	y	2	zzzz
+2	xx	2	y	2	zzz
+2	xx	2	yy	2	zzz
+SET SESSION optimizer_switch=DEFAULT;
+DROP TABLE t1,t2,t3;
 End of 5.1 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 48fcc646d28..23e4fa75ffb 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -4383,7 +4383,7 @@ EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	100.00	Using index condition; Using where; Using MRR
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` > 1)) limit 2
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` > 1)) limit 2
 DROP TABLE t1;
 #
 # Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when 
@@ -4875,4 +4875,106 @@ f1	f1	f1	f1	f2	f1	f1
 18	9	NULL	NULL	NULL	5	7
 SET SESSION join_buffer_size = DEFAULT;
 DROP TABLE t1,t2,t3,t4,t5,t6;
+#
+# Bug #698882: best equality substitution not applied to ref 
+#
+CREATE TABLE t1 (a1 int NOT NULL, b1 char(10), INDEX idx (a1));
+CREATE TABLE t2 (a2 int NOT NULL, b2 char(10), INDEX idx (a2));
+CREATE TABLE t3 (a3 int NOT NULL, b3 char(10), INDEX idx (a3));
+INSERT INTO t1 VALUES (2,'xx'), (1,'xxx'),  (11,'xxxxxxx');
+INSERT INTO t2 VALUES
+(7,'yyyy'), (2,'y'), (3,'yyy'), (1,'yy'), (1,'yyyyy'),
+(3,'yy'),  (1,'y'), (4,'yyy'), (7,'y'),  (4,'yyyyy'), (7,'yyy'),
+(7,'yyyy'), (2,'yy'),  (3,'yyy'), (1,'yyyyyyyy'), (1,'yyyyy'),
+(3,'yy'), (1,'yyy'), (4,'yyy'), (7,'y'), (4,'yyyyy'), (7,'yyy');
+INSERT INTO t3 VALUES
+(9,'zzzzzzz'), (2,'zzzzz'), (1,'z'), (9,'zz'), (1,'zz'), (5,'zzzzzzz'),
+(4,'zz'), (3,'z'), (5,'zzzzzz'), (3,'zz'), (4,'zzzz'), (3,'z'),
+(9,'zzzzzzzz'), (2,'zz'), (1,'zz'), (9,'zzz'), (1,'zzz'), (5,'zzzzzzzz'),
+(4,'zzz'), (3,'zz'), (5,'zzzzzzz'), (3,'zzz'), (4,'zzzzz'), (3,'zz'),
+(9,'zzzzzz'), (2,'zzzz'), (1,'zzz'), (9,'z'), (1,'z'), (5,'zzzzzz'),
+(4,'z'), (3,'zzz'), (5,'zzzzz'), (3,'z'), (4,'zzz'), (3,'zzzz'),
+(9,'zzzzz'), (2,'zzz'), (1,'zzzz'), (9,'zzz'), (1,'zzzz'), (5,'zzzzz'),
+(4,'zzz'), (3,'zzzz'), (5,'zzzz'), (3,'zzz'), (4,'zz'), (3,'zzzzz');
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+EXPLAIN SELECT * from t1,t2,t3 WHERE t3.a3=t1.a1 AND t2.a2=t1.a1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	
+1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	
+EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t1.a1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	
+1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	
+EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t2.a2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.a1	2	
+1	SIMPLE	t3	ref	idx	idx	4	test.t1.a1	5	
+SELECT * from t1,t2,t3
+WHERE t3.a3=t1.a1 AND t2.a2=t1.a1 AND
+LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7;
+a1	b1	a2	b2	a3	b3
+2	xx	2	y	2	zz
+2	xx	2	y	2	zzzz
+2	xx	2	y	2	zzz
+2	xx	2	yy	2	zz
+2	xx	2	yy	2	zzz
+1	xxx	1	yy	1	z
+1	xxx	1	yy	1	zz
+1	xxx	1	yy	1	zz
+1	xxx	1	yy	1	z
+1	xxx	1	y	1	z
+1	xxx	1	y	1	zz
+1	xxx	1	y	1	zz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	z
+1	xxx	1	yyy	1	z
+1	xxx	1	yyy	1	z
+SELECT * FROM t1,t2,t3
+WHERE t2.a2=t1.a1 AND t3.a3=t1.a1 AND 
+LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7;
+a1	b1	a2	b2	a3	b3
+2	xx	2	y	2	zz
+2	xx	2	y	2	zzzz
+2	xx	2	y	2	zzz
+2	xx	2	yy	2	zz
+2	xx	2	yy	2	zzz
+1	xxx	1	yy	1	z
+1	xxx	1	yy	1	zz
+1	xxx	1	yy	1	zz
+1	xxx	1	yy	1	z
+1	xxx	1	y	1	z
+1	xxx	1	y	1	zz
+1	xxx	1	y	1	zz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	z
+1	xxx	1	yyy	1	z
+1	xxx	1	yyy	1	z
+SELECT * FROM t1,t2,t3
+WHERE t2.a2=t1.a1 AND t3.a3=t2.a2 AND
+LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7;
+a1	b1	a2	b2	a3	b3
+2	xx	2	y	2	zz
+2	xx	2	y	2	zzzz
+2	xx	2	y	2	zzz
+2	xx	2	yy	2	zz
+2	xx	2	yy	2	zzz
+1	xxx	1	yy	1	z
+1	xxx	1	yy	1	zz
+1	xxx	1	yy	1	zz
+1	xxx	1	yy	1	z
+1	xxx	1	y	1	z
+1	xxx	1	y	1	zz
+1	xxx	1	y	1	zz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	zzz
+1	xxx	1	y	1	z
+1	xxx	1	yyy	1	z
+1	xxx	1	yyy	1	z
+SET SESSION optimizer_switch=DEFAULT;
+DROP TABLE t1,t2,t3;
 End of 5.1 tests
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 708f1d3f95d..b64b9109636 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -983,7 +983,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	8	func	1	1.00	
 2	SUBQUERY	t1	ALL	varchar_key	NULL	NULL	NULL	15	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`))
+Note	1003	select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_key` < 'n') xor `test`.`t1`.`pk`))
 SELECT varchar_nokey
 FROM t2  
 WHERE ( `varchar_nokey`  , `varchar_nokey`  )  IN (  
diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index 9a7b6748d29..546ab1c6fdd 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -313,7 +313,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR
 1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using index condition; Using where
-1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t3.Country	1	Using index condition; Using where
+1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result
index ab97d59302c..dcc4716554b 100644
--- a/mysql-test/r/subselect_sj2_jcl6.result
+++ b/mysql-test/r/subselect_sj2_jcl6.result
@@ -320,7 +320,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR
 1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using index condition; Using where; Using join buffer (flat, BKA join)
-1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t3.Country	1	Using index condition(BKA); Using where; Using join buffer (incremental, BKA join)
+1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; Using join buffer (incremental, BKA join)
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index ee52f372f81..e80c20e4926 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -990,7 +990,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	8	func	1	1.00	
 2	SUBQUERY	t1	ALL	varchar_key	NULL	NULL	NULL	15	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_nokey` < 'n') xor `test`.`t1`.`pk`))
+Note	1003	select `test`.`t2`.`varchar_nokey` AS `varchar_nokey` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`varchar_nokey` = `test`.`t1`.`varchar_key`) and ((`test`.`t1`.`varchar_key` < 'n') xor `test`.`t1`.`pk`))
 SELECT varchar_nokey
 FROM t2  
 WHERE ( `varchar_nokey`  , `varchar_nokey`  )  IN (  
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index da38dde2bdf..de567dbf777 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -4121,4 +4121,47 @@ SET SESSION join_buffer_size = DEFAULT;
 
 DROP TABLE t1,t2,t3,t4,t5,t6;
 
+--echo #
+--echo # Bug #698882: best equality substitution not applied to ref 
+--echo #
+
+CREATE TABLE t1 (a1 int NOT NULL, b1 char(10), INDEX idx (a1));
+CREATE TABLE t2 (a2 int NOT NULL, b2 char(10), INDEX idx (a2));
+CREATE TABLE t3 (a3 int NOT NULL, b3 char(10), INDEX idx (a3));
+INSERT INTO t1 VALUES (2,'xx'), (1,'xxx'),  (11,'xxxxxxx');
+INSERT INTO t2 VALUES
+  (7,'yyyy'), (2,'y'), (3,'yyy'), (1,'yy'), (1,'yyyyy'),
+  (3,'yy'),  (1,'y'), (4,'yyy'), (7,'y'),  (4,'yyyyy'), (7,'yyy'),
+  (7,'yyyy'), (2,'yy'),  (3,'yyy'), (1,'yyyyyyyy'), (1,'yyyyy'),
+  (3,'yy'), (1,'yyy'), (4,'yyy'), (7,'y'), (4,'yyyyy'), (7,'yyy');
+INSERT INTO t3 VALUES
+  (9,'zzzzzzz'), (2,'zzzzz'), (1,'z'), (9,'zz'), (1,'zz'), (5,'zzzzzzz'),
+  (4,'zz'), (3,'z'), (5,'zzzzzz'), (3,'zz'), (4,'zzzz'), (3,'z'),
+  (9,'zzzzzzzz'), (2,'zz'), (1,'zz'), (9,'zzz'), (1,'zzz'), (5,'zzzzzzzz'),
+  (4,'zzz'), (3,'zz'), (5,'zzzzzzz'), (3,'zzz'), (4,'zzzzz'), (3,'zz'),
+  (9,'zzzzzz'), (2,'zzzz'), (1,'zzz'), (9,'z'), (1,'z'), (5,'zzzzzz'),
+  (4,'z'), (3,'zzz'), (5,'zzzzz'), (3,'z'), (4,'zzz'), (3,'zzzz'),
+  (9,'zzzzz'), (2,'zzz'), (1,'zzzz'), (9,'zzz'), (1,'zzzz'), (5,'zzzzz'),
+  (4,'zzz'), (3,'zzzz'), (5,'zzzz'), (3,'zzz'), (4,'zz'), (3,'zzzzz');
+  
+SET SESSION optimizer_switch='index_condition_pushdown=off';
+
+EXPLAIN SELECT * from t1,t2,t3 WHERE t3.a3=t1.a1 AND t2.a2=t1.a1;
+EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t1.a1;
+EXPLAIN SELECT * FROM t1,t2,t3 WHERE t2.a2=t1.a1 AND t3.a3=t2.a2;
+
+SELECT * from t1,t2,t3
+  WHERE t3.a3=t1.a1 AND t2.a2=t1.a1 AND
+        LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7;
+SELECT * FROM t1,t2,t3
+  WHERE t2.a2=t1.a1 AND t3.a3=t1.a1 AND 
+        LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7;
+SELECT * FROM t1,t2,t3
+  WHERE t2.a2=t1.a1 AND t3.a3=t2.a2 AND
+        LENGTH(CONCAT(CONCAT(t1.b1,t2.b2),t3.b3)) <= 7;
+
+SET SESSION optimizer_switch=DEFAULT;
+
+DROP TABLE t1,t2,t3;
+
 --echo End of 5.1 tests
diff --git a/sql/item.cc b/sql/item.cc
index 310e6994c7d..122688198b9 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4814,7 +4814,8 @@ bool Item_field::set_no_const_sub(uchar *arg)
   Replace an Item_field for an equal Item_field that evaluated earlier
   (if any).
 
-  The function returns a pointer to an item that is taken from
+  If this->item_equal points to some item and coincides with arg then
+  the function returns a pointer to an item that is taken from
   the very beginning of the item_equal list which the Item_field
   object refers to (belongs to) unless item_equal contains  a constant
   item. In this case the function returns this constant item, 
@@ -4822,7 +4823,7 @@ bool Item_field::set_no_const_sub(uchar *arg)
   If the Item_field object does not refer any Item_equal object
   'this' is returned .
 
-  @param arg   a dummy parameter, is not used here
+  @param arg   NULL or points to so some item of the Item_equal type  
 
 
   @note
@@ -4837,7 +4838,7 @@ bool Item_field::set_no_const_sub(uchar *arg)
 
 Item *Item_field::replace_equal_field(uchar *arg)
 {
-  if (item_equal)
+  if (item_equal && item_equal == (Item_equal *) arg)
   {
     Item *const_item= item_equal->get_const();
     if (const_item)
@@ -4848,7 +4849,7 @@ Item *Item_field::replace_equal_field(uchar *arg)
       return const_item;
     }
     Item_field *subst= item_equal->get_first(this);
-    if (subst && field->table != subst->field->table && !field->eq(subst->field))
+    if (subst && !field->eq(subst->field))
       return subst;
   }
   return this;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 0b89adb75e8..32641a7ea3b 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -5521,6 +5521,7 @@ Item_equal::Item_equal(Item_field *f1, Item_field *f2)
   const_item_cache= 0;
   fields.push_back(f1);
   fields.push_back(f2);
+  f1->item_equal= f2->item_equal= this;
 }
 
 Item_equal::Item_equal(Item *c, Item_field *f)
@@ -5598,6 +5599,7 @@ void Item_equal::add(Item *c)
 void Item_equal::add(Item_field *f)
 {
   fields.push_back(f);
+  f->item_equal= this;
 }
 
 uint Item_equal::members()
@@ -5668,7 +5670,7 @@ void Item_equal::merge(Item_equal *item)
   If cmp(item_field1,item_field2,arg)<0 than item_field1 must be
   placed after item_fiel2.
 
-  The function sorts field items by the exchange sort algorithm.
+  The function sorts field items by the bubble sort algorithm.
   The list of field items is looked through and whenever two neighboring
   members follow in a wrong order they are swapped. This is performed
   again and again until we get all members in a right order.
@@ -5679,7 +5681,7 @@ void Item_equal::merge(Item_equal *item)
 
 void Item_equal::sort(Item_field_cmpfunc compare, void *arg)
 {
-  exchange_sort<Item_field>(&fields, compare, arg);
+  bubble_sort<Item_field>(&fields, compare, arg);
 }
 
 
diff --git a/sql/opt_table_elimination.cc b/sql/opt_table_elimination.cc
index be1471b7e2c..7497395d628 100644
--- a/sql/opt_table_elimination.cc
+++ b/sql/opt_table_elimination.cc
@@ -1232,7 +1232,7 @@ void build_eq_mods_for_cond(Dep_analysis_context *ctx,
     if (fvl->elements)
     {
       
-      exchange_sort<Dep_value_field>(fvl, compare_field_values, NULL);
+      bubble_sort<Dep_value_field>(fvl, compare_field_values, NULL);
       add_module_expr(ctx, eq_mod, *and_level, NULL, bound_item, fvl);
     }
     break;
diff --git a/sql/sql_list.h b/sql/sql_list.h
index 76b3145f24d..2dade14f211 100644
--- a/sql/sql_list.h
+++ b/sql/sql_list.h
@@ -515,36 +515,40 @@ public:
 
 
 /*
-  Exchange sort algorithm for List<T>.
+  Bubble sort algorithm for List<T>.
+  This sort function is supposed to be used only for very short list.
+  Currently it is used for the lists of Item_equal objects and
+  for some lists in the table elimination algorithms. In both
+  cases the sorted lists are very short.
 */
+
 template <class T> 
-inline void exchange_sort(List<T> *list_to_sort,
-                          int (*sort_func)(T *a, T *b, void *arg), void *arg)
+inline void bubble_sort(List<T> *list_to_sort,
+                        int (*sort_func)(T *a, T *b, void *arg), void *arg)
 {
   bool swap;
+  T **ref1= 0;
+  T **ref2= 0;
   List_iterator<T> it(*list_to_sort);
   do
   {
+    T **last_ref= ref1;
     T *item1= it++;
-    T **ref1= it.ref();
+    ref1= it.ref();
     T *item2;
 
     swap= FALSE;
-    while ((item2= it++))
+    while ((item2= it++) && (ref2= it.ref()) != last_ref)
     {
-      T **ref2= it.ref();
       if (sort_func(item1, item2, arg) < 0)
       {
-        T *item= *ref1;
-        *ref1= *ref2;
-        *ref2= item;
+        *ref1= item2;
+        *ref2= item1;
         swap= TRUE;
       }
       else
-      {
         item1= item2;
-        ref1= ref2;
-      }
+      ref1= ref2;
     }
     it.rewind();
   } while (swap);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 4713dc40f04..757669e0f9f 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1036,7 +1036,7 @@ JOIN::optimize()
   }
 
   /*
-    Permorm the the optimization on fields evaluation mentioned above
+    Perform the optimization on fields evaluation mentioned above
     for all on expressions.
   */ 
   for (JOIN_TAB *tab= join_tab + const_tables; tab < join_tab + tables ; tab++)
@@ -1048,8 +1048,38 @@ JOIN::optimize()
                                                          map2table);
       (*tab->on_expr_ref)->update_used_tables();
     }
+
+    
   }
 
+  /*
+    Perform the optimization on fields evaliation mentioned above
+    for all used ref items.
+  */
+  for (JOIN_TAB *tab= join_tab + const_tables; tab < join_tab + tables; tab++)
+  {
+    for (uint i=0; i < tab->ref.key_parts; i++)
+    {
+      
+      Item **ref_item_ptr= tab->ref.items+i;
+      Item *ref_item= *ref_item_ptr;
+      COND_EQUAL *equals= tab->first_inner ? tab->first_inner->cond_equal : 
+	                                     cond_equal;
+      ref_item= substitute_for_best_equal_field(ref_item, equals, map2table);
+      ref_item->update_used_tables();
+      if (*ref_item_ptr != ref_item)
+      {
+        *ref_item_ptr= ref_item;
+        Item *item= ref_item->real_item();
+        if (item->type() == Item::FIELD_ITEM)
+        {
+          store_key_field *key_copy= (store_key_field *) tab->ref.key_copy[i];
+          key_copy->change_source_field((Item_field *) item);
+        }
+      }
+    }
+  }   
+
   if (conds && const_table_map != found_const_table_map &&
       (select_options & SELECT_DESCRIBE))
   {
@@ -9508,10 +9538,14 @@ static COND *build_equal_items(THD *thd, COND *cond,
 /**
   Compare field items by table order in the execution plan.
 
+    If field1 and field2 belong to different tables then
     field1 considered as better than field2 if the table containing
     field1 is accessed earlier than the table containing field2.   
     The function finds out what of two fields is better according
     this criteria.
+    If field1 and field2 belong to the same table then the result
+    of comparison depends on whether the fields are parts of
+    the key that are used to access this table.  
 
   @param field1          first field item to compare
   @param field2          second field item to compare
@@ -9526,8 +9560,8 @@ static COND *build_equal_items(THD *thd, COND *cond,
 */
 
 static int compare_fields_by_table_order(Item_field *field1,
-                                  Item_field *field2,
-                                  void *table_join_idx)
+                                         Item_field *field2,
+                                         void *table_join_idx)
 {
   int cmp= 0;
   bool outer_ref= 0;
@@ -9536,7 +9570,7 @@ static int compare_fields_by_table_order(Item_field *field1,
     outer_ref= 1;
     cmp= -1;
   }
-  if (field2->used_tables() & OUTER_REF_TABLE_BIT)
+  if (field1->used_tables() & OUTER_REF_TABLE_BIT)
   {
     outer_ref= 1;
     cmp++;
@@ -9545,6 +9579,42 @@ static int compare_fields_by_table_order(Item_field *field1,
     return cmp;
   JOIN_TAB **idx= (JOIN_TAB **) table_join_idx;
   cmp= idx[field2->field->table->tablenr]-idx[field1->field->table->tablenr];
+  if (!cmp)
+  {
+    JOIN_TAB *tab= idx[field1->field->table->tablenr];
+    uint keyno= MAX_KEY;
+    if (tab->ref.key_parts)
+      keyno= tab->ref.key;
+    else if (tab->select && tab->select->quick)
+       keyno = tab->select->quick->index;
+    if (keyno != MAX_KEY)
+    {
+      if (field2->field->part_of_key.is_set(keyno))
+        cmp= -1;
+      if (field1->field->part_of_key.is_set(keyno))
+        cmp++;
+      if (!cmp)
+      {
+        KEY *key_info= tab->table->key_info + keyno;
+        for (uint i= 0; i < key_info->key_parts; i++)
+	{
+          Field *fld= key_info->key_part[i].field;
+          if (fld->eq(field2->field))
+	  {
+	    cmp= -1;
+            break;
+          }
+          if (fld->eq(field1->field))
+	  {
+	    cmp= 1;
+            break;
+          }
+        }
+      }              
+    }              
+    else   
+      cmp= field2->field->field_index-field1->field->field_index;
+  }
   return cmp < 0 ? -1 : (cmp ? 1 : 0);
 }
 
@@ -9833,8 +9903,14 @@ static COND* substitute_for_best_equal_field(COND *cond,
     cond= eliminate_item_equal(0, cond_equal, item_equal);
     return cond ? cond : org_cond;
   }
-  else
-    cond->transform(&Item::replace_equal_field, 0);
+  else if (cond_equal)
+  {
+    List_iterator_fast<Item_equal> it(cond_equal->current_level);
+    while((item_equal= it++))
+    {
+      cond= cond->transform(&Item::replace_equal_field, (uchar *) item_equal);
+    }
+  }
   return cond;
 }
 
diff --git a/sql/sql_select.h b/sql/sql_select.h
index ecc19f763fa..8f0feb0e766 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1071,6 +1071,12 @@ class store_key_field: public store_key
   }
   const char *name() const { return field_name; }
 
+  void change_source_field(Item_field *fld_item)
+  {
+    copy_field.set(to_field, fld_item->field, 0);
+    field_name= fld_item->full_name();
+  }
+
  protected: 
   enum store_key_result copy_inner()
   {