CODEWITHSUNDEEP
mysql
Jacky Wang
Jacky Wang

Reputation: 3490

Precision loss when performing large number operation in mysql

In MySQL 5.7, a table defined as following shown

CREATE TABLE `person` (
  `person_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`person_id`),
  KEY `ix_name` (`name`)
) ENGINE=InnoDB CHARSET=utf8

And then we prepared two records for testing, the value of name field (with varchar type) are

respectively.

Case 1

select * from person where name = 123456789123456789-1;

Note that we are using a number instead of string inside the where clause. The record with name 123456789123456789 returned, and it seemed that -1 in the end are ignored!

Furthermore, we add another record with name = 123456789123456788, and this time the above select returns two records, including both 123456789123456789 and 123456789123456788;

The output looks so strange!

Case 2

select * from person where name = 123456789123456789-123456789123456788;

We could get the record with name 1, and in this case it seems that the - act as a minus operator.

Why the behavior of - in two cases are so different!

Upvotes: 2

Views: 123

Answers (2)

userlond
userlond

Reputation: 3818

Look here:

SELECT person_id, name, name + 0.0, 123456789123456789-1 + 0.0,  name = 123456789123456789-1
FROM person
ORDER BY person_id;

Perhaps, before comparing name = 123456789123456789-1 MySQL converts name and 123456789123456789-1 to DOUBLE as I showed in select. So some digits are lost.

Demo.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

I can't immediately tell you what the type of 123456789123456789-1 is but for the comparison operation, we're almost certainly falling through most of the more "normal" data type conversion rules for mysql and ending up at:

In all other cases, the arguments are compared as floating-point (real) numbers.

Because one of the argument for the comparison (name) is a string type and the other is numeric, nothing else matches. So both get converted to floats and float types don't have too many digits of precision. Certainly less than the 18 required to represent 123456789123456789 and 123456789123456788 as two different numbers.

Upvotes: 2

Related Questions