makssent opened a new issue, #40308: URL: https://github.com/apache/shardingsphere/issues/40308
### 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 [`7e63199751c`](https://github.com/apache/shardingsphere/commit/7e63199751c4c5143f4fd82fbd10ea04bf77bb1a) ### ShardingSphere project ShardingSphere-Proxy ### Expected behavior The proxy should prepare and execute [`INSERT ... SELECT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L6574-L6581) as the Firebird server does: describe the parameters of the query in the order of the parameter markers, insert the selected rows, and for `INSERT ... SELECT ... RETURNING` return a cursor with all inserted rows ([`isc_info_sql_stmt_select`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/dsql.cpp#L823-L826)). ### Actual behavior The proxy rejects every `INSERT ... SELECT` at prepare, with or without the column list and `RETURNING`, and inserts no rows. The table `T_PLAIN` has the rows with ID = 2901 (NAME 'a'), ID = 2902 (NAME NULL) and ID = 2903 (NAME 'c'). The same statements were prepared and executed directly on the Firebird server and through the proxy: | Steps | Firebird server | Proxy | | --- | --- | --- | | `INSERT INTO T_PLAIN (ID, NAME) SELECT ID + 100, NAME FROM T_PLAIN WHERE ID = ?` with 2901 | The statement has one INTEGER parameter, the row with ID = 3001 is inserted | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception. More details: java.lang.NullPointerException: Cannot invoke "org.antlr.v4.runtime.tree.ParseTree.accept(org.antlr.v4.runtime.tree.ParseTreeVisitor)" because "tree" is null" at prepare | | `INSERT INTO T_PLAIN SELECT ID + 200, NAME FROM T_PLAIN WHERE NAME = ?` with 'c' | The statement has one VARCHAR(20) parameter, the row with ID = 3103 is inserted | The same error at prepare | | `INSERT INTO T_PLAIN (ID, NAME) SELECT ID + 300, CAST(? AS VARCHAR(20)) FROM T_PLAIN WHERE ID = ?` with 'x' and 2901 | The statement has a VARCHAR(20) and an INTEGER parameter, the row with ID = 3201 is inserted | The same error at prepare | | `INSERT INTO T_PLAIN (ID, NAME) SELECT ID + 400, NAME FROM T_PLAIN WHERE ID BETWEEN ? AND ? ORDER BY ID RETURNING ID, NAME` with 2901 and 2903 | The statement has two INTEGER parameters, the execution returns a cursor with the inserted rows ID = 3301, 3302 and 3303 | The same error at prepare | | Read the rows of `T_PLAIN` directly on the Firebird server | The rows with ID = 2901, 2902, 2903, 3001, 3103, 3201, 3301, 3302 and 3303 | Only the rows with ID = 2901, 2902 and 2903 | So through the proxy an application cannot copy rows with `INSERT ... SELECT`: the statement fails at prepare and no rows are inserted. ### Reason analysis - [`FirebirdDMLStatementVisitor#visitInsert`](https://github.com/apache/shardingsphere/blob/7e63199751c4c5143f4fd82fbd10ea04bf77bb1a/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/type/FirebirdDMLStatementVisitor.java#L137-L157) always visits `insertValuesClause`. The [Firebird grammar](https://github.com/apache/shardingsphere/blob/7e63199751c4c5143f4fd82fbd10ea04bf77bb1a/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/DMLStatement.g4#L22-L32) parses `INSERT ... SELECT` as `insertSelectClause`, so for such a statement `insertValuesClause` is `null` and `visit(null)` throws `NullPointerException`. The visitor has no `visitInsertSelectClause`, so the insert columns and the `SELECT` subquery with its parameter markers are never built. The proxy answers the exception with [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown except ion.". The Firebird server takes the query as the source of the inserted rows ([`StoreNode::internalDsqlPass`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/StmtNodes.cpp#L7633-L7649)). - Once the statement is parsed, the prepare answer also does not support it. [`FirebirdPrepareStatementCommandExecutor#processInsertStatement`](https://github.com/apache/shardingsphere/blob/7e63199751c4c5143f4fd82fbd10ea04bf77bb1a/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L357-L373) describes only the parameters of `VALUES` rows, not the parameters of the query. [`FirebirdPrepareStatementCommandExecutor#getFirebirdStatementType`](https://github.com/apache/shardingsphere/blob/7e63199751c4c5143f4fd82fbd10ea04bf77bb1a/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L184-L190) answers [`isc_info_sql_stmt_exec_procedure`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/dsql.cpp#L859-L861) for every `INSERT ... RETURNING`, while the Firebird server returns a cursor for `INSERT ... SELECT ... RETURNING` ([`StmtNodes.cpp`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/StmtNodes.cpp#L7783), [`dsqlProcessReturning`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/StmtNodes.cpp#L10500-L10504)) and answers [`isc_info_sql_stmt_select`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/dsql.cpp#L823-L826). [`FirebirdPrepareStatementCommandExecutor#processShorthandProjection`](https://github.com/apache/shardingsphere/blob/7e63199751c4c5143f4fd82fbd10ea04bf77bb1a/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L308-L325) expands `RETURNING *` over all tables of the statement, that is also over the tables of the query, while the Firebird server expands it over the target table only ([`dsqlProcessReturning`](https://github.com/FirebirdSQL/firebird/blo b/v5.0.4/src/dsql/StmtNodes.cpp#L10382-L10387)). ### 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>?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 rows are prepared and read directly on the Firebird server. 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.ParameterMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.ArrayList; import java.util.List; public class InsertSelect { // 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 prepare the rows and to read them directly static final String SERVER_URL = "jdbc:firebirdsql://<host>/<database file>?encoding=UTF8"; 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 2901 AND 3399"); executeOnServer("INSERT INTO T_PLAIN (ID, NAME) VALUES (2901, 'a')"); executeOnServer("INSERT INTO T_PLAIN (ID, NAME) VALUES (2902, NULL)"); executeOnServer("INSERT INTO T_PLAIN (ID, NAME) VALUES (2903, 'c')"); try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { insertSelectWithWhereParameter(connection); insertSelectWithoutColumnList(connection); insertSelectWithProjectionParameter(connection); insertSelectReturning(connection); } printRows(); executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 2901 AND 3399"); } // Firebird server: "WHERE ID = ?: parameters INTEGER, 1 row inserted"; // proxy: "WHERE ID = ?: Unknown exception. More details: java.lang.NullPointerException: Cannot invoke "org.antlr.v4.runtime.tree.ParseTree.accept(org.antlr.v4.runtime.tree.ParseTreeVisitor)" because "tree" is null [SQLState:HY000, ISC error code:335544382]" static void insertSelectWithWhereParameter(final Connection connection) { update(connection, "WHERE ID = ?", "INSERT INTO T_PLAIN (ID, NAME) SELECT ID + 100, NAME FROM T_PLAIN WHERE ID = ?", 2901); } // Firebird server: "no column list: parameters VARCHAR(20), 1 row inserted"; // proxy: "no column list: Unknown exception. More details: java.lang.NullPointerException: Cannot invoke "org.antlr.v4.runtime.tree.ParseTree.accept(org.antlr.v4.runtime.tree.ParseTreeVisitor)" because "tree" is null [SQLState:HY000, ISC error code:335544382]" static void insertSelectWithoutColumnList(final Connection connection) { update(connection, "no column list", "INSERT INTO T_PLAIN SELECT ID + 200, NAME FROM T_PLAIN WHERE NAME = ?", "c"); } // Firebird server: "parameter in the select list: parameters VARCHAR(20), INTEGER, 1 row inserted"; // proxy: "parameter in the select list: Unknown exception. More details: java.lang.NullPointerException: Cannot invoke "org.antlr.v4.runtime.tree.ParseTree.accept(org.antlr.v4.runtime.tree.ParseTreeVisitor)" because "tree" is null [SQLState:HY000, ISC error code:335544382]" static void insertSelectWithProjectionParameter(final Connection connection) { update(connection, "parameter in the select list", "INSERT INTO T_PLAIN (ID, NAME) SELECT ID + 300, CAST(? AS VARCHAR(20)) FROM T_PLAIN WHERE ID = ?", "x", 2901); } // Firebird server: "RETURNING: parameters INTEGER, INTEGER, rows 3301 a; 3302 null; 3303 c"; // proxy: "RETURNING: Unknown exception. More details: java.lang.NullPointerException: Cannot invoke "org.antlr.v4.runtime.tree.ParseTree.accept(org.antlr.v4.runtime.tree.ParseTreeVisitor)" because "tree" is null [SQLState:HY000, ISC error code:335544382]" static void insertSelectReturning(final Connection connection) { String sql = "INSERT INTO T_PLAIN (ID, NAME) SELECT ID + 400, NAME FROM T_PLAIN WHERE ID BETWEEN ? AND ? ORDER BY ID RETURNING ID, NAME"; try (PreparedStatement statement = connection.prepareStatement(sql)) { statement.setInt(1, 2901); statement.setInt(2, 2903); String parameters = describeParameters(statement); List<String> rows = new ArrayList<>(); // The Firebird server returns a cursor with all inserted rows if (statement.execute()) { try (ResultSet resultSet = statement.getResultSet()) { while (resultSet.next()) { rows.add(resultSet.getInt(1) + " " + resultSet.getString(2)); } } } System.out.println("RETURNING: parameters " + parameters + ", rows " + String.join("; ", rows)); } catch (final SQLException ex) { System.out.println("RETURNING: " + ex.getMessage().replace('\n', ' ')); } } // Reads the rows directly on the Firebird server. // Firebird server: "rows: 2901 a; 2902 null; 2903 c; 3001 a; 3103 c; 3201 x; 3301 a; 3302 null; 3303 c"; // proxy: "rows: 2901 a; 2902 null; 2903 c" static void printRows() throws SQLException { List<String> rows = new ArrayList<>(); try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD); ResultSet resultSet = connection.createStatement().executeQuery("SELECT ID, NAME FROM T_PLAIN WHERE ID BETWEEN 2901 AND 3399 ORDER BY ID")) { while (resultSet.next()) { rows.add(resultSet.getInt(1) + " " + resultSet.getString(2)); } } System.out.println("rows: " + String.join("; ", rows)); } // Prepares the statement, prints the described parameters and the inserted row count or the error static void update(final Connection connection, final String label, final String sql, final Object... values) { try (PreparedStatement statement = connection.prepareStatement(sql)) { for (int i = 0; i < values.length; i++) { statement.setObject(i + 1, values[i]); } String parameters = describeParameters(statement); System.out.println(label + ": parameters " + parameters + ", " + statement.executeUpdate() + " row inserted"); } catch (final SQLException ex) { System.out.println(label + ": " + ex.getMessage().replace('\n', ' ')); } } static String describeParameters(final PreparedStatement statement) throws SQLException { ParameterMetaData metaData = statement.getParameterMetaData(); List<String> result = new ArrayList<>(); for (int i = 1; i <= metaData.getParameterCount(); i++) { result.add(metaData.getParameterTypeName(i) + (Types.VARCHAR == metaData.getParameterType(i) ? "(" + metaData.getPrecision(i) + ")" : "")); } return String.join(", ", result); } 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]
