makssent opened a new issue, #40263: URL: https://github.com/apache/shardingsphere/issues/40263
### 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 queries with the context variables [`CURRENT_CONNECTION`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7817-L7818), [`CURRENT_TRANSACTION`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7819-L7820), [`CURRENT_USER`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7807) and [`CURRENT_ROLE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7812) as the Firebird server does. ### Actual behavior The proxy rejects each of these queries with an error. The same statements were executed directly on the Firebird server and through the proxy: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SELECT 1 FROM RDB$DATABASE WHERE CURRENT_CONNECTION > 0` | Success, column CONSTANT of type INTEGER, one row with 1 | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown column 'CURRENT_CONNECTION' in 'where clause'." | | `SELECT 1 FROM RDB$DATABASE WHERE CURRENT_TRANSACTION > 0` | Success, column CONSTANT of type INTEGER, one row with 1 | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown column 'CURRENT_TRANSACTION' in 'where clause'." | | `SELECT CURRENT_ROLE FROM RDB$DATABASE` | Success, column ROLE of type VARCHAR, one row with NONE | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception." | | `SELECT CURRENT_USER FROM RDB$DATABASE` | Success, column USER of type VARCHAR, one row with the name of the connected user | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception." | So through the proxy an application cannot read the ID of its connection and transaction, its user name and its role. ### Reason analysis - The rule [`contextVariables`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L527-L536) lists [`CURRENT_CONNECTION`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L528) and [`CURRENT_TRANSACTION`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L531), but neither [`Keyword.g4`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/Keyword.g4) nor [`FirebirdKeyword.g4`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/Fireb irdKeyword.g4) defines these tokens. The lexer reads them as identifiers, and [`ColumnSegmentBinder#bind`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/ColumnSegmentBinder.java#L111-L112) throws [`ColumnNotFoundException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/common/src/main/java/org/apache/shardingsphere/infra/exception/kernel/metadata/ColumnNotFoundException.java#L30-L32), because `RDB$DATABASE` has no columns with these names. - To describe the column of [`CURRENT_ROLE`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L438) or [`CURRENT_USER`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L439), [`FirebirdPrepareStatementCommandExecutor#getFunctionType`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L434-L450) gives it the type [`VARCHAR`](https://github.com/apache/shardingsp here/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L441), and [`FirebirdPrepareStatementCommandExecutor#resolveColumnLength`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L506-L512) returns `null` for its length, because the column has no table. - [`FirebirdReturnColumnPacket#write`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/prepare/FirebirdReturnColumnPacket.java#L79-L84) passes this `null` length to [`FirebirdPrepareStatementReturnPacket#writeInt`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/prepare/FirebirdPrepareStatementReturnPacket.java#L67), which takes an `int`. The `NullPointerException` reaches the client as [`UnknownSQLException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/common/src/main/java/org/apache/shardingsphere/infra/exception/generic/UnknownSQLException.java#L30-L32) with the message "Unknown exception." - The open pull request #39759 replaces [`FirebirdPrepareStatementCommandExecutor#getFunctionType`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L434-L450) and adds a `null` check of the length to [`FirebirdReturnColumnPacket#write`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/prepare/FirebirdReturnColumnPacket.java#L79-L84), which may remove the error for [`CURRENT_USER`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepa reStatementCommandExecutor.java#L439) and [`CURRENT_ROLE`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L438). It does not add the [`CURRENT_CONNECTION`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L528) and [`CURRENT_TRANSACTION`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L531) tokens. ### 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 ContextVariables { // 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: "SELECT 1 FROM RDB$DATABASE WHERE CURRENT_CONNECTION > 0 -> CONSTANT INTEGER: 1"; // proxy: "SELECT 1 FROM RDB$DATABASE WHERE CURRENT_CONNECTION > 0 -> error: Unknown column 'CURRENT_CONNECTION' in 'where clause'. ..." execute(connection, "SELECT 1 FROM RDB$DATABASE WHERE CURRENT_CONNECTION > 0"); // Firebird server: "SELECT 1 FROM RDB$DATABASE WHERE CURRENT_TRANSACTION > 0 -> CONSTANT INTEGER: 1"; // proxy: "SELECT 1 FROM RDB$DATABASE WHERE CURRENT_TRANSACTION > 0 -> error: Unknown column 'CURRENT_TRANSACTION' in 'where clause'. ..." execute(connection, "SELECT 1 FROM RDB$DATABASE WHERE CURRENT_TRANSACTION > 0"); // Firebird server: "SELECT CURRENT_ROLE FROM RDB$DATABASE -> ROLE VARCHAR(63): NONE"; // proxy: "SELECT CURRENT_ROLE FROM RDB$DATABASE -> error: Unknown exception. ..." execute(connection, "SELECT CURRENT_ROLE FROM RDB$DATABASE"); // Firebird server: "SELECT CURRENT_USER FROM RDB$DATABASE -> USER VARCHAR(63): " and the name of the connected user; // proxy: "SELECT CURRENT_USER FROM RDB$DATABASE -> error: Unknown exception. ..." execute(connection, "SELECT CURRENT_USER FROM RDB$DATABASE"); } } // 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]
