Bug #42758 INFORMATION_SCHEMA.COLUMNS is inconsistent
Submitted: 11 Feb 2009 12:24 Modified: 18 Mar 2009 15:51
Reporter: Andrey Hristov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:5.0+ OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any

[11 Feb 2009 12:24] Andrey Hristov 
Description:
The DATA_TYPE column shows inconsistent data for (tinyint|smallint|mediumint|bigint) unsigned. OTOH, float unsigned and double unsigned are shown correctly. The MySQL extension COLUMN_TYPE shows correct data.

CREATE TABLE `manycols` (
  `tiny` tinyint(4) DEFAULT NULL,
  `tiny_uns` tinyint(3) unsigned DEFAULT NULL,
  `small` smallint(6) DEFAULT NULL,
  `small_uns` smallint(5) unsigned DEFAULT NULL,
  `medium` mediumint(9) DEFAULT NULL,
  `medium_uns` mediumint(8) unsigned DEFAULT NULL,
  `int_col` int(11) DEFAULT NULL,
  `int_col_uns` int(10) unsigned DEFAULT NULL,
  `big` bigint(20) DEFAULT NULL,
  `big_uns` bigint(20) unsigned DEFAULT NULL,
  `decimal_col` decimal(10,5) DEFAULT NULL,
  `fcol` float DEFAULT NULL,
  `fcol_uns` float unsigned DEFAULT NULL,
  `dcol` double DEFAULT NULL,
  `dcol_uns` double unsigned DEFAULT NULL,
  `date_col` date DEFAULT NULL,
  `time_col` time DEFAULT NULL,
  `timestamp_col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `year_col` year(4) DEFAULT NULL,
  `bit_col` bit(5) DEFAULT NULL,
  `char_col` char(5) DEFAULT NULL,
  `varchar_col` varchar(10) DEFAULT NULL,
  `binary_col` binary(10) DEFAULT NULL,
  `varbinary_col` varbinary(10) DEFAULT NULL,
  `tinyblob_col` tinyblob,
  `blob_col` blob,
  `mediumblob_col` mediumblob,
  `longblob_col` longblob,
  `text_col` text,
  `mediumtext_col` mediumtext,
  `longtext_col` longtext
)

How to repeat:
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.31-debug |
+--------------+
1 row in set (0.00 sec)

mysql> create table manycols(tiny tinyint, tiny_uns tinyint unsigned, small smallint, small_uns smallint unsigned, medium mediumint, medium_uns mediumint unsigned, int_col int, int_col_uns int unsigned, big bigint, big_uns bigint unsigned, decimal_col decimal(10,5), fcol float, fcol_uns float unsigned, dcol double, dcol_uns double unsigned, date_col date, time_col time, timestamp_col timestamp, year_col year, bit_col bit(5),  char_col char(5), varchar_col varchar(10), binary_col binary(10), varbinary_col varbinary(10), tinyblob_col tinyblob, blob_col blob, mediumblob_col mediumblob, longblob_col longblob, text_col text, mediumtext_col mediumtext, longtext_col longtext);
Query OK, 0 rows affected (0.01 sec)

mysql> select COLUMN_NAME, DATA_TYPE,COLUMN_TYPE from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='manycols';
+----------------+-----------------+-----------------------+
| COLUMN_NAME    | DATA_TYPE       | COLUMN_TYPE           |
+----------------+-----------------+-----------------------+
| tiny           | tinyint         | tinyint(4)            |
| tiny_uns       | tinyint         | tinyint(3) unsigned   |
| small          | smallint        | smallint(6)           |
| small_uns      | smallint        | smallint(5) unsigned  |
| medium         | mediumint       | mediumint(9)          |
| medium_uns     | mediumint       | mediumint(8) unsigned |
| int_col        | int             | int(11)               |
| int_col_uns    | int             | int(10) unsigned      |
| big            | bigint          | bigint(20)            |
| big_uns        | bigint          | bigint(20) unsigned   |
| decimal_col    | decimal         | decimal(10,5)         |
| fcol           | float           | float                 |
| fcol_uns       | float unsigned  | float unsigned        |
| dcol           | double          | double                |
| dcol_uns       | double unsigned | double unsigned       |
| date_col       | date            | date                  |
| time_col       | time            | time                  |
| timestamp_col  | timestamp       | timestamp             |
| year_col       | year            | year(4)               |
| bit_col        | bit             | bit(5)                |
| char_col       | char            | char(5)               |
| varchar_col    | varchar         | varchar(10)           |
| binary_col     | binary          | binary(10)            |
| varbinary_col  | varbinary       | varbinary(10)         |
| tinyblob_col   | tinyblob        | tinyblob              |
| blob_col       | blob            | blob                  |
| mediumblob_col | mediumblob      | mediumblob            |
| longblob_col   | longblob        | longblob              |
| text_col       | text            | text                  |
| mediumtext_col | mediumtext      | mediumtext            |
| longtext_col   | longtext        | longtext              |
+----------------+-----------------+-----------------------+
31 rows in set (0.00 sec)
[11 Feb 2009 12:31] MySQL Verification Team 
Thank you for the bug report. Verified as described.
[24 Feb 2009 10:26] 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/commits/67335

2755 Sergey Glukhov	2009-02-24
      Bug#42758 INFORMATION_SCHEMA.COLUMNS is inconsistent
      MySQL column type has the following format:
      base_type [(dimension)] [unsigned] [zerofill]
      The problem is that we extract only base_type for DATA_TYPE value.
      The fix is to extract full type except of dimention part.
     @ mysql-test/r/information_schema.result
        test result
     @ mysql-test/suite/funcs_1/r/is_columns_innodb.result
        result fix
     @ mysql-test/suite/funcs_1/r/is_columns_memory.result
        result fix
     @ mysql-test/suite/funcs_1/r/is_columns_myisam.result
        result fix
     @ mysql-test/suite/funcs_1/r/is_columns_mysql.result
        result fix
     @ mysql-test/t/information_schema.test
        test case
     @ sql/sql_show.cc
        MySQL column type has the following format:
        base_type [(dimension)] [unsigned] [zerofill]
        The problem is that we extract only base_type for DATA_TYPE value.
        The fix is to extract full type except of dimention part.
[24 Feb 2009 11:58] 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/commits/67357

2755 Sergey Glukhov	2009-02-24
      Bug#42758 INFORMATION_SCHEMA.COLUMNS is inconsistent
      MySQL column type has the following format:
      base_type [(dimension)] [unsigned] [zerofill]
      The problem is that we extract only base_type for DATA_TYPE value.
      The fix is to extract full type except of dimention part.
     @ mysql-test/r/information_schema.result
        test result
     @ mysql-test/suite/funcs_1/r/is_columns_innodb.result
        result fix
     @ mysql-test/suite/funcs_1/r/is_columns_memory.result
        result fix
     @ mysql-test/suite/funcs_1/r/is_columns_myisam.result
        result fix
     @ mysql-test/suite/funcs_1/r/is_columns_mysql.result
        result fix
     @ mysql-test/t/information_schema.test
        test case
     @ sql/sql_show.cc
        MySQL column type has the following format:
        base_type [(dimension)] [unsigned] [zerofill]
        The problem is that we extract only base_type for DATA_TYPE value.
        The fix is to extract full type except of dimention part.
[26 Feb 2009 14:10] 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/commits/67689

2755 Sergey Glukhov	2009-02-26
      Bug#42758 INFORMATION_SCHEMA.COLUMNS is inconsistent
      remove 'unsigned', 'zerofill' attributes 
      from DATA_TYPE column value.
     @ mysql-test/suite/funcs_1/r/is_columns_innodb.result
        result fix
     @ mysql-test/suite/funcs_1/r/is_columns_memory.result
        result fix
     @ mysql-test/suite/funcs_1/r/is_columns_myisam.result
        result fix
     @ sql/sql_show.cc
        remove 'unsigned', 'zerofill' attributes 
        from DATA_TYPE column value.
[27 Feb 2009 8:01] 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/commits/67791

2755 Sergey Glukhov	2009-02-27
      Bug#42758 INFORMATION_SCHEMA.COLUMNS is inconsistent
      remove 'unsigned', 'zerofill' attributes 
      from DATA_TYPE column value.
     @ mysql-test/suite/funcs_1/r/is_columns_innodb.result
        result fix
     @ mysql-test/suite/funcs_1/r/is_columns_memory.result
        result fix
     @ mysql-test/suite/funcs_1/r/is_columns_myisam.result
        result fix
     @ sql/sql_show.cc
        remove 'unsigned', 'zerofill' attributes 
        from DATA_TYPE column value.
[5 Mar 2009 11:16] 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/commits/68356

2831 Sergey Glukhov	2009-03-05
      Bug#42758 INFORMATION_SCHEMA.COLUMNS is inconsistent
      remove 'unsigned', 'zerofill' attributes
      from DATA_TYPE column value.
     @ mysql-test/suite/funcs_1/r/is_columns_innodb.result
        result fix
     @ mysql-test/suite/funcs_1/r/is_columns_memory.result
        result fix
     @ mysql-test/suite/funcs_1/r/is_columns_myisam.result
        result fix
     @ sql/sql_show.cc
        remove 'unsigned', 'zerofill' attributes 
        from DATA_TYPE column value.
[13 Mar 2009 19:03] Bugs System 
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:zhou.li@sun.com-20090311061050-ihp0g77znonq1tuq) (merge vers: 5.1.33) (pib:6)
[15 Mar 2009 2:31] Paul DuBois 
Noted in 5.1.33 changelog.

The DATA_TYPE column of the INFORMATION_SCHEMA.COLUMNS table
displayed the UNSIGNED attribute for floating-point data types. (The
column should contain only the data type name.)

Setting report to NDI pending push into 6.0.x.
[18 Mar 2009 13:21] Bugs System 
Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:matthias.leich@sun.com-20090310140952-gwtoq87wykhji3zi) (merge vers: 6.0.11-alpha) (pib:6)
[18 Mar 2009 15:51] Paul DuBois 
Noted in 6.0.11 changelog.
[9 May 2009 16:41] Bugs System 
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:38] Bugs System 
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:36] Bugs System 
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)