MySQL Bugs: #63468: different behavior for int/bigint of comparing

Bug #63468	different behavior for int/bigint of comparing
Submitted:	29 Nov 2011 8:12	Modified:	29 Nov 2011 11:08
Reporter:	liu hickey (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1/5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	int/bigint; compare; different hehavior

Description:
For one application, we worked well for long time. While recently we upgraded one column of the table from int to bigint, unfortunately, the business logs has some problem after the change. We looked into the problem and found the bigint and int treated float/double comparing differently. 

Below is the clear demo of this problem:

root@test 03:40:07>desc tmp_zx1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a     | bigint(20) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

root@test 03:40:35>desc tmp_zx2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

root@test 03:40:38>select * from tmp_zx1;
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

root@test 03:40:42>select * from tmp_zx2;
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

root@test 03:40:46>select * from tmp_zx1 where a>=2.1;
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

root@test 03:40:55>select * from tmp_zx2 where a>=2.1; 
Empty set (0.00 sec)

How to repeat:
Above

Suggested fix:
For the int compare SQL, Arg_comparator::compare_decimal is used while for bigint compare SQL, it's Arg_comparator::compare_int_signed. That different behavior caused the problem. 

Is this the designed behavior or unexpected?  It's a defect from my view point of type converting such as C language.

Thank you for the bug report.

h:\dbs>h:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.18-log Source distribution

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 5.5 >use test
Database changed
mysql 5.5 >create table tmp_zx1 (a bigint);
Query OK, 0 rows affected (0.27 sec)

mysql 5.5 >create table tmp_zx2 (a int);
Query OK, 0 rows affected (0.28 sec)

mysql 5.5 >insert into tmp_zx1 values (2);
Query OK, 1 row affected (0.09 sec)

mysql 5.5 >insert into tmp_zx2 values (2);
Query OK, 1 row affected (0.04 sec)

mysql 5.5 >select * from tmp_zx1;
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql 5.5 >select * from tmp_zx2;
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql 5.5 >select * from tmp_zx1 where a>=2.1;
+------+
| a    |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

mysql 5.5 >select * from tmp_zx2 where a>=2.1;
Empty set (0.00 sec)

mysql 5.5 >

a suggest fix for this

Attachment: bigint.diff (application/octet-stream, text), 1.23 KiB.

This is my patch base on hickey liu's patch

Attachment: bug897970_fix_bigint_cmp.patch (text/x-patch), 2.18 KiB.

root@localhost : test 09:11:12> select * from t_bigint where id>=1.1;
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)

root@localhost : test 09:11:23> select * from t_bigint where id>=1;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)

root@localhost : test 09:11:26> SELECT id, id >= 1 FROM t_bigint;
+----+---------+
| id | id >= 1 |
+----+---------+
| 1 | 1 |
| 2 | 1 |
+----+---------+
2 rows in set (0.00 sec)

root@localhost : test 09:11:29> SELECT id, id >= 1.1 FROM t_bigint;
+----+-----------+
| id | id >= 1.1 |
+----+-----------+
| 1 | 0 |
| 2 | 1 |
+----+-----------+
2 rows in set (0.00 sec)

http://bugs.mysql.com/bug.php?id=63502

This bug the same.

--- Percona-Server-5.1.59/sql/field.h	2011-08-11 21:52:53.000000000 +0800
+++ Percona-Server-5.1.59-debug/sql/field.h	2011-12-01 19:26:13.000000000 +0800
@@ -1065,7 +1065,7 @@
   void sort_string(uchar *buff,uint length);
   uint32 pack_length() const { return 8; }
   void sql_type(String &str) const;
