MySQL Bugs: #44662: Inconsistent handling of decimal separator when using functions

Bug #44662	Inconsistent handling of decimal separator when using functions
Submitted:	5 May 2009 10:47	Modified:	15 Jul 2009 13:16
Reporter:	Harry Meyer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.34	OS:	MacOS (Leopard)
Assigned to:		CPU Architecture:	Any
Tags:	decimal separator

Description:
I am currently migrating a MySQL Server 4.1.10a running under SuSE Linux 9.3 to a MySQL Server 5.1.34 running under Mac OS X Leopard. Clients in on the local network access the DB using MyODBC 3.51. When one of these clients requests a result using a function on a decimal field, the decimal separator is not set properly:

In the old setup it correctly returns 12,34 for example, 
while in the new setup it would return 12.34

How to repeat:
0) We are in Germany and have all our machines set to german locale settings.

1) Have a table that stores amounts of euros for example in a column named amount of the type decimal(10,2)

2) Build a query that selects the column directly:

SELECT * FROM your_table;

3) Run that query against a 4.1.10a server (Linux) and against a 5.1.34 server (Mac OS X) using MyODBC 3.51 (although the MyODBC Version does NOT seem to be the problem!) In this special case here the client is MS Access 97 but any other client would probably show the same behaviour.

--> Result should look the same on either platform, the comma is the decimal separator.

4) Build a query, that uses a function, in my case it is:

SELECT SUM(amount) FROM your_table;

5) Run this qurey against the two setups described above. 

--> The 4.1.10a will return the result with a comma as the decimal separator as it should be, while the 5.1.34 will return a dot as the decimal separator which causes the clients to fail!

6) Modify the query from step 4 into:

SELECT CAST(SUM(amount) AS DECIMAL(10,2)) FROM your_table;

--> results will look identical again.

Suggested fix:
In the meantime modify all your queries using the CAST function.

Thank you for the report.

Please specify exact locale do you use (output of locale command).

The locale command in a terminal window on the Mac OS X Server produces the following output:

LANG="de_DE.UTF-8"
LC_COLLATE="de_DE.UTF-8"
LC_CTYPE="de_DE.UTF-8"
LC_MESSAGES="de_DE.UTF-8"
LC_MONETARY="de_DE.UTF-8"
LC_NUMERIC="de_DE.UTF-8"
LC_TIME="de_DE.UTF-8"
LC_ALL=

In the meantime I have been playing around with other versions, downgrading as far as to 5.0.24a. Since I cannot reproduce this behaviour on another machine with 5.0.26 running on SuSE Enterprise Linux 10.1, I get the suspicion, it is an Mac OS X related issue.

Thank you for the feedback.

How do you use ODBC? Do you use iodbctest or some C program or other interface like OO?

Pleas also run queries you use in mysql command line client started with option -T (versions 4.1/5.0) and option  --column-type-info in version 5.1 and paste its output: looks like result types have changed from 4.1.x to 5.1.x

The clients use MyODBC 3.51 (as said above) and the app is build with MS Access 97 (Yeah, I know it is an old thing, but there is no budget to build a new app).

In the meantime I converted all decimal columns on Mac OSX to double columns, which solved my issue. Sorry, but since the OSX System is in production now, I cannot revert back to the old status. So I could not run the test on the original database where the issue first occured. So I put up a new table for testing and manually entered some data. Hope this helps anyway.

Queries on Linux:
Output from locale command:
LANG=
LC_CTYPE=de_DE.UTF-8
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=

Query without function
Catalog:    'def'
Database:   'mydb'
Table:      'auszahlung'
Name:       'betrag'
Type:       0
Length:     12
Max length: 5
Is_null:    0
Flags:      32769
Decimals:   2

+--------+
| betrag |
+--------+
|  36.81 |
|  33.77 |
|  33.77 |
|  33.77 |
|  33.77 |
|  33.77 |
|  36.81 |
|  36.81 |
|  36.81 |
|  36.81 |
+--------+
10 rows in set (0.16 sec)

Query with function:
Catalog:    'def'
Database:   ''
Table:      ''
Name:       'sum(betrag)'
Type:       5
Length:     19
Max length: 7
Is_null:    1
Flags:      32896
Decimals:   2

+-------------+
| sum(betrag) |
+-------------+
|       25.00 |
|      100.00 |
|     2514.53 |
|      181.68 |
|      100.00 |
|      314.16 |
|       25.00 |
|      573.58 |
|       50.00 |
|       25.00 |
+-------------+
10 rows in set (0.00 sec)

Queries on Mac OSX
Field   1:  `betrag`
Catalog:    `def`
Database:   `test`
Table:      `new_table`
Org_table:  `new_table`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     12
Max_length: 5
Decimals:   2
Flags:      

+--------+
| betrag |
+--------+
|  10.00 | 
|  10.20 | 
|   1.09 | 
|   3.07 | 
|   2.25 | 
+--------+
5 rows in set (0.00 sec)

Field   1:  `SUM(betrag)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     34
Max_length: 5
Decimals:   2
Flags:      BINARY 

+-------------+
| SUM(betrag) |
+-------------+
|       26.61 | 
+-------------+
1 row in set (0.00 sec)

Many thanks for writing a bug report.

Unfortunately, this is not a bug.

You run into several issues here that all aren't MySQL related.

German Access 97 has had some localisation/globalisation bugs with German language (I remember lots of pains).

Maybe because of using homogeneous system you just didn't recognise this comma/dot pains.

Your Linux system is not homogenous German. Only German charmap but both collation and numeric (which is for double, float and so on) is set to posix which means English.

Depending on which kind of MAC additionally your MAC might be endian different to your Windows and Linux machines.

This all are factors for your described issue and as you are seeing nothing of it is a MySQL bug.

MySQL itself only support english number layout like most of the other DBMS too. Means German 3,14 is 3.14.