Bug #63502 Incorrect results of BIGINT and DECIMAL comparison
Submitted: 30 Nov 2011 10:23 Modified: 27 Jan 2012 19:06
Reporter: Alexey Kopytov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.58 OS:Any
Assigned to: CPU Architecture:Any

[30 Nov 2011 10:23] Alexey Kopytov
Description:
The manual describes the following type conversion rules when comparing to a DECIMAL value:

http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html

"
If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value. 
"

It appears to be true when the other argument of comparison is an INT column:

mysql> CREATE TABLE t_int(id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t_int VALUES (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT id, id >= 1.1 FROM t_int;
+------+-----------+
| id   | id >= 1.1 |
+------+-----------+
|    1 |         0 |
|    2 |         1 |
+------+-----------+
2 rows in set (0.00 sec)

However, it's not the case for BIGINT columns:

mysql> CREATE TABLE t_bigint(id BIGINT);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t_bigint VALUES (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT id, id >= 1.1 FROM t_bigint;
+------+-----------+
| id   | id >= 1.1 |
+------+-----------+
|    1 |         1 |
|    2 |         1 |
+------+-----------+
2 rows in set (0.00 sec)

Debugging shows that in this case the 1.1 constant is converted to BIGINT instead, and comparison is performed in the integer context. Which contradicts the manual.

If the other argument is an expression rather than a column, the comparison is performed in the DECIMAL context as documented:

mysql> SELECT id, id+0 >= 1.1 FROM t_bigint;
+------+-------------+
| id   | id+0 >= 1.1 |
+------+-------------+
|    1 |           0 |
|    2 |           1 |
+------+-------------+
2 rows in set (0.00 sec)

How to repeat:
CREATE TABLE t_bigint(id BIGINT);
INSERT INTO t_bigint VALUES (1), (2);
SELECT id, id >= 1.1 FROM t_bigint;
[30 Nov 2011 11:31] Valeriy Kravchuk
Thank you for the problem report. Verified with 5.1.58:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.58-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE TABLE t_bigint(id BIGINT);
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO t_bigint VALUES (1), (2);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT id, id >= 1.1 FROM t_bigint;
+------+-----------+
| id   | id >= 1.1 |
+------+-----------+
|    1 |         1 |
|    2 |         1 |
+------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT id, id+0 >= 1.1 FROM t_bigint;
+------+-------------+
| id   | id+0 >= 1.1 |
+------+-------------+
|    1 |           0 |
|    2 |           1 |
+------+-------------+
2 rows in set (0.03 sec)
[30 Nov 2011 13:28] Lixun Peng
The Bug is the same as http://bugs.mysql.com/bug.php?id=63502
[30 Nov 2011 13:29] Lixun Peng
Sorry, the same as http://bugs.mysql.com/bug.php?id=63468
[27 Jan 2012 19:06] 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 19:07] Jon Stephens
Fixed in 5.5.21/5.6.5. 

Closed. 

See BUG#50756 for changelog entry.
[27 Jan 2012 19:09] Jon Stephens
Fixed in 5.5.21/5.6.5,

Closed.

See BUG#50756 for changelog entry.