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 16:04]
Andrey Hristov
[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.