makssent opened a new issue, #40267: URL: https://github.com/apache/shardingsphere/issues/40267
### 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 the number of inserted rows of an INSERT with [`RETURNING`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L6822-L6835) in [`isc_info_sql_records`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/dsql.cpp#L916-L920) of [`op_info_sql`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L4709-L4714) as the Firebird server does. Support of the other items of [`op_info_sql`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L4709-L4714) is tracked in #39939. This issue is about [`isc_info_sql_records`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/dsql.cpp#L916-L920), which the proxy already answers, but with a wrong count. ### Actual behavior The proxy inserts the row and returns the right generated keys, but reports that no row was inserted. The same steps were executed directly on the Firebird server and through the proxy, and the rows were read directly from the server: | Steps | Firebird server | Proxy | | --- | --- | --- | | Insert a row with ID = 8863 with [`prepareStatement(sql, RETURN_GENERATED_KEYS)`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBConnection.java#L726-L733) and [`executeUpdate()`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBPreparedStatement.java#L213-L229) | 1 | 0 | | [`getGeneratedKeys()`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBStatement.java#L436-L443) | ID = 8863, NAME = keys | ID = 8863, NAME = keys | | Commit and read the row with ID = 8863 directly on the server | The row is saved | The row is saved | So through the proxy an application that checks the update count of an insert with generated keys (for example, an ORM that expects one inserted row) decides that the insert failed, although the row is saved. ### Reason analysis - For [`RETURN_GENERATED_KEYS`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBConnection.java#L726-L733) Jaybird [adds](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/GeneratedKeysQueryBuilder.java#L282-L292) [`RETURNING`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L6822-L6835) to the INSERT, and [`executeUpdate()`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBPreparedStatement.java#L213-L229) takes the update count from [`isc_info_sql_records`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/dsql.cpp#L916-L920) ([`getUpdateCount`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBStatement.java#L631-L650), [`getSqlCounts`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/gds/ng/AbstractFbStatement.java#L612-L628)). - The proxy executes the statement with [`RETURNING`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L6822-L6835) as a query, and the result is a [`QueryResponseHeader`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/response/header/query/QueryResponseHeader.java#L31). [`FirebirdExecuteStatementCommandExecutor#execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/execute/FirebirdExecuteStatementCommandExecutor.java#L86-L93) stores the affected rows in [`FirebirdServerPreparedStatement`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/Firebird ServerPreparedStatement.java#L40-L41) only for an [`UpdateResponseHeader`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/core/src/main/java/org/apache/shardingsphere/proxy/backend/response/header/update/UpdateResponseHeader.java#L36), so for this statement the count stays 0. - [`FirebirdSQLInfoExecutor#getRecordsInfo`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/info/FirebirdSQLInfoExecutor.java#L52-L63) builds [`isc_info_sql_records`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/dsql.cpp#L916-L920) from this count, so the insert count in the answer to [`op_info_sql`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L4709-L4714) is 0. The open PR #40189 changes [`FirebirdSQLInfoExecutor`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/info/FirebirdSQLInfoExecutor.java#L41) (it checks the statement handle), but does not change this count. ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 6.0.7. #### Steps 1. Create the table on the Firebird server: ```sql CREATE TABLE T_PLAIN (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(20)); ``` 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; public class ReturningUpdateCount { // 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 Exception { executeOnServer("DELETE FROM T_PLAIN WHERE ID = 8863"); insertWithGeneratedKeys(); executeOnServer("DELETE FROM T_PLAIN WHERE ID = 8863"); } // Firebird server: "executeUpdate: 1", "getGeneratedKeys: ID = 8863, NAME = keys", "ID = 8863: the row is saved"; // proxy: "executeUpdate: 0", "getGeneratedKeys: ID = 8863, NAME = keys", "ID = 8863: the row is saved" static void insertWithGeneratedKeys() throws SQLException { try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { connection.setAutoCommit(false); // Jaybird adds RETURNING to the INSERT and reads the update count with op_info_sql try (PreparedStatement statement = connection.prepareStatement("INSERT INTO T_PLAIN (ID, NAME) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS)) { statement.setInt(1, 8863); statement.setString(2, "keys"); System.out.println("executeUpdate: " + statement.executeUpdate()); try (ResultSet keys = statement.getGeneratedKeys()) { System.out.println("getGeneratedKeys: " + (keys.next() ? "ID = " + keys.getInt("ID") + ", NAME = " + keys.getString("NAME") : "no row")); } } connection.commit(); } printRow(8863); } // 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]
