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 1pz1WC-0008Bi-70 for pgsql-bugs@arkaria.postgresql.org; Tue, 16 May 2023 20:43:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pz1WB-0008VQ-51 for pgsql-bugs@arkaria.postgresql.org; Tue, 16 May 2023 20:43:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pz10Q-0003uz-5a for pgsql-bugs@lists.postgresql.org; Tue, 16 May 2023 20:10:46 +0000 Received: from mta-231a.oxsus-vadesecure.net ([15.204.3.4] helo=nmtao201.oxsus-vadesecure.net) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pz10J-000CiP-47 for pgsql-bugs@lists.postgresql.org; Tue, 16 May 2023 20:10:45 +0000 DKIM-Signature: v=1; a=rsa-sha256; bh=5jhI4V3hbGxRB1E+ve+Q0Ptmdc2laxVKYyMDVL aUgdM=; c=relaxed/relaxed; d=webcom.xion.oxcs.net; h=from:reply-to: subject:date:to:cc:resent-date:resent-from:resent-to:resent-cc: in-reply-to:references:list-id:list-help:list-unsubscribe: list-subscribe:list-post:list-owner:list-archive; q=dns/txt; s=mail1; t=1684267836; x=1684872636; b=nNAJS8a/occNLxFcEp5S+tXTUIXZ3NLmXBYv+C5fs QN1QbM7r/m9Ifu8QPTYkfYC/ChEd0kop/BDZo40pxVw67GzQa2TJycJqeFTlALi0DwJa2x9 1jaZN+p6ot2OSz2KEFRQAVsW35QLsjLoLjXnrDFgHMnZ4kGcGalfqWcVZ+hwvU56j8AyBdZ 2IEY+Q1MbvMoN8nuDtiOd31d5ZFiGfbaNarWpsySlLJCreildqNrlG0bI4VCg1HqULwLFvS Sg8nqSpJTyX/R/o+PMYBxjRrhONZudIpaDmtJqCsv3IGQUAYCCaHkRkb8Sm33RQWCL7JpQA 7mWXwEw+nhd06radQ== Received: from proxy-9.proxy.cloudus.ewr.xion.oxcs.net ([71.239.101.47]) by oxsus2nmtao01p.internal.vadesecure.com with ngmta id 8eb42c00-175fb8ac08efedb3; Tue, 16 May 2023 20:10:36 +0000 From: Sender: "Stephen C. Bergeon" To: "'Dave Cramer'" , References: <17911-76b72ef0b040e9da@postgresql.org> In-Reply-To: Subject: RE: BUG #17911: Database or JDBC Driver Provides Incorrect Type Date: Tue, 16 May 2023 15:09:32 -0500 Message-ID: <007e01d98832$566bfbc0$0343f340$@Bergeon.org> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_007F_01D98808.6D9BE730" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQII+QJeFcdKhQlOmGGgzGn36+FmYwLIXdxUrugfO7A= Content-Language: en-us Authentication-Results: oxsus-vadesecure.net; auth=pass smtp.auth=3@13822 smtp.mailfrom=Stephen@Bergeon.org; List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_007F_01D98808.6D9BE730 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0080_01D98808.6D9BE730" ------=_NextPart_001_0080_01D98808.6D9BE730 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable 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. =20 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. =20 In my current test environment the program results display as = follows=E2=80=A6 =20 Connecting to the DB. =20 DB Product: PostgreSQL version 15.2 DB Driver: PostgreSQL JDBC Driver version 42.2.14 =20 Dropping the table if it exists. Creating the table. =20 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 =20 All done. =20 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. =20 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. =20 Stephen C. Bergeon =20 From: Dave Cramer [mailto:davecramer@postgres.rocks]=20 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 =20 Hi, =20 Can you provide more detail please? Possibly are reproducer? Dave Cramer www.postgres.rocks =20 =20 On Thu, 27 Apr 2023 at 14:27, 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: =20 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]. ------=_NextPart_001_0080_01D98808.6D9BE730 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

= =C2=A0=C2=A0=C2=A0Attached is a simple one-class program that 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 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 the = ResultSetMetaData resulting from executind a DB select = statement.

 

=C2=A0=C2=A0=C2= =A0 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.

 

=C2=A0=C2=A0=C2= =A0 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.=C2=A0 = 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.

 

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

 

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 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].

