[CONJ-45] JDBC types in metadata for FLOAT and INT UNSIGNED are different than in ConnectorJ Created: 2013-06-16  Updated: 2014-04-29  Resolved: 2013-06-17

Status: Closed
Project: MariaDB Connector/J
Component/s: None
Affects Version/s: 1.1.2
Fix Version/s: 1.1.3

Type: Bug Priority: Major
Reporter: Davy Verstappen Assignee: Vladislav Vaintroub
Resolution: Fixed Votes: 0
Labels: None

Attachments: File 0001-Fix-compile-error.patch     File 0002-Fix-JDBC-type-code-for-MySQL-FLOAT-and-SMALLINT-INT-.patch    
Issue Links:
Relates
relates to CONJ-96 JDBC types differ between DatabaseMet... Closed

 Description   

The getColumnType() method of java.sql.ResultSetMetaData returns an
SQL type code (one of the constants in java.sql.Types). For some MySQL
types, the implementation of this method in the MariaDB Java Client
returns a different code than the implementation in MySQL
Connector/J. For example, for a column declared as "INT UNSIGNED", the
MariaDB Java Client returns BIGINT, but MySQL Connector/J returns
INTEGER.

This gives problems with Hibernate, which uses this metadata to do its
thing.

Here is the list of numerical types for which I found a mismatch:

Declared column type MariaDB Java Client MySQL Connector/J
FLOAT FLOAT REAL
FLOAT UNSIGNED FLOAT REAL
SMALLINT UNSIGNED INTEGER SMALLINT
INTEGER UNSIGNED BIGINT INTEGER
INT UNSIGNED BIGINT INTEGER

(INT and INTEGER are equivalent of course)

I'm attaching two patches:

  • The first one to make the current trunk compile (which it doesn't,
    at least not for me, because of a '-' in a method name).
  • The second fixes the problem, fixes the unit tests and adds one more
    unit tests, that combines test the metadata with actual data as
    well.

 Comments   
Comment by Vladislav Vaintroub [ 2013-06-17 ]

Thank you for the patch!

Comment by Jacob Poder [ 2014-04-03 ]

Hi,

If the metadata is retrieved through DatabaseMetadata, it seems to return wrong values for int unsigned and smallint unsigned:

// Example with smallint unsigned
// my_table has a single column my_col of type smallint unsigned
DatabaseMetaData connectionMetaData = connection.getMetaData();
ResultSet column = connectionMetaData.getColumns(null, null, "my_table", "my_col");
column.next(); // step to first row
int type = column.getInt(5); // column #5 contains the java sql type (see DatabaseMetaData.getColumns)

type will be 4 (INTEGER) instead of 5 (SMALLINT)!

This stems from org.mariadb.jdbc.MySQLDatabaseMetaData.dataTypeClause(...) which still has the "old" behavior:
WHEN 'smallint' THEN IF( " + fullTypeColumnName + " like '%unsigned%', "Types.INTEGER","+ Types.SMALLINT + ")

My apologies if I posted this in the wrong place.

Best regards,
Jacob

Generated at Thu Feb 08 03:12:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.