MySQL Bugs: #27916: DatabaseMetaData.getTypeInfo() does not return all the available data types

Bug #27916	DatabaseMetaData.getTypeInfo() does not return all the available data types
Submitted:	18 Apr 2007 7:05	Modified:	7 Feb 2011 10:15
Reporter:	Alexander Hristov (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	getColumns, getTypeInfo, metadata

Description:
getTypeInfo() should return (as per the JDBC spec) all the data types supported by the database. Yet for many tables reading the type information of a column using the getColumns() method from DatabaseMetaData provides a TYPE_NAME that wasn't previously reported by getTypeInfo().

An Example of TYPE_NAMEs reported by getColumns() but not present in getTypeInfo() is "int unsigned".

Also, capitalization of the type name in getTypeInfo() and getColumns() is inconsistent : in the first one types are reported uppercased, in the second - lowercased

How to repeat:
import java.sql.*;
import java.util.*;

public class Bug {
  public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Properties properties = new Properties();
    properties.setProperty("user","...");
    properties.setProperty("password","...");
    properties.setProperty("useInformationSchema","true");
    properties.setProperty("nullCatalogMeansCurrent","false");
    Connection con =DriverManager.getConnection("jdbc:mysql://localhost/test",properties);
    DatabaseMetaData dbmt = con.getMetaData();
    ResultSet rs = dbmt.getTypeInfo();
    ArrayList types = new ArrayList();
    System.out.println("Available data types");
    while (rs.next()) {
    	System.out.println(rs.getString("TYPE_NAME"));
    	types.add(rs.getString("TYPE_NAME").toLowerCase());
    }
    
    System.out.println();
    System.out.println();
    rs = dbmt.getColumns("mysql",null,"time_zone_transition","%");
    while (rs.next()) {
    	String typeName = rs.getString("TYPE_NAME").toLowerCase();
    	System.out.println(typeName+ " "+(types.contains(typeName)?"found":"not found"));
    }
  }
}

Hi Alexander and thanks for your report.

Verified as described with MySQL server 5.0.38BK on WinXP Pro SP2. Details inside attached test case.

There was 1 failure:
1) testBug27916(testsuite.simple.TestBug27916)junit.framework.AssertionFailedError: int unsigned

Test case

Attachment: TestBug27916.java (text/x-java), 2.78 KiB.

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/34828

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/34829

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/34830

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/34839

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/34840

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/34855

Fixed for 5.0.8.

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/35011

A note has been added to the 5.0.8 changelog:

UNSIGNED types not reported via DBMD.getTypeInfo(), and capitalization of type names is not consistent between DBMD.getColumns(), RSMD.getColumnTypeName() and DBMD.getTypeInfo().

This fix also ensures that the precision of UNSIGNED MEDIUMINT and UNSIGNED BIGINT is reported correctly via DBMD.getColumns().

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/35407

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/35410

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/35414

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/38019

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/38024

Still not complete. Data type "set unsigned" is not reported by getTypeInfo()

In the code where I first reported the bug, replace 

rs = dbmt.getColumns("mysql",null,"time_zone_transition","%");

with 

rs = dbmt.getColumns("mysql",null,"proc","%");

to view the problem

Thank you Alexander, fails just like described on latest sources:
1) testBug27916(testsuite.simple.TestBug27916)junit.framework.AssertionFailedError: SET UNSIGNED

