#At file:///Users/martinhansson/bzr/bug47650/5.1bt-commit/ based on revid:alexey.kopytov@stripped
3200 Martin Hansson 2009-11-16
Bug#47650: using group by with rollup without indexes returns
incorrect results with where : worked on fix.
An ugly hack that serves to prove a point. If this ugly hack
turns out to do the job, we could base the fix on it once we
implement it properly.
modified:
sql/sql_select.cc
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-11-12 17:14:07 +0000
+++ b/sql/sql_select.cc 2009-11-16 11:49:05 +0000
@@ -7080,6 +7080,46 @@ static void update_depend_map(JOIN *join
}
}
+static bool const_table_is_left_joined_and_sorted_by(JOIN *join, ORDER *order)
+{
+ if (join->tables && join->rollup.state == ROLLUP::STATE_INITED)
+ {
+ DBUG_PRINT("bug47650", ("query has ROLLUP"));
+ Item *first_order_item= order->item[0];
+ if (first_order_item->type() == Item::FUNC_ITEM)
+ {
+ DBUG_PRINT("bug47650", ("ORDER/GROUP BY %s(...)",
+ ((Item_func*)first_order_item)->func_name()));
+ Item *order_item= ((Item_func*)first_order_item)->arguments()[0];
+ if (order_item && order_item->type() == Item::FIELD_ITEM)
+ {
+ Item_field *order_field= ((Item_field*)order_item);
+ const char *table_name= order_field->table_name;
+ JOIN_TAB *order_by_join_tab= 0;
+ for (uint i= 0; i < join->tables; i++)
+ if (strcmp(table_name, join->join_tab[i].table->alias) == 0)
+ {
+ order_by_join_tab= join->join_tab + i;
+ break;
+ }
+ DBUG_PRINT("bug47650", ("table is %s", order_by_join_tab->table->alias));
+
+ if (join->tables > (order_by_join_tab - join->join_tab) &&
+ (order_by_join_tab + 1)->last_inner)
+ {
+ DBUG_PRINT("bug47650", ("%s is LEFT OUTER JOINed with %s",
+ order_by_join_tab->table ?
+ order_by_join_tab->table->alias : "<anonymous>",
+ (order_by_join_tab + 1)->table ?
+ (order_by_join_tab + 1)->table->alias : "<anonymous>"));
+ return TRUE;
+ }
+ }
+ }
+ }
+ return FALSE;
+}
+
/**
Remove all constants and check if ORDER only contains simple
@@ -7122,7 +7162,8 @@ remove_const(JOIN *join,ORDER *first_ord
for (order=first_order; order ; order=order->next)
{
table_map order_tables=order->item[0]->used_tables();
- if (order->item[0]->with_sum_func)
+ if (order->item[0]->with_sum_func ||
+ const_table_is_left_joined_and_sorted_by(join, order))
*simple_order=0; // Must do a temp table to sort
else if (!(order_tables & not_const_tables))
{
Attachment: [text/bzr-bundle] bzr/martin.hansson@sun.com-20091116114905-71xyz2y0w521q84r.bundle
