MySQL Bugs: #23260: String converted to floating-point when comparing to DECIMAL type

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

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)

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.

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

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)

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

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.

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

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

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.

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.

Fix is also in the 5.0 and 5.1 main trees now, will be in 5.1.15.

Noted in 5.0.32, 5.1.15 changelogs.

Accuracy was improved for comparisons between DECIMAL columns and
numbers represented as strings.

Bug: http://bugs.mysql.com/bug.php?id=40067 it was marked as duplicate of this bug.