makssent opened a new issue, #40253: URL: https://github.com/apache/shardingsphere/issues/40253
### 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 the session management statements ([`SET TIME ZONE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5523-L5531), [`SET BIND`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5405-L5407), [`SET DECFLOAT ROUND`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5391-L5393), [`SET STATEMENT TIMEOUT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5501-L5502), [`SET SESSION IDLE TIMEOUT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5499-L5500), [`ALTER SESSION RESET`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L5371-L5373)) 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: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SET TIME ZONE 'Europe/Moscow'` | Success | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: SET TIME ZONE 'Europe/Moscow', null" | | `SET BIND OF DECFLOAT TO LEGACY` | Success | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: SET BIND OF DECFLOAT TO LEGACY, null" | | `SET DECFLOAT ROUND CEILING` | Success | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: SET DECFLOAT ROUND CEILING, null" | | `SET STATEMENT TIMEOUT 10` | Success | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: SET STATEMENT TIMEOUT 10, null" | | `SET SESSION IDLE TIMEOUT 600` | Success | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: SET SESSION IDLE TIMEOUT 600, null" | | `ALTER SESSION RESET` | Success | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'ALTERSESSION'" | So through the proxy an application cannot change the session time zone, the data type bindings, the rounding mode or the timeouts, and cannot reset the session. ### Reason analysis - [`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) in the Firebird grammar of ShardingSphere has no session management statements ([`mng_statement`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L915-L926) of the Firebird server), and [`setTransaction`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/TCLStatement.g4#L22-L24) is the only statement in it that starts with SET. - [`FirebirdAdminExecutorCreator#create`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/backend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/backend/firebird/handler/admin/FirebirdAdminExecutorCreator.java#L38-L47) handles [`SetStatement`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dal/SetStatement.java#L32) and [`ShowStatement`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/statement/type/dal/ShowStatement.java#L27), but the Firebird parser never produces these statements. ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 6.0.7. #### Steps 1. 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: - "*.*" ``` 2. 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 SessionStatements { // 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: "SET TIME ZONE 'Europe/Moscow' -> update count -1"; // proxy: "SET TIME ZONE 'Europe/Moscow' -> error: Dynamic SQL Error; You have an error in your SQL syntax: SET TIME ZONE 'Europe/Moscow', null ..." execute(connection, "SET TIME ZONE 'Europe/Moscow'"); // Firebird server: "SET BIND OF DECFLOAT TO LEGACY -> update count -1"; // proxy: "SET BIND OF DECFLOAT TO LEGACY -> error: Dynamic SQL Error; You have an error in your SQL syntax: SET BIND OF DECFLOAT TO LEGACY, null ..." execute(connection, "SET BIND OF DECFLOAT TO LEGACY"); // Firebird server: "SET DECFLOAT ROUND CEILING -> update count -1"; // proxy: "SET DECFLOAT ROUND CEILING -> error: Dynamic SQL Error; You have an error in your SQL syntax: SET DECFLOAT ROUND CEILING, null ..." execute(connection, "SET DECFLOAT ROUND CEILING"); // Firebird server: "SET STATEMENT TIMEOUT 10 -> update count -1"; // proxy: "SET STATEMENT TIMEOUT 10 -> error: Dynamic SQL Error; You have an error in your SQL syntax: SET STATEMENT TIMEOUT 10, null ..." execute(connection, "SET STATEMENT TIMEOUT 10"); // Firebird server: "SET SESSION IDLE TIMEOUT 600 -> update count -1"; // proxy: "SET SESSION IDLE TIMEOUT 600 -> error: Dynamic SQL Error; You have an error in your SQL syntax: SET SESSION IDLE TIMEOUT 600, null ..." execute(connection, "SET SESSION IDLE TIMEOUT 600"); // Firebird server: "ALTER SESSION RESET -> update count -1"; // proxy: "ALTER SESSION RESET -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'ALTERSESSION' ..." execute(connection, "ALTER SESSION RESET"); } } // 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]
