List:Commits« Previous MessageNext Message »
From:Evgeny Potemkin Date:December 10 2009 4:41pm
Subject:Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3200)
Bug#47650
View as plain text  
Hi Martin,

I think it's enough to check whether there is rollup & tables >1 & at least 1 
outer join.
Also, please add a test(select & explain) showing that same query nut with inner 
query works and works without tmp table.


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))
>      {
> 
> 
> 
> ------------------------------------------------------------------------
> 
> 
Thread
bzr commit into mysql-5.1-bugteam branch (martin.hansson:3200)Bug#47650Martin Hansson16 Nov
  • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3200)Bug#47650Gleb Shchepa19 Nov
    • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3200)Bug#47650Martin Hansson19 Nov
      • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3200)Bug#47650Gleb Shchepa19 Nov
        • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3200)Bug#47650Martin Hansson20 Nov
          • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3200)Bug#47650Gleb Shchepa20 Nov
  • Re: bzr commit into mysql-5.1-bugteam branch (martin.hansson:3200)Bug#47650Evgeny Potemkin10 Dec