| Bug #13975 | "same string" + 0 has 2 different results | ||
|---|---|---|---|
| Submitted: | 12 Oct 2005 19:33 | Modified: | 22 May 2006 20:15 |
| Reporter: | Martin Friebe (Gold Quality Contributor) (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.14, 4.1.16-BK, 5.0.15-rc-BK | OS: | Linux (Linux, Windows, freebsd) |
| Assigned to: | Jim Winstead | CPU Architecture: | Any |
[14 Oct 2005 16:52]
Valeriy Kravchuk
Thank you for a bug report.
Verified on 4.1.16-BK (ChangeSet@1.2472.1.2, 2005-10-13 01:14:58+04:00, evgen@moonbone.local):
mysql> select "18383815659218730760" + 0;
+----------------------------+
| "18383815659218730760" + 0 |
+----------------------------+
| 1.8383815659219e+19 |
+----------------------------+
1 row in set (0,00 sec)
mysql> select conv("18383815659218730760",10,10) + 0;
+----------------------------------------+
| conv("18383815659218730760",10,10) + 0 |
+----------------------------------------+
| 18383815659218728960 |
+----------------------------------------+
1 row in set (0,01 sec)
mysql> select conv("18383815659218730760",10,10);
+------------------------------------+
| conv("18383815659218730760",10,10) |
+------------------------------------+
| 18383815659218730760 |
+------------------------------------+
1 row in set (0,00 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 4.1.16-debug |
+--------------+
1 row in set (0,00 sec)
According to the manual (http://dev.mysql.com/doc/refman/4.1/en/string-functions.html), conv() should work with 64-bit precision. I like it's precise results, personally, but these 3 different results are really strange, and should be described in the manual, if this "precision" of conv() is intended behaviour.
By the way, 5.0.13-rc-nt gives different results:
mysql> select conv("18383815659218730760",10,10) + 0;
+----------------------------------------+
| conv("18383815659218730760",10,10) + 0 |
+----------------------------------------+
| 18383815659218727000 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select "18383815659218730760" + 0;
+----------------------------+
| "18383815659218730760" + 0 |
+----------------------------+
| 1.8383815659219e+019 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select conv("18383815659218730760",10,10);
+------------------------------------+
| conv("18383815659218730760",10,10) |
+------------------------------------+
| 18383815659218730760 |
+------------------------------------+
1 row in set (0.00 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.00 sec)
And 5.0.15-BK on Linux (ChangeSet@1.2024.1.20, 2005-10-12 22:44:42-07:00, patg@krsna.patg.net) - another, but still different:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.15-rc
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select conv("18383815659218730760",10,10) + 0;
+----------------------------------------+
| conv("18383815659218730760",10,10) + 0 |
+----------------------------------------+
| 18383815659218731008 |
+----------------------------------------+
1 row in set (0,00 sec)
mysql> select conv("18383815659218730760",10,10);
+------------------------------------+
| conv("18383815659218730760",10,10) |
+------------------------------------+
| 18383815659218730760 |
+------------------------------------+
1 row in set (0,00 sec)
mysql> select "18383815659218730760" + 0;
+----------------------------+
| "18383815659218730760" + 0 |
+----------------------------+
| 1.8383815659219e+19 |
+----------------------------+
1 row in set (0,00 sec)
[18 Oct 2005 1:53]
Bugs System
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/internals/31203
[24 Nov 2005 1:03]
Bugs System
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/internals/32630
[18 May 2006 17:46]
Bugs System
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/6582
[18 May 2006 20:16]
Jim Winstead
Fixed in 5.0.22. Still awaiting merge to 5.1.
[20 May 2006 6:08]
Jim Winstead
Fixed in 5.0.22 and 5.1.11.
[22 May 2006 20:15]
Paul DuBois
Noted in 5.0.22, 5.1.11 changelogs. The result from <literal>CONV()</literal> is a string, but was not always treated the same way as a string when converted to a real value for an arithmetic operation.
Description: Adding a string and an intefer, is done via float. Thios results in the loss of precission, which is correct. Also the function "conv()" returns a string, according to the documentation. So "conv (x,10,10)" returns the same string x. select conv("18383815659218730760",10,10); +------------------------------------+ | conv("18383815659218730760",10,10) | +------------------------------------+ | 18383815659218730760 | returns the same number, as string select conv("18383815659218730760",10,10) + 0; +----------------------------------------+ | conv("18383815659218730760",10,10) + 0 | +----------------------------------------+ | 18383815659218726912 | select "18383815659218730760" + 0; +----------------------------+ | "18383815659218730760" + 0 | +----------------------------+ | 1.8383815659219e+19 | both selects add 0 to the same stringified number. yet the result differs. It has a different precission. And in that terms, the 2nd result, is more correct, as it indicates the loss of precission in the result. while the 1st result, pretends to have the full precission, which it does not have. It works also correct if you cast the result from conv to char select cast(conv("18383815659218730760",10,10) as char) + 0; 1.8383815659219e+19 How to repeat: select conv("18383815659218730760",10,10) + 0; select "18383815659218730760" + 0; Suggested fix: the result, from conv(), should behave like any other string, as it is documented to be a string. Loss of precission, should be visible in the result.