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/in...urce-tree.html

ChangeSet
1.1962 05/06/14 12:52:44 timour (AT) mysql (DOT) com +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 (AT) mysql (DOT) com +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 (AT) mysql (DOT) com +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 (AT) mysql (DOT) com +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;

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw