makssent opened a new issue, #40251: URL: https://github.com/apache/shardingsphere/issues/40251
### 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 execute statements with the [`DATE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7757-L7772), [`TIME`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7773-L7788) and [`TIMESTAMP`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7789-L7790) literals as the Firebird server does. ### Actual behavior The proxy rejects each statement with a date or time literal with an error. The same statements were executed directly on the Firebird server and through the proxy, in one transaction with auto-commit off, after inserting a row with ID = 6801 into `T_PLAIN`: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SELECT DATE '2026-10-15', TIME '10:11:12.3456', TIMESTAMP '2026-10-15 10:11:12.3456' FROM RDB$DATABASE` | 2026-10-15, 10:11:12.3456, 2026-10-15 10:11:12.3456 | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception." | | `SELECT COUNT(*) FROM T_PLAIN WHERE ID = 6801 AND CURRENT_DATE > DATE '2000-01-01'` | 1 | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception." | | `INSERT INTO T_TS (ID, TS) VALUES (6802, TIMESTAMP '2026-10-15 10:11:12.3456')` | Success | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception." | | `COMMIT`, then read the row with ID = 6802 from `T_TS` | The row is saved with TS = 2026-10-15 10:11:12.3456 | The row is lost | So through the proxy an application cannot read, compare or insert date and time values written as literals. ### Reason analysis - The Firebird grammar of ShardingSphere parses these literals with the rule [`dateTimeLiterals`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L44-L47) of [`literals`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L26-L34). - [`FirebirdStatementVisitor#visitLiterals`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/FirebirdStatementVisitor.java#L142-L163) has no branch for [`dateTimeLiterals`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L44-L47) and [throws](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/FirebirdStatementVisitor.java#L162) `IllegalStateException` "Literals must have string, number, dateTime, hex, bit, boolean or null.". - [`FirebirdErrorPacketFactory#newInstance`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/err/FirebirdErrorPacketFactory.java#L44-L47) passes this exception to [`SQLExceptionTransformEngine#toSQLException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/exception/core/src/main/java/org/apache/shardingsphere/database/exception/core/SQLExceptionTransformEngine.java#L68), which turns it into [`UnknownSQLException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/common/src/main/java/org/apache/shardingsphere/infra/exception/generic/UnknownSQLException.java#L30-L32) "Unknown exception." with the error code 0, and [`FirebirdErrorSegmentResolver#resolve`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/pro tocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/err/FirebirdErrorSegmentResolver.java#L55-L58) sends this code as [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63). ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 6.0.7. #### Steps 1. Create the tables on the Firebird server: ```sql CREATE TABLE T_PLAIN (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(20)); CREATE TABLE T_TS (ID INTEGER NOT NULL PRIMARY KEY, TS TIMESTAMP); ``` 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. Turn auto-commit off, execute `INSERT INTO T_PLAIN (ID, NAME) VALUES (6801, 'before')` and then the statements from the table under "Actual behavior" in the same transaction through the proxy and directly on the Firebird server, and compare the results. "Example code" runs all of them. ### Example code The statements from the table on Jaybird 6.0.7. The comment above each call shows what it prints against the Firebird server and through the proxy. ```java import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import java.util.List; public class DatetimeLiterals { // 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 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 SQLException { executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 6801 AND 6809"); executeOnServer("DELETE FROM T_TS WHERE ID BETWEEN 6801 AND 6809"); try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { connection.setAutoCommit(false); // Firebird server: "INSERT INTO T_PLAIN (ID, NAME) VALUES (6801, 'before') -> update count 1"; // proxy: "INSERT INTO T_PLAIN (ID, NAME) VALUES (6801, 'before') -> update count 1" execute(connection, "INSERT INTO T_PLAIN (ID, NAME) VALUES (6801, 'before')"); // Firebird server: "SELECT DATE '2026-10-15', TIME '10:11:12.3456', TIMESTAMP '2026-10-15 10:11:12.3456' FROM RDB$DATABASE // -> CONSTANT DATE, CONSTANT TIME, CONSTANT TIMESTAMP: 2026-10-15, 10:11:12, 2026-10-15 10:11:12.3456"; // proxy: "SELECT DATE '2026-10-15', TIME '10:11:12.3456', TIMESTAMP '2026-10-15 10:11:12.3456' FROM RDB$DATABASE // -> error: Unknown exception. ..." execute(connection, "SELECT DATE '2026-10-15', TIME '10:11:12.3456', TIMESTAMP '2026-10-15 10:11:12.3456' FROM RDB$DATABASE"); // Firebird server: "SELECT COUNT(*) FROM T_PLAIN WHERE ID = 6801 AND CURRENT_DATE > DATE '2000-01-01' -> COUNT BIGINT: 1"; // proxy: "SELECT COUNT(*) FROM T_PLAIN WHERE ID = 6801 AND CURRENT_DATE > DATE '2000-01-01' -> error: Unknown exception. ..." execute(connection, "SELECT COUNT(*) FROM T_PLAIN WHERE ID = 6801 AND CURRENT_DATE > DATE '2000-01-01'"); // Firebird server: "INSERT INTO T_TS (ID, TS) VALUES (6802, TIMESTAMP '2026-10-15 10:11:12.3456') -> update count 1"; // proxy: "INSERT INTO T_TS (ID, TS) VALUES (6802, TIMESTAMP '2026-10-15 10:11:12.3456') -> error: Unknown exception. ..." execute(connection, "INSERT INTO T_TS (ID, TS) VALUES (6802, TIMESTAMP '2026-10-15 10:11:12.3456')"); connection.commit(); } // Firebird server: "ID = 6802: the row is saved with TS = 2026-10-15 10:11:12.3456"; proxy: "ID = 6802: the row is lost" printRow(6802); executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 6801 AND 6809"); executeOnServer("DELETE FROM T_TS WHERE ID BETWEEN 6801 AND 6809"); } // Executes the statement and prints the described columns with the rows, the update count or the error static void execute(final Connection connection, final String sql) { try (Statement statement = connection.createStatement()) { if (!statement.execute(sql)) { System.out.println(sql + " -> update count " + statement.getUpdateCount()); return; } try (ResultSet resultSet = statement.getResultSet()) { ResultSetMetaData metaData = resultSet.getMetaData(); List<String> columns = new ArrayList<>(); for (int i = 1; i <= metaData.getColumnCount(); i++) { int type = metaData.getColumnType(i); boolean text = Types.CHAR == type || Types.VARCHAR == type; columns.add(metaData.getColumnLabel(i) + " " + metaData.getColumnTypeName(i) + (text ? "(" + metaData.getPrecision(i) + ")" : "")); } List<String> rows = new ArrayList<>(); while (resultSet.next()) { List<String> values = new ArrayList<>(); for (int i = 1; i <= metaData.getColumnCount(); i++) { values.add(String.valueOf(resultSet.getObject(i))); } rows.add(String.join(", ", values)); } System.out.println(sql + " -> " + String.join(", ", columns) + ": " + (rows.isEmpty() ? "no rows" : String.join("; ", rows))); } } catch (final SQLException ex) { System.out.println(sql + " -> error: " + ex.getMessage()); } } // Reads the row of T_TS directly on the Firebird server static void printRow(final int id) throws SQLException { try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD); ResultSet resultSet = connection.createStatement().executeQuery("SELECT TS FROM T_TS WHERE ID = " + id)) { System.out.println("ID = " + id + ": " + (resultSet.next() ? "the row is saved with TS = " + resultSet.getTimestamp(1) : "the row is lost")); } } 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]
