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
Triage: D3 (Medium)

[18 Apr 2007 7:05] Alexander Hristov
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"));
    }
  }
}
[2 May 2007 8:31] Tonci Grgin
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
[2 May 2007 8:32] Tonci Grgin
Test case

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

[3 Oct 2007 14:24] 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/34828
[3 Oct 2007 14:53] 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/34829
[3 Oct 2007 14:59] 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/34830
[3 Oct 2007 16:42] 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/34839
[3 Oct 2007 16:46] 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/34840
[3 Oct 2007 18:59] 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/34855
[4 Oct 2007 20:42] Mark Matthews
Fixed for 5.0.8.
[5 Oct 2007 18:53] 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/35011
[8 Oct 2007 15:40] MC Brown
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().
[11 Oct 2007 20:11] 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/35407
[11 Oct 2007 20:24] 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/35410
[11 Oct 2007 20:52] 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/35414
[19 Nov 2007 0:57] 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/38019
[19 Nov 2007 2:52] 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/38024
[21 Dec 2008 21:59] Alexander Hristov
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
[12 Jan 2009 14:01] Tonci Grgin
Thank you Alexander, fails just like described on latest sources:
1) testBug27916(testsuite.simple.TestBug27916)junit.framework.AssertionFailedError: SET UNSIGNED
[7 Feb 2011 7:04] Tonci Grgin
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
[7 Feb 2011 7:55] Tonci Grgin
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.
[7 Feb 2011 8:39] Tonci Grgin
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.
[7 Feb 2011 8:55] Tonci Grgin
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;
	}
[7 Feb 2011 10:15] Tonci Grgin
Pushed up to revision 1037.