BIGINT for comparing is error

[Lixun Peng (P.Linux)]

THIS IS THE CASE:
root@localhost : (none) 10:02:29> use test
Database changed
root@localhost : test 10:02:31> create table t_bigint(id bigint unsigned not null primary key auto_increment );
Query OK, 0 rows affected (0.01 sec)

root@localhost : test 10:02:59> create table t_int(id int unsigned not null primary key auto_increment );
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 10:03:08> insert into t_bigint values (1);
Query OK, 1 row affected (0.00 sec)

root@localhost : test 10:03:24> insert into t_bigint values (2);
Query OK, 1 row affected (0.00 sec)

root@localhost : test 10:03:26> insert into t_int values (1);
Query OK, 1 row affected (0.00 sec)

root@localhost : test 10:03:32> insert into t_int values (2);
Query OK, 1 row affected (0.00 sec)

root@localhost : test 10:03:34> select * from t_bigint;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)

root@localhost : test 10:03:39> select * from t_int;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)

root@localhost : test 10:03:41> select * from t_bigint where id >1.1;
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)

root@localhost : test 10:04:27> select * from t_int where id >1.1;
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)

root@localhost : test 10:04:33> select * from t_bigint where id >=1.1;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)

root@localhost : test 10:06:37> select * from t_int where id >=1.1;
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)

Bigint for comparing and calculation is often a problem, but never really fix

[Alexey Kopytov (akopytov)]

Thanks. It's an upstream bug, so I have reported it as http://bugs.mysql.com/bug.php?id=63502.

[Lixun Peng (P.Linux) (plx)]

I think the reason is array "comparator_matrix" result error.
  541 int Arg_comparator::set_compare_func(Item_result_field *item,
Item_result type)
  542 {
  543 owner= item;
  544 func= comparator_matrix[type]
  545 [is_owner_equal_func()];
  546
  547 switch (type) {

I think "bigint" compare "decimal" well be in "case DECIMAL_RESULT", but
not "case INT_RESULT".

On 11/30/2011 06:26 PM, Alexey Kopytov wrote:
> Thanks. It's an upstream bug, so I have reported it as
> http://bugs.mysql.com/bug.php?id=63502.
>
> ** Changed in: percona-server
> Status: New => Confirmed
>
> ** Bug watch added: MySQL Bug System #63502
> http://bugs.mysql.com/bug.php?id=63502
>
> ** Also affects: mysql-server via
> http://bugs.mysql.com/bug.php?id=63502
> Importance: Unknown
> Status: Unknown
>

--
彭立勋 (P.Linux)
B2B-运维部-数据库管理-MySQL数据库管理
Tel: [phone]-30948
Moble: 186 5815 6856
Blog: http://www.penglixun.com
Twitter: https://twitter.com/plinux

________________________________

This email (including any attachments) is confidential and may be legally privileged. If you received this email in error, please delete it immediately and do not copy it or use it for any purpose or disclose its contents to any other person. Thank you.

本电邮(包括任何附件)可能含有机密资料并受法律保护。如您不是正确的收件人，请您立即删除本邮件。请不要将本电邮进行复制并用作任何其他用途、或透露本邮件之内容。谢谢。

[Alexey Kopytov (akopytov)]

The reason is this code in item_cmpfunc.cc:

    if (args[0]->real_item()->type() == FIELD_ITEM)
    {
      Item_field *field_item= (Item_field*) (args[0]->real_item());
      if (field_item->field->can_be_compared_as_longlong() &&
          !(field_item->is_datetime() &&
            args[1]->result_type() == STRING_RESULT))
      {
        if (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;
        }
      }
    }

So if Field::can_be_compared_as_longlong() returns true (which is the case for Field_longlong, but not the case for Field_long), the comparison context is converted from DECIMAL_RESULT to INT_RESULT and the constant is converted to an integer.

[Lixun Peng (P.Linux) (plx)]

so we modify the IF statement add judgement of filed->type(), this bug can fix?
if ( (!(field_item->field->type() == MYSQL_TYPE_LONGLONG && args[0]->real_item()->type() == DECIMAL_ITEM))
    && convert_constant_item(thd, field_item, &args[0]))

[Lixun Peng (P.Linux) (plx)]

I think I fixed it, Please Check.

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)

--- 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-11-30 21:12:23.000000000 +0800
@@ -506,14 +506,20 @@
           !(field_item->is_datetime() &&
             args[1]->result_type() == STRING_RESULT))
       {
- if (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;
- }
- }
+ if ( (!(field_item->field->type() == MYSQL_TYPE_LONGLONG && args[1]->real_item()->type() == DECIMAL_ITEM))
+ && 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,14 +528,20 @@
           !(field_item->is_datetime() &&
             args[0]->result_type() == STRING_RESULT))
       {
- if (convert_constant_item(thd, field_item, &args[0]))
- {
- 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;
- }
- }
+ if ( (!(field_item->field->type() == MYSQL_TYPE_LONGLONG && args[0]->real_item()->type() == DECIMAL_ITEM))
+ && convert_constant_item(thd, field_item, &args[0]))
+ {
+ 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;
+ }
+ }
     }
   }
   set_cmp_func();

