Bug #9343 INFORMATION_SCHEMA contains wrong data type names
Submitted: 22 Mar 2005 18:58 Modified: 1 Nov 2006 15:22
Reporter: Matthias Leich Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S4 (Feature request)
Version:5.0 OS:
Assigned to: CPU Architecture:Any

[22 Mar 2005 18:58] Matthias Leich
Description:
I propose that the content of the table 
INFORMATION_SCHEMA.COLUMNS column DATA_TYPE
has to be changed into the direction of the SQL standard. 

In the moment I get such values like

 CREATE/Standard        | MySQL
---------------------------------------
CHARACTER VARYING(10) varchar(10)
CHARACTER                   char
BIGINT                         bigint
INTEGER                       int
SMALLINT                     smallint
DECIMAL                       decimal
NUMERIC                      decimal !! Bug#8434 Precision math: silent data type change ...
REAL                            double
FLOAT                          float
DOUBLE PRECISION       double

CREATE/Standard means I used this data type name within the CREATE TABLE 
           statement and the draft of the SQL 200X standard 
                  32N1011-WD9075-11-Schemata-2003-09.pdf
           + the NIST tests expects such content of the column DATA_TYPE.
MySQL  The content of the column INFORMATION_SCHEMA.COLUMNS.DATA_TYPE.

I know that SQL standard allows to use data type names like 'char', 'int' 
within the CREATE TABLE statements, but I fear this is not applicable for the
INFORMATION_SCHEMA.

My environment:
   - Intel PC with Linux(SuSE 9.1)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1840, 2005-03-22 

How to repeat:
set sql_mode='traditional';

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1
( x_character_varying CHARACTER VARYING(10),
  x_character CHARACTER(10),
  x_bigint BIGINT,
  x_integer INTEGER,
  x_smallint SMALLINT,
  x_decimal DECIMAL(5,3),
  x_numeric NUMERIC(5,3),
  x_real REAL,
  x_float FLOAT,
  x_double_precision DOUBLE PRECISION );
show create table t1;

SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME= 't1';
[1 Nov 2006 15:22] Valeriy Kravchuk
Thank you for a reasonable feature request.
[21 Nov 2006 1:05] Roland Bouman
I think that it would make more sense to add a column for the equivalent standards compliant type and not alter existing behaviour.