[Commits] Rev 2876: Fixed LP bug #698882. in file:///home/igor/maria/maria-5.3-bugs/

Igor Babaev igor at askmonty.org
Thu Jan 13 01:11:00 EET 2011


At file:///home/igor/maria/maria-5.3-bugs/

------------------------------------------------------------
revno: 2876
revision-id: igor at askmonty.org-20110112231059-prhv2vncj6y2cizn
parent: igor at askmonty.org-20110105230330-2nzlm8641wcrwzio
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-bugs
timestamp: Wed 2011-01-12 15:10:59 -0800
message:
  Fixed LP bug #698882.
  Made sure that the optimal fields are used by TABLE_REF objects
  when building index access keys to joined tables.
  Fixed a bug in the template function that sorts the elements of
  a list using the bubble sort algorithm. The bug caused poor
  performance of the function. Also added an optimization that
  skips comparison with the most heavy elements that has been 
  already properly placed in the list.
  Made the comparison of the fields belonging to the same Item_equal
  more granular: fields belonging to the same table are also ordered
  according to some rules.
-------------- next part --------------
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2011-01-05 23:03:30 +0000
+++ b/mysql-test/r/join_cache.result	2011-01-12 23:10:59 +0000
@@ -870,7 +870,7 @@
 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 @@
 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 @@
 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 @@
 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 @@
 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 @@
 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 @@
 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 @@
 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 @@
 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 @@
 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 @@
 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 @@
 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

=== modified file 'mysql-test/r/join_nested.result'
--- a/mysql-test/r/join_nested.result	2010-10-27 23:31:22 +0000
+++ b/mysql-test/r/join_nested.result	2011-01-12 23:10:59 +0000
@@ -1733,7 +1733,7 @@
 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)

=== modified file 'mysql-test/r/join_nested_jcl6.result'
--- a/mysql-test/r/join_nested_jcl6.result	2010-11-01 17:43:02 +0000
+++ b/mysql-test/r/join_nested_jcl6.result	2011-01-12 23:10:59 +0000
@@ -1740,7 +1740,7 @@
 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)

=== modified file 'mysql-test/r/myisam_mrr.result'
--- a/mysql-test/r/myisam_mrr.result	2010-12-13 10:42:40 +0000
+++ b/mysql-test/r/myisam_mrr.result	2011-01-12 23:10:59 +0000
@@ -504,7 +504,7 @@
 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;

=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2010-11-01 17:43:02 +0000
+++ b/mysql-test/r/select.result	2011-01-12 23:10:59 +0000
@@ -4383,7 +4383,7 @@
 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 
@@ -4796,4 +4796,106 @@
 1
 1
 DROP TABLE t1;
+#
+# 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

=== modified file 'mysql-test/r/select_jcl6.result'
--- a/mysql-test/r/select_jcl6.result	2010-11-01 17:43:02 +0000
+++ b/mysql-test/r/select_jcl6.result	2011-01-12 23:10:59 +0000
@@ -4390,7 +4390,7 @@
 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 
@@ -4803,6 +4803,108 @@
 1
 1
 DROP TABLE t1;
+#
+# 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';

=== modified file 'mysql-test/r/select_pkeycache.result'
--- a/mysql-test/r/select_pkeycache.result	2010-11-01 17:43:02 +0000
+++ b/mysql-test/r/select_pkeycache.result	2011-01-12 23:10:59 +0000
@@ -4383,7 +4383,7 @@
 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 
@@ -4796,4 +4796,106 @@
 1
 1
 DROP TABLE t1;
+#
+# 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

=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2010-12-11 07:23:34 +0000
+++ b/mysql-test/r/subselect_sj.result	2011-01-12 23:10:59 +0000
@@ -983,7 +983,7 @@
 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 (  

=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result	2010-11-03 19:26:18 +0000
+++ b/mysql-test/r/subselect_sj2.result	2011-01-12 23:10:59 +0000
@@ -313,7 +313,7 @@
 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 '',

=== modified file 'mysql-test/r/subselect_sj2_jcl6.result'
--- a/mysql-test/r/subselect_sj2_jcl6.result	2010-11-03 19:26:18 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result	2011-01-12 23:10:59 +0000
@@ -320,7 +320,7 @@
 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 '',

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2010-12-11 07:23:34 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2011-01-12 23:10:59 +0000
@@ -990,7 +990,7 @@
 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 (  

=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test	2010-06-24 17:13:08 +0000
+++ b/mysql-test/t/select.test	2011-01-12 23:10:59 +0000
@@ -4088,4 +4088,47 @@
 DROP TABLE t1;
 
 
+--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

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2010-12-06 08:25:44 +0000
+++ b/sql/item.cc	2011-01-12 23:10:59 +0000
@@ -4814,7 +4814,8 @@
   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 @@
   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 @@
 
 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 @@
       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;

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2010-12-08 14:16:32 +0000
+++ b/sql/item_cmpfunc.cc	2011-01-12 23:10:59 +0000
@@ -5521,6 +5521,7 @@
   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_field *f)
 {
   fields.push_back(f);
+  f->item_equal= this;
 }
 
 uint Item_equal::members()
@@ -5668,7 +5670,7 @@
   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::sort(Item_field_cmpfunc compare, void *arg)
 {
-  exchange_sort<Item_field>(&fields, compare, arg);
+  bubble_sort<Item_field>(&fields, compare, arg);
 }
 
 

=== modified file 'sql/opt_table_elimination.cc'
--- a/sql/opt_table_elimination.cc	2010-11-23 22:08:48 +0000
+++ b/sql/opt_table_elimination.cc	2011-01-12 23:10:59 +0000
@@ -1232,7 +1232,7 @@
     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;

=== modified file 'sql/sql_list.h'
--- a/sql/sql_list.h	2010-09-28 06:24:46 +0000
+++ b/sql/sql_list.h	2011-01-12 23:10:59 +0000
@@ -515,36 +515,35 @@
 
 
 /*
-  Exchange sort algorithm for List<T>.
+  Bubble sort algorithm for List<T>.
 */
 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);

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-12-27 22:22:05 +0000
+++ b/sql/sql_select.cc	2011-01-12 23:10:59 +0000
@@ -1036,7 +1036,7 @@
   }
 
   /*
-    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 @@
                                                          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 @@
 /**
   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 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 @@
     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 @@
     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 @@
     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;
 }
 

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2010-12-13 10:42:40 +0000
+++ b/sql/sql_select.h	2011-01-12 23:10:59 +0000
@@ -1071,6 +1071,12 @@
   }
   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()
   {



More information about the commits mailing list