Bug #19667 group by a decimal expression yields wrong result
Submitted: 10 May 2006 8:57 Modified: 23 Jun 2006 5:06
Reporter: Hiroshi Takenaka Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.21 OS:FreeBSD (FreeBSD)
Assigned to: Sergei Glukhov CPU Architecture:Any

[10 May 2006 8:57] Hiroshi Takenaka
Description:
'select EXPR, count(distinct COL) ... group by 1' yields a wrong result
when EXPR contains a cast to decimal, like 'integer_column + 0.0' or
'cast(integer_column to decimal(..))'.

This is the same issue as Bug #19631, marked as 'not a bug' unfortunately.
I'll post this again with a patch, because now I'm sure where the problem lies.

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,3), (2,4);
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 |                   4 |
+---------+---------------------+
1 row in set (0.00 sec)

Suggested fix:
This problem is caused by a tiny bug
in item_buff.cc: bool Cached_item_decimal::cmp().

Apply following patch:

*** item_buff.cc        Wed May 10 17:45:14 2006
--- item_buff.cc.orig   Wed May 10 17:45:06 2006
***************
*** 132,138 ****
  {
    my_decimal tmp;
    my_decimal *ptmp= item->val_decimal(&tmp);
!   if (null_value != item->null_value || my_decimal_cmp(&value, ptmp) != 0)
    {
      null_value= item->null_value;
      my_decimal2decimal(ptmp, &value);
--- 132,138 ----
  {
    my_decimal tmp;
    my_decimal *ptmp= item->val_decimal(&tmp);
!   if (null_value != item->null_value || my_decimal_cmp(&value, ptmp) == 0)
    {
      null_value= item->null_value;
      my_decimal2decimal(ptmp, &value);
[10 May 2006 12:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6190
[2 Jun 2006 14:10] Reggie Burnett
Sergey

Can you review this attached patch?
[15 Jun 2006 11:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7697
[15 Jun 2006 11:24] Sergei Glukhov
Fixed in 5.0.23
[23 Jun 2006 5:06] Paul Dubois
Noted in 5.0.23 changelog.

GROUP BY on an expression that contained a cast to DECIMAL produced 
an incorrect result.