Bug #20832 SELECT FORMAT()...UNION SELECT FORMAT() broken on windows
Submitted: 3 Jul 2006 19:49 Modified: 5 Jul 2006 21:11
Reporter: Sheeri Cabral (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.19-nt OS:Windows (Windows XP Pro)
Assigned to: CPU Architecture:Any

[3 Jul 2006 19:49] Sheeri Cabral
Description:
When UNIONing SELECT FORMAT() statements, the FORMATting is way off, and rounds/truncates bizarrely.

This does not occur on 5.0.19-standard-log on Fedora Core 1, and I'm guessing it's because FORMAT defers to the OS for rounding, so something about the OS is interfering here.  I have no idea what, or why.

How to repeat:
mysql> select format(1.23,2),format(4.56,2);
+----------------+----------------+
| format(1.23,2) | format(4.56,2) |
+----------------+----------------+
| 1.23           | 4.56           |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> select format(1.23,2) union select format(4.56,2);
+----------------+
| format(1.23,2) |
+----------------+
| 1              |
| 4              |
+----------------+
2 rows in set (0.00 sec)

mysql> select format(123,2),format(4567,2);
+---------------+----------------+
| format(123,2) | format(4567,2) |
+---------------+----------------+
| 123.00        | 4,567.00       |
+---------------+----------------+
1 row in set (0.00 sec)

mysql> select format(123,2) union select format(4567,2);
+---------------+
| format(123,2) |
+---------------+
| 1             |
| 4             |
+---------------+
2 rows in set (0.00 sec)

mysql> select format(123.45,2) union select format(4567.89,2);
+------------------+
| format(123.45,2) |
+------------------+
| 12               |
| 4,               |
+------------------+
2 rows in set (0.00 sec)

Suggested fix:
No idea.  Sorry I can't be more helpful.
[3 Jul 2006 22:21] MySQL Verification Team
Thank you for the bug report. I was unable to repeat with current release
version. Could you please upgrade for 5.0.22.:

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-community-nt

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

mysql> select format(1.23,2),format(4.56,2);
+----------------+----------------+
| format(1.23,2) | format(4.56,2) |
+----------------+----------------+
| 1.23           | 4.56           |
+----------------+----------------+
1 row in set (0.06 sec)

mysql> select format(1.23,2) union select format(4.56,2);
+----------------+
| format(1.23,2) |
+----------------+
| 1.23           |
| 4.56           |
+----------------+
2 rows in set (0.00 sec)
<cut>

Thanks in advance.
[5 Jul 2006 21:11] Sheeri Cabral
I upgraded and it indeed is fixed.  Thank you.