Thread: BUG #17911: Database or JDBC Driver Provides Incorrect Type

BUG #17911: Database or JDBC Driver Provides Incorrect Type

From
PG Bug reporting form
Date:
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].


Re: BUG #17911: Database or JDBC Driver Provides Incorrect Type

From
Bruce Momjian
Date:
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.



Re: BUG #17911: Database or JDBC Driver Provides Incorrect Type

From
Dave Cramer
Date:
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].

   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].

Attachment

Re: BUG #17911: Database or JDBC Driver Provides Incorrect Type

From
Dave Cramer
Date:



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)

Care to take it up ?

Dave