MySQL Bugs: #63502: Incorrect results of BIGINT and DECIMAL comparison

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

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;

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)

The Bug is the same as http://bugs.mysql.com/bug.php?id=63502

Sorry, the same as http://bugs.mysql.com/bug.php?id=63468

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.21/5.6.5. 

Closed. 

See BUG#50756 for changelog entry.

Fixed in 5.5.21/5.6.5,

Closed.

See BUG#50756 for changelog entry.