List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 19 2001 11:43am
Subject:COUNT(DISTINCT) -- is this correct behaviour?
View as plain text  
Hi!

Thanks for the nice test case!

Next time you have as nice a test case, please send it to
bugs@stripped for fast treatment!

>>>>> "tommie" == tommie  <tommie@stripped> 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
Thread
COUNT(DISTINCT) -- is this correct behaviour?Michael Widenius19 Jun