bk commit into 5.0 tree (timour:1.1962) BUG#11044

[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.1962 05/06/14 12:52:44 timour@stripped +3 -0
  Fix for BUG#11044 - "SELECT DISTINCT on indexed column returns inconsistent results"
  
  The problem was that when there was no MIN or MAX function, after finding the
  group prefix based on the DISTINCT or GROUP BY attributes we did not search further
  for a key in the group that satisfies the equi-join conditions on attributes that
  follow the group attributes. Thus we ended up with the wrong rows, and subsequent
  calls to select_cond->val_int() in evaluate_join_record() were filtering those
  rows. Hence - the query result set was empty.
  
  The problem occured both for GROUP BY queries without MIN/MAX and for queries
  with DISTINCT (which were internally executed as GROUP BY queries).

  sql/opt_range.cc
    1.169 05/06/14 12:52:38 timour@stripped +14 -8
    * Use the extended prefix in QUICK_GROUP_MIN_MAX_SELECT::get_next()
      to find keys that satisfy equality conditions in the case when there is
      no MIN or MAX function.
    * Corrected some method comments.
    * Corrected debug printout of cost information.

  mysql-test/t/group_min_max.test
    1.12 05/06/14 12:52:38 timour@stripped +12 -1
    Added test for BUG#11044. Notice that the group by query is
    equivalent to the distinct query and both are executed via the
    same algorithm.

  mysql-test/r/group_min_max.result
    1.14 05/06/14 12:52:38 timour@stripped +18 -0
    Added test result for BUG#11044. Notice that the group by query is
    equivalent to the distinct query and both are executed via the same
    algorithm.

# 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:	zmei.home
# Root:	/home/timka/mysql/src/5.0-bug-11044

--- 1.168/sql/opt_range.cc	2005-06-07 00:31:47 +03:00
+++ 1.169/sql/opt_range.cc	2005-06-14 12:52:38 +03:00
@@ -7612,8 +7612,8 @@
   *records= num_groups;
 
   DBUG_PRINT("info",
-             ("records=%u, keys/block=%u, keys/group=%u, records=%u, blocks=%u",
-              table_records, keys_per_block, keys_per_group, records,
+             ("table rows=%u, keys/block=%u, keys/group=%u, result rows=%u, blocks=%u",
+              table_records, keys_per_block, keys_per_group, *records,
               num_blocks));
   DBUG_VOID_RETURN;
 }
@@ -8120,6 +8120,15 @@
       DBUG_ASSERT((have_max && !have_min) ||
                   (have_max && have_min && (max_res == 0)));
     }
+    /*
+      If this is a just a GROUP BY or DISTINCT without MIN or MAX and there
+      are equality predicates for the key parts after the group, find the
+      first sub-group with the extended prefix.
+    */
+    if (!have_min && !have_max && key_infix_len > 0)
+      result= file->index_read(record, group_prefix, real_prefix_len,
+                               HA_READ_KEY_EXACT);
+
     result= have_min ? min_res : have_max ? max_res : result;
   }
   while (result == HA_ERR_KEY_NOT_FOUND && is_last_prefix != 0);
@@ -8146,9 +8155,8 @@
     QUICK_GROUP_MIN_MAX_SELECT::next_min()
 
   DESCRIPTION
-    Load the prefix of the next group into group_prefix and find the minimal
-    key within this group such that the key satisfies the query conditions and
-    NULL semantics. The found key is loaded into this->record.
+    Find the minimal key within this group such that the key satisfies the query
+    conditions and NULL semantics. The found key is loaded into this->record.
 
   IMPLEMENTATION
     Depending on the values of min_max_ranges.elements, key_infix_len, and
@@ -8232,9 +8240,7 @@
     QUICK_GROUP_MIN_MAX_SELECT::next_max()
 
   DESCRIPTION
-    If there was no previous next_min call to determine the next group prefix,
-    then load the next prefix into group_prefix, then lookup the maximal key of
-    the group, and store it into this->record.
+    Lookup the maximal key of the group, and store it into this->record.
 
   RETURN
     0                    on success

--- 1.13/mysql-test/r/group_min_max.result	2005-05-30 19:54:29 +03:00
+++ 1.14/mysql-test/r/group_min_max.result	2005-06-14 12:52:38 +03:00
@@ -1954,6 +1954,24 @@
 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 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
+select a1 from t1 where a2 = 'b' group by a1;
+a1
+a
+b
+c
+d
+explain select distinct a1 from t1 where a2 = 'b';
+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
+select distinct a1 from t1 where a2 = 'b';
+a1
+a
+b
+c
+d
 drop table t1;
 drop table t2;
 drop table t3;

--- 1.11/mysql-test/t/group_min_max.test	2005-05-19 05:54:28 +03:00
+++ 1.12/mysql-test/t/group_min_max.test	2005-06-14 12:52:38 +03:00
@@ -482,7 +482,6 @@
 select a1,a2,b,c from t2 where (a2 >= 'b') and (b = 'a') and (c = 'i121') group by
a1,a2,b;
 select a1,a2,b from t2 where (a1 > 'a') and (a2 > 'a') and (b = 'c') group by
a1,a2,b;
 
-
 --
 -- DISTINCT queries
 --
@@ -526,6 +525,7 @@
 select distinct a2,a1,a2,a1 from t1;
 select distinct t1.a1,t2.a1 from t1,t2;
 
+
 --
 -- DISTINCT queries with GROUP-BY
 --
@@ -640,6 +640,17 @@
 explain select a1,a2,count(a2) from t1 group by a1,a2,b;
 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#11044: DISTINCT or GROUP BY queries with equality predicates instead of MIN/MAX.
+#
+
+explain select a1 from t1 where a2 = 'b' group by a1;
+select a1 from t1 where a2 = 'b' group by a1;
+
+explain select distinct a1 from t1 where a2 = 'b';
+select distinct a1 from t1 where a2 = 'b';
+
 
 drop table t1;
 drop table t2;