MySQL Bugs: #17500: Format() + GROUP BY + PHP error

Bug #17500	Format() + GROUP BY + PHP error
Submitted:	17 Feb 2006 2:28	Modified:	16 Dec 2006 14:09
Reporter:	Ian Lord	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.19-BK, 5.0.18-LOG	OS:	Linux (Linux, Freebsd 6.0)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
The format() command seems to return an invalid string when used in conjonction with a GROUP BY CLAUSE.

Seems to happen only in php using mysqli (That's the only environment in which I tested it, but it works when using the mysql command line utility)

How to repeat:
SETUP:
~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE `tTest` (`dTestAmount` decimal(9,2) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1

INSERT INTO `tTest` VALUES ('100000.00');
INSERT INTO `tTest` VALUES ('259099.99');
INSERT INTO `tTest` VALUES ('9999999.99');
INSERT INTO `tTest` VALUES ('125.22');
INSERT INTO `tTest` VALUES ('0.25');

QUERY:
~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT FORMAT(dTestAmount,2)
FROM `tTest`

This query will return proper result in PHP AND in MYSQL command line utility which is:
100000.00
259099.99
9999999.99
125.22
0.25

QUERY2:
~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT FORMAT( dTestAmount, 2 ) 
FROM `tTest` 
GROUP BY dTestAmount

This will return BOGUS results in PHP:
0.25
125.
100,
259,
9,99

But will product CORRECT results in Mysql command line utility:
mysql> SELECT FORMAT(dTestAmount,2)
    -> FROM `tTest`
    -> GROUP BY dTestAmount;
+-----------------------+
| FORMAT(dTestAmount,2) |
+-----------------------+
| 0.25                  |
| 125.22                |
| 100,000.00            |
| 259,099.99            |
| 9,999,999.99          |
+-----------------------+

Suggested fix:
No clue at all sorry :(

Btw: Query 1 result is: (For both php and mysql command line)

100,000.00
259,099.99
9,999,999.99
125.22
0.25

Not what I previously posted.
Sorry

Hi,

It might be related to bug #16678 about UTF-8 Connections...

Cause if I use --default-character-set=utf8 on the command line, I can reproduce the same problem...

Just strange I don't have the problem neither in PHP neither in command Line when I don't use group by

Verified just as described on 5.0.19-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql --default-character-set=utf8 -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.19

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

mysql> CREATE TABLE `tTest` (`dTestAmount` decimal(9,2) NOT NULL) ENGINE=MyISAM
 DEFAULT
    -> CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `tTest` VALUES ('100000.00');
IQuery OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `tTest` VALUES ('259099.99');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `tTest` VALUES ('9999999.99');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `tTest` VALUES ('125.22');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `tTest` VALUES ('0.25');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT FORMAT(dTestAmount,2)
    -> FROM `tTest`;
+-----------------------+
| FORMAT(dTestAmount,2) |
+-----------------------+
| 100,000.00            |
| 259,099.99            |
| 9,999,999.99          |
| 125.22                |
| 0.25                  |
+-----------------------+
5 rows in set (0.00 sec)

mysql> SELECT FORMAT(dTestAmount,2) FROM `tTest` GROUP BY dTestAmount;
+-----------------------+
| FORMAT(dTestAmount,2) |
+-----------------------+
| 0.25                  |
| 125.                  |
| 100,                  |
| 259,                  |
| 9,99                  |
+-----------------------+
5 rows in set (0.00 sec)

Can't repeat. Tested on 5.0.32.
Most probably duplicate of the bug#16678.