MySQL Bugs: #19631: count(distinct COL) yields wrong results when group-by key contains a cast

Bug #19631	count(distinct COL) yields wrong results when group-by key contains a cast
Submitted:	9 May 2006 10:18	Modified:	10 May 2006 11:46
Reporter:	Hiroshi Takenaka	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.21	OS:	FreeBSD (FreeBSD)
Assigned to:		CPU Architecture:	Any

Description:
'select EXPR, count(distinct COL) group by 1' yields a wrong result
when EXPR contains a cast from integer to float, like 'integer_column + 0.0'.

I'm not sure what is happening, but maybe it's returning only
the first record of the result on that situation.

How to repeat:
Here is my session log:
----
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.21-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tbl (foo integer, bar integer);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tbl values (1, 1), (1, 2), (2,1), (2,2);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select foo, count(distinct bar) from tbl group by 1;
+------+---------------------+
| foo  | count(distinct bar) |
+------+---------------------+
|    1 |                   2 |
|    2 |                   2 |
+------+---------------------+
2 rows in set (0.01 sec)

mysql> select foo+0.0, count(distinct bar) from tbl group by 1;
+---------+---------------------+
| foo+0.0 | count(distinct bar) |
+---------+---------------------+
|     1.0 |                   2 |
+---------+---------------------+
1 row in set (0.00 sec)

-- it seems ok when we add 0 to foo, instead of 0.0.

mysql> select foo+0, count(distinct bar) from tbl group by 1;
+-------+---------------------+
| foo+0 | count(distinct bar) |
+-------+---------------------+
|     1 |                   2 |
|     2 |                   2 |
+-------+---------------------+
2 rows in set (0.01 sec)

-- and simple sum(), count(), avg() ... seem to be ok.

mysql> select foo+0.0, sum(bar) from tbl group by 1;
+---------+----------+
| foo+0.0 | sum(bar) |
+---------+----------+
|     1.0 |        3 |
|     2.0 |        3 |
+---------+----------+
2 rows in set (0.00 sec)

Thank you for the bug report. Please read: http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html

Hmm..., you mean, both 1+0.0 and 2+0.0 are
evaluated into the same value '1.0' because of rounding error,
and hence that result?

If so, why my another query, 'select foo+0.0, sum(bar) ...'
returns a expected result, instead of:

+---------+----------+
| foo+0.0 | sum(bar) |
+---------+----------+
|     1.0 |        6 |
+---------+----------+

or alike?

AFAIK,  rounding errors are not something indeterminate.
Two same expressions should give the same result, isn't?

Reopened and marked as duplicte of bug #19667