Hi!
Thanks for the nice test case!
Next time you have as nice a test case, please send it to
[EMAIL PROTECTED] for fast treatment!
>>>>> "tommie" == tommie <[EMAIL PROTECTED]> writes:
tommie> count() gives 0 but count(distinct) gives 1. is this correct or
tommie> a bug or is there something wrong with my SELECT?
mysql> create table t1 (f1 int);
mysql> insert into t1 values (1);
mysql> create table t2 (f1 int,f2 int);
mysql> select t1.f1,count(t2.f2) from t1
-> left join t2 on t1.f1=t2.f1 group by t1.f1;
tommie> +------+--------------+
tommie> | f1 | count(t2.f2) |
tommie> +------+--------------+
tommie> | 1 | 0 |
tommie> +------+--------------+
tommie> 1 row in set (0.00 sec)
mysql> select t1.f1,count(distinct t2.f2) from t1
-> left join t2 on t1.f1=t2.f1 group by t1.f1;
tommie> +------+-----------------------+
tommie> | f1 | count(distinct t2.f2) |
tommie> +------+-----------------------+
tommie> | 1 | 1 |
tommie> +------+-----------------------+
tommie> 1 row in set (0.01 sec)
Fix:
===== sql/item_sum.cc 1.7 vs edited =====
*** /tmp/item_sum.cc-1.7-18231 Sun Jan 28 21:35:50 2001
--- edited/sql/item_sum.cc Tue Jun 19 12:58:35 2001
***************
*** 809,816 ****
List<Item> list;
/* Create a table with an unique key over all parameters */
for (uint i=0; i < arg_count ; i++)
! if (list.push_back(args[i]))
! return 1;
count_field_types(tmp_table_param,list,0);
if (table)
{
--- 809,827 ----
List<Item> list;
/* Create a table with an unique key over all parameters */
for (uint i=0; i < arg_count ; i++)
! {
! Item *item=args[i];
! if (list.push_back(item))
! return 1; // End of memory
! if (item->const_item())
! {
! (void) item->val_int();
! if (item->null_value)
! always_null=1;
! }
! }
! if (always_null)
! return 0;
count_field_types(tmp_table_param,list,0);
if (table)
{
***************
*** 827,841 ****
void Item_sum_count_distinct::reset()
{
! table->file->extra(HA_EXTRA_NO_CACHE);
! table->file->delete_all_rows();
! table->file->extra(HA_EXTRA_WRITE_CACHE);
! (void) add();
}
bool Item_sum_count_distinct::add()
{
int error;
copy_fields(tmp_table_param);
copy_funcs(tmp_table_param->funcs);
--- 838,857 ----
void Item_sum_count_distinct::reset()
{
! if (table)
! {
! table->file->extra(HA_EXTRA_NO_CACHE);
! table->file->delete_all_rows();
! table->file->extra(HA_EXTRA_WRITE_CACHE);
! (void) add();
! }
}
bool Item_sum_count_distinct::add()
{
int error;
+ if (always_null)
+ return 0;
copy_fields(tmp_table_param);
copy_funcs(tmp_table_param->funcs);
===== sql/item_sum.h 1.5 vs edited =====
*** /tmp/item_sum.h-1.5-18231 Sat Jan 27 01:20:55 2001
--- edited/sql/item_sum.h Tue Jun 19 12:59:50 2001
***************
*** 145,155 ****
table_map used_table_cache;
bool fix_fields(THD *thd,TABLE_LIST *tables);
TMP_TABLE_PARAM *tmp_table_param;
public:
Item_sum_count_distinct(List<Item> &list)
:Item_sum_int(list),table(0),used_table_cache(~(table_map) 0),
! tmp_table_param(0)
{ quick_group=0; }
~Item_sum_count_distinct();
table_map used_tables() const { return used_table_cache; }
--- 145,156 ----
table_map used_table_cache;
bool fix_fields(THD *thd,TABLE_LIST *tables);
TMP_TABLE_PARAM *tmp_table_param;
+ bool always_null;
public:
Item_sum_count_distinct(List<Item> &list)
:Item_sum_int(list),table(0),used_table_cache(~(table_map) 0),
! tmp_table_param(0),always_null(0)
{ quick_group=0; }
~Item_sum_count_distinct();
table_map used_tables() const { return used_table_cache; }
The above will be in the next MySQL release.
Regards,
Monty
---------------------------------------------------------------------
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
