Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pz3AK-0004IO-1J for pgsql-bugs@arkaria.postgresql.org; Tue, 16 May 2023 22:29:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pz3AI-0001MU-8P for pgsql-bugs@arkaria.postgresql.org; Tue, 16 May 2023 22:29:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pz3AH-0001ML-Nc for pgsql-bugs@lists.postgresql.org; Tue, 16 May 2023 22:29:05 +0000 Received: from pgintl.fastcrypt.com ([149.56.129.164]) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1pz3AE-000CUx-OW for pgsql-bugs@lists.postgresql.org; Tue, 16 May 2023 22:29:04 +0000 Received: from mail-ed1-f45.google.com (mail-ed1-f45.google.com [209.85.208.45]) by pgintl.fastcrypt.com (Postfix) with ESMTPSA id 12BB9201FA for ; Tue, 16 May 2023 18:29:00 -0400 (EDT) Received: by mail-ed1-f45.google.com with SMTP id 4fb4d7f45d1cf-50bc2feb320so69284a12.3 for ; Tue, 16 May 2023 15:29:00 -0700 (PDT) X-Gm-Message-State: AC+VfDwr/ouiS4f+txfS8AUWsFlth0r99h01FW6pD3UDT/WVZzQMHzkN AwUBJgO9gBCOd65TXX9zTzgfyaMHVgdjx/+pkHI= X-Google-Smtp-Source: ACHHUZ5bjf1u164y7U9jUgD5OQG+BRoZGIMSawGuVqc4Qrg4ofDh/RR4LQWvo7GRcG2SRskO4gLbhcxZuzlvDRl4i7I= X-Received: by 2002:a17:907:9347:b0:94f:694e:b953 with SMTP id bv7-20020a170907934700b0094f694eb953mr33603163ejc.59.1684276139748; Tue, 16 May 2023 15:28:59 -0700 (PDT) MIME-Version: 1.0 References: <17911-76b72ef0b040e9da@postgresql.org> <007e01d98832$566bfbc0$0343f340$@Bergeon.org> In-Reply-To: <007e01d98832$566bfbc0$0343f340$@Bergeon.org> From: Dave Cramer Date: Tue, 16 May 2023 18:28:42 -0400 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: BUG #17911: Database or JDBC Driver Provides Incorrect Type To: PostgreSQL@bergeon.org Cc: pgsql-bugs@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000003743705fbd719b9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000003743705fbd719b9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, 16 May 2023 at 16:10, 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 resulti= ng > from executind a DB select statement. > > > > In my current test environment the program results display as follows= =E2=80=A6 > > > > 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=E2=80=A6 by lopata2 =C2=B7 Pull Request #2715 = =C2=B7 pgjdbc/pgjdbc (github.com) Care to take it up ? Dave > > --00000000000003743705fbd719b9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

<= div dir=3D"ltr" class=3D"gmail_signature">

=

On Tue, 16 May 2023 at 16:10, <PostgreSQL@bergeon.org> wrote:

=C2=A0=C2=A0=C2=A0Attached is a simple one-class program tha= t should demonstrate the issue.=C2=A0 The constants DB_URL, DB_USER_ID and = DB_USER_PASSWORD must be changed for your environment.=C2=A0 You also must = have the PostgreSQL JDBC driver on the class-path.

=

=C2=A0

=C2=A0=C2=A0=C2=A0 The program creates a table named TestTable = and then queries the JDBC driver for column information about the table.=C2= =A0 Then it displays the results.=C2=A0 The same issue occurs if checking t= he ResultSetMetaData resulting from executind a DB select statement.=

=C2=A0

=C2=A0=C2=A0=C2=A0 In my current test environ= ment the program results display as follows=E2=80=A6

=C2=A0

Connecting to the= DB.

=C2=A0<= /u>

DB Product: PostgreSQL version 15.2

DB Driver: PostgreSQL JDBC Driver ver= sion 42.2.14

= =C2=A0

Dropping the table if it exists.

Creating the table.

=C2=A0

The d= ata type for column 'id' is: Integer

The data type for column 'charvalue&= #39; is: Char

The data type for column 'varcharvalue' is: VarChar

The data type for col= umn 'textvalue' is: VarChar

The data type for column 'binaryvalue' is= : Binary

T= he 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 ty= pe for column 'booleanvalue' is: Bit

The data type for column 'datevalue&= #39; is: Date

The data type for column 'timevaluenotimezone' is: Time<= span style=3D"font-family:Consolas">

The data type for= column 'timevaluewithtimezone' is: Time

The data type for column 'timest= ampnotimezone' is: Timestamp

The data type for column 'timestampwithtimezone&= #39; is: Timestamp

= =C2=A0

All done.

=C2=A0

=C2=A0=C2=A0=C2=A0 The problem is demonstrated by the fact that col= umn timevaluewithti= mezone shows format Time instead of TimeWithTimeZone, and column timestampwithtimezone shows format Tim= estamp instead of TimestampWithTimeZone.=C2=A0 Which means the driver = is returning the java.sql.Types constants of Types.TIME= instead of Types.TIME_WITH_TIMEZONE, and Types.= TIM= ESTAMP instead of Types.TIMESTAMP_WITH_TIMEZONE<= /b>, respectively.

=C2=A0

=C2=A0=C2=A0=C2= =A0 By reporting the wrong SQL-Type, applications and libraries do not know= the correct column type and cannot automatically process them properly.=C2= =A0 This is important if the application/library wants to call the ResultSe= t.getObject(column, class) method to return the appropriate Java type such = as java.t= ime.LocalTime vs java.time.Offs= etTime or java.time.LocalDateTi= me vs java.time.OffsetDateTime<= span style=3D"color:rgb(31,73,125)">.

=C2=A0



Ah, yes, we are aware of this and ther= e is a PR to fix it, but it appears to have been dropped by the author=C2= =A0correct mapping f= or postgres timestamptz type to sql type TIMESTAMP_W=E2=80=A6 by lopata2 = =C2=B7 Pull Request #2715 =C2=B7 pgjdbc/pgjdbc (github.com)
<= br>
Ca= re to take it up ?

Dave=C2=A0


--00000000000003743705fbd719b9--