Bug #23260 String converted to floating-point when comparing to DECIMAL type
Submitted: 13 Oct 2006 14:06 Modified: 5 Dec 2007 18:55
Reporter: Geert Vanderkelen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.0.26 OS:Any (*)
Assigned to: CPU Architecture:Any
Tags: backport_050030SP1, bfsm_2006_12_07, decimal, FLOAT
Triage: Triaged: D5 (Feature request)

[13 Oct 2006 14:06] Geert Vanderkelen
Description:
Currently when one compares a string with a column of DECIMAL type, it gets converted to a double-precision floating-point. From the manual:

"If a numeric expression contains any strings, they are converted to double-precision floating-point values and the expression is approximate."

Why not, if the comparing to a DECIMAL type, convert the string instead to decimal instead?

The current behaviour makes comparison of decimals inconsistant:

mysql> SELECT * FROM test1 WHERE adecimal = 1.5700;
+----------+----+
| adecimal | id |
+----------+----+
|   1.5700 |  1 | 
+----------+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM test1 WHERE adecimal = '1.5700';
Empty set (0.00 sec)

mysql> SELECT * FROM test1 WHERE adecimal = 16.00;
+----------+----+
| adecimal | id |
+----------+----+
|  16.0000 |  1 | 
+----------+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM test1 WHERE adecimal = '16.00';
+----------+----+
| adecimal | id |
+----------+----+
|  16.0000 |  1 | 
+----------+----+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS test1;
CREATE TABLE test1 (
`adecimal` DECIMAL(11,4) NOT NULL DEFAULT '0.0000',
`id` BIGINT(20) NOT NULL DEFAULT '0'
);

INSERT INTO test1 (id,adecimal) VALUES (1,1.5700);
INSERT INTO test1 (id,adecimal) VALUES (1,16.0000);

SELECT * FROM test1 WHERE adecimal = 1.5700;
SELECT * FROM test1 WHERE adecimal = '1.5700';
SELECT * FROM test1 WHERE adecimal = 16.00;
SELECT * FROM test1 WHERE adecimal = '16.00';

Suggested fix:
With this example:
  SELECT * FROM test1 WHERE adecimal = '1.5700';

When the left operand is of type DECIMAL, then the right operand should be converted to a DECIMAL as well, just like you would get using a CAST():

mysql> SELECT * FROM test1 WHERE adecimal = CAST('1.5700' AS DECIMAL);
+----------+----+
| adecimal | id |
+----------+----+
|   1.5700 |  1 | 
+----------+----+
1 row in set (0.00 sec)
[13 Oct 2006 14:10] Valeriy Kravchuk
Verified just as described. For me it looks like a feature request. '1.2345' in 5.x.y should be converted to DECIMAL, not to FLOAT or DOUBLE. But let the developers decide.
[13 Oct 2006 16:25] Shane Bester
I get the results as follows:
mysql> SELECT * FROM test1 WHERE adecimal = 1.5700;
+----------+----+
| adecimal | id |
+----------+----+
|   1.5700 |  1 |
+----------+----+
1 row in set (0.03 sec)

mysql> SELECT * FROM test1 WHERE adecimal = '1.5700';
+----------+----+
| adecimal | id |
+----------+----+
|   1.5700 |  1 |
+----------+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM test1 WHERE adecimal = 16.00;
+----------+----+
| adecimal | id |
+----------+----+
|  16.0000 |  1 |
+----------+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM test1 WHERE adecimal = '16.00';
+----------+----+
| adecimal | id |
+----------+----+
|  16.0000 |  1 |
+----------+----+
1 row in set (0.00 sec)

This is suse 9.3 x86, mysql-standard-5.0.26-linux-i686-glibc23
[13 Oct 2006 16:29] Shane Bester
hmm indeed it's inconsistent.
On win2000, mysql-5.0.26-win32:

mysql> SELECT * FROM test1 WHERE adecimal = 1.5700;
+----------+----+
| adecimal | id |
+----------+----+
|   1.5700 |  1 |
+----------+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM test1 WHERE adecimal = '1.5700';
Empty set (0.00 sec)

