MySQL Bugs: #8794: MySQL-4.1.9 on AIX adds commas to aggregate results of DECIMAL fields

Bug #8794	MySQL-4.1.9 on AIX adds commas to aggregate results of DECIMAL fields
Submitted:	24 Feb 2005 21:53	Modified:	3 Jul 2006 12:52
Reporter:	Mark Matthews	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.9	OS:	IBM AIX (aix)
Assigned to:		CPU Architecture:	Any

Description:
Hi Mark,

Thank you for your help.

I have the same problem when I execute the query with the MySQL client on my
AIX server :

mysql> select sum(AMFTPF) from DWH_AMF;

+-------------+
| sum(AMFTPF) |
+-------------+
|    12000,00 |
+-------------+

1 row in set (0,03 sec)

The problem occurs only when I use an operator like SUM, AVG, MAX, MIN...

mysql> select AMFTPF from DWH_AMF LIMIT 1;

+--------+
| AMFTPF |
+--------+
|  15.00 |
+--------+

1 row in set (0,01 sec)                     

Moreover the local on the client (swing GUI) is already set depending on the
preferences of the user that's connected.
Hence I cannot force it.

I don't think the problem is with Connector/J. I thing the getBigDecimal
method fail because of the comma returned by MySQL. But to me the MySQL
server shouldn't be returning a comma in that case.

It seems like a MySQL bug to me, but maybe I'm doing something wrong.

Regards,

Julien

-----Message d'origine-----
De : Mark Matthews [mailto:mark@mysql.com]
Envoyé : lundi 21 février 2005 16:24
À : LAFONTAINE Julien - LYO
Cc : mysql@lists.mysql.com
Objet : Re: Problem with SUM and DECIMAL field

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

LAFONTAINE Julien - LYO wrote:

>> Hi everyone,
>> 
>> I've recentely upgarded my servers from MySQL 3.23.49 to MySQL 4.1.9.
>> 
>> Now I have a problem when using the SUM operator on DECIMAL field.
>> 
>> The value returned by the SUM operator when used with DECIMAL field has a
>> coma (,) as decimal separator while it used to be have a dot (.) . If I
>> query my table to display the DECIMAL fields (SELECT * FROM ...)  I get a
>> dot as decimal separator as expected.
>> 
>> This doesn't look like a big issue but it prevents Connector/J from
>> retieving the data properly. Connector/J can't parse the value of the

field

>> as it's expecting a dot as decimal separator.
>> 
>> Here is the stack trace :
>> 
>> java.sql.SQLException: Bad format for BigDecimal '12660,95' in column 1( .
>> () . SUM(AMFTPF)()).
>>             at com.mysql.jdbc.ResultSet.getBigDecimal(ResultSet.java:493)
>> 
>> 
>> 
>> I'm using Connector/J 3.0.16.
>> 
>> One last thing : this seems to happen only on AIX. I have tried on Linux

and

>> Windows XP and everything works as expected.
>> 
>> Is there something wrong with some of my database parameters or is this a
>> bug ?
>> 
>> Please let me know if someone is interested by a testcase.
>> 
>> 
>> Reagrds,
>> 
>> Julien LAFONTAINE

How to repeat:
I have the same problem when I execute the query with the MySQL client on my
AIX server :

mysql> select sum(AMFTPF) from DWH_AMF;

+-------------+
| sum(AMFTPF) |
+-------------+
|    12000,00 |
+-------------+

1 row in set (0,03 sec)

The problem occurs only when I use an operator like SUM, AVG, MAX, MIN...

mysql> select AMFTPF from DWH_AMF LIMIT 1;

+--------+
| AMFTPF |
+--------+
|  15.00 |
+--------+

1 row in set (0,01 sec)

This problem occured with the following coonfiguration :

Server :
JDK 1.3.1 (J2RE 1.3.1 IBM AIX build ca1311-20011123a)
OS : AIX 4.3.3
MySQL : 4.1.9
Connector/J : 3.0.16

Client :
OS : Windows XP SP2
JDK 1.3.1 (build 1.3.1-b24)

Test case : 

CREATE TABLE `DWH_IRC`.`TEST` (
  `COL1` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `COL2` DECIMAL(4,2) NOT NULL,
  PRIMARY KEY(`COL1`)
)
TYPE = InnoDB;

mysql> INSERT INTO TEST VALUES (1, 1.2);

mysql> SELECT COL2 FROM TEST;

+------+
| COL2 |
+------+
| 1,20 |
+------+

1 row in set (0,00 sec)

mysql> SELECT SUM(COL2) FROM TEST;

+-----------+
| SUM(COL2) |
+-----------+
|      1,00 |
+-----------+
1 row in set (0,01 sec)

Then if I modify the value of COL2 from 1,20 to 1.20 with MySQL query browser, I get the folowing result :

mysql> SELECT COL2 FROM TEST;
+------+
| COL2 |
+------+
| 1.20 |
+------+
1 row in set (0,00 sec)

This query is ok, but I still have a problem with the second one :

mysql> SELECT SUM(COL2) FROM TEST;
+-----------+
| SUM(COL2) |
+-----------+
|      1,20 |
+-----------+
1 row in set (0,00 sec)

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

In which release of MySQL will this bug fix be incorporated ?
I have checked the change log og all the MySQL 4.1 release since 4.1.9 and I couldn't find it.

Hi all,

  I have exactly the same problem on all of our AIX servers.

Could somebody tell me if the bug was patched and in WHICH VERSION ???

Thank you in advance,
Dan

I've just made the test again, in  mysql 4.1.14  (mysql-standard-4.1.14-ibm-aix4.3.3.0-powerpc)
and the probles is STILL THERE.

Could someone give us a hint on WHEN this announced fix will be availble ?
In mars, Jorge del Conde said that the fix was  commited into the source tree, and that il would be availble in the "next version".... We are in October, more that 5 versions later and the bug is still there !

I would just like a short answer in order to understand what's hapenning...

Many thanks,
Dan

Please, try to repeat with a latest version, 4.1.20, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".