Bug #83589 DECIMAL(4,1) turns into DECIMAL (4,0)

• Submitted: 27 Oct 2016 23:41
• Modified: 28 Oct 2016 1:51
• Reporter: Rick James
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.0
• OS: Any
• CPU Architecture: Any
• Tags: decimal precision

[27 Oct 2016 23:41] Rick James

Description:
Create a column with DECIMAL(4,1).  It turns into DECIMAL (4,0).

Bad on (at least) 5.5.43, 5.6.12, 8.0.0, MariaDB 10.2.2

From http://forums.mysql.com/read.php?10,651873,651873 (Anthony Nelson)

How to repeat:
CREATE TABLE f651873 ( x DECIMAL(4.1) );
SHOW CREATE TABLE f651873 \G
INSERT INTO f651873 (x) VALUE (4.7);
SELECT * FROM f651873;
-- --
Output (on one of the systems):

mysql> SHOW CREATE TABLE f651873 \G
*************************** 1. row ***************************
       Table: f651873
Create Table: CREATE TABLE `f651873` (
  `x` decimal(3,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO f651873 (x) VALUE (4.7);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'x' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM f651873;
+------+
| x    |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

Suggested fix:
unknown

[28 Oct 2016 1:51] MySQL Verification Team

Thank you for the bug report.

c:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.1-dmr Source distribution PULL: 2016-OCT-14

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 8.0 > CREATE DATABASE g;
Query OK, 1 row affected (0.23 sec)

mysql 8.0 > USE g
Database changed
mysql 8.0 > CREATE TABLE f651873 ( x DECIMAL(4,1) ); << see here 4,1 instead of 4.1 from your test case
Query OK, 0 rows affected (0.19 sec)

mysql 8.0 > SHOW CREATE TABLE f651873 \G
*************************** 1. row ***************************
       Table: f651873
Create Table: CREATE TABLE `f651873` (
  `x` decimal(4,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 8.0 > INSERT INTO f651873 (x) VALUE (4.7);
Query OK, 1 row affected (0.04 sec)

mysql 8.0 > SELECT * FROM f651873;
+------+
| x    |
+------+
|  4.7 |
+------+
1 row in set (0.00 sec)

mysql 8.0 >