Victoria Reznichenko writes:
> Hi!
> Sinisa, i tested the following example on 4.0.1 and got the same
> result. If I don't use ORDER BY in the SELECT statement all worked
> fine:
>
> mysql> SELECT u.gender AS gender, count(DISTINCT u.id) AS dist_count,
>(count(DISTINCT u.id)/5*100) AS percentage from users_table u, log_table l where
>l.user_id = u.id GROUP BY u.gender;
> +--------+------------+------------+
> | gender | dist_count | percentage |
> +--------+------------+------------+
> | F | 3 | 60.00 |
> | M | 1 | 20.00 |
> +--------+------------+------------+
> 2 rows in set (0.02 sec)
>
> But if I use ORDER BY ....
>
> mysql> SELECT u.gender AS gender, count(DISTINCT u.id) AS dist_count,
>(count(DISTINCT u.id)/5*100) AS percentage from users_table u, log_table l where
>l.user_id = u.id GROUP BY u.gender order by percentage;
> +--------+------------+------------+
> | gender | dist_count | percentage |
> +--------+------------+------------+
> | NULL | 1 | 20.00 |
> | NULL | 3 | 60.00 |
> +--------+------------+------------+
> 2 rows in set (0.01 sec)
>
Thank you for your bug report which helped us fix a bug. A fix will
appear in 4.0.2.
This is a temporary fix patch :
===== sql/sql_select.cc 1.169 vs edited =====
*** /tmp/sql_select.cc-1.169-6056 Sun May 26 16:11:27 2002
--- edited/sql/sql_select.cc Sat Jun 8 19:37:29 2002
***************
*** 135,141 ****
static void calc_group_buffer(JOIN *join,ORDER *group);
static bool alloc_group_fields(JOIN *join,ORDER *group);
static bool make_sum_func_list(JOIN *join,List<Item> &fields);
! static bool change_to_use_tmp_fields(List<Item> &func);
static bool change_refs_to_tmp_fields(THD *thd, List<Item> &func);
static void init_tmptable_sum_functions(Item_sum **func);
static void update_tmptable_sum_func(Item_sum **func,TABLE *tmp_table);
--- 135,141 ----
static void calc_group_buffer(JOIN *join,ORDER *group);
static bool alloc_group_fields(JOIN *join,ORDER *group);
static bool make_sum_func_list(JOIN *join,List<Item> &fields);
! static bool change_to_use_tmp_fields(List<Item> &func, bool change=false);
static bool change_refs_to_tmp_fields(THD *thd, List<Item> &func);
static void init_tmptable_sum_functions(Item_sum **func);
static void update_tmptable_sum_func(Item_sum **func,TABLE *tmp_table);
***************
*** 788,794 ****
tmp_table=tmp_table2;
join.join_tab[0].table=0; // Table is freed
! if (change_to_use_tmp_fields(all_fields)) // No sum funcs anymore
goto err;
join.tmp_table_param.field_count+=join.tmp_table_param.sum_func_count;
join.tmp_table_param.sum_func_count=0;
--- 788,794 ----
tmp_table=tmp_table2;
join.join_tab[0].table=0; // Table is freed
! if (change_to_use_tmp_fields(all_fields,true)) // No sum funcs anymore
goto err;
join.tmp_table_param.field_count+=join.tmp_table_param.sum_func_count;
join.tmp_table_param.sum_func_count=0;
***************
*** 6764,6770 ****
*/
static bool
! change_to_use_tmp_fields(List<Item> &items)
{
List_iterator<Item> it(items);
Item *item_field,*item;
--- 6764,6770 ----
*/
static bool
! change_to_use_tmp_fields(List<Item> &items, bool change)
{
List_iterator<Item> it(items);
Item *item_field,*item;
***************
*** 6776,6781 ****
--- 6776,6786 ----
continue;
if (item->type() == Item::FIELD_ITEM)
{
+ if (change)
+ {
+ ((Item_field*) item)->result_field->null_ptr=0;
+ item->maybe_null=0;
+ }
((Item_field*) item)->field=
((Item_field*) item)->result_field;
}
--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
