Hello Martin,
BTW your patch affects not only outer joins, but queries without
joins like
create table t1 (a int);
insert into t1 values (1), (2);
select * from t1 group by abs(a) with rollup;
where your function fails with a SEGV.
Thank you,
Gleb.
Martin Hansson wrote:
> #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))
> {
>
>
>
> ------------------------------------------------------------------------
>
>
