Bug #78821 When comparing bigint and varchar constant, inconsistent results are returned
Submitted: 13 Oct 2015 15:28 Modified: 15 Oct 2015 8:13
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.7.8, 5.7.10, 5.6.27, 5.5.47 OS:Any
Assigned to: CPU Architecture:Any

[13 Oct 2015 15:28] Su Dylan
Description:
Output:
=======
mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1(a bigint);
Query OK, 0 rows affected (0.01 sec)

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

mysql> select a, a = '9223372036854775806', cast(9223372036854775807 as signed) = '9223372036854775806' from t1;
+---------------------+---------------------------+-------------------------------------------------------------+
| a                   | a = '9223372036854775806' | cast(9223372036854775807 as signed) = '9223372036854775806' |
+---------------------+---------------------------+-------------------------------------------------------------+
| 9223372036854775807 |                         0 |                                                           1 |
+---------------------+---------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
The following two expressions are expected to return same boolean value:
a = '9223372036854775806', cast(9223372036854775807 as signed) = '9223372036854775806'

How to repeat:
drop table if exists t1;
create table t1(a bigint);
insert into t1 values(9223372036854775807);
select a, a = '9223372036854775806', cast(9223372036854775807 as signed) = '9223372036854775806' from t1;

Suggested fix:
The following two expressions return same boolean value:
a = '9223372036854775806', cast(9223372036854775807 as signed) = '9223372036854775806'.
[14 Oct 2015 9:57] MySQL Verification Team
Hello Su Dylan,

Thank you for the report and test case.
Observed this with 5.6.27,5.7.10 and 5.5.47.

Thanks,
Umesh
[14 Oct 2015 12:00] Tor Didriksen
Here you are comparing an integer constant with a string:
cast(9223372036854775807 as signed) = '9223372036854775806'
that comparison is done using floating-point

If you do:
cast(9223372036854775807 as signed) = 9223372036854775806
you get the expected result.

http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html
[15 Oct 2015 2:26] Su Dylan
Hi Tor,

Thanks for the response.

I am aware of the accuracy problem of comparison using float-point value. From the document you provided, when comparing bigint values (from column or from cast function result) with string constant, float-point values are used.
However, the following two equivalent expressions are still expected to return same boolean value, since they should both use float-point values to compare:
a = '9223372036854775806', cast(9223372036854775807 as signed) = '9223372036854775806'
[15 Oct 2015 7:59] Tor Didriksen
Posted by developer:
 
When comparing to a field, the string argument is converted to int.
When comparing to an expression, it is not:
mysql> select a, a+0 = '9223372036854775806',  cast(9223372036854775807 as signed) = '9223372036854775806' from t1;
+---------------------+-----------------------------+-------------------------------------------------------------+
| a                   | a+0 = '9223372036854775806' | cast(9223372036854775807 as signed) = '9223372036854775806' |
+---------------------+-----------------------------+-------------------------------------------------------------+
| 9223372036854775807 |                           1 |                                                           1 |
+---------------------+-----------------------------+-------------------------------------------------------------+
[15 Oct 2015 8:13] Su Dylan
Hi Tor,

Thanks for this valuable information.
So from the above information, here are the current behaviors:
1. bigint field = string constant, compare type is bigint;
2. bigint field expression = string constant, compare type is float-point value(may be double);
3. cast function with int constant = string constant, compare type is also float-point value.

Then mostly important for me, what is the final decision?
Will these inconsistency behaviors will be modified or just kept the way they are now?
Thanks!