[Lixun Peng (P.Linux) (plx)]

bug897970_fix_bigint_cmp.patch

[Alexey Kopytov (akopytov)]

I think the patch doesn't cover all cases, for example the following queries should still produce wrong results:

SELECT id, id >= 1.1e0 FROM t_bigint;
SELECT id, id >= "1.1" FROM t_bigint;

Field::can_be_compared_as_longlong() was introduced to allow temporal data types to be compared in the integer context with compatible data types, i.e. other temporal values or BIGINTs. This obviously doesn't apply here, so a correct fix would be to only convert the context if at least one of the arguments has a temporal data type, i.e. DATE, TIME, YEAR, DATETIME or TIMESTAMP.

[Lixun Peng (P.Linux) (plx)]

I commentted the "can_be_compared_as_longlong() " of Field_longlong, then all test case can pass. Can fix this bug?

--- 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)),

root@localhost : test 07:54:39> create table t_bigint_int (c1 bigint,c2 int);
Query OK, 0 rows affected (0.01 sec)

root@localhost : test 07:54:43> insert into t_bigint_int values (1,2),(3,4);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

root@localhost : test 07:54:48> select * from t_bigint_int where c1>c2;
Empty set (0.00 sec)

root@localhost : test 07:54:59> select * from t_bigint_int where c1<c2;
+------+------+
| c1 | c2 |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+
2 rows in set (0.00 sec)

root@localhost : test 07:55:03> select c1>='1.1' from t_bigint_int;
+-----------+
| c1>='1.1' |
+-----------+
| 0 |
| 1 |
+-----------+
2 rows in set (0.00 sec)

root@localhost : test 07:55:11> select c1>=1.1 from t_bigint_int;
+---------+
| c1>=1.1 |
+---------+
| 0 |
| 1 |
+---------+
2 rows in set (0.01 sec)

root@localhost : test 07:55:16> select c1>=1.1e0 from t_bigint_int;
+-----------+
| c1>=1.1e0 |
+-----------+
| 0 |
| 1 |
+-----------+
2 rows in set (0.00 sec)

root@localhost : test 07:55:27> select c1>=c1 from t_bigint_int;
+--------+
| c1>=c1 |
+--------+
| 1 |
| 1 |
+--------+
2 rows in set (0.00 sec)

[Alexey Kopytov (akopytov)]

No, I'm sure this will break comparisons of BIGINT with temporal values. Have you tried running the test suite with this fix?

[Lixun Peng (P.Linux) (plx)]

main.type_newdecimal-big [ skipped ] Test needs 'big-test' option
main.type_date [ pass ] 51
main.type_datetime [ pass ] 72
main.type_timestamp [ pass ] 63
main.type_time [ pass ] 12
main.type_newdecimal [ pass ] 147
main.type_decimal [ pass ] 79
main.type_year [ fail ]
        Test ended at 2011-12-01 21:25:28

