Bug #34693 DECIMAL(65) colum can accept out of range value in TRADITIONAL mode

• Submitted: 20 Feb 2008 14:50
• Modified: 20 Feb 2008 15:23
• Reporter: Roland Bouman
• Status: Closed
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.1.23
• OS: Any
• CPU Architecture: Any

[20 Feb 2008 14:50] Roland Bouman

Description:
DECIMAL column can at most have precision of 65. For a DECIMAL(65) column, An attempt to insert a literal decimal value with more than 65 digits will result in a warning (or an error depending on the SQL mode)

However, some expressions that result in a decimal expression which evaluates to a value that would need more than 65 digits can be inserted without error or warning. The behaviour is erratic - some other expressions are rejected in this case.

How to repeat:
set @@sql_mode := 'traditional';

create table d(d decimal(65));

insert into d values (repeat(9,65));

(so far all is well)

mysql> insert into d values (repeat(9,66));
ERROR 1264 (22003): Out of range value for column 'd' at row 1

(this is expected - 66 9's exceeds thevalue)

mysql> insert into d select d * 10 from d;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

(*Wrong* ! attempts to insert a 66 digit decimal value, succeeds silently )

Suggested fix:
Please treat detect a decimal overflow for all expressions and report a warning or error accordingly.

[20 Feb 2008 15:23] Tonci Grgin

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Roland, I take this as a continuation of our todays discussion. Afais, this is fixed:
WinXP Pro SP2 localhost running
C:\mysql507\mysql-5-1-24-PB1604\bin>mysqld --standalone --console
080220 16:18:06  InnoDB: Started; log sequence number 0 126137
080220 16:18:08 [Note] Event Scheduler: Loaded 0 events
080220 16:18:08 [Note] mysqld: ready for connections.
Version: '5.1.24-rc-pb1604'  socket: ''  port: 3306  MySQL Pushbuild Edition, bu
ild 1604

C:\mysql507\mysql-5-1-24-PB1604\bin>mysql -uroot -p test
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.24-rc-pb1604 MySQL Pushbuild Edition, build 1604

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

mysql> set @@sql_mode := 'traditional';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------
------------------------------------------------+
| @@sql_mode
                                                |
+-------------------------------------------------------------------------------
------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_D
IVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+-------------------------------------------------------------------------------
------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table d(d decimal(65));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into d values (repeat(9,65));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM d;
+-------------------------------------------------------------------+
| d                                                                 |
+-------------------------------------------------------------------+
| 99999999999999999999999999999999999999999999999999999999999999999 |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into d values (repeat(9,66));
ERROR 1264 (22003): Out of range value for column 'd' at row 1
mysql> insert into d select d * 10 from d;
ERROR 1264 (22003): Out of range value for column 'd' at row 1