Bug #49253 Inconsistent behavior concering overflow using numeric expressions and literals.

• Submitted: 1 Dec 2009 9:38
• Modified: 1 Dec 2009 10:04
• Reporter: Horst Hunger
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.1.41, mysql-next-mr(-alik)
• OS: Linux (SUSE 11.1, Linux 2.6.27.37-0.1-default #1 SMP x86_64 )
• CPU Architecture: Any

[1 Dec 2009 9:38] Horst Hunger

Description:
this are the results of the statements:

+SELECT 1024*1024*1024*1024*1024*1024*8;
+1024*1024*1024*1024*1024*1024*8
+-9223372036854775808
+SELECT cast(9223372036854775808 as signed);
+cast(9223372036854775808 as signed)
+-9223372036854775808
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (c1 int, c2 bigint);
+INSERT INTO t1 VALUES (1,1024*1024*1024*1024*1024*1024*8);
+INSERT INTO t1 VALUES (2,9223372036854775808);
+Warnings:
+Warning        1264    Out of range value for column 'c2' at row 1
+SELECT * FROM t1;
+c1     c2
+1      -9223372036854775808
+2      9223372036854775807

When using the expression "1024*1024*1024*1024*1024*1024*8" I don't get an overflow warning though the value switches to a negative value. Also when using the cast function for "9223372036854775808". That differs from the behavior of the INSERT of "9223372036854775808", which fires the expected warning.

How to repeat:
execute the following statements with mysql or as sequence with mtr:

SELECT @@GLOBAL.sql_mode;
SET @@SESSION.sql_mode= "";
SELECT @@SESSION.sql_mode;
SELECT 1024*1024*1024*1024*1024*1024*8;
SELECT cast(9223372036854775808 as signed);
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 int, c2 bigint);
INSERT INTO t1 VALUES (1,1024*1024*1024*1024*1024*1024*8);
INSERT INTO t1 VALUES (2,9223372036854775808);
SELECT * FROM t1;

I used the tree mysql-next-mr-alik as parent.

Suggested fix:
The statements
SELECT 1024*1024*1024*1024*1024*1024*8;
SELECT cast(9223372036854775808 as signed);
INSERT INTO t1 VALUES (1,1024*1024*1024*1024*1024*1024*8);

shall behave like

INSERT INTO t1 VALUES (2,9223372036854775808);

means give a warning and deliver the same value.

[1 Dec 2009 10:04] Valeriy Kravchuk

Verified just as described also with 5.1.41 on Windows XP:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.41-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT @@GLOBAL.sql_mode;
+----------------------------------------------------------------+
| @@GLOBAL.sql_mode                                              |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.05 sec)

mysql> SET @@SESSION.sql_mode= "";
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT 1024*1024*1024*1024*1024*1024*8;
+---------------------------------+
| 1024*1024*1024*1024*1024*1024*8 |
+---------------------------------+
|            -9223372036854775808 |
+---------------------------------+
1 row in set (0.02 sec)

mysql> SELECT cast(9223372036854775808 as signed);
+-------------------------------------+
| cast(9223372036854775808 as signed) |
+-------------------------------------+
|                -9223372036854775808 |
+-------------------------------------+
1 row in set (0.02 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE t1 (c1 int, c2 bigint);
Query OK, 0 rows affected (0.22 sec)

mysql> INSERT INTO t1 VALUES (1,1024*1024*1024*1024*1024*1024*8);
Query OK, 1 row affected (0.19 sec)

mysql> INSERT INTO t1 VALUES (2,9223372036854775808);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SELECT * FROM t1;
+------+----------------------+
| c1   | c2                   |
+------+----------------------+
|    1 | -9223372036854775808 |
|    2 |  9223372036854775807 |
+------+----------------------+
2 rows in set (0.02 sec)

[8 Dec 2009 7:55] Horst Hunger

For more tests look at WL#5176.