Merge spetrunia@bk-internal.mysql.com:/home/bk/mysql-5.1-opt

into  mysql.com:/home/psergey/mysql-5.1-bug22393


mysql-test/t/range.test:
  Auto merged
sql/sql_select.cc:
  Auto merged
mysql-test/r/range.result:
  Manual merge

3 files changed, 64 insertions, 0 deletions

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index a7074b9a9ed..cfdee6e630e 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -941,3 +941,17 @@ item	started	price
 A1	2005-11-01 08:00:00	1000.000
 A1	2005-11-15 00:00:00	2000.000
 DROP TABLE t1;
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, filler char(100));
+insert into t2 select A.a + 10 * (B.a + 10 * C.a), 10, 'filler' from t1 A,
+t1 B, t1 C where A.a < 5;
+insert into t2 select 1000, b, 'filler' from t2;
+alter table t2 add index (a,b);
+select 'In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)' Z;
+Z
+In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)
+explain select * from t2 where a=1000 and b<11;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	a	a	5	const	502	Using where
+drop table t1, t2;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index 89508f468a7..1a80234e485 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -740,3 +740,30 @@ SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
 DROP TABLE t1;
 
 # End of 5.0 tests
+
+# BUG#22393 fix: Adjust 'ref' estimate if we have 'range' estimate for
+#                a smaller scan interval
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t2 (a int, b int, filler char(100));
+insert into t2 select A.a + 10 * (B.a + 10 * C.a), 10, 'filler' from t1 A,
+t1 B, t1 C where A.a < 5;
+
+insert into t2 select 1000, b, 'filler' from t2;
+alter table t2 add index (a,b);
+# t2 values 
+#  ( 1  , 10, 'filler')
+#  ( 2  , 10, 'filler')
+#  ( 3  , 10, 'filler')
+#  (... , 10, 'filler')
+#   ...
+#  (1000, 10, 'filler') - 500 times
+
+# 500 rows, 1 row
+
+select 'In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)' Z;
+explain select * from t2 where a=1000 and b<11;
+
+drop table t1, t2;
+
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 8b8bf9e38d8..9a05aa7da04 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3717,7 +3717,30 @@ best_access_path(JOIN      *join,
             {
               /* Check if we have statistic about the distribution */
               if ((records= keyinfo->rec_per_key[max_key_part-1]))
+              {
+                /* 
+                  Fix for the case where the index statistics is too
+                  optimistic: If 
+                  (1) We're considering ref(const) and there is quick select
+                      on the same index, 
+                  (2) and that quick select uses more keyparts (i.e. it will
+                      scan equal/smaller interval then this ref(const))
+                  (3) and E(#rows) for quick select is higher then our
+                      estimate,
+                  Then 
+                    We'll use E(#rows) from quick select.
+
+                  Q: Why do we choose to use 'ref'? Won't quick select be
+                  cheaper in some cases ?
+                  TODO: figure this out and adjust the plan choice if needed.
+                */
+                if (!found_ref && table->quick_keys.is_set(key) &&    // (1)
+                    table->quick_key_parts[key] > max_key_part &&     // (2)
+                    records < (double)table->quick_rows[key])         // (3)
+                  records= (double)table->quick_rows[key];
+
                 tmp= records;
+              }
               else
               {
                 /*