mysql> SELECT * FROM test1 WHERE adecimal = 16.00;
+----------+----+
| adecimal | id |
+----------+----+
|  16.0000 |  1 |
+----------+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM test1 WHERE adecimal = '16.00';
+----------+----+
| adecimal | id |
+----------+----+
|  16.0000 |  1 |
+----------+----+
1 row in set (0.00 sec)
[20 Oct 2006 6:25] Timothy Smith
Hi, Geert,

While I can see your point on this, I believe this is not a bug, as it's behaving in a consistent way as described in the manual.  Using CAST() is a way to get the exact conversion you need.

If we were to change this behavior, it would likely have more sweeping effects that we'd want, and could break existing apps.

I hope, if you disagree strongly, you'll let me know your reasoning.

Tim
[20 Oct 2006 6:33] Timothy Smith
Hmmm, re-evaluating this again; I need to talk with some other devs and find out a bit more about this.  Perhaps there is a way to handle string->decimal conversions without losing info.
[27 Oct 2006 3:27] Timothy Smith
Hi.  After some discussion internally, it's been decided that this can't be fixed as a bug in 5.0/5.1, and must wait, as a feature request, until at least 5.2.

I'll attempt to expose the deliberations that went into this.  The basic points which led to this decision are:

- 4.1 also will lose data when comparing strings against DECIMAL values.  For example (from 4.1.22):

mysql> create table t2 (adecimal decimal(32,30));                                         
Query OK, 0 rows affected (0.01 sec)                                                      
                                                                                          
mysql> insert into t2 values (1.57000000000000006217248937900);                           
Query OK, 1 row affected (0.00 sec)                                                       
                                                                                          
mysql> insert into t2 values (1.569999999999999840127884453977);                          
Query OK, 1 row affected (0.00 sec)                                                       

mysql> select * from t2 where adecimal =
'1.5700000000000000000000000000001';
+----------------------------------+
| adecimal                         |
+----------------------------------+
| 1.570000000000000062172489379009 |
| 1.569999999999999840127884453977 |
+----------------------------------+
2 rows in set (0.00 sec)

- Using quote marks is non-standard and it's not known what other DBMS allows that syntax.  In this sense, MySQL's current behavior may not be optimal, but it's not necessarily incompatible either.

- It was originally decided to do this because it's not possible to represent some values as DECIMAL, for example '2.5e32', even though it is a valid number.

The feature request is certainly valid, and we will do our best to get it into 5.2.  It will likely entail a noticable performance hit when comparing strings against decimals, relative to the current implementation.  But it will be more exact, which is probably a useful trade-off.

Of course, the general recommendation to not quote number values will be the most efficient solution in all cases.  I realize that some client bindings, etc., don't make that as easy as it should be.

I hope this is helpful.

Regards

Timothy
[10 Nov 2006 18:54] 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/15157

ChangeSet@1.2296, 2006-11-10 19:54:01+01:00, hartmut@mysql.com +1 -0
  Alternative decimal2double implementation using an algorithm 
  more similar to my_strtod() (and maybe even a bit faster due
  to less floating point divisions) 
  This should at least partially fix Bug #23260 for DECIMALs
  with a moderate number of total digits
[20 Dec 2006 0:42] 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/17190

ChangeSet@1.2354, 2006-12-19 17:42:26-07:00, tsmith@siva.hindu.god +1 -0
  Alternative decimal2double implementation using an algorithm
  more similar to my_strtod() (and maybe even a bit faster due
  to less floating point divisions).
  
  This should at least partially fix Bug #23260 for DECIMALs
  with a moderate number of total digits.
[20 Dec 2006 10:33] Joerg Bruehe
Fix is included in the 5.0.32 release build already,
but not yet merged to main trees - 
please reset status after mentioning it for 5.0.32.
[21 Dec 2006 14:41] Joerg Bruehe
Fix is also in the 5.0 and 5.1 main trees now, will be in 5.1.15.
[21 Dec 2006 19:44] Paul Dubois
Noted in 5.0.32, 5.1.15 changelogs.

Accuracy was improved for comparisons between DECIMAL columns and
numbers represented as strings.
[15 Oct 2008 21:35] Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=40067 it was marked as duplicate of this bug.