Fixed LP bug #754521.

The function test_quick_select by mistake did not update the value
of table->quick_condition_rows for index intersection scans though
the specification explicitly required to do so from any table access
plan once the plan provided a better upper bound for the number of
rows selected from the table. It resulted in a bogus, usually very
big number saved as the cost of the table access. This, in its turn,
in many cases forced the optimizer to make a bad choice of the
execution plan for join queries.

4 files changed, 28 insertions, 1 deletions

diff --git a/mysql-test/r/index_intersect.result b/mysql-test/r/index_intersect.result
index 33654979d93..713674430cf 100644
--- a/mysql-test/r/index_intersect.result
+++ b/mysql-test/r/index_intersect.result
@@ -998,6 +998,13 @@ ID	Name	Country	Population
 1898	Chengdu	CHN	3361500
 1900	Changchun	CHN	2812000
 1910	Changsha	CHN	1809800
+EXPLAIN 
+SELECT * FROM City, Country 
+WHERE City.Name LIKE 'C%' AND City.Population > 1000000 AND
+Country.Code=City.Country;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	Population,Name,CountryID,CountryName	Name,Population	35,4	NULL	#	Using sort_intersect(Name,Population); Using where
+1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	#	
 DROP DATABASE world;
 use test;
 CREATE TABLE t1 (
diff --git a/mysql-test/r/index_intersect_innodb.result b/mysql-test/r/index_intersect_innodb.result
index 4fbdb3b70fa..c2aa281c7f3 100644
--- a/mysql-test/r/index_intersect_innodb.result
+++ b/mysql-test/r/index_intersect_innodb.result
@@ -999,6 +999,13 @@ ID	Name	Country	Population
 1898	Chengdu	CHN	3361500
 1900	Changchun	CHN	2812000
 1910	Changsha	CHN	1809800
+EXPLAIN 
+SELECT * FROM City, Country 
+WHERE City.Name LIKE 'C%' AND City.Population > 1000000 AND
+Country.Code=City.Country;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	City	index_merge	Population,Name,CountryID,CountryName	Population,Name	4,35	NULL	#	Using sort_intersect(Population,Name); Using where
+1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.City.Country	#	
 DROP DATABASE world;
 use test;
 CREATE TABLE t1 (
diff --git a/mysql-test/t/index_intersect.test b/mysql-test/t/index_intersect.test
index 0beba7a126c..9ea2f5e3101 100644
--- a/mysql-test/t/index_intersect.test
+++ b/mysql-test/t/index_intersect.test
@@ -396,6 +396,17 @@ SELECT * FROM City
   WHERE Country='CHN' AND Population > 1500000 AND Name LIKE 'C%';
 
 
+#
+# Bug #754521: wrong cost of index intersection leading
+#              to the choice of a suboptimal execution plan
+#
+
+--replace_column 9 #
+EXPLAIN 
+SELECT * FROM City, Country 
+ WHERE City.Name LIKE 'C%' AND City.Population > 1000000 AND
+       Country.Code=City.Country;
+
 DROP DATABASE world;
 
 use test;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 8d0d1c03ee5..a605b3f8fe1 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -3116,7 +3116,9 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
                                                     best_read_time)))
         {
           best_trp= intersect_trp;
-          best_read_time= best_trp->read_cost;         
+          best_read_time= best_trp->read_cost; 
+          set_if_smaller(param.table->quick_condition_rows, 
+                         intersect_trp->records);
         }
       }