MySQL Bugs: #31195: Unable to query certain DECIMAL data type values on 64-bit linux

Bug #31195	Unable to query certain DECIMAL data type values on 64-bit linux
Submitted:	25 Sep 2007 23:01	Modified:	25 Sep 2007 23:35
Reporter:	Jay Rossiter	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.22-Debian_0ubuntu6.06.3	OS:	Linux (Ubuntu Dapper Drake (6.06.1))
Assigned to:		CPU Architecture:	Any

Description:
Certain values in decimal columns return zero rows from the database when queried when there are actually multiple rows containing that value.

I am currently seeing two behaviors on two identical machines.

On machine 'a', the query returns zero rows when the decimal value is quoted, and the proper number of rows when the decimal is unquoted.

On machine 'b' the query returns zero rows when the decimal value is unquoted and the proper number when the decimal is quoted.

MySQL Configuration on the two machines is identical.

32-bit machine functions properly with both quoted and unquoted values.

How to repeat:
CREATE TABLE `t` (
  `d` decimal(12,8) default NULL
) ENGINE=MyISAM;

INSERT INTO `t` VALUES ('1.62265000'),
('1.11111100'),
('1.11111100'),
('1.11111120'),
('1.11111120'),
('1.11110100'),
('1.62265001'),
('1.62265100'),
('1.62265000'),
('1.62260000'),
('1.61700000'),
('1.61600000'),
('1.62265000'),
('1.62260000'),
('1.62265010');

mysql> select * from t where d = 1.62265000; 
+------------+
| d          |
+------------+
| 1.62265000 | 
| 1.62265000 | 
| 1.62265000 | 
+------------+
3 rows in set (0.01 sec)

mysql> select * from t where d = '1.62265000';
Empty set (0.00 sec)

mysql> select * from t where d = '1.62265010';
+------------+
| d          |
+------------+
| 1.62265010 | 
+------------+
1 row in set (0.00 sec)

mysql> select * from t where d = 1.62265010;  
+------------+
| d          |
+------------+
| 1.62265010 | 
+------------+
1 row in set (0.00 sec)

Of possible/probable relevance:

http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html

The relevant point is that strings in numeric comparisons will be converted to floating-point numbers (which are approximate). So the comparison may be inexact.

Good information, but that doesn't explain why the behavior works on one machine when the value is unquoted, and fails on the other (when it's unquoted).  In that situation, the comparisons are exact.

Machine A:
'1.62265000' = 1.62265000   -> 0
'1.62265000' = '1.62265000' -> 1

Machine B:
'1.62265000' = 1.62265000   -> 1
'1.62265000' = '1.62265000' -> 0

I wrote that comparison incorrectly - the first value of both should be unquoted (i.e. from the DB)

Machine A:
1.62265000 = 1.62265000   -> 0
1.62265000 = '1.62265000' -> 1

Machine B:
1.62265000 = 1.62265000   -> 1
1.62265000 = '1.62265000' -> 0

Thank you for the bug report. I wasn't able to repeat with latest source server:

[miguel@luar 5.0]$ bin/mysql -uroot db1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.50-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.0.50-debug        |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | unknown-linux-gnu   |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE `t` (
    ->   `d` decimal(12,8) default NULL
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO `t` VALUES ('1.62265000'),
    -> ('1.11111100'),
    -> ('1.11111100'),
    -> ('1.11111120'),
    -> ('1.11111120'),
    -> ('1.11110100'),
    -> ('1.62265001'),
    -> ('1.62265100'),
    -> ('1.62265000'),
    -> ('1.62260000'),
    -> ('1.61700000'),
    -> ('1.61600000'),
    -> ('1.62265000'),
    -> ('1.62260000'),
    -> ('1.62265010');
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> select * from t where d = 1.62265000;
+------------+
| d          |
+------------+
| 1.62265000 |
| 1.62265000 |
| 1.62265000 |
+------------+
3 rows in set (0.00 sec)

mysql> select * from t where d = '1.62265000';
+------------+
| d          |
+------------+
| 1.62265000 |
| 1.62265000 |
| 1.62265000 |
+------------+
3 rows in set (0.00 sec)

mysql> select * from t where d = '1.62265010';
+------------+
| d          |
+------------+
| 1.62265010 |
+------------+
1 row in set (0.00 sec)

mysql>  select * from t where d = 1.62265010;
+------------+
| d          |
+------------+
| 1.62265010 |
+------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[miguel@luar 5.0]$ cat /etc/issue
Fedora release 7 (Moonshine)
Kernel \r on an \m

[miguel@luar 5.0]$ file libexec/mysqld
libexec/mysqld: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped
[miguel@luar 5.0]$

how a string is converted to a double and back depends on the hardware and the OS, one cannot rely that it'll work identically on all architectures.