makssent opened a new issue, #40250: URL: https://github.com/apache/shardingsphere/issues/40250
### 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 [`SELECT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5781-L5792) with [`FOR UPDATE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5795-L5798), [`FOR UPDATE OF`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5801-L5804), [`WITH LOCK`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5807-L5810) and [`PLAN`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L6370-L6373) as the Firebird server does. ### Actual behavior The proxy rejects each of these statements with a syntax 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 = 6701: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 FOR UPDATE` | The row with ID = 6701 | Error [`isc_dsql_error`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L250) "Dynamic SQL Error; You have an error in your SQL syntax: SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 FOR UPDATE, null" | | `SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 WITH LOCK` | The row with ID = 6701 | Error [`isc_dsql_error`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L250) "Dynamic SQL Error; You have an error in your SQL syntax: SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 WITH LOCK, null" | | `SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 FOR UPDATE OF NAME WITH LOCK` | The row with ID = 6701 | Error [`isc_dsql_error`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L250) "Dynamic SQL Error; You have an error in your SQL syntax: SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 FOR UPDATE OF NAME WITH LOCK, null" | | `SELECT ID FROM T_PLAIN WHERE ID = 6701 PLAN (T_PLAIN NATURAL)` | The row with ID = 6701 | Error [`isc_dsql_error`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L250) "Dynamic SQL Error; You have an error in your SQL syntax: SELECT ID FROM T_PLAIN WHERE ID = 6701 PLAN (T_PLAIN NATURAL), null" | So through the proxy an application cannot lock the rows it reads and cannot give a query its access plan. ### Reason analysis - The rules [`select`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/DMLStatement.g4#L71-L73) and [`selectClause`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/DMLStatement.g4#L79-L81) of the Firebird grammar of ShardingSphere have no [`FOR UPDATE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5795-L5798), no [`WITH LOCK`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5807-L5810) and no [`PLAN`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L6370-L6373), so the proxy rejects these statements with a syntax error. ### 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. Turn auto-commit off, execute `INSERT INTO T_PLAIN (ID, NAME) VALUES (6701, '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 SelectLockClauses { // 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 delete the rows of the example 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 6701 AND 6709"); try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { connection.setAutoCommit(false); // Firebird server: "INSERT INTO T_PLAIN (ID, NAME) VALUES (6701, 'before') -> update count 1"; // proxy: "INSERT INTO T_PLAIN (ID, NAME) VALUES (6701, 'before') -> update count 1" execute(connection, "INSERT INTO T_PLAIN (ID, NAME) VALUES (6701, 'before')"); // Firebird server: "SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 FOR UPDATE -> ID INTEGER, NAME VARCHAR(20): 6701, before"; // proxy: "SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 FOR UPDATE -> error: Dynamic SQL Error; You have an error in your SQL syntax: // SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 FOR UPDATE, null ..." execute(connection, "SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 FOR UPDATE"); // Firebird server: "SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 WITH LOCK -> ID INTEGER, NAME VARCHAR(20): 6701, before"; // proxy: "SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 WITH LOCK -> error: Dynamic SQL Error; You have an error in your SQL syntax: // SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 WITH LOCK, null ..." execute(connection, "SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 WITH LOCK"); // Firebird server: "SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 FOR UPDATE OF NAME WITH LOCK -> ID INTEGER, NAME VARCHAR(20): 6701, before"; // proxy: "SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 FOR UPDATE OF NAME WITH LOCK -> error: Dynamic SQL Error; // You have an error in your SQL syntax: SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 FOR UPDATE OF NAME WITH LOCK, null ..." execute(connection, "SELECT ID, NAME FROM T_PLAIN WHERE ID = 6701 FOR UPDATE OF NAME WITH LOCK"); // Firebird server: "SELECT ID FROM T_PLAIN WHERE ID = 6701 PLAN (T_PLAIN NATURAL) -> ID INTEGER: 6701"; // proxy: "SELECT ID FROM T_PLAIN WHERE ID = 6701 PLAN (T_PLAIN NATURAL) -> error: Dynamic SQL Error; You have an error in your SQL syntax: // SELECT ID FROM T_PLAIN WHERE ID = 6701 PLAN (T_PLAIN NATURAL), null ..." execute(connection, "SELECT ID FROM T_PLAIN WHERE ID = 6701 PLAN (T_PLAIN NATURAL)"); connection.commit(); } executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 6701 AND 6709"); } // 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()); } } 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]