------=_NextPart_001_0080_01D98808.6D9BE730-- ------=_NextPart_000_007F_01D98808.6D9BE730 Content-Type: application/octet-stream; name="TestSQL.java" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="TestSQL.java" /** * Test SQL Data Type */ import java.sql.*; /** * Custom SQL Testing */ public class TestSQL { // Database constants public static final String DB_TABLE =3D "TestTable"; // If the table = name is changed, the SQL constants that follow needs to be updated too public static final String DB_URL =3D = "jdbc:postgresql://localhost:5432/mydatabase"; public static final String DB_USER_ID =3D "myuserid"; public static final String DB_USER_PASSWORD =3D "MyPassword"; =09 // SQL statement constants public static final String SQL_CREATE_TABLE =3D=20 "Create table if not exists TestTable (" +=20 "ID serial not null," +=20 "CharValue char(32)," +=20 "VarCharValue varchar(255)," +=20 "TextValue Text," +=20 "BinaryValue ByteA," +=20 "IntValue Int," +=20 "FloatValue Real," +=20 "DoubleValue Double Precision," +=20 "BooleanValue Boolean," +=20 "DateValue Date," +=20 "TimeValueNoTimeZone Time(3) without time zone," +=20 "TimeValueWithTimeZone Time(3) with time zone," +=20 "TimestampNoTimeZone TimeStamp(3) without time zone default = Current_TimeStamp," +=20 "TimestampWithTimeZone TimeStamp(3) with time zone default = Current_TimeStamp" +=20 ")"; public static final String SQL_DROP_TABLE =3D "Drop table if exists = TestTable";=20 =09 /** * No need to instantiate this class. */ private TestSQL() { =09 =09 } =09 /** * Testing entry point *=20 * @param strArgs the arguments passed in. */ public static void main(String[] strArgs) { =09 =09 test(); } =09 /** * Performs the test. */ public static void test() { // Local variables Connection connection =3D null; DatabaseMetaData databaseMetaData =3D null; ResultSet resultSet =3D null; Statement statement =3D null; =09 // Just in case try { =09 // Create a connection to the DB log("Connecting to the DB."); connection =3D DriverManager.getConnection(DB_URL, DB_USER_ID, = DB_USER_PASSWORD); =09 // Get the associated DatabaseMetaData databaseMetaData =3D connection.getMetaData(); =09 // Show the database and driver information log(); log("DB Product: " + databaseMetaData.getDatabaseProductName() + " = version " + databaseMetaData.getDatabaseProductVersion()); // log("DB Product Name: " + = databaseMetaData.getDatabaseProductName()); // log("DB Product Version: " + = databaseMetaData.getDatabaseProductVersion()); // log("DB Product Version: " + = databaseMetaData.getDatabaseMajorVersion() + '.' + = databaseMetaData.getDatabaseMinorVersion()); log("DB Driver: " + databaseMetaData.getDriverName() + " version " + = databaseMetaData.getDriverVersion()); // log("DB Driver Name: " + databaseMetaData.getDriverName()); // log("DB Driver Version: " + databaseMetaData.getDriverVersion()); // log("DB Driver Version: " + = databaseMetaData.getDriverMajorVersion() + '.' + = databaseMetaData.getDatabaseMinorVersion()); log(); =09 statement =3D connection.createStatement(); =09 log("Dropping the table if it exists."); statement.execute(SQL_DROP_TABLE); =09 log("Creating the table."); statement.execute(SQL_CREATE_TABLE); =09 // Show the column info log(); showColumnInfo(databaseMetaData, DB_TABLE); // Completed without error log(); log("All done."); =09 } catch (Exception e) { =09 log("Unexpected " + e.getClass().getSimpleName() + ": " + = e.getMessage(), e); =09 } finally { =09 // Ensure the result-set gets closed if (resultSet !=3D null) { try { resultSet.close(); } catch (SQLException e) { log("Unexpected " + e.getClass().getSimpleName() + " while closing = the DB result-set: " + e.getMessage(), e); } resultSet =3D null; } =09 // Ensure the statement gets closed if (statement !=3D null) { try { statement.close(); } catch (SQLException e) { log("Unexpected " + e.getClass().getSimpleName() + " while closing = the DB statement: " + e.getMessage(), e); } statement =3D null; } =09 // Ensure the connection gets closed if (connection !=3D null) { try { connection.close(); } catch (SQLException e) { log("Unexpected " + e.getClass().getSimpleName() + " while closing = the DB connection: " + e.getMessage(), e); } connection =3D null; } =09 } =09 } =09 =09 /** * Show the column info for each column in the supplied table. *=20 * @param databaseMetaData the DatabaseMetaData to use. * @param strTable the name of the table to show the column info for. *=20 * @throws SQLException if one should occur. */ private static void showColumnInfo(DatabaseMetaData databaseMetaData, = String strTable) throws SQLException { =09 // Local variables String strColumnName; ResultSet resultsetColumns =3D null; int intDataType; String strDataType; =09 // Ensure things get closed try { =09 // Get the column info with case adjustment as necessary resultsetColumns =3D databaseMetaData.getColumns(null, null, = strTable.toLowerCase(), null); if (resultsetColumns.next()) { =09 do { =09 // Get the column information strColumnName =3D resultsetColumns.getString("COLUMN_NAME"); intDataType =3D resultsetColumns.getInt("DATA_TYPE"); =09 // Get the text of the DataType switch (intDataType) { case Types.ARRAY: strDataType =3D "Array"; break; case Types.BIGINT: strDataType =3D "BigInt"; break; case Types.BINARY: strDataType =3D "Binary"; break; case Types.BIT: strDataType =3D "Bit"; break; case Types.BLOB: strDataType =3D "BLOB"; break; case Types.BOOLEAN: strDataType =3D "Boolean"; break; case Types.CHAR: strDataType =3D "Char"; break; case Types.CLOB: strDataType =3D "CLOB"; break; case Types.DATALINK: strDataType =3D "DataLink"; break; case Types.DATE: strDataType =3D "Date"; break; case Types.DECIMAL: strDataType =3D "Decimal"; break; case Types.DISTINCT: strDataType =3D "Distinct"; break; case Types.DOUBLE: strDataType =3D "Double"; break; case Types.FLOAT: strDataType =3D "Float"; break; case Types.INTEGER: strDataType =3D "Integer"; break; case Types.JAVA_OBJECT: strDataType =3D "JavaObject"; break; case Types.LONGNVARCHAR: strDataType =3D "LongNVarChar"; break; case Types.LONGVARBINARY: strDataType =3D "LongVarBinary"; break; case Types.LONGVARCHAR: strDataType =3D "LongVarChar"; break; case Types.NCHAR: strDataType =3D "NChar"; break; case Types.NCLOB: strDataType =3D "NCLOB"; break; case Types.NULL: strDataType =3D "Null"; break; case Types.NUMERIC: strDataType =3D "Numeric"; break; case Types.NVARCHAR: strDataType =3D "NVarChar"; break; case Types.OTHER: strDataType =3D "Other"; break; case Types.REAL: strDataType =3D "Real"; break; case Types.REF: strDataType =3D "Ref"; break; case Types.REF_CURSOR: strDataType =3D "RefCursor"; break; case Types.ROWID: strDataType =3D "RowID"; break; case Types.SMALLINT: strDataType =3D "SmallInt"; break; case Types.SQLXML: strDataType =3D "SQL_XML"; break; case Types.STRUCT: strDataType =3D "Struct"; break; case Types.TIME: strDataType =3D "Time"; break; case Types.TIME_WITH_TIMEZONE: strDataType =3D "TimeWithTimeZone"; break; case Types.TIMESTAMP: strDataType =3D "Timestamp"; break; case Types.TIMESTAMP_WITH_TIMEZONE: strDataType =3D "TimestampWithTimeZone"; break; case Types.TINYINT: strDataType =3D "TinyInt"; break; case Types.VARBINARY: strDataType =3D "VarBinary"; break; case Types.VARCHAR: strDataType =3D "VarChar"; break; default: strDataType =3D "Unknown(" + intDataType + ")"; } =09 // Show the DataType for the column log("The data type for column '" + strColumnName + "' is: " + = strDataType); =09 } while (resultsetColumns.next()); =09 } else { =09 log("Unable to obtain column information for table '" + strTable + = "'!"); =09 } =09 =09 } finally { =09 // Ensure the ResultSet gets closed if (resultsetColumns !=3D null) { try { resultsetColumns.close(); } catch (SQLException e) { log("SQLUtil.getTableColumnInfo - Unexpected SQLException closing = the ResultSet:", e); } resultsetColumns =3D null; } } =09 } =09 =09 /** * Logs an empty line. */ private static void log() { =09 System.out.println(); =09 } =09 =09 /** * Logs the supplied line. *=20 * @param strMessage the message to log. */ private static void log(String strMessage) { =09 System.out.println(strMessage); =09 } =09 =09 /** * Logs the supplied line. *=20 * @param strMessage the message to log. * @param throwable the Throwable to log. */ private static void log(String strMessage, Throwable throwable) { =09 System.out.println(strMessage); throwable.printStackTrace(System.out); =09 } =09 } ------=_NextPart_000_007F_01D98808.6D9BE730--