Bug #11057 information_schema: columns table has some questionable contents
Submitted: 3 Jun 2005 0:30 Modified: 20 Jul 2005 15:05
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.7-beta-debug OS:Linux (SUSE 9.2)
Assigned to: Sergey Glukhov

[3 Jun 2005 0:30] Peter Gulutzan
Description:
Looking at INFORMATION_SCHEMA.COLUMNS, I see some data that I regard
as "questionable".

BIT is a numeric data type.
But COLUMNS.NUMERIC_PRECISION IS NULL.

LONGTEXT maximum length is 4GB.
But COLUMNS.CHARACTER_OCTET_LENGTH=16777215.

BLOB has a maximum length.
But COLUMNS.CHARACTER_OCTET_LENGTH IS NULL.
And COLUMNS.CHARACTER_MAXIMUM_LENGTH IS NULL.
Changing this is not a standard-SQL requirement; however,
other DBMSs seem to have a length in equivalent columns.

SMALLINT can only have 5 digits.
But COLUMNS.NUMERIC_PRECISION=6.
(I only worry that this might affect a program that uses the data with a Connector.)

COLUMNS.COLUMN_TYPE should be VARCHAR.
But I see that it's defined as LONGTEXT.

In some cases I will understand if the response is that this behaviour
is unchangeable for now.

How to repeat:
SELECT ... FROM INFORMATION_SCHEMA.COLUMNS
[3 Jun 2005 0:58] Miguel Solorzano
Verified BK source 5.0
[3 Jun 2005 12:12] Sergei Golubchik
And just for the sake of completeness:

A column defined as TIMESTAMP NOT NULL, is not nullable, but
COLUMNS.IS_NULLABLE is 'YES'

this is one of the cases where the behaviour cannot be easily changed.
[8 Jul 2005 8:32] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26805
[18 Jul 2005 9:29] Alexander Barkov
Ok to push.
[19 Jul 2005 9:24] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27290
[20 Jul 2005 7:42] Sergey Glukhov
Fixed in 5.0.10
[20 Jul 2005 15:05] Mike Hillyer
Documented in 5.1.10 changelog.