MySQL Bugs: #58878: UPDATE fails when DECIMAL column value is quoted

Bug #58878	UPDATE fails when DECIMAL column value is quoted
Submitted:	10 Dec 2010 21:20	Modified:	2 Mar 2011 19:45
Reporter:	Tuan Hoang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.55	OS:	Linux (CentOS 5.5)
Assigned to:		CPU Architecture:	Any
Tags:	decimal, quoted value, UPDATE

Description:
I'm using CentOS Linux 5.5 and have back-ported MySQL 5.1.52 using the source RPM from the Fedora Repository.

I have a table where I use the decimal(20,6) column type to store two UNIX timestamps (txTime and rxTime) of a data packet.  When I try to update the table with the txTime value quoted, my query fails to update any rows.

This query has worked throughout the 5.0.x series and at the very least up through the 5.1.47 release.  I recently upgraded to 5.1.52 and found this change in behavior.

FWIW, I experience this problem with several client applications.
- My application which uses the Qt4 SQL module
- MySQL command-line client
- MySQL Query Browser (yes I know this is old but can't get MySQL Workbench compiled yet)

How to repeat:
CREATE TABLE test(id int unsigned, txTime decimal(20,6), rxTime decimal(20,6), txName varchar(64), rxName varchar(64));

INSERT INTO test VALUES(37,1291235101.528541,NULL,"host1",NULL);
INSERT INTO test VALUES(37,"1291235101.528541",NULL,"host1",NULL);

This works:
UPDATE test SET rxTime=0.0, rxName="host2" 
WHERE (id=37 AND txTime=1291235101.528541 AND txName="host1");

This does not work in v5.1.52 (perhaps earlier and later):
UPDATE test SET rxTime=0.0, rxName="host2" 
WHERE (id=37 AND txTime="1291235101.528541" AND txName="host1");

Suggested fix:
Make both the quoted and un-quoted values work.

Single quotes don't work either (MySQL 5.1.53 Win64 server):

UPDATE test SET rxTime=0.0, rxName="host2" 
WHERE (id=37 AND txTime='1291235101.528541' AND txName="host1");
-- 0 rows affected

This is definitely a change of behavior and will break lots of applications! Priority fix, please!

well .. a simpler test case works OK.

DROP TABLE IF EXISTS `dbtest`;

CREATE TABLE `dmaltest` (
  `id` int(11) DEFAULT NULL,
  `dmal` decimal(9,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO `dmaltest` values (1,1.1);

UPDATE dmaltest SET dmal = '2.2' WHERE db = '1.1';

.. so it seems the original test case by Tuan is an 'edge case'.

The claim is "This query has worked throughout the 5.0.x series"
but I can't repeat that, I see zero rows updated with MySQL
version 5.0.87, no behaviour change. Also with 5.0.87, I see
"
mysql> select '1291235101.528541' = 1291235101.528541;
+-----------------------------------------+
| '1291235101.528541' = 1291235101.528541 |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (0.00 sec)
"
which is probably the case documented here:
http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html
and that case is not a bug.

Incidentally we might in some future version disallow
""s as a substitute for ''s. That's unofficial though,
don't take this as a deprecation warning.

@Peter G

Is it 'floating point logic' described on that page your are referring to?  If DECIMAL is sometimes converted to a floating point this contradicts documentation about DECIMAL type as far as I can see.

You mix string and DECIMAL in one expression. Manual, http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html, clearly says:

"The following rules describe how conversion occurs for comparison operations:

- If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
- If both arguments in a comparison operation are strings, they are compared as strings.
- If both arguments are integers, they are compared as integers.
- Hexadecimal values are treated as binary strings if not compared to a number.
- If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
- In all other cases, the arguments are compared as floating-point (real) numbers."

Consider all cases above to find out that your is the last one, so both DECIMAL and string are converted to floating-point numbers and then compared. We have dozens of bug reports about this, we may not agree with the decision made, we get results we do not expect with *some* values, but it is not a bug formally.

Something has changed recently that is causing this problem.  My Qt v3 application uses a prepared statement to perform the UPDATE query.  I keep the DECIMAL columns (txTime and rxTime) as strings inside my C++ application so I don't introduce any floating point errors.  Then I bind them to the prepared statement for the UPDATE query.

This has worked fine in MySQL 5.0.74sp1 and a few earlier enterprise releases where I've updated a few million rows without problems.  Also when I upgraded to 5.1.47 for fast index creation (it was latest at the time), I've performed the same UPDATE query on databases with 30-50 million rows also without problems.  Keeping everything the same, except for upgrading MySQL 5.1.52, I get unexpected behavior and probably 20% of my UPDATE queries fail.  Switching the database server back to 5.1.47 resolves the problem.

So I'm a little confused on something here.  Isn't it true that if I quote or don't quote my DECIMAL values in my UPDATE query, then they both result in a floating point comparison?  It seems like the string->floating point conversion has changed in behavior.  Perhaps it's rounding now instead of truncating before the comparison?

Going with what Peter L. said above, not having a special handling case for DECIMAL is a bug.  The entire purpose of using the DECIMAL type is to have REAL number type checking but with limited precision so we don't have floating point errors in comparisons.

Yes, I was referring to the manual example, though
Mr Kravchuk's excellent reply supersedes mine.
No, there's no contradiction with documentation,
or at least there's no specific reference that
indicates such a thing.

Personally I don't quite see the point of the DECIMAL type if I can't *predictably* compare it with anything.  I'd expect this behavior if I was using a FLOAT/REAL/DOUBLE, hence my usage of the DECIMAL type.  

But let's agree to disagree.  What is the recommend way of comparing the DECIMAL type in my WHERE clause?  Use the CAST function?

Workaround #1:
UPDATE test SET rxTime='0.0', rxName='host2'
WHERE (id=37 AND CAST(txTime AS CHAR(21))='1291235101.528541' AND txName='host1');

Workaround #2:
UPDATE test SET rxTime='0.0', rxName='host2'
WHERE (id=37 AND txTime=CAST('1291235101.528541' AS DECIMAL(20,6)) AND txName='host1');

The only problem is that with #2, I don't think a prepared statement will work.

As I understand it will work if you don't "quote" or 'quote' numbers. But I don't know if your connector returns the information in the result set.  I think there could be a problem with PHP (with C-API the result set has the information).

In my Qt3 application, I'm using a prepared statement and I'm not explicitly quoting anything.  The values are QString objects that are bound to the prepared statement (i.e. the UPDATE query) and then it's executed.

I will try the CAST() function and see if that fixes my problem.

Ok, so doing the UPDATE statements with the query browser work with the CAST() function.  But I can't seem to get my C++ prepared statement to work with the CAST() function.

In my original WHERE clause, I used this comparison with the DECIMAL txTime and the bound value which was a QString...this works with MySQL 5.1.47:
  (txTime = ?)

Without changing the database version, I tried adding the CAST() function and it doesn't work...I get zero hits with my WHERE clause now.
  (CAST(txTime) AS CHAR(21) = ?)
I also tried with extra parens:
  ((CAST(txTime) AS CHAR(21)) = ?)

I even tried changing it back to original (txTime=?) but instead converted my QString to a double with (QString::toDouble()) when binding and as I suspected, the floating point errors introduced cause random problems.

So this begs the question...how am I supposed to reliably match a DECIMAL's value with a prepared statement?

I am not sure about the details of your environment/connector, but why not to cast value to the type of column, DECIMAL in this case. Like this:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.54-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> prepare stmt from 'select 1.1 = cast(? as decimal(15,10))';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @a='1.1';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a;
+---------------------------------+
| 1.1 = cast(? as decimal(15,10)) |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.01 sec)

mysql> set @a='1.11';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a;
+---------------------------------+
| 1.1 = cast(? as decimal(15,10)) |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set (0.00 sec)

Converting DECIMAL column to FLOAT or CHAR(N) will not help to force comparison values as DECIMALs, with defined precision...

@Valeriy - Thank you for the help.  My Qt application's prepared statement is working with the CAST() function against 5.1.47 now.  

I will test against 5.1.52 soon to make sure that it's still behaving correctly...please don't mark this as closed until I do so though.

Set status to "Need Feedback" as this is more appropriate status.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

All - Sorry for the delay, I had to wait until the server could be brought offline.  All is working fine and tested against my custom MySQL v5.1.55 RPM, built using the Fedora 14 Source RPM.