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
Triage: Triaged: D2 (Serious)

[30 Jan 2010 13:04] Sadao Hiratsuka
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.
[30 Jan 2010 15:30] Valeriy Kravchuk
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?
[27 Jan 2012 13:01] Jon Stephens
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
[27 Jan 2012 13:09] Jon Stephens
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.