MySQL Bugs: #2005: Long decimal comparison bug.

Bug #2005	Long decimal comparison bug.
Submitted:	3 Dec 2003 11:56	Modified:	10 Dec 2003 6:06
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.17bk	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

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);

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)

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

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.

Ramil - this mysqldump bug is all yours :)

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