MySQL Bugs: #50756: Bigint '100' matches 1.001E2

Bug #50756	Bigint '100' matches 1.001E2
Submitted:	30 Jan 2010 13:04	Modified:	27 Jan 2012 13:01
Reporter:	Sadao Hiratsuka (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.91, 5.1.42	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	BIGINT, qc

Description:
Bigint '100' matches 1.001E2.
(Int '100' doesn't match 1.001E2.)

How to repeat:
mysql> create table t1 (c1 int, c2 bigint);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (100, 100);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1 where c1 = 1.001E2;
Empty set (0.00 sec)

mysql> select * from t1 where c2 = 1.001E2;
+------+------+
| c1   | c2   |
+------+------+
|  100 |  100 |
+------+------+
1 row in set (0.00 sec)

Suggested fix:
I think the above condition should be false.

Verified just as described:

77-52-24-143:5.0 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 5
Server version: 5.0.91-debug Source distribution

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

mysql> drop table t1;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t1 (c1 int, c2 bigint);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (100, 100);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 where c1 = 1.001E2;
Empty set (0.00 sec)

mysql> select * from t1 where c2 = 1.001E2;
+------+------+
| c1   | c2   |
+------+------+
|  100 |  100 | 
+------+------+
1 row in set (0.00 sec)

mysql> select *, c1 - 1.001E2 a, c2 - 1.001E2 b from t1 where c2 = 1.001E2;
+------+------+---------------------+---------------------+
| c1   | c2   | a                   | b                   |
+------+------+---------------------+---------------------+
|  100 |  100 | -0.0999999999999943 | -0.0999999999999943 | 
+------+------+---------------------+---------------------+
1 row in set (0.00 sec)

Our manual (http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html) says that in both cases "arguments are compared as floating-point (real) numbers", but why we have the difference then?

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed in 5.5+. Documented in the 5.5.21 and 5.6.5 changelogs as follows:

        Expressions that compared a BIGINT column with any non-integer
        constant were performed using integers rather than decimal or
        float values, with the result that the constant could be
        truncated. This could lead to any such comparison that used
        <, >, <=, >=, =, !=, IN, or BETWEEN yielding false
        positive or negative results.

Closed.