makssent opened a new issue, #40244: URL: https://github.com/apache/shardingsphere/issues/40244
### Before reporting a bug - [x] I have searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - [x] I have read the [ShardingSphere documentation](https://shardingsphere.apache.org/document/current/en/overview). ### ShardingSphere version master, commit [`01446d4fad4`](https://github.com/apache/shardingsphere/commit/01446d4fad48450e7fc3eb3b3631b25d6bf411df) ### ShardingSphere project ShardingSphere-Proxy ### Expected behavior The proxy should return and save [`TIME WITH TIME ZONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4837-L4845) and [`TIMESTAMP WITH TIME ZONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4853-L4859) values with their time zone, as the Firebird server does. ### Actual behavior The proxy returns [`TIMESTAMP WITH TIME ZONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4853-L4859) values in another time zone, saves a [`TIMESTAMP WITH TIME ZONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4853-L4859) parameter without its time zone and answers both a [`TIME WITH TIME ZONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4837-L4845) result and a [`TIME WITH TIME ZONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4837-L4845) parameter with an error. The same steps were executed directly on the Firebird server and through the proxy, the values were read with [`getString`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBResultSet.java#L452-L454), and the saved rows were read directly from the Firebird server: | Steps | Firebird server | Proxy | | --- | --- | --- | | `SELECT TSZ FROM T_DTZ WHERE ID = 8911`, the value is `2026-10-15 10:11:12.3456 -05:30` | `2026-10-15T10:11:12.345600-05:30` | `2026-10-15T21:41:12.345600+06:00` | | `SELECT TSZ FROM T_DTZ WHERE ID = 8912`, the value is `2026-10-15 10:11:12 America/Sao_Paulo` | `2026-10-15T10:11:12-03:00` | `2026-10-15T08:11:12-05:00` | | `SELECT TMZ FROM T_DTZ WHERE ID = 8911`, the value is `10:11:12.3456 +03:00` | `10:11:12.345600+03:00` | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception." | | Insert a row with ID = 8913 and TSZ = `2026-10-15T10:11:12-05:30`, an `OffsetDateTime` parameter of [`setObject`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBPreparedStatement.java#L312-L314) | Success, the row is saved with the time `10:11:12.0000 -05:30` | Success, the row is saved with the time `15:41:12.0000 +00:00`, in the time zone of the proxy JVM (UTC) | | Insert a row with ID = 8914 and TMZ = `10:11:12+03:00`, an `OffsetTime` parameter of [`setObject`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBPreparedStatement.java#L312-L314) | Success, the row is saved with the time `10:11:12.0000 +03:00` | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception.", the row is not saved | So through the proxy an application gets [`TIMESTAMP WITH TIME ZONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4853-L4859) values in a time zone it did not write, saves them in the time zone of the proxy instead of their own and cannot read or write [`TIME WITH TIME ZONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4837-L4845) values at all. ### Reason analysis - [`FirebirdTimestampTZBinaryProtocolValue#write`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/execute/protocol/FirebirdTimestampTZBinaryProtocolValue.java#L37-L43) writes the offset in minutes (-330 for `-05:30`) to the time zone field of a result value. The Firebird server [writes](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/common/xdr.cpp#L295-L303) the time zone id there, and the id of an offset is the offset plus 1439 ([`displacementToOffsetZone`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/common/TimeZoneUtil.cpp#L1171-L1174), [`ONE_DAY`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/common/TimeZoneUtil.cpp#L302)), so the client reads the same moment in another time zone. - [`FirebirdTimestampTZBinaryProtocolValue#read`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/execute/protocol/FirebirdTimestampTZBinaryProtocolValue.java#L32-L35) reads a parameter with [`FirebirdDateTimeUtils#getDateTimeWithOffset`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/execute/protocol/util/FirebirdDateTimeUtils.java#L207-L210), which ignores the time zone field (the comment [`// TODO add time zone support`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/ex ecute/protocol/util/FirebirdDateTimeUtils.java#L208)) and returns a `Timestamp` with the UTC date and time. The backend driver then saves this value in the default time zone of the proxy JVM. - [`FirebirdBinaryProtocolValueFactory`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/execute/protocol/FirebirdBinaryProtocolValueFactory.java#L38-L53) registers no value for [`TIME_TZ`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/FirebirdBinaryColumnType.java#L61), so [`FirebirdBinaryProtocolValueFactory#getBinaryProtocolValue`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/execute/protocol/FirebirdBinaryProtocolValueFactory.java#L140-L143) throws `IllegalArgumentException` for [`TIME WITH TIME ZONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4837-L4845). [`SQLExceptionTransformEngine#toSQLException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/exception/core/src/main/java/org/apache/shardingsphere/database/exception/core/SQLExceptionTransformEngine.java#L68) turns it into [`UnknownSQLException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/common/src/main/java/org/apache/shardingsphere/infra/exception/generic/UnknownSQLException.java#L31) "Unknown exception.". - For a parameter, the exception is thrown while [`FirebirdExecuteStatementPacket`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/execute/FirebirdExecuteStatementPacket.java#L77-L81) reads the [`op_execute`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5243-L5246) packet, and [`FirebirdPacketCodecEngine#decode`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/codec/FirebirdPacketCodecEngine.java#L81-L91) answers with the error and closes the connection. - For a result, the exception is thrown while [`FirebirdFetchResponsePacket`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/generic/FirebirdFetchResponsePacket.java#L61-L64) writes the row for [`op_fetch`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5253-L5256), and [`FirebirdPacketCodecEngine#encode`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/codec/FirebirdPacketCodecEngine.java#L212-L215) writes the error instead of the row. ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 6.0.7. #### Steps 1. Create the table and the rows on the Firebird server: ```sql CREATE TABLE T_DTZ (ID INTEGER NOT NULL PRIMARY KEY, TMZ TIME WITH TIME ZONE, TSZ TIMESTAMP WITH TIME ZONE); INSERT INTO T_DTZ (ID, TMZ, TSZ) VALUES (8911, TIME '10:11:12.3456 +03:00', TIMESTAMP '2026-10-15 10:11:12.3456 -05:30'); INSERT INTO T_DTZ (ID, TMZ, TSZ) VALUES (8912, TIME '23:59:59 Europe/Moscow', TIMESTAMP '2026-10-15 10:11:12 America/Sao_Paulo'); ``` 2. Start ShardingSphere-Proxy with this configuration: ```yaml # global.yaml authority: users: - user: root@% password: root privilege: type: ALL_PERMITTED props: proxy-frontend-database-protocol-type: Firebird ``` ```yaml # database-firebird.yaml databaseName: firebird_db dataSources: ds_0: url: jdbc:firebirdsql://<host>/<database file> username: <user> password: <password> rules: - !SINGLE tables: - "*.*" ``` 3. Execute the steps from the table under "Actual behavior" through the proxy and directly on the Firebird server, and compare the results. "Example code" runs all of them. ### Example code All cases on Jaybird 6.0.7, one method per case. The comment above each method shows what it prints against the Firebird server and through the proxy. ```java import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.time.OffsetDateTime; import java.time.OffsetTime; import java.time.ZoneOffset; public class TimeZoneTypes { // The proxy; put the URL of the Firebird server here to compare static final String URL = "jdbc:firebirdsql://<proxy host>:<proxy port>/firebird_db?encoding=UTF8&wireCrypt=DISABLED"; static final String USER = "root"; static final String PASSWORD = "root"; // The Firebird server, to write and read the rows directly static final String SERVER_URL = "jdbc:firebirdsql://<host>/<database file>"; static final String SERVER_USER = "<user>"; static final String SERVER_PASSWORD = "<password>"; public static void main(final String[] args) throws Exception { executeOnServer("DELETE FROM T_DTZ WHERE ID BETWEEN 8911 AND 8914"); executeOnServer("INSERT INTO T_DTZ (ID, TMZ, TSZ) VALUES (8911, TIME '10:11:12.3456 +03:00', TIMESTAMP '2026-10-15 10:11:12.3456 -05:30')"); executeOnServer("INSERT INTO T_DTZ (ID, TMZ, TSZ) VALUES (8912, TIME '23:59:59 Europe/Moscow', TIMESTAMP '2026-10-15 10:11:12 America/Sao_Paulo')"); // One connection per case: after the error on a TIME WITH TIME ZONE parameter the proxy closes the connection selectTimestampWithOffset(); selectTimestampWithNamedZone(); selectTime(); insertTimestamp(); insertTime(); executeOnServer("DELETE FROM T_DTZ WHERE ID BETWEEN 8911 AND 8914"); } // Firebird server: "SELECT TSZ FROM T_DTZ WHERE ID = 8911: 2026-10-15T10:11:12.345600-05:30"; // proxy: "SELECT TSZ FROM T_DTZ WHERE ID = 8911: 2026-10-15T21:41:12.345600+06:00" static void selectTimestampWithOffset() { printSelect("SELECT TSZ FROM T_DTZ WHERE ID = 8911"); } // Firebird server: "SELECT TSZ FROM T_DTZ WHERE ID = 8912: 2026-10-15T10:11:12-03:00"; // proxy: "SELECT TSZ FROM T_DTZ WHERE ID = 8912: 2026-10-15T08:11:12-05:00" static void selectTimestampWithNamedZone() { printSelect("SELECT TSZ FROM T_DTZ WHERE ID = 8912"); } // Firebird server: "SELECT TMZ FROM T_DTZ WHERE ID = 8911: 10:11:12.345600+03:00"; // proxy: "SELECT TMZ FROM T_DTZ WHERE ID = 8911: Unknown exception. ..." static void selectTime() { printSelect("SELECT TMZ FROM T_DTZ WHERE ID = 8911"); } // Firebird server: "Insert ID = 8913: success", "ID = 8913: 10:11:12.0000 -05:30"; // proxy: "Insert ID = 8913: success", "ID = 8913: 15:41:12.0000 +00:00" static void insertTimestamp() throws SQLException { insert(8913, "TSZ", OffsetDateTime.of(2026, 10, 15, 10, 11, 12, 0, ZoneOffset.ofHoursMinutes(-5, -30))); printSaved(8913, "TSZ"); } // Firebird server: "Insert ID = 8914: success", "ID = 8914: 10:11:12.0000 +03:00"; // proxy: "Insert ID = 8914: Unknown exception. ...", "ID = 8914: the row is not saved" static void insertTime() throws SQLException { insert(8914, "TMZ", OffsetTime.of(10, 11, 12, 0, ZoneOffset.ofHours(3))); printSaved(8914, "TMZ"); } // Executes the query through the proxy and prints the value as a string static void printSelect(final String sql) { try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql)) { resultSet.next(); System.out.println(sql + ": " + resultSet.getString(1)); } catch (final SQLException ex) { System.out.println(sql + ": " + ex.getMessage()); } } // Inserts the row through the proxy with the value as a parameter static void insert(final int id, final String column, final Object value) { try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD); PreparedStatement statement = connection.prepareStatement("INSERT INTO T_DTZ (ID, " + column + ") VALUES (?, ?)")) { statement.setInt(1, id); statement.setObject(2, value); statement.executeUpdate(); System.out.println("Insert ID = " + id + ": success"); } catch (final SQLException ex) { System.out.println("Insert ID = " + id + ": " + ex.getMessage()); } } // Reads the time and the time zone of the saved value directly on the Firebird server static void printSaved(final int id, final String column) throws SQLException { try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD); ResultSet resultSet = connection.createStatement().executeQuery( "SELECT CAST(CAST(" + column + " AS TIME WITH TIME ZONE) AS VARCHAR(60)) FROM T_DTZ WHERE ID = " + id)) { System.out.println("ID = " + id + ": " + (resultSet.next() ? resultSet.getString(1) : "the row is not saved")); } } static void executeOnServer(final String sql) throws SQLException { try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD)) { connection.createStatement().execute(sql); } } } ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
