Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-45

JDBC types in metadata for FLOAT and INT UNSIGNED are different than in ConnectorJ

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.1.2
    • 1.1.3
    • None
    • None

    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.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. CONJ-96 JDBC types differ between DatabaseMetaData.getColumns and ResultSetMetaData.getColumnType

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            wlad Vladislav Vaintroub added a comment -

            Thank you for the patch!

            wlad Vladislav Vaintroub added a comment - Thank you for the patch!
            JP Jacob Poder added a comment -

            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

            JP Jacob Poder added a comment - 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

            People

              wlad Vladislav Vaintroub
              dverstap Davy Verstappen
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.