-  bool can_be_compared_as_longlong() const { return TRUE; }
+  //bool can_be_compared_as_longlong() const { return TRUE; }
   uint32 max_display_length() { return 20; }
   virtual uchar *pack(uchar* to, const uchar *from,
                       uint max_length  __attribute__((unused)),

Maybe commentted the "can_be_compared_as_longlong() " of Field_longlong can fix this bug?

Hi, Lixun Peng
i've test your patch ,and find a failed  test case while running mysql-test-run as follows:

==============================================================================

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.type_year                           [ fail ]
        Test ended at 2011-12-01 13:33:01

CURRENT_TEST: main.type_year
--- /u01/mysql-b2b/mysql-test/r/type_year.result    2011-12-01 05:35:17.000000000 +0300
+++ /u01/mysql-b2b/mysql-test/r/type_year.reject    2011-12-01 08:33:01.000000000 +0300
@@ -199,11 +199,11 @@
 yy c2
 00 2000
 Warnings:
-Warning    1292    Truncated incorrect DOUBLE value: 'test'
+Warning    1292    Truncated incorrect DECIMAL value: 'test'
 SELECT * FROM t4 WHERE yyyy = 'test';
 yyyy   c4  
 Warnings:
-Warning    1292    Truncated incorrect DOUBLE value: 'test'
+Warning    1292    Truncated incorrect DECIMAL value: 'test'
 SELECT * FROM t2 WHERE yy = '1999';
 yy c2
 99 1999

I think this failure of warning is doesn't matter.

But the patch can't fix "SELECT id, id >= 1.1e0 FROM t_bigint" and "SELECT id, id >= "1.1" FROM t_bigint" case.

I fix again.

--- Percona-Server-5.1.59/sql/item_cmpfunc.cc	2011-08-11 21:52:53.000000000 +0800
+++ Percona-Server-5.1.59-debug/sql/item_cmpfunc.cc	2011-12-02 17:04:35.000000000 +0800
@@ -506,13 +506,24 @@
           !(field_item->is_datetime() &&
             args[1]->result_type() == STRING_RESULT))
       {
-        if (convert_constant_item(thd, field_item, &args[1]))
+        if (!(field_item->field->type() == MYSQL_TYPE_LONGLONG && 
+              (args[1]->real_item()->type() == DECIMAL_ITEM ||
+               (args[1]->real_item()->type() == REAL_ITEM && 
+				args[1]->cmp_context == REAL_RESULT ) ||
+               (args[1]->real_item()->type() == STRING_ITEM && 
+                args[1]->cmp_context == REAL_RESULT ))) &&
+            convert_constant_item(thd, field_item, &args[1]) )
         {
           cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
                            INT_RESULT);		// Works for all types.
           args[0]->cmp_context= args[1]->cmp_context= INT_RESULT;
           return;
-        }
+        } else {
+          cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
+                           DECIMAL_RESULT);		// Works for all types.
+          args[0]->cmp_context= args[1]->cmp_context= DECIMAL_RESULT;
+          return;
+		}
       }
     }
     if (args[1]->real_item()->type() == FIELD_ITEM)
@@ -522,13 +533,24 @@
           !(field_item->is_datetime() &&
             args[0]->result_type() == STRING_RESULT))
       {
-        if (convert_constant_item(thd, field_item, &args[0]))
+        if (!(field_item->field->type() == MYSQL_TYPE_LONGLONG && 
+              (args[0]->real_item()->type() == DECIMAL_ITEM ||
+               (args[0]->real_item()->type() == REAL_ITEM && 
+				args[0]->cmp_context == REAL_RESULT ) ||
+               (args[0]->real_item()->type() == STRING_ITEM && 
+                args[0]->cmp_context == REAL_RESULT))) &&
+            convert_constant_item(thd, field_item, &args[0]) )
         {
           cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
-                           INT_RESULT); // Works for all types.
+                           INT_RESULT);		// Works for all types.
           args[0]->cmp_context= args[1]->cmp_context= INT_RESULT;
           return;
-        }
+        } else {
+          cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
+                           DECIMAL_RESULT);		// Works for all types.
+          args[0]->cmp_context= args[1]->cmp_context= DECIMAL_RESULT;
+          return;
+		}
       }
     }
   }

this is the newest patch

Attachment: bug897970_fix_bigint_cmp_ver2.patch (text/x-patch), 2.44 KiB.