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 Email Updates:
Status: Verified Impact on me:
None 
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 )
Assigned to: 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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.