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.1981 05/11/24 10:10:36 timour@stripped +8 -0
Fix for BUG#14920 Ordering aggregated result sets corrupts resultset.
The cause of the bug was the use of end_write_group instead of end_write
in the case when ORDER BY required a temporary table, which didn't take
into account the fact that loose index scan already computes the result
of MIN/MAX aggregate functions (and performs grouping).
The solution is to call end_write instead of end_write_group and to add
the MIN/MAX functions to the list of regular functions so that their
values are inserted into the temporary table.
sql/sql_update.cc
1.181 05/11/24 10:10:33 timour@stripped +1 -1
New parameter to create_tmp_table.
sql/sql_union.cc
1.131 05/11/24 10:10:33 timour@stripped +1 -1
New parameter to create_tmp_table.
sql/sql_show.cc
1.298 05/11/24 10:10:33 timour@stripped +1 -1
New parameter to create_tmp_table.
sql/sql_select.h
1.103 05/11/24 10:10:33 timour@stripped +1 -1
New parameter to create_tmp_table.
sql/sql_select.cc
1.378 05/11/24 10:10:32 timour@stripped +58 -13
Enable result rows generated by loose index scan being written into
a temporary table. The change is necessary because loose index
scan already computes the result of GROUP BY and the MIN/MAX aggregate
functions. This is realized by three changes:
- create_tmp_table allocates space for aggregate functions in the
list of regular functions,
- use end_write instead of end_write group,
- copy the pointers to the MIN/MAX aggregate functions to the list
of regular functions TMP_TABLE_PARAM::items_to_copy.
sql/item_sum.cc
1.168 05/11/24 10:10:32 timour@stripped +2 -2
New parameter to create_tmp_table.
mysql-test/t/group_min_max.test
1.16 05/11/24 10:10:32 timour@stripped +13 -0
Test for BUG#14920
mysql-test/r/group_min_max.result
1.18 05/11/24 10:10:32 timour@stripped +31 -0
Test for BUG#14920
# 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-14920
--- 1.167/sql/item_sum.cc 2005-11-01 16:21:43 +02:00
+++ 1.168/sql/item_sum.cc 2005-11-24 10:10:32 +02:00
@@ -2293,7 +2293,7 @@
if (!(table= create_tmp_table(thd, tmp_table_param, list, (ORDER*) 0, 1,
0,
(select_lex->options | thd->options),
- HA_POS_ERROR, (char*)"")))
+ HA_POS_ERROR, (char*)"", FALSE)))
return TRUE;
table->file->extra(HA_EXTRA_NO_ROWS); // Don't update rows
table->no_rows=1;
@@ -3077,7 +3077,7 @@
if (!(table= create_tmp_table(thd, tmp_table_param, all_fields,
(ORDER*) 0, 0, TRUE,
(select_lex->options | thd->options),
- HA_POS_ERROR, (char*) "")))
+ HA_POS_ERROR, (char*) "", FALSE)))
DBUG_RETURN(TRUE);
table->file->extra(HA_EXTRA_NO_ROWS);
table->no_rows= 1;
--- 1.377/sql/sql_select.cc 2005-11-16 16:58:11 +02:00
+++ 1.378/sql/sql_select.cc 2005-11-24 10:10:32 +02:00
@@ -1017,6 +1017,11 @@
tmp_table_param.hidden_field_count= (all_fields.elements -
fields_list.elements);
+ bool is_using_loose_index_scan=
+ (join_tab->select && join_tab->select->quick &&
+ (join_tab->select->quick->get_type() ==
+ QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX));
+
if (!(exec_tmp_table1 =
create_tmp_table(thd, &tmp_table_param, all_fields,
((!simple_group && !procedure &&
@@ -1027,7 +1032,7 @@
select_options,
(order == 0 || skip_sort_order) ? select_limit :
HA_POS_ERROR,
- (char *) "")))
+ (char *) "", is_using_loose_index_scan)))
DBUG_RETURN(1);
/*
@@ -1410,6 +1415,11 @@
else
{
/* group data to new table */
+ bool is_using_loose_index_scan=
+ (curr_join->join_tab->select && curr_join->join_tab->select->quick &&
+ (curr_join->join_tab->select->quick->get_type() ==
+ QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX));
+
if (!(curr_tmp_table=
exec_tmp_table2= create_tmp_table(thd,
&curr_join->tmp_table_param,
@@ -1419,7 +1429,8 @@
!curr_join->group_list,
1, curr_join->select_options,
HA_POS_ERROR,
- (char *) "")))
+ (char *) "",
+ is_using_loose_index_scan)))
DBUG_VOID_RETURN;
curr_join->exec_tmp_table2= exec_tmp_table2;
}
@@ -8252,6 +8263,7 @@
rows_limit
table_alias possible name of the temporary table that can be used
for name resolving; can be "".
+ sum_funcs_as_regular treat aggregate functions as regular functions
DESCRIPTION
Given field pointers are changed to point at tmp_table for
@@ -8274,11 +8286,12 @@
create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
ORDER *group, bool distinct, bool save_sum_fields,
ulonglong select_options, ha_rows rows_limit,
- char *table_alias)
+ char *table_alias, bool sum_funcs_as_regular)
{
MEM_ROOT *mem_root_save, own_root;
TABLE *table;
uint i,field_count,null_count,null_pack_length;
+ uint copy_func_count= param->func_count;
uint hidden_null_count, hidden_null_pack_length, hidden_field_count;
uint blob_count,group_null_items, string_count;
uint temp_pool_slot=MY_BIT_NONE;
@@ -8342,6 +8355,16 @@
field_count=param->field_count+param->func_count+param->sum_func_count;
hidden_field_count=param->hidden_field_count;
+ /*
+ When loose index scan is employed as access method, it already
+ computes the result of all aggregate functions. We make space for
+ the items of the aggregate function in the list of functions
+ TMP_TABLE_PARAM::items_to_copy, so that the values of these items
+ are stored in the temporary table.
+ */
+ if (sum_funcs_as_regular)
+ copy_func_count+= param->sum_func_count;
+
init_sql_alloc(&own_root, TABLE_ALLOC_BLOCK_SIZE, 0);
if (!multi_alloc_root(&own_root,
@@ -8349,7 +8372,7 @@
®_field, sizeof(Field*) * (field_count+1),
&blob_field, sizeof(uint)*(field_count+1),
&from_field, sizeof(Field*)*field_count,
- ©_func, sizeof(*copy_func)*(param->func_count+1),
+ ©_func, sizeof(*copy_func)*(copy_func_count+1),
¶m->keyinfo, sizeof(*param->keyinfo),
&key_part_info,
sizeof(*key_part_info)*(param->group_parts+1),
@@ -9242,6 +9265,20 @@
{
TABLE *table= join->tmp_table;
Next_select_func end_select;
+
+ /*
+ True if data is accessed via QUICK_GROUP_MIN_MAX_SELECT. This quick
+ select access method guarantees that there are no duplicate result
+ rows, therefore grouping/distinct has already been done inside it.
+ When this is the case, below do not use the 'end_[write/sent]_group'
+ procedures, because they do extra work, and they corrupt the record
+ data in this case.
+ */
+ bool is_using_loose_index_scan=
+ (join->join_tab->select && join->join_tab->select->quick &&
+ (join->join_tab->select->quick->get_type() ==
+ QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX));
+
/* Set up select_end */
if (table)
{
@@ -9258,7 +9295,7 @@
end_select=end_unique_update;
}
}
- else if (join->sort_and_group)
+ else if (join->sort_and_group && !is_using_loose_index_scan)
{
DBUG_PRINT("info",("Using end_write_group"));
end_select=end_write_group;
@@ -9267,19 +9304,28 @@
{
DBUG_PRINT("info",("Using end_write"));
end_select=end_write;
+ if (is_using_loose_index_scan)
+ {
+ /*
+ A preceding call to create_tmp_table in the case when loose
+ index scan is used guarantees that
+ TMP_TABLE_PARAM::items_to_copy has enough space for the group
+ by functions. It is OK here to use memcpy since we copy
+ Item_sum pointers into an array of Item pointers.
+ */
+ TMP_TABLE_PARAM *tmp_tbl= &join->tmp_table_param;
+ memcpy(tmp_tbl->items_to_copy + tmp_tbl->func_count,
+ join->sum_funcs,
+ sizeof(Item*)*tmp_tbl->sum_func_count);
+ tmp_tbl->items_to_copy[tmp_tbl->func_count+tmp_tbl->sum_func_count]= 0;
+ }
}
}
else
{
- /* Test if data is accessed via QUICK_GROUP_MIN_MAX_SELECT. */
- bool is_using_quick_group_min_max_select=
- (join->join_tab->select && join->join_tab->select->quick &&
- (join->join_tab->select->quick->get_type() ==
- QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX));
-
if ((join->sort_and_group ||
(join->procedure && join->procedure->flags & PROC_GROUP)) &&
- !is_using_quick_group_min_max_select)
+ !is_using_loose_index_scan)
end_select= end_send_group;
else
end_select= end_send;
@@ -10553,7 +10599,6 @@
{
copy_fields(&join->tmp_table_param);
copy_funcs(join->tmp_table_param.items_to_copy);
-
#ifdef TO_BE_DELETED
if (!table->uniques) // If not unique handling
{
--- 1.102/sql/sql_select.h 2005-11-03 15:21:19 +02:00
+++ 1.103/sql/sql_select.h 2005-11-24 10:10:33 +02:00
@@ -399,7 +399,7 @@
TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
ORDER *group, bool distinct, bool save_sum_fields,
ulonglong select_options, ha_rows rows_limit,
- char* alias);
+ char* alias, bool sum_funcs_as_regular);
TABLE *create_virtual_tmp_table(THD *thd, List<create_field> &field_list);
void free_tmp_table(THD *thd, TABLE *entry);
void count_field_types(TMP_TABLE_PARAM *param, List<Item> &fields,
--- 1.297/sql/sql_show.cc 2005-11-17 23:37:27 +02:00
+++ 1.298/sql/sql_show.cc 2005-11-24 10:10:33 +02:00
@@ -3474,7 +3474,7 @@
field_list, (ORDER*) 0, 0, 0,
(select_lex->options | thd->options |
TMP_TABLE_ALL_COLUMNS),
- HA_POS_ERROR, table_list->alias)))
+ HA_POS_ERROR, table_list->alias, FALSE)))
DBUG_RETURN(0);
table_list->schema_table_param= tmp_table_param;
DBUG_RETURN(table);
--- 1.180/sql/sql_update.cc 2005-11-02 14:43:23 +02:00
+++ 1.181/sql/sql_update.cc 2005-11-24 10:10:33 +02:00
@@ -1100,7 +1100,7 @@
(ORDER*) &group, 0, 0,
TMP_TABLE_ALL_COLUMNS,
HA_POS_ERROR,
- (char *) "")))
+ (char *) "", FALSE)))
DBUG_RETURN(1);
tmp_tables[cnt]->file->extra(HA_EXTRA_WRITE_CACHE);
}
--- 1.130/sql/sql_union.cc 2005-10-13 10:52:53 +03:00
+++ 1.131/sql/sql_union.cc 2005-11-24 10:10:33 +02:00
@@ -122,7 +122,7 @@
if (! (table= create_tmp_table(thd, &tmp_table_param, *column_types,
(ORDER*) 0, is_union_distinct, 1,
- options, HA_POS_ERROR, (char*) alias)))
+ options, HA_POS_ERROR, (char*) alias, FALSE)))
return TRUE;
table->file->extra(HA_EXTRA_WRITE_CACHE);
table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
--- 1.17/mysql-test/r/group_min_max.result 2005-09-25 16:43:52 +03:00
+++ 1.18/mysql-test/r/group_min_max.result 2005-11-24 10:10:32 +02:00
@@ -2002,3 +2002,34 @@
1 1
NULL 1
drop table t1;
+create table t1 (c1 int not null,c2 int not null, primary key(c1,c2));
+insert into t1 (c1,c2) values
+(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);
+select distinct c1, c2 from t1 order by c2;
+c1 c2
+10 1
+10 2
+10 3
+20 4
+20 5
+20 6
+30 7
+30 8
+30 9
+select c1,min(c2) as c2 from t1 group by c1 order by c2;
+c1 c2
+10 1
+20 4
+30 7
+select c1,c2 from t1 group by c1,c2 order by c2;
+c1 c2
+10 1
+10 2
+10 3
+20 4
+20 5
+20 6
+30 7
+30 8
+30 9
+drop table t1;
--- 1.15/mysql-test/t/group_min_max.test 2005-09-25 16:43:53 +03:00
+++ 1.16/mysql-test/t/group_min_max.test 2005-11-24 10:10:32 +02:00
@@ -693,3 +693,16 @@
insert into t1 values(1);
select a, count(a) from t1 group by a with rollup;
drop table t1;
+
+#
+# Bug #14920 Ordering aggregated result sets with composite primary keys
+# corrupts resultset
+#
+
+create table t1 (c1 int not null,c2 int not null, primary key(c1,c2));
+insert into t1 (c1,c2) values
+(10,1),(10,2),(10,3),(20,4),(20,5),(20,6),(30,7),(30,8),(30,9);
+select distinct c1, c2 from t1 order by c2;
+select c1,min(c2) as c2 from t1 group by c1 order by c2;
+select c1,c2 from t1 group by c1,c2 order by c2;
+drop table t1;
| Thread |
|---|
| • bk commit into 5.0 tree (timour:1.1981) BUG#14920 | timour | 24 Nov |
