MySQL Bugs: #19667: group by a decimal expression yields wrong result

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

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);

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

Sergey

Can you review this attached patch?

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

Fixed in 5.0.23

Noted in 5.0.23 changelog.

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