Bug #84558 Cast to NUMERIC not working

• Submitted: 18 Jan 2017 17:07
• Modified: 18 Jan 2017 19:22
• Reporter: Yuri Farina
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.5/5.6/5.7/8.0
• OS: Any
• CPU Architecture: Any

[18 Jan 2017 17:07] Yuri Farina

Description:
From the documentation "In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC.", but CAST doesn't accept NUMERIC as target type (unlike DECIMAL):

SELECT CAST(1.3 AS DECIMAL(8, 2));
> 1.30

SELECT CAST(1.3 AS NUMERIC(8, 2));
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NUMERIC(8, 2))' at line 1

How to repeat:
SELECT CAST(1.3 AS NUMERIC(8, 2));

[18 Jan 2017 19:22] MySQL Verification Team

Thank you for the bug report. Not sure if it's server or documentation bug:

c:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18 Source distribution PULL: 2016-DEC-25

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 5.7 > use test
Database changed
mysql 5.7 > SELECT CAST(1.3 AS DECIMAL(8, 2));
+----------------------------+
| CAST(1.3 AS DECIMAL(8, 2)) |
+----------------------------+
|                       1.30 |
+----------------------------+
1 row in set (0.00 sec)

mysql 5.7 > SELECT CAST(1.3 AS NUMERIC(8, 2));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NUMERIC(8, 2))' at line 1

mysql 5.7 > create table x (n numeric(8,2), d decimal(8,2));
Query OK, 0 rows affected (0.25 sec)

mysql 5.7 > desc x;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| n     | decimal(8,2) | YES  |     | NULL    |       |
| d     | decimal(8,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql 5.7 >