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 9:05	Modified:	14 Aug 23:47
Reporter:	Alexander Hristov (Candidate Quality Contributor)
Status:	Verified
Category:	Connector/J	Severity:	S3 (Non-critical)
Version:	5.1.7	OS:	Any
Assigned to:	Mark Matthews	Target Version:
Tags:	getTypeInfo, metadata, getColumns
Triage:	D3 (Medium)

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