Bug #12301 INT data types, wrong value in INFORMATION_SCHEMA column NUMERIC_SCALE
Submitted: 1 Aug 2005 17:48 Modified: 17 Aug 2005 19:19
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0 OS:
Assigned to: Alexander Ivanov CPU Architecture:Any

[1 Aug 2005 17:48] Matthias Leich
Description:
INFORMATION_SCHEMA.COLUMNS.NUMERIC_SCALE contains for
columns of data type integer the wrong value NULL instead of 0.
This bug was introduced after 2005-07-13, because the NIST
tests showed correct values on that date.

References :
1) SQL standard 200X draft:  32N1011-WD9075-11-Schemata-2003-09.pdf
    Page 146 -- 6.21 DATA_TYPE_DESCRIPTOR contains the following

CONSTRAINT DATA_TYPE_DESCRIPTOR_DATA_TYPE_CHECK_COMBINATIONS
CHECK ( ( DATA_TYPE IN  ....... <several lines deleted>)
OR ( DATA_TYPE IN ( 'SMALLINT', 'INTEGER', 'BIGINT' )
       AND
       ( CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH,
       COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME ) 
                  IS NULL
       AND
                  NUMERIC_PRECISION_RADIX IN ( 2, 10 )
       AND
                  NUMERIC_PRECISION IS NOT NULL
       AND
                  NUMERIC_SCALE = 0
       <several lines deleted>
       

2. SQL-99 Complete, Really by Peter Gulutzan and Trudy Pelzer
    Chapter 16: SQL Catalogs

3. The NIST tests (more SQL-92 related tests)   
    file ist003.sql   testcase 0605

I assume that NUMERIC_SCALE should be also 0 for int data types
like TINYINT and MEDIUMINT.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1908.7.2, 2005-08-01

How to repeat:
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 (f1 INT, f2 TINYINT, f3 SMALLINT, 
                            f4 MEDIUMINT, f5 BIGINT);

SELECT NUMERIC_SCALE, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
[16 Aug 2005 18:00] Alexander Ivanov
The cause of the bug is missed value setting for the NUMERIC_SCALE column in the
get_schema_column_record() function (sql_show.cc).
[17 Aug 2005 11:47] 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/28385
[17 Aug 2005 19:06] Alexander Ivanov
ChangeSet
  1.1984 05/08/16 23:11:04 aivanov@mysql.com +3 -0
  information_schema.test:
    Added a comment line for a test case used for bug #12301.
  information_schema.result:
    Fixed some test cases results (bug #12301).
  sql_show.cc:
    Fixed bug #12301.
    The bug was due to a missing value setting for the NUMERIC_SCALE column
     in the get_schema_column_record() function (sql_show.cc).

The fix will appear in 5.0.12
[17 Aug 2005 19:19] Paul Dubois
Noted in 5.0.12 changelog.