makssent opened a new issue, #40264: URL: https://github.com/apache/shardingsphere/issues/40264
### 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 [`RECREATE TABLE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L1562-L1563), [`SET GENERATOR`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L1919-L1930), [`RECREATE SEQUENCE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L1578-L1579), [`DROP SEQUENCE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4627-L4628), [`CREATE INDEX`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L1518-L1529), [`DROP INDEX`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4603-L4604), [`RECREATE EXCEPTION`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L1574-L1575) and [`DROP EXCEPTION`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4601-L4602) as the Firebird server does. ### Actual behavior The proxy rejects each of these statements with a syntax error, and [`DROP TABLE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4607-L4608) then fails because the table was not created. The same statements were executed directly on the Firebird server and through the proxy, in this order: | Statement | Firebird server | Proxy | | --- | --- | --- | | `RECREATE TABLE T_AUDIT_RECREATE (ID INTEGER)` | Success | 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: no viable alternative at input 'RECREATE'" | | `DROP TABLE T_AUDIT_RECREATE` | Success | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Table or view 'T_AUDIT_RECREATE' does not exist." | | `SET GENERATOR S_AUDIT_SEQ TO 0` | Success | 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: SET GENERATOR S_AUDIT_SEQ TO 0, null" | | `RECREATE SEQUENCE S_AUDIT_TMP` | Success | 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: no viable alternative at input 'RECREATE'" | | `DROP SEQUENCE S_AUDIT_TMP` | Success | 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: no viable alternative at input 'DROPSEQUENCE'" | | `CREATE INDEX I_AUDIT_DDL ON T_PLAIN (NAME)` | Success | 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: no viable alternative at input 'CREATEINDEX'" | | `DROP INDEX I_AUDIT_DDL` | Success | 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: no viable alternative at input 'DROPINDEX'" | | `RECREATE EXCEPTION E_AUDIT_DDL 'audit'` | Success | 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: no viable alternative at input 'RECREATE'" | | `DROP EXCEPTION E_AUDIT_DDL` | Success | 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: no viable alternative at input 'DROPEXCEPTION'" | So through the proxy an application cannot create, change or drop tables, sequences, indexes and exceptions with these statements, although the Firebird server executes them. ### Reason analysis - The rule [`execute`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/org/apache/shardingsphere/sql/parser/autogen/FirebirdStatement.g4#L22-L55) of the Firebird grammar of ShardingSphere has no alternative for any of these statements, and the grammar has no rules for them, so the parser rejects them. ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 6.0.7. #### Steps 1. Create the table and the sequence on the Firebird server: ```sql CREATE TABLE T_PLAIN (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(20)); CREATE SEQUENCE S_AUDIT_SEQ; ``` 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" 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 DdlStatements { // 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"; public static void main(final String[] args) throws SQLException { try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { // Firebird server: "RECREATE TABLE T_AUDIT_RECREATE (ID INTEGER) -> update count -1"; // proxy: "RECREATE TABLE T_AUDIT_RECREATE (ID INTEGER) -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'RECREATE' ..." execute(connection, "RECREATE TABLE T_AUDIT_RECREATE (ID INTEGER)"); // Firebird server: "DROP TABLE T_AUDIT_RECREATE -> update count -1"; // proxy: "DROP TABLE T_AUDIT_RECREATE -> error: Table or view 'T_AUDIT_RECREATE' does not exist. ..." execute(connection, "DROP TABLE T_AUDIT_RECREATE"); // Firebird server: "SET GENERATOR S_AUDIT_SEQ TO 0 -> update count -1"; // proxy: "SET GENERATOR S_AUDIT_SEQ TO 0 -> error: Dynamic SQL Error; You have an error in your SQL syntax: SET GENERATOR S_AUDIT_SEQ TO 0, null ..." execute(connection, "SET GENERATOR S_AUDIT_SEQ TO 0"); // Firebird server: "RECREATE SEQUENCE S_AUDIT_TMP -> update count -1"; // proxy: "RECREATE SEQUENCE S_AUDIT_TMP -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'RECREATE' ..." execute(connection, "RECREATE SEQUENCE S_AUDIT_TMP"); // Firebird server: "DROP SEQUENCE S_AUDIT_TMP -> update count -1"; // proxy: "DROP SEQUENCE S_AUDIT_TMP -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DROPSEQUENCE' ..." execute(connection, "DROP SEQUENCE S_AUDIT_TMP"); // Firebird server: "CREATE INDEX I_AUDIT_DDL ON T_PLAIN (NAME) -> update count -1"; // proxy: "CREATE INDEX I_AUDIT_DDL ON T_PLAIN (NAME) -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'CREATEINDEX' ..." execute(connection, "CREATE INDEX I_AUDIT_DDL ON T_PLAIN (NAME)"); // Firebird server: "DROP INDEX I_AUDIT_DDL -> update count -1"; // proxy: "DROP INDEX I_AUDIT_DDL -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DROPINDEX' ..." execute(connection, "DROP INDEX I_AUDIT_DDL"); // Firebird server: "RECREATE EXCEPTION E_AUDIT_DDL 'audit' -> update count -1"; // proxy: "RECREATE EXCEPTION E_AUDIT_DDL 'audit' -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'RECREATE' ..." execute(connection, "RECREATE EXCEPTION E_AUDIT_DDL 'audit'"); // Firebird server: "DROP EXCEPTION E_AUDIT_DDL -> update count -1"; // proxy: "DROP EXCEPTION E_AUDIT_DDL -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DROPEXCEPTION' ..." execute(connection, "DROP EXCEPTION E_AUDIT_DDL"); } } // 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()); } } } ``` -- 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]