CURRENT_TEST: main.type_year
--- /home/mysql/percona/Percona-Server-5.1.59-debug/mysql-test/r/type_year.result 2011-08-11 17:07:01.000000000 +0300
+++ /home/mysql/percona/Percona-Server-5.1.59-debug/mysql-test/r/type_year.reject 2011-12-01 16:25:28.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

mysqltest: Result content mismatch

 - saving '/home/mysql/percona/Percona-Server-5.1.59-debug/mysql-test/var/log/main.type_year/' to '/home/mysql/percona/Percona-Server-5.1.59-debug/mysql-test/var/log/main.type_year/'

Only 8 of 9 completed.
mysql-test-run: *** ERROR: Not all tests completed

I test about temporal values cases.
only main.type_year warning different.

and I test comparisons of BIGINT with temporal values. I think the result is correct......

root@localhost : (none) 09:27:46> use test
Database changed
root@localhost : test 09:27:48> show create table t_bigint_time\G
*************************** 1. row ***************************
       Table: t_bigint_time
Create Table: CREATE TABLE `t_bigint_time` (
  `c1` bigint(20) DEFAULT NULL,
  `c2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `c3` date DEFAULT NULL,
  `c4` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

root@localhost : test 09:27:54> select *,c1>=c2,c1>=c3,c1>=c4 from t_bigint_time;
+------+---------------------+------------+---------------------+--------+--------+--------+
| c1 | c2 | c3 | c4 | c1>=c2 | c1>=c3 | c1>=c4 |
+------+---------------------+------------+---------------------+--------+--------+--------+
| 1 | 2011-12-01 20:26:10 | 2011-12-01 | 2011-12-01 20:26:10 | 0 | 0 | 0 |
| 2 | 2011-12-01 20:26:10 | 2011-12-01 | 2011-12-01 20:26:10 | 0 | 0 | 0 |
| 1 | 0000-00-00 00:00:00 | 2011-12-01 | 2011-12-01 20:27:57 | 1 | 0 | 0 |
| 2 | 0000-00-00 00:00:00 | 2011-12-01 | 2011-12-01 20:27:57 | 1 | 0 | 0 |
+------+---------------------+------------+---------------------+--------+--------+--------+
4 rows in set (0.00 sec)

root@localhost : test 09:28:20> select *,c1>=c2,c1>=c3,c1>=c4,c1<=year(c2),c1<=date(c4) from t_bigint_time;
+------+---------------------+------------+---------------------+--------+--------+--------+--------------+---------...

[Alexey Kopytov (akopytov)]

I got the following results after running the test suite with the last version of the fix applied:

---
Completed: Failed 5/454 tests, 98.90% were successful.

Failing test(s): main.func_equal main.func_in main.implicit_char_to_num_conversion main.varbinary main.select
---

The command was: ./mtr --force --suite=main
All of the failures were due to results differences.

[Lixun Peng (P.Linux) (plx)]

I modified the patch, and test again, the failed cases your test are pass.

Completed: Failed 1/4 tests, 75.00% were successful.

Failing test(s): main.select

The log files in var/log may give you some hint of what went wrong.

If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html

CURRENT_TEST: main.select
mysqltest: At line 3748: query 'CREATE TABLE t1(a LINESTRING NOT NULL, SPATIAL KEY(a))' failed: 1289: The 'Spatial extentions' feature is disabled; you need MySQL built with 'HAVE_SPATIAL' to have it working

this failure because compile option.

this is the newest patch:

--- 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;
+ }
       }
     }
   }

Test result:

root@localhost : test 05:32:...

[Lixun Peng (P.Linux) (plx)]

bug897970_fix_bigint_cmp_ver2.patch

[Alexey Kopytov (akopytov)]

Will be fixed in MySQL 5.5.21:

"
Expressions that compared a BIGINT column with any non-integer constant were performed using integers rather than decimal or float values, with the result that the constant could be truncated. This could lead to any such comparison that used <, >, <=, >=, =, !=/<>, IN, or BETWEEN yielding false positive or negative results. (Bug #13463415, Bug #11758543, Bug #63502, Bug #50756)
"

http://dev.mysql.com/doc/refman/5.5/en/news-5-5-21.html

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2709