makssent opened a new issue, #40275: URL: https://github.com/apache/shardingsphere/issues/40275
### 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 describe the [subtraction of dates](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L1134-L1139), the [subtraction of timestamps](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L1147-L1154) and the [addition of a number to a timestamp](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L1173-L1183) in the select list as the Firebird server does. ### Actual behavior The proxy describes date and time arithmetic as [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70) named by the operator sign, and the value of the [subtraction of timestamps](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L1147-L1154) breaks the connection. The same statements were executed directly on the Firebird server and through the proxy, and the rows were read directly from the Firebird server: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SELECT CURRENT_DATE - CURRENT_DATE FROM RDB$DATABASE` | Column `SUBTRACT` of type [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70), value 0 | Column `-` of type [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70), value 0 | | `SELECT TS + 1 FROM T_TS WHERE ID = -1` | Column `ADD` of type [TIMESTAMP](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L74), no rows | Column `+` of type [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70), no rows | | With auto-commit off, `INSERT INTO T_PLAIN (ID, NAME) VALUES (9301, 'before')` | Success | Success | | `SELECT CURRENT_TIMESTAMP - CURRENT_TIMESTAMP FROM RDB$DATABASE` | Column `SUBTRACT` of type [NUMERIC](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L80), value 0E-9 | Column `-` of type [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70), then Jaybird error [`jb_unexpectedOperationCode`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/gds/ng/wire/AbstractWireOperations.java#L201-L205) "Unsupported or unexpected operation code 0 in processOperation" | | `INSERT INTO T_PLAIN (ID, NAME) VALUES (9302, 'after')` | Success | Jaybird error [`jb_connectionClosed`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/gds/ng/FbExceptionBuilder.java#L313-L317) "The connection is closed" | | `COMMIT` | Success, the rows with ID = 9301 and 9302 are saved | Jaybird [error](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBConnection.java#L393-L395) "You cannot commit a closed connection.", the row with ID = 9301 is lost | So through the proxy an application gets wrong column types and names for date and time arithmetic, and the [subtraction of timestamps](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L1147-L1154) closes the connection and loses the open transaction. ### Reason analysis - [`FirebirdPrepareStatementCommandExecutor#processExpressionProjection`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L414-L417) describes a [`BinaryOperationExpression`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/expr/BinaryOperationExpression.java#L30) by the operator only. [`getOperationType`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L461-L468) returns [`4`](https://github.com/apache/shardingsphere/blob/01 446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L465-L466), the JDBC code of an integer, for every operator except [`||`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L4013-L4033), whatever the types of the arguments, and [`getOperationName`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L452-L459) uses the operator sign as the column name. The open pull request #40210 names these columns `ADD` and `SUBTRACT` as the Firebird server does, but still describes date and time arithmetic as [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h #L70). - [`FirebirdBinaryRowBuilder#build`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/FirebirdBinaryRowBuilder.java#L46) encodes the row by the type of the backend result, so the 8-byte [NUMERIC](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L80) value of the [subtraction of timestamps](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L1147-L1154) is sent under the described 4-byte [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70) column. Jaybird reads the rest of the value as the next operation code ([`processOperation`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/gds/ng/wire/AbstractWireOperations.java#L201-L205)) and closes the connection, and the open transaction is r olled back. ### 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. Execute the statements from the table under "Actual behavior" in this order 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 DatetimeArithmeticDescribe { // 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 9301 AND 9309"); try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { // Firebird server: "SELECT CURRENT_DATE - CURRENT_DATE FROM RDB$DATABASE -> SUBTRACT INTEGER: 0"; // proxy: "SELECT CURRENT_DATE - CURRENT_DATE FROM RDB$DATABASE -> - INTEGER: 0" execute(connection, "SELECT CURRENT_DATE - CURRENT_DATE FROM RDB$DATABASE"); // Firebird server: "SELECT TS + 1 FROM T_TS WHERE ID = -1 -> ADD TIMESTAMP: no rows"; // proxy: "SELECT TS + 1 FROM T_TS WHERE ID = -1 -> + INTEGER: no rows" execute(connection, "SELECT TS + 1 FROM T_TS WHERE ID = -1"); connection.setAutoCommit(false); // Firebird server and proxy: "INSERT INTO T_PLAIN (ID, NAME) VALUES (9301, 'before') -> update count 1" execute(connection, "INSERT INTO T_PLAIN (ID, NAME) VALUES (9301, 'before')"); // Firebird server: "SELECT CURRENT_TIMESTAMP - CURRENT_TIMESTAMP FROM RDB$DATABASE -> SUBTRACT NUMERIC: 0E-9"; // proxy: "SELECT CURRENT_TIMESTAMP - CURRENT_TIMESTAMP FROM RDB$DATABASE -> error: Unsupported or unexpected operation code 0 in processOperation ..." execute(connection, "SELECT CURRENT_TIMESTAMP - CURRENT_TIMESTAMP FROM RDB$DATABASE"); // Firebird server: "INSERT INTO T_PLAIN (ID, NAME) VALUES (9302, 'after') -> update count 1"; // proxy: "INSERT INTO T_PLAIN (ID, NAME) VALUES (9302, 'after') -> error: The connection is closed ..." execute(connection, "INSERT INTO T_PLAIN (ID, NAME) VALUES (9302, 'after')"); // Firebird server: "commit: success"; proxy: "commit: You cannot commit a closed connection." try { connection.commit(); System.out.println("commit: success"); } catch (final SQLException ex) { System.out.println("commit: " + ex.getMessage()); } } // Firebird server: "ID = 9301: the row is saved"; proxy: "ID = 9301: the row is lost" printRow(9301); // Firebird server: "ID = 9302: the row is saved"; proxy: "ID = 9302: the row is lost" printRow(9302); executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 9301 AND 9309"); } // 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 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 NAME FROM T_PLAIN WHERE ID = " + id)) { System.out.println("ID = " + id + ": " + (resultSet.next() ? "the row is saved" : "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]
