| 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: | |
| 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 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.
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)