MySQL Bugs: #8434: Precision math: silent data type change from NUMERIC to DECIMAL

Bug #8434	Precision math: silent data type change from NUMERIC to DECIMAL
Submitted:	11 Feb 2005 2:25	Modified:	12 Apr 2005 19:36
Reporter:	Trudy Pelzer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
MySQL should never change the data type of a column.
But in the case of NUMERIC column definitions, the server
changes the data type to DECIMAL.

How to repeat:
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (col1 numeric(4,2));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                  |
+-------+-----------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `col1` decimal(4,2) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- This is incorrect. The data type chosen for the column
was NUMERIC and that should be reflected in the SHOW
statement.

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

mysql> create table t2 (col1 numeric(4,2));
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t2;
+-------+-----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                  |
+-------+-----------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `col1` decimal(4,2) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- Again, this is incorrect. Even under 'traditional' mode, MySQL
is silently changing the column data type from NUMERIC to
DECIMAL. The correct response, regardless of sql_mode, is to
either (a) leave the data type definition as the user has it
or (b) if the definition is invalid, reject the statement with
an error: SQLSTATE 42000 invalid data type definition

I am changing this bug to closed. A review of SQL:2003 shows these 
statements:

"An SQL-implementation is permitted to regard certain <exact numeric
type>s as equivalent, if they have the same precision, scale, and
radix, as permitted by the Syntax Rules of Subclause 6.1,  <data type>. 
When two or more <exact numeric type>s are equivalent, the
SQL-implementation chooses one of these equivalent <exact numeric 
type>s as the normal form representing that equivalence class of <exact 
numeric type>s. The normal form determines the name of the exact
numeric type in the numeric type descriptor."

"11) If <data type> DT specifies an exact numeric type, then:
      a) There shall be an implementation-defined function ENNF() 
that converts any <exact numeric type> ENT1 into some possibly 
different <exact numeric type> ENT2 (the normal form of ENT1), subject 
to the following constraints on ENNF():
          i) For every <exact numeric type> ENT, ENNF(ENT) shall not 
specify DEC or INT.
NOTE 86   The preceding requirement prohibits the function ENNF from
returning a value that uses the abbreviated spelling of the two data
types; the function shall instead return the long versions of DECIMAL
or INTEGER.
         ii) For every <exact numeric type> ENT, the precision, scale, 
and radix of ENNF(ENT) shall be the precision, scale, and radix of ENT.
        iii) For every <exact numeric type> ENT, ENNF(ENT) shall be the 
same as ENNF(ENNF(ENT)).
       iv) For every <exact numeric type> ENT, if ENNF(ENT) specifies 
DECIMAL, then ENNF(ENT) shall specify <precision>, and the precision of 
ENNF(ENT) shall be the value of the <precision> specified in ENNF(ENT)."

In other words, the SQL:2003 Standard says it is okay to change NUMERIC 
to DECIMAL, provided that the precision, scale and radix remain the same.
Since this is the case for MySQL, there is no need to change the current
behaviour provided the Reference Manual makes it clear that NUMERIC is
a synonym for DECIMAL.