List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 29 1999 5:45pm
Subject:bug with count distinct and order by
View as plain text  
>>>>> "ai" == ai  <ai@stripped> writes:

>> Description:
ai>         count distinct in combination with order by gives wrong results
>> How-To-Repeat:

<cut>

mysql> select i.areandx as area, count(*) as msgs,sum(i.size) as size,count(distinct
> i.fromndx) as usr from ndx as i group by i.areandx;
ai> +------+------+------+-----+
ai> | area | msgs | size | usr |
ai> +------+------+------+-----+
ai> |   38 |    1 | 1766 |   1 |
ai> |  141 |    2 | 1512 |   2 |
ai> +------+------+------+-----+
ai> 2 rows in set (0.00 sec)

mysql> select i.areandx as area, count(*) as msgs,sum(i.size) as size,count(distinct
> i.fromndx) as usr from ndx as i group by i.areandx order by size;
ai> +------+------+------+-----+
ai> | area | msgs | size | usr |
ai> +------+------+------+-----+
ai> |  141 |    2 | NULL |   2 |
ai> |  141 |    2 | NULL |   2 |
ai> |  141 |    2 | 1512 |   2 |
ai> +------+------+------+-----+
ai> 3 rows in set (0.00 sec)


Hi!

I did run this with 3.23.3:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.3-alpha-debug

Type 'help' for help.

mysql> select i.areandx as area, count(*) as msgs,sum(i.size) as size,count(distinct
> i.fromndx) as usr from ndx as i group by i.areandx;
+------+------+------+-----+
| area | msgs | size | usr |
+------+------+------+-----+
|   38 |    1 | 1766 |   1 |
|  141 |    2 | 1512 |   2 |
+------+------+------+-----+
2 rows in set (0.05 sec)

mysql> select i.areandx as area, count(*) as msgs,sum(i.size) as size,count(distinct
> i.fromndx) as usr from ndx as i group by i.areandx order by size;
+------+------+------+-----+
| area | msgs | size | usr |
+------+------+------+-----+
|  141 |    2 | 1512 |   2 |
|   38 |    1 | 1766 |   1 |
+------+------+------+-----+
2 rows in set (0.01 sec)

So it looks like I have already managed to solve this.
I will release 3.23 as soon as I have gone through all my mails and
checked that all reported bugs are solved.

Regards,
Monty
Thread
bug with count distinct and order byai22 Aug
  • bug with count distinct and order byMichael Widenius27 Aug
  • bug with count distinct and order byMichael Widenius29 Aug