Bug #49523 The coltype "double" gives an error in case of overflow, not a warning.

• Submitted: 8 Dec 2009 4:40
• Modified: 10 Dec 2009 21:06
• Reporter: Horst Hunger
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: mysql-next-mr(-alik), 5.0, 5.1
• OS: Linux (SUSE 11.1, 2.6.27.37-0.1-default #1 SMP 2009-10-15 14:56:58 +0200 x86_64 x86_64 x86_64 GNU/Linux)
• CPU Architecture: Any

[8 Dec 2009 4:40] Horst Hunger

Description:
Independent of the sql-mode statements accessing double columns gives the error 1367. Other numeric types insert the maximum value and set a warning like 

Warning 1264    Out of range value for column 'c2' at row 1

They only set an error in the sql-mode traditional.
This is an unconsistent behaviour. Example:

mysql> use test;
Database changed
mysql> create table t1 (c1 int, c2 double);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t1 values (9999999999999999999, 999999999999999999999999999999999999);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t1;
+------------+-------+
| c1         | c2    |
+------------+-------+
| 2147483647 | 1e+36 |
+------------+-------+
1 row in set (0.00 sec)

mysql> insert into t1 values (9999999999999999999, 999999999999999999E309);
ERROR 1367 (22007): Illegal double '999999999999999999E309' value found during parsing
mysql> insert into t1 values (999999999999999999999999999999, 99999999999999999);
Query OK, 1 row affected, 2 warnings (0.00 sec)

How to repeat:
execute with mysql:

use test;
create table t1 (c1 int, c2 double);
insert into t1 values (9999999999999999999, 9999999999999999999999999999999999);
select * from t1;
insert into t1 values (9999999999999999999, 999999999999999999E309);
insert into t1 values (999999999999999999999999999999, 99999999999999999);

Suggested fix:
Like the other numeric column types also "double" shall give a warning and set the nearest minimum or maximum value. Only in sql-mode "traditional" an error shall be given back.

[8 Dec 2009 7:55] Horst Hunger

For more tests look at WL#5176.

[8 Dec 2009 8:18] Sveta Smirnova

Thank you for the report.

Verified as described.

[10 Dec 2009 21:06] Omer Barnir

Error is expected as an illegal constant is used (E309)