MySQL Bugs: #69042: MEDIUMINT precision/type incorrect in INFORMATION

Bug #69042	MEDIUMINT precision/type incorrect in INFORMATION_SCHEMA.COLUMNS
Submitted:	23 Apr 2013 16:31	Modified:	24 Apr 2013 5:17
Reporter:	Filipe Silva	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.68, 5.5.31, 5.6.11	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	INFORMATION_SCHEMA.COLUMNS, mediumint

Description:
MEDIUMINT data type information in INFORMATION_SCHEMA.COLUMNS is not consistent with other integer types.

A MEDIUMINT (signed) value is between -8388608 and 8388607, having numeric precision equals 7 and column type "mediumint(8)". The INFORMATION_SCHEMA.COLUMNS contains a COLUMN_TYPE of "mediumint(9)" instead.

A MEDIUMINT UNSIGNED value is between 0 and 16777215, having numeric precision equals 8 and column type "mediumint(8)". The INFORMATION_SCHEMA.COLUMNS contains a NUMERIC_PRECISION of 7 instead.

How to repeat:
USE test;

CREATE TABLE mediumint_precision_and_type (
m1 MEDIUMINT,
m2 MEDIUMINT UNSIGNED
);

SELECT COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='mediumint_precision_and_type';

/*
m1 should have COLUMN_TYPE = 'mediumint(8)' and m2 should have NUMERIC_PRECISION = 8.
*/

SHOW FULL COLUMNS FROM mediumint_precision_and_type FROM test LIKE '%';
-- also returns an incorrect column type for m1.

Suggested fix:
None.

Hello Filipe,

Thank you for the report.
Verified as described.

Thanks,
Umesh

My code is comparing a MySQL-reported schema against a reference schema without length, so the code has to apply a default length for the reference schema to compare properly. I have added an exception for signed MEDIUMINT to be either 8 or 9, in case this issue is fixed in the future. Testing with a temporary table to determine the default integer lengths would mitigate the issue, too.