Bug #22026 Warning when using IF statement and large unsigned bigint
Submitted: 5 Sep 2006 17:08 Modified: 21 Feb 2007 3:31
Reporter: Dave Corcoran Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.0.20/5.0/5.1BK OS:Linux (Linux 2.6.15-1-k7-smp)
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: bigint if quote

[5 Sep 2006 17:08] Dave Corcoran
Description:
If you use a value above max signed bigint (9223372036854775807) as part of an IF statement you get warning 1264 ("Out of range value adjusted for column 'bvalue' at row 1") and the value gets converted to 0.
If you quote the value it works fine.

How to repeat:
CREATE DATABASE IF NOT EXISTS bug;
USE bug;

CREATE TABLE IF NOT EXISTS t1
(
  avalue       INT NOT NULL,
  bvalue       BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY   (avalue)
) TYPE = MyISAM;

TRUNCATE TABLE t1;

INSERT INTO t1 set avalue = 1, bvalue = IF(1, 9223372036854775808, 4321);
SELECT * FROM t1;
TRUNCATE TABLE t1;
INSERT INTO t1 set avalue = 1, bvalue = IF(1, '9223372036854775808', 4321);
SELECT * FROM t1;

DROP DATABASE bug;

Running this gives:
> mysql -u root < bug.sql
avalue  bvalue
1       0
avalue  bvalue
1       9223372036854775808

Suggested fix:
This may be related to bug#21555 where the suggested fix was to parse values using strtoull.

I tried this code on a 64bit machine with 64bit mysql and it also didn't work.
[5 Sep 2006 17:41] MySQL Verification Team
Thank you for the bug report. This bug not affects 4.1.XX.

miguel@hegel:~/dbs/5.1> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE IF NOT EXISTS bug;
Query OK, 1 row affected (0.02 sec)

<cut>

mysql> INSERT INTO t1 set avalue = 1, bvalue = IF(1, 9223372036854775808, 4321);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1264 | Out of range value for column 'bvalue' at row 1 | 
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1;
+--------+--------+
| avalue | bvalue |
+--------+--------+
|      1 |      0 | 
+--------+--------+
1 row in set (0.00 sec)

mysql> TRUNCATE TABLE t1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 set avalue = 1, bvalue = IF(1, '9223372036854775808', 4321);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+--------+---------------------+
| avalue | bvalue              |
+--------+---------------------+
|      1 | 9223372036854775808 | 
+--------+---------------------+
1 row in set (0.00 sec)

<cut>

Your MySQL connection id is 1 to server version: 4.1.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE IF NOT EXISTS bug;
Query OK, 1 row affected (0.02 sec)

mysql> USE bug;
Database changed
mysql> CREATE TABLE IF NOT EXISTS t1
    -> (
    ->   avalue       INT NOT NULL,
    ->   bvalue       BIGINT UNSIGNED NOT NULL,
    ->   PRIMARY KEY   (avalue)
    -> ) TYPE = MyISAM;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> 
mysql> TRUNCATE TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 set avalue = 1, bvalue = IF(1, 9223372036854775808, 4321);
Query OK, 1 row affected (0.00 sec)

mysql> SHOW WARNINGS;
Empty set (0.00 sec)

mysql> SELECT * FROM t1;
+--------+---------------------+
| avalue | bvalue              |
+--------+---------------------+
|      1 | 9223372036854775808 |
+--------+---------------------+
1 row in set (0.00 sec)

mysql> TRUNCATE TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 set avalue = 1, bvalue = IF(1, '9223372036854775808', 4321);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+--------+---------------------+
| avalue | bvalue              |
+--------+---------------------+
|      1 | 9223372036854775808 |
+--------+---------------------+
1 row in set (0.00 sec)

mysql> DROP DATABASE bug;
Query OK, 1 row affected (0.01 sec)
[11 Oct 2006 9:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/13461

ChangeSet@1.2543, 2006-10-11 14:36:41+05:00, ramil@mysql.com +3 -0
  Fix for bug #22026: Warning when using IF statement and large unsigned bigint 
  
  We don't take into account unsigned_flags aggregating result types.
[27 Nov 2006 7:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15831

ChangeSet@1.2277, 2006-11-27 11:17:55+04:00, ramil@mysql.com +3 -0
  Fix for bug #22026: Warning when using IF statement and large unsigned bigint
  
  We use INT_RESULT type if all arguments are of type INT for 'if', 'case', 
  'coalesce' functions regardless of arguments' unsigned flag, so sometimes we can
  exceed the INT bounds.
  
  Fix: use INT_RESULT only if all arguments have equal unsigned_flag, otherwise 
  use DECIMAL_RESULT.
[22 Jan 2007 10:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18526

ChangeSet@1.2386, 2007-01-22 14:52:23+04:00, ramil@mysql.com +3 -0
  Fix for bug #22026: Warning when using IF statement and large unsigned bigint
    
  We use INT_RESULT type if all arguments are of type INT for 'if', 'case', 
  'coalesce' functions regardless of arguments' unsigned flag, so sometimes we can
  exceed the INT bounds.
[14 Feb 2007 15:07] Chad MILLER
Available in 5.0.36 and 5.1.16-beta.
[21 Feb 2007 3:31] Paul DuBois
Noted in 5.0.36, 5.1.16 changelogs.