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:
None 
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

[12 Oct 2005 19:33] Martin Friebe
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.
[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.