This comes from malformed I__S query mapping:
		   15 Query	SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM,TABLE_NAME,COLUMN_NAME,CASE  WHEN DATA_TYPE='LONGTEXT' THEN -1 WHEN DATA_TYPE='BLOB' THEN -4 WHEN DATA_TYPE='DOUBLE' THEN 8 WHEN DATA_TYPE='DOUBLE unsigned' THEN 8 WHEN DATA_TYPE='TINYTEXT' THEN 12 WHEN DATA_TYPE='CHAR' THEN 1 WHEN DATA_TYPE='FLOAT' THEN 7 WHEN DATA_TYPE='FLOAT unsigned' THEN 7 WHEN DATA_TYPE='YEAR' THEN 91 WHEN DATA_TYPE='BIT' THEN -7 WHEN DATA_TYPE='DECIMAL' THEN 3 WHEN DATA_TYPE='DECIMAL unsigned' THEN 3 WHEN DATA_TYPE='VARCHAR' THEN 12 WHEN DATA_TYPE='BINARY' THEN -2 WHEN DATA_TYPE='TINYBLOB' THEN -2 WHEN DATA_TYPE='TIME' THEN 92 WHEN DATA_TYPE='INTEGER' THEN 4 WHEN DATA_TYPE='TIMESTAMP' THEN 93 WHEN DATA_TYPE='SMALLINT' THEN 5 WHEN DATA_TYPE='VARBINARY' THEN -3 WHEN DATA_TYPE='MEDIUMBLOB' THEN -4 WHEN DATA_TYPE='DATETIME' THEN 93 WHEN DATA_TYPE='INT' THEN 4 WHEN DATA_TYPE='BIGINT' THEN -5 WHEN DATA_TYPE='TINYINT' THEN -6 WHEN DATA_TYPE='DATE' THEN 91 WHEN DATA_TYPE='TEXT' THEN -1 WHEN DATA_TYPE='GEOMETRY' THEN -2 WHEN DATA_TYPE='SET' THEN 1 WHEN DATA_TYPE='LONGBLOB' THEN -4 WHEN DATA_TYPE='INT24' THEN 4 WHEN DATA_TYPE='REAL' THEN 8 WHEN DATA_TYPE='NUMERIC' THEN 3 WHEN DATA_TYPE='NUMERIC unsigned' THEN 3 WHEN DATA_TYPE='MEDIUMINT' THEN 4 WHEN DATA_TYPE='MEDIUMTEXT' THEN -1 WHEN DATA_TYPE='ENUM' THEN 1 ELSE 1111 END  AS DATA_TYPE, UPPER(CASE WHEN LOCATE('unsigned', COLUMN_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END) AS TYPE_NAME,CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > 2147483647 THEN 2147483647 ELSE CHARACTER_MAXIMUM_LENGTH END AS COLUMN_SIZE, 65535 AS BUFFER_LENGTH,NUMERIC_SCALE AS DECIMAL_DIGITS,10 AS NUM_PREC_RADIX,CASE WHEN IS_NULLABLE='NO' THEN 0 ELSE CASE WHEN IS_NULLABLE='YES' THEN 1 ELSE 2 END END AS NULLABLE,COLUMN_COMMENT AS REMARKS,COLUMN_DEFAULT AS COLUMN_DEF,0 AS SQL_DATA_TYPE,0 AS SQL_DATETIME_SUB,CASE WHEN CHARACTER_OCTET_LENGTH > 2147483647 THEN 2147483647 ELSE CHARACTER_OCTET_LENGTH END AS CHAR_OCTET_LENGTH,ORDINAL_POSITION,IS_NULLABLE,NULL AS SCOPE_CATALOG,NULL AS SCOPE_SCHEMA,NULL AS SCOPE_TABLE,NULL AS SOURCE_DATA_TYPE,IF (EXTRA LIKE '%auto_increment%','YES','NO') AS IS_AUTOINCREMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA LIKE 'mysql' AND TABLE_NAME LIKE 'proc' AND COLUMN_NAME LIKE '%' ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION\G
*************************** 15. row ***************************
        TABLE_CAT: mysql
      TABLE_SCHEM: NULL
       TABLE_NAME: proc
      COLUMN_NAME: sql_mode
        DATA_TYPE: 1
        TYPE_NAME: SET UNSIGNED
      COLUMN_SIZE: 478
    BUFFER_LENGTH: 65535
   DECIMAL_DIGITS: NULL
   NUM_PREC_RADIX: 10
         NULLABLE: 0

SQL_MODE 'NO_UNSIGNED_SUBTRACTION' is picked up by automatically generated query against I__S resulting in sql_mode column of proc table becoming SET UNSIGNED.

Fixing.

Proposed solution:
  Autogenerated I__S query relies on 'unsigned' not being in DATA_TYPE of the field which is wrong in case when SET (and probably ENUM) list contains 'unsigned' word. Thus I will add following to the generated query:
UPPER(CASE WHEN LOCATE('unsigned', COLUMN_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 
AND LOCATE('set', DATA_TYPE) <> 1 AND LOCATE('enum', DATA_TYPE) <> 1 <<<
THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END) AS TYPE_NAME
in DatabaseMetaDataUsingInfoSchema.java, ~ln. 234.

Similar thing happens in DatabaseMetaData.java, ~ln.181 and should be fixed too.

Proposed solution:
  For older MySQL servers that do not have I__S, we need to exclude ENUM and SET types from 'unsigned' keyword lookup (DatabaseMetaData.java, ~ln. 194):
	if ((StringUtils.indexOfIgnoreCase(typeInfo, "unsigned") != -1) && 
		(StringUtils.indexOfIgnoreCase(typeInfo, "set") != 0) &&
		(StringUtils.indexOfIgnoreCase(typeInfo, "enum") != 0)) {
			fullMysqlType = mysqlType + " unsigned";
			isUnsigned = true;
	} else {
		fullMysqlType = mysqlType;
	}

Pushed up to revision 1037.