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: | |
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
[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!