bk commit into 5.0 tree (timour:1.2050) BUG#16710

[timour]

Below is the list of changes that have just been committed into a local
5.0 repository of timka. When timka does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2050 06/03/30 23:42:17 timour@stripped +4 -0
  Fix for BUG#16710.
  
  The bug was due to a missed case in the detection of whether an index
  can be used for loose scan. More precisely, the range optimizer chose
  to use loose index scan for queries for which the condition(s) over
  an index key part could not be pushed to the index together with the
  loose scan.
  
  As a result, loose index scan was selecting the first row in the
  index with a given GROUP BY prefix, and was applying the WHERE
  clause after that, while it should have inspected all rows with
  the given prefix, and apply the WHERE clause to all of them.
  
  The fix detects and skips such cases.  

  sql/opt_range.cc
    1.205 06/03/30 23:42:14 timour@stripped +30 -0
    Handle the case when there is no MIN/MAX aggregate function, and a
    keypart of the index being considered, that is after the GROUP BY
    prefix, is used in the WHERE clause and the condition where it is
    used cannot be pushed to the index.
    If this is the case, we rule out this index.

  sql/item.cc
    1.205 06/03/30 23:42:13 timour@stripped +1 -1
    corrected case in comment

  mysql-test/t/group_min_max.test
    1.19 06/03/30 23:42:13 timour@stripped +8 -0
    Added test for BUG#16710.

  mysql-test/r/group_min_max.result
    1.21 06/03/30 23:42:13 timour@stripped +9 -0
    Added test for BUG#16710.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	timour
# Host:	lamia.home
# Root:	/home/timka/mysql/src/5.0-bug-16710

--- 1.204/sql/item.cc	2006-01-24 19:15:08 +02:00
+++ 1.205/sql/item.cc	2006-03-30 23:42:13 +03:00
@@ -499,7 +499,7 @@
     arguments in a condition the method must return false.
 
   RETURN
-    false to force the evaluation of collect_item_field_processor
+    FALSE to force the evaluation of collect_item_field_processor
           for the subsequent items.
 */
 

--- 1.204/sql/opt_range.cc	2006-02-06 21:35:06 +02:00
+++ 1.205/sql/opt_range.cc	2006-03-30 23:42:14 +03:00
@@ -6876,6 +6876,7 @@
                    bool have_min, bool have_max,
                    double *read_cost, ha_rows *records);
 
+
 /*
   Test if this access method is applicable to a GROUP query with MIN/MAX
   functions, and if so, construct a new TRP object.
@@ -7282,11 +7283,40 @@
       }
       else if (min_max_arg_part &&
                (min_max_arg_part - first_non_group_part > 0))
+      {
         /*
           There is a gap but no range tree, thus no predicates at all for the
           non-group keyparts.
         */
         goto next_index;
+      }
+      else if (first_non_group_part && join->conds)
+      {
+        /*
+          If there is no MIN/MAX function in the query, but some index
+          key part is referenced in the WHERE clause, then this index
+          cannot be used because the WHERE condition over the keypart's
+          field cannot be 'pushed' to the index (because there is no
+          range 'tree'), and the WHERE clause must be evaluated before
+          GROUP BY/DISTINCT.
+        */
+        List<Item_field> cond_fields;
+        /* Collect all fields from all conditions in a list. */
+        join->conds->walk(&Item::collect_item_field_processor,
+                          (byte*) &cond_fields);
+        List_iterator_fast<Item_field> cond_fields_it(cond_fields);
+        Item_field *cur_item;
+
+        /* Check if cur_part is referenced in the WHERE clause. */
+        while ((cur_item= cond_fields_it++))
+        {
+          for (cur_part= first_non_group_part; cur_part != last_part; cur_part++)
+          {
+            if (cur_part->field->eq(cur_item->field))
+              goto next_index;
+          }
+        }
+      }
     }
 
     /*

--- 1.20/mysql-test/r/group_min_max.result	2006-02-06 21:35:05 +02:00
+++ 1.21/mysql-test/r/group_min_max.result	2006-03-30 23:42:13 +03:00
@@ -1954,6 +1954,15 @@
 explain select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	163	NULL	128	Using where; Using
index
+explain select distinct(a1) from t1 where ord(a2) = 98;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	Using where; Using index
+select distinct(a1) from t1 where ord(a2) = 98;
+a1
+a
+b
+c
+d
 explain select a1 from t1 where a2 = 'b' group by a1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	NULL	idx_t1_1	130	NULL	5	Using where; Using index for group-by

--- 1.18/mysql-test/t/group_min_max.test	2006-02-06 21:35:06 +02:00
+++ 1.19/mysql-test/t/group_min_max.test	2006-03-30 23:42:13 +03:00
@@ -641,6 +641,14 @@
 explain select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
 explain select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
 
+
+#
+# Bug #16710: select distinct doesn't return all it should
+#
+
+explain select distinct(a1) from t1 where ord(a2) = 98;
+select distinct(a1) from t1 where ord(a2) = 98;
+
 #
 # BUG#11044: DISTINCT or GROUP BY queries with equality predicates instead of MIN/MAX.
 #