BUG #17911: Database or JDBC Driver Provides Incorrect Type
The following bug has been logged on the website:
Bug reference: 17911
Logged by: Stephen Bergeon
Email address: postgresql@bergeon.org
PostgreSQL version: 15.2
Operating system: Windows 10
Description:
When the JDBC driver returns a ResultSet, both from obtaining table column
info, as well as from an actual query, the DATA_TYPE returned is incorrect
for Time-with-Time-Zone and Timestamp-With-Time-Zone columns. In both cases
the returned value returned indicates that the non-time-zone value is
returned.
This has been tested on multiple versions of PostgreSQL and its JDBC
driver through the latest release [database version 15.2 and JDBC driver
42.6.0].
Please report this issue here:
pgsql-jdbc@postgresql.org
---------------------------------------------------------------------------
On Thu, Apr 27, 2023 at 02:59:30PM +0000, PG Bug reporting form wrote:
The following bug has been logged on the website:
Bug reference: 17911
Logged by: Stephen Bergeon
Email address: postgresql@bergeon.org
PostgreSQL version: 15.2
Operating system: Windows 10
Description:When the JDBC driver returns a ResultSet, both from obtaining table column
info, as well as from an actual query, the DATA_TYPE returned is incorrect
for Time-with-Time-Zone and Timestamp-With-Time-Zone columns. In both cases
the returned value returned indicates that the non-time-zone value is
returned.This has been tested on multiple versions of PostgreSQL and its JDBC
driver through the latest release [database version 15.2 and JDBC driver
42.6.0].
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Embrace your flaws. They make you human, rather than perfect,
which you will never be.
Hi,
Can you provide more detail please? Possibly are reproducer?
Dave Cramer
www.postgres.rocks
On Thu, 27 Apr 2023 at 14:27, PG Bug reporting form <noreply@postgresql.org>
wrote:
Show quoted text
The following bug has been logged on the website:
Bug reference: 17911
Logged by: Stephen Bergeon
Email address: postgresql@bergeon.org
PostgreSQL version: 15.2
Operating system: Windows 10
Description:When the JDBC driver returns a ResultSet, both from obtaining table column
info, as well as from an actual query, the DATA_TYPE returned is incorrect
for Time-with-Time-Zone and Timestamp-With-Time-Zone columns. In both cases
the returned value returned indicates that the non-time-zone value is
returned.This has been tested on multiple versions of PostgreSQL and its JDBC
driver through the latest release [database version 15.2 and JDBC driver
42.6.0].
Attached is a simple one-class program that should demonstrate the issue. The constants DB_URL, DB_USER_ID and DB_USER_PASSWORD must be changed for your environment. You also must have the PostgreSQL JDBC driver on the class-path.
The program creates a table named TestTable and then queries the JDBC driver for column information about the table. Then it displays the results. The same issue occurs if checking the ResultSetMetaData resulting from executind a DB select statement.
In my current test environment the program results display as follows…
Connecting to the DB.
DB Product: PostgreSQL version 15.2
DB Driver: PostgreSQL JDBC Driver version 42.2.14
Dropping the table if it exists.
Creating the table.
The data type for column 'id' is: Integer
The data type for column 'charvalue' is: Char
The data type for column 'varcharvalue' is: VarChar
The data type for column 'textvalue' is: VarChar
The data type for column 'binaryvalue' is: Binary
The data type for column 'intvalue' is: Integer
The data type for column 'floatvalue' is: Real
The data type for column 'doublevalue' is: Double
The data type for column 'booleanvalue' is: Bit
The data type for column 'datevalue' is: Date
The data type for column 'timevaluenotimezone' is: Time
The data type for column 'timevaluewithtimezone' is: Time
The data type for column 'timestampnotimezone' is: Timestamp
The data type for column 'timestampwithtimezone' is: Timestamp
All done.
The problem is demonstrated by the fact that column timevaluewithtimezone shows format Time instead of TimeWithTimeZone, and column timestampwithtimezone shows format Timestamp instead of TimestampWithTimeZone. Which means the driver is returning the java.sql.Types constants of Types.TIME instead of Types.TIME_WITH_TIMEZONE, and Types.TIMESTAMP instead of Types.TIMESTAMP_WITH_TIMEZONE, respectively.
By reporting the wrong SQL-Type, applications and libraries do not know the correct column type and cannot automatically process them properly. This is important if the application/library wants to call the ResultSet.getObject(column, class) method to return the appropriate Java type such as java.time.LocalTime vs java.time.OffsetTime or java.time.LocalDateTime vs java.time.OffsetDateTime.
Stephen C. Bergeon
From: Dave Cramer [mailto:davecramer@postgres.rocks]
Sent: Tuesday, May 16, 2023 6:43 AM
To: postgresql@bergeon.org; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17911: Database or JDBC Driver Provides Incorrect Type
Hi,
Can you provide more detail please? Possibly are reproducer?
Dave Cramer
www.postgres.rocks
On Thu, 27 Apr 2023 at 14:27, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 17911
Logged by: Stephen Bergeon
Email address: postgresql@bergeon.org
PostgreSQL version: 15.2
Operating system: Windows 10
Description:
When the JDBC driver returns a ResultSet, both from obtaining table column
info, as well as from an actual query, the DATA_TYPE returned is incorrect
for Time-with-Time-Zone and Timestamp-With-Time-Zone columns. In both cases
the returned value returned indicates that the non-time-zone value is
returned.
This has been tested on multiple versions of PostgreSQL and its JDBC
driver through the latest release [database version 15.2 and JDBC driver
42.6.0].
Attachments:
On Tue, 16 May 2023 at 16:10, <PostgreSQL@bergeon.org> wrote:
Attached is a simple one-class program that should demonstrate the
issue. The constants DB_URL, DB_USER_ID and DB_USER_PASSWORD must be
changed for your environment. You also must have the PostgreSQL JDBC
driver on the class-path.The program creates a table named TestTable and then queries the JDBC
driver for column information about the table. Then it displays the
results. The same issue occurs if checking the ResultSetMetaData resulting
from executind a DB select statement.In my current test environment the program results display as follows…
Connecting to the DB.
DB Product: PostgreSQL version 15.2
DB Driver: PostgreSQL JDBC Driver version 42.2.14
Dropping the table if it exists.
Creating the table.
The data type for column 'id' is: Integer
The data type for column 'charvalue' is: Char
The data type for column 'varcharvalue' is: VarChar
The data type for column 'textvalue' is: VarChar
The data type for column 'binaryvalue' is: Binary
The data type for column 'intvalue' is: Integer
The data type for column 'floatvalue' is: Real
The data type for column 'doublevalue' is: Double
The data type for column 'booleanvalue' is: Bit
The data type for column 'datevalue' is: Date
The data type for column 'timevaluenotimezone' is: Time
The data type for column 'timevaluewithtimezone' is: Time
The data type for column 'timestampnotimezone' is: Timestamp
The data type for column 'timestampwithtimezone' is: Timestamp
All done.
The problem is demonstrated by the fact that column
timevaluewithtimezone shows format Time instead of TimeWithTimeZone, and
column timestampwithtimezone shows format Timestamp instead of
TimestampWithTimeZone. Which means the driver is returning the
java.sql.Types constants of *Types**.TIME* instead of *Types*
*.TIME_WITH_TIMEZONE*, and *Types.**TIMESTAMP* instead of *Types.*
*TIMESTAMP_WITH_TIMEZONE*, respectively.By reporting the wrong SQL-Type, applications and libraries do not
know the correct column type and cannot automatically process them
properly. This is important if the application/library wants to call the
ResultSet.getObject(column, class) method to return the appropriate Java
type such as java.time.LocalTime vs java.time.OffsetTime or
java.time.LocalDateTime vs java.time.OffsetDateTime.
Ah, yes, we are aware of this and there is a PR to fix it, but it appears
to have been dropped by the author correct mapping for postgres timestamptz
type to sql type TIMESTAMP_W… by lopata2 · Pull Request #2715 ·
pgjdbc/pgjdbc (github.com) <https://github.com/pgjdbc/pgjdbc/pull/2715>
<https://github.com/pgjdbc/pgjdbc/pull/2715>Care to take it up ?
Dave