makssent opened a new issue, #40285: URL: https://github.com/apache/shardingsphere/issues/40285
### 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) values with their time zone, as the Firebird server does. ### Actual behavior The proxy 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 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 = 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 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, and gets only a generic error with an `IllegalArgumentException` in its details. ### Reason analysis - [`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>?encoding=UTF8 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 TimeWithTimeZone { // The proxy; put the URL, user and password 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 selectTime(); insertTime(); executeOnServer("DELETE FROM T_DTZ WHERE ID BETWEEN 8911 AND 8914"); } // 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. More details: java.lang.IllegalArgumentException: Cannot find Firebird type 'TIME_TZ' in column type when process binary protocol value [SQLState:HY000, ISC error code:335544382]" static void selectTime() { printSelect("SELECT TMZ FROM T_DTZ WHERE ID = 8911"); } // Firebird server: "Insert ID = 8914: success", "ID = 8914: 10:11:12.0000 +03:00"; // proxy: "Insert ID = 8914: Unknown exception. More details: java.lang.IllegalArgumentException: Cannot find Firebird type 'TIME_TZ' in column type when process binary protocol value [SQLState:HY000, ISC error code:335544382]", "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]
