| 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: | |
| 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: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.
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.