|
Test case:
create table t1 (a int, b int, index (a,b));
|
insert into t1 values (1,1), (1,2), (1,3), (2,1), (2,2), (3,1);
|
select SQL_CALC_FOUND_ROWS a, b from t1 group by a,b order by a fetch first 1 rows with ties;
|
expected result:
|
+------+------+
|
| a | b |
|
+------+------+
|
| 1 | 1 |
|
| 1 | 2 |
|
| 1 | 3 |
|
+------+------+
|
actual result:
|
+------+------+
|
| a | b |
|
+------+------+
|
| 1 | 1 |
|
| 1 | 2 |
|
| 1 | 3 |
|
| 2 | 1 |
|
| 2 | 2 |
|
| 3 | 1 |
|
+------+------+
|
The reason:
Due to the order by field is the beginning part of group by fields, so it will neglect order by field to avoid reorder when group by will order the records.
Below is the code in file sql_select.cc
/*
|
Remove ORDER BY in the following cases:
|
- GROUP BY is more specific. Example GROUP BY a, b ORDER BY a
|
- If there are aggregate functions and no GROUP BY, this always leads
|
to one row result, no point in sorting.
|
*/
|
if (test_if_subpart(group_list, order) ||
|
(!group_list && tmp_table_param.sum_func_count))
|
{
|
order= 0;
|
if (is_indexed_agg_distinct(this, NULL))
|
sort_and_group= 0;
|
}
|
But if the statement has with ties, it will use order fields to judge if the order fields have same values with previous records. So here will result error and can not know how to stop to get next record.
sql_select.cc:
|
if (join->send_records >= join->unit->lim.get_select_limit() &&
|
join->unit->lim.is_with_ties())
|
{
|
/*
|
Stop sending rows if the order fields corresponding to WITH TIES
|
have changed.
|
*/
|
int idx= test_if_item_cache_changed(join->order_fields);
|
if (idx >= 0)
|
join->do_send_rows= false;
|
}
|
Due to join->order_fields is null, idx will always equal -1 and join->do_send_rows always equal true.
To fix this bug, I update with following code.
--- a/sql/sql_select.cc
|
+++ b/sql/sql_select.cc
|
@@ -3960,7 +3960,7 @@ bool JOIN::make_aggr_tables_info()
|
*/
|
if (unit->lim.is_with_ties())
|
{
|
- if (alloc_order_fields(this, order, with_ties_order_count))
|
+ if (alloc_order_fields(this, order ? order : group_list, with_ties_order_count))
|
DBUG_RETURN(true);
|
}
|
|
|