MySQL Bugs: #11057: information_schema: columns table has some questionable contents

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:	Sergei Glukhov	CPU Architecture:	Any

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

Verified BK source 5.0

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.

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

Ok to push.

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

Fixed in 5.0.10

Documented in 5.1.10 changelog.