Re: Fwd: A column of the SELECT-part of a query returns allways NULL in some cases. - Sinisa Milivojevic - com.mysql.lists.bugs

2 messages in com.mysql.lists.bugsRe: Fwd: A column of the SELECT-part ...

From	Sent On	Attachments
Sinisa Milivojevic	08 Jun 2002 10:05
Michael Widenius	09 Jun 2002 05:27

Subject:	Re: Fwd: A column of the SELECT-part of a query returns allways NULL in some cases.
From:	Sinisa Milivojevic (sin...@mysql.com)
Date:	06/08/2002 10:05:06 AM
List:	com.mysql.lists.bugs

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; }