Bug #43566 Wrong INFORMATION_SCHEMA data in COLUMNS table when type is YEAR, DATETIME, etc.
Submitted: 11 Mar 2009 16:04 Modified: 18 Aug 2009 16:13
Reporter: Andrey Hristov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:6.0/5.0/5.1 OS:Any
Assigned to: CPU Architecture:Any

[11 Mar 2009 16:04] Andrey Hristov
Description:
Multiple columns contain wrong data. Cross check with the metadata sent from the server with the result set:
- CHARACTER_MAXIMUM_LENGTH
- CHARACTER_OCTET_LENGTH
- NUMERIC_PRECISION
- NUMERIC_SCALE
- COLLATION_NAME

The same is valid for date, datetime, time and timestamp!

How to repeat:
mysql> create table tyear(a year(2));
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tyear;
Field   1:  `a`
Catalog:    `def`
Database:   `test`
Table:      `tyear`
Org_table:  `tyear`
Type:       YEAR
Collation:  binary (63)
Length:     2
Max_length: 2
Decimals:   0
Flags:      UNSIGNED ZEROFILL NUM

mysql> select * from information_schema.columns where table_name='tyear'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: test
              TABLE_NAME: tyear
             COLUMN_NAME: a
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: year
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: year(2)
              COLUMN_KEY:
                   EXTRA:
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:
                 STORAGE: Default
                  FORMAT: Default
1 row in set (0.02 sec)
[11 Mar 2009 17:18] MySQL Verification Team
Thank you for the bug report. Verified as described.
[18 Aug 2009 16:13] Peter Gulutzan
For datetime columns these values should be NULL.
From the SQL standard document:
"
Case:
a) If the data type being described is a character string type, then ...
b) If the data type being described is a binary string type, then ...
c) If the data type being described is a reference type, then ...
d) Otherwise, the values of CHARACTER_MAXIMUM_LENGTH and
   CHARACTER_OCTET_LENGTH are the null value.
"
There are also provisions that NUMERIC_PRECISION and NUMERIC_SCALE
and COLLATION_NAME must be NULL for 'DATE', 'TIME', 'TIMESTAMP'.

So for datetime columns MySQL is merely following the standard,
which is our declared general intent, as the manual says:
"Our intent is approximate compliance with SQL:2003 core feature
F021 Basic information schema."

YEAR is not a standard type, but we declare that it's a "date and
time type", and the (2) in YEAR(2) is not precision but display width.
Perhaps some argument could be made about whether YEAR is classified
wrong, but that's not what this bug report is about.

Not a bug.