Bug #2005 Long decimal comparison bug.
Submitted: 3 Dec 2003 12:56 Modified: 10 Dec 2003 7:06
Reporter: Peter Zaitsev (Basic Quality Contributor)
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:4.0.17bk OS:Any
Assigned to: Ramil Kalimullin Target Version:

[3 Dec 2003 12:56] Peter Zaitsev 
Description:
Having very long DECIMAL field you can get different results from the query depending on
if you have indexes or you do not:

mysql> select * from dt where d =
"1111111111111111111111111111111111111111111111111111111";
+------------------------------------------------------------------------------+
| d                                                                            |
+------------------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111.00000000000000000000 |
| 1111111111111111111111111111111111111111111111111111112.00000000000000000000 |
| 1111111111111111111111111111111111111111111111111111111.20000000000000000000 |
+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> alter table dt add key(d);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from dt where d =
"1111111111111111111111111111111111111111111111111111111";
+------------------------------------------------------------------------------+
| d                                                                            |
+------------------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111.00000000000000000000 |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Note it also has effects on   >, < operators which return different and even not
consistent data ( ie some rows are none of "<" ">" or "=" for the given constant)

How to repeat:
CREATE TABLE dt (
  d decimal(240,20) default NULL,
  KEY d (d)
) TYPE=MyISAM;

--
-- Dumping data for table 'dt'
--

INSERT INTO dt VALUES
(1111111111111111111111111111111111111111111111111111111.00000000000000000000),(1111111111111111111111111111111111111111111111111111111.20000000000000000000),(1111111111111111111111111111111111111111111111111111112.00000000000000000000);
[4 Dec 2003 18:03] Alexander Keremidarski 
I got completely different results.

mysql> select * from dt;
+------------------------------------------------------------------------------+
| d                                                                            |
+------------------------------------------------------------------------------+
| 1111111111111111179197135620610356299040699920330784768.00000000000000000000 |
| 1111111111111111179197135620610356299040699920330784768.00000000000000000000 |
| 1111111111111111179197135620610356299040699920330784768.00000000000000000000 |
+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

-- Note what table contains after performing INSERTs
 
mysql> select * from dt where d = "111111111111111111111111111111111111111111111
1111111111";

Empty set (0.01 sec)

-- That's correct

mysql> select * from dt where d="11111111111111111791971356206103562990406999203
30784768";
+------------------------------------------------------------------------------+
| d                                                                            |
+------------------------------------------------------------------------------+
| 1111111111111111179197135620610356299040699920330784768.00000000000000000000 |
| 1111111111111111179197135620610356299040699920330784768.00000000000000000000 |
| 1111111111111111179197135620610356299040699920330784768.00000000000000000000 |
+------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> alter table dt add key(d); Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from dt where d="11111111111111111791971356206103562990406999203
30784768";
+------------------------------------------------------------------------------+
| d                                                                            |
+------------------------------------------------------------------------------+
| 1111111111111111179197135620610356299040699920330784768.00000000000000000000 |
| 1111111111111111179197135620610356299040699920330784768.00000000000000000000 |
| 1111111111111111179197135620610356299040699920330784768.00000000000000000000 |
+------------------------------------------------------------------------------+
3 rows in set (0.03 sec)
[4 Dec 2003 18:08] Alexander Keremidarski 
Just to add that I got above results with mysqld compiled from 4.0 bk tree

ChangeSet@1.1637, 2003-11-28 20:41:33+02:00, monty@mysql.com
  rpl_parse and rpl_probe don't have to be reset as they are already 0

BUILD/compile-pentium-debug-max
[5 Dec 2003 1:43] Peter Zaitsev 
Aha cool !

There actually 2 bugs here, not one.  The first one is - MySQLDump does not handle
"DECIMAL" type propery.  It  dumps it as  pure numbers, without quoting them, so 
loading them back results in conversion to double and loading completely different data
than was in original table.

This explains why Alexandr gets different result even from select *

To make results repeatable you need to quote decimals in mysqldump output,
Ie

insert into dt values("11111111111111111111111111") not  values(1111111111111111)

That is one more ugly surprise from DECIMAL - quoted and not quoted constants are treated
differently.
[5 Dec 2003 14:25] Sergei Golubchik 
Ramil - this mysqldump bug is all yours :)
[10 Dec 2003 7:06] Ramil Kalimullin 
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html