Обсуждение: [JDBC] getTypeInfo

Hi,

I noticed that getTypeInfo() may not return data in the order specified in Oracle documentation (https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTypeInfo()):

Retrieves a description of all the data types supported by this database. They are ordered by DATA_TYPE and then by how closely the data type maps to the corresponding JDBC SQL type.

With the following code snippet:

try (Connection connection = dataSource.getConnection(); ResultSet rs = connection.getMetaData().getTypeInfo()) {

    while (rs.next()) {

        System.out.println(rs.getInt("DATA_TYPE"));

    }

} catch (SQLException e) {}

I am getting DATA_TYPE in a non-monotonic order:

-7

-2

1

12

-5

...

Additional metadata:

JDBC driver: PostgreSQL 9.4.1212

Server version: 9.6beta1.0 (9.6beta1.0)

What do I do wrong?

Hi,

I noticed that getTypeInfo() may not return data in the order specified in Oracle documentation (https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTypeInfo()):

Retrieves a description of all the data types supported by this database. They are ordered by DATA_TYPE and then by how closely the data type maps to the corresponding JDBC SQL type.

With the following code snippet:

try (Connection connection = dataSource.getConnection(); ResultSet rs = connection.getMetaData().getTypeInfo()) {

    while (rs.next()) {

        System.out.println(rs.getInt("DATA_TYPE"));

    }

} catch (SQLException e) {}

I am getting DATA_TYPE in a non-monotonic order:

-7

-2

1

12

-5

...

Additional metadata:

JDBC driver: PostgreSQL 9.4.1212

Server version: 9.6beta1.0 (9.6beta1.0)

What do I do wrong?

On 14 December 2016 at 07:32, Jan Motl <yzan@volny.cz> wrote:

Hi,

I noticed that getTypeInfo() may not return data in the order specified in Oracle documentation (https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTypeInfo()):

Retrieves a description of all the data types supported by this database. They are ordered by DATA_TYPE and then by how closely the data type maps to the corresponding JDBC SQL type.

With the following code snippet:

try (Connection connection = dataSource.getConnection(); ResultSet rs = connection.getMetaData().getTypeInfo()) {

    while (rs.next()) {

        System.out.println(rs.getInt("DATA_TYPE"));

    }

} catch (SQLException e) {}

I am getting DATA_TYPE in a non-monotonic order:

-7

-2

1

12

-5

...

Additional metadata:

JDBC driver: PostgreSQL 9.4.1212

Server version: 9.6beta1.0 (9.6beta1.0)

What do I do wrong?

Nothing, I just looked at the code and there is no order by on there

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

"SELECT t.typname,t.oid FROM pg_catalog.pg_type t"
      + " JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) "
      + " WHERE n.nspname  != 'pg_toast'";

On 14 December 2016 at 07:32, Jan Motl <yzan@volny.cz> wrote:

Hi,

I noticed that getTypeInfo() may not return data in the order specified in Oracle documentation (https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTypeInfo()):

Retrieves a description of all the data types supported by this database. They are ordered by DATA_TYPE and then by how closely the data type maps to the corresponding JDBC SQL type.

With the following code snippet:

try (Connection connection = dataSource.getConnection(); ResultSet rs = connection.getMetaData().getTypeInfo()) {

    while (rs.next()) {

        System.out.println(rs.getInt("DATA_TYPE"));

    }

} catch (SQLException e) {}

I am getting DATA_TYPE in a non-monotonic order:

-7

-2

1

12

-5

...

Additional metadata:

JDBC driver: PostgreSQL 9.4.1212

Server version: 9.6beta1.0 (9.6beta1.0)

What do I do wrong?

Nothing, I just looked at the code and there is no order by on there

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

"SELECT t.typname,t.oid FROM pg_catalog.pg_type t"
      + " JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) "
      + " WHERE n.nspname  != 'pg_toast'";

Hi Dave,

Looked at this and here is the query that we use:

"SELECT t.typname,t.oid FROM pg_catalog.pg_type t"
      + " JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) "
      + " WHERE n.nspname  != 'pg_toast'";

So we could order on typname, but there's no guarantee that it would line up with java.sqltypes

I do not think that order on TYPE_NAME would improve the compliance as the documentation talks about DATA_TYPE. Maybe a sort on the filled:

byte[][] tuple

by column 1 (DATA_TYPE) could be used? 

It is not nice to perform sorting in Java instead of in SQL, but I do not think that PostgreSQL is going to contain Java DATA_TYPE anywhere -> if we wanted to perform sorting in SQL, we would have to first upload DATA_TYPE and I am not sure it is worth that.

To make the ordering unique, the comparator could first compare by DATA_TYPE (has the priority) then by TYPE_NAME (TYPE_NAME is unique -> the ordering is unique).

The result is still not going to be perfect, because bigserial is going to be before int8, even thought range of int8 matches JDBC BIGINT better (https://www.postgresql.org/docs/9.6/static/datatype-numeric.html and http://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/mapping.html). And the documentation states:

...and then by how closely the data type maps to the corresponding JDBC SQL type.

Fortunately, PostgreSQL has only a few data types where the order can make a difference (DATA_TYPEs =< 93). Proposed ordering:

bit, boolean (because MySQL driver uses the same order)

int8, bigserial, oid (because BIGINT should be signed and oid is internal)

char, bchar (because bchar is internal)

int4, serial (because INTEGER should be signed)

float, money (because DOUBLE should be a floating point)

varchar, text, name (because VARCHAR has typically length up to 254 character and name is internal)

time, timetz (because TIME is without time zone)

timestamp, timestamptz (because TIMESTAMP is without time zone)

Best regards,

 Jan Motl

PS:

Oid data type should possibly not be classified as BIGINT but as INTEGER, because oid is using 4 bytes, not 8 bytes:

https://www.postgresql.org/docs/9.6/static/datatype-oid.html

To me, oid looks a lot like serial data type, which is classified as INTEGER.

Money data type should possibly not be classified as DOUBLE but as NUMERIC, because money is using fixed fractional precision.

Text data type should possibly not be classified as VARCHAR but as LONGVARCHAR, because text can be much longer than VARCHAR.

Boolean could be classified as BOOLEAN (introduced into JDBC in Java 1.4). Since XML is already correctly classified and XML is in JDBC since 1.6, the transition should be justifiable.

Hi Dave,

Looked at this and here is the query that we use:

"SELECT t.typname,t.oid FROM pg_catalog.pg_type t"
      + " JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) "
      + " WHERE n.nspname  != 'pg_toast'";

So we could order on typname, but there's no guarantee that it would line up with java.sqltypes

I do not think that order on TYPE_NAME would improve the compliance as the documentation talks about DATA_TYPE. Maybe a sort on the filled:

byte[][] tuple

by column 1 (DATA_TYPE) could be used? 

It is not nice to perform sorting in Java instead of in SQL, but I do not think that PostgreSQL is going to contain Java DATA_TYPE anywhere -> if we wanted to perform sorting in SQL, we would have to first upload DATA_TYPE and I am not sure it is worth that.

To make the ordering unique, the comparator could first compare by DATA_TYPE (has the priority) then by TYPE_NAME (TYPE_NAME is unique -> the ordering is unique).

The result is still not going to be perfect, because bigserial is going to be before int8, even thought range of int8 matches JDBC BIGINT better (https://www.postgresql.org/docs/9.6/static/datatype-numeric.html and http://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/mapping.html). And the documentation states:

...and then by how closely the data type maps to the corresponding JDBC SQL type.

Fortunately, PostgreSQL has only a few data types where the order can make a difference (DATA_TYPEs =< 93). Proposed ordering:

bit, boolean (because MySQL driver uses the same order)

int8, bigserial, oid (because BIGINT should be signed and oid is internal)

char, bchar (because bchar is internal)

int4, serial (because INTEGER should be signed)

float, money (because DOUBLE should be a floating point)

varchar, text, name (because VARCHAR has typically length up to 254 character and name is internal)

time, timetz (because TIME is without time zone)

timestamp, timestamptz (because TIMESTAMP is without time zone)

Best regards,

 Jan Motl

PS:

Oid data type should possibly not be classified as BIGINT but as INTEGER, because oid is using 4 bytes, not 8 bytes:

https://www.postgresql.org/docs/9.6/static/datatype-oid.html

To me, oid looks a lot like serial data type, which is classified as INTEGER.

Money data type should possibly not be classified as DOUBLE but as NUMERIC, because money is using fixed fractional precision.

Text data type should possibly not be classified as VARCHAR but as LONGVARCHAR, because text can be much longer than VARCHAR.

Boolean could be classified as BOOLEAN (introduced into JDBC in Java 1.4). Since XML is already correctly classified and XML is in JDBC since 1.6, the transition should be justifiable.

Hi Dave,

> Curious: Why do you care about the order ?
The driver can return quite many data types because, beside other, it returns 2 tuples per relation in the database:
        https://www.postgresql.org/message-id/CADK3HHLK6V4JSfiGHLst2yQKO8rnKqObZefUaF0rDCqRASZpfw%40mail.gmail.com
So I thought I could read rows from the resultSet while DATA_TYPES =< 93 and ignore the rest (currently thousands of rows on my instance of PostgreSQL but in theory billions of rows are possible), since I am interested only in processing of DATA_TYPES =< 93. But if the order of the rows in the resultSet is not in ascending order of DATA_TYPE, I have to read all rows from the resultSet.

I do not have a current need for correct ordering beyond DATA_TYPE resolution. But I can imagine that the correct ordering could be helpful if I wanted to write Java data types into a database the agnostic way (e.g. if I wanted to write down BIGINT, it could be helpful if the driver proposed to use int8 instead of oid and I did not have to hardcode the mapping for each database vendor (or use a library that would do that)).

Beside of that, all official drivers for other databases that I tested (MySQL, Oracle, SAS) return the data types in the correct order.

Hi Dave,

> Curious: Why do you care about the order ?
The driver can return quite many data types because, beside other, it returns 2 tuples per relation in the database:
        https://www.postgresql.org/message-id/CADK3HHLK6V4JSfiGHLst2yQKO8rnKqObZefUaF0rDCqRASZpfw%40mail.gmail.com
So I thought I could read rows from the resultSet while DATA_TYPES =< 93 and ignore the rest (currently thousands of rows on my instance of PostgreSQL but in theory billions of rows are possible), since I am interested only in processing of DATA_TYPES =< 93. But if the order of the rows in the resultSet is not in ascending order of DATA_TYPE, I have to read all rows from the resultSet.

I do not have a current need for correct ordering beyond DATA_TYPE resolution. But I can imagine that the correct ordering could be helpful if I wanted to write Java data types into a database the agnostic way (e.g. if I wanted to write down BIGINT, it could be helpful if the driver proposed to use int8 instead of oid and I did not have to hardcode the mapping for each database vendor (or use a library that would do that)).

Beside of that, all official drivers for other databases that I tested (MySQL, Oracle, SAS) return the data types in the correct order.