makssent opened a new issue, #40252: URL: https://github.com/apache/shardingsphere/issues/40252
### 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 statements with the pseudo columns [`RDB$DB_KEY`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7604) and [`RDB$RECORD_VERSION`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7605) as the Firebird server does. ### Actual behavior The proxy rejects both pseudo columns with an Unknown column error. The same statements were executed directly on the Firebird server and through the proxy, with the rows with ID = 6901 and 6902 in `T_PLAIN`: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SELECT ID, OCTET_LENGTH(RDB$DB_KEY) FROM T_PLAIN WHERE ID = 6901` | One row: ID = 6901, key length 8 | Error [`isc_random`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/err/FirebirdErrorSegmentResolver.java#L56) "Unknown column 'RDB$DB_KEY' in 'field list'." | | `SELECT COUNT(*) FROM T_PLAIN WHERE ID BETWEEN 6901 AND 6909 AND RDB$RECORD_VERSION > 0` | One row: 2 | Error [`isc_random`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/err/FirebirdErrorSegmentResolver.java#L56) "Unknown column 'RDB$RECORD_VERSION' in 'where clause'." | So through the proxy an application cannot use the record key and the record version of a row, for example in an updatable result set of a table without a primary key, where Jaybird identifies the rows by [`RDB$DB_KEY`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7604) ([`FBRowUpdater`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBRowUpdater.java#L238-L242)). ### Reason analysis - [`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#L26-L31) for a column that is not in the table metadata. - [`FirebirdMetaDataLoader#load`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/connector/dialect/firebird/src/main/java/org/apache/shardingsphere/database/connector/firebird/metadata/data/loader/FirebirdMetaDataLoader.java#L40-L48) loads the table columns with the generic [`TableMetaDataLoader#loadNormalized`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/connector/core/src/main/java/org/apache/shardingsphere/database/connector/core/metadata/data/loader/type/TableMetaDataLoader.java#L80-L82), and no Firebird code adds the pseudo columns [`RDB$DB_KEY`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7604) and [`RDB$RECORD_VERSION`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7605) to the metadata. ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 6.0.7. #### Steps 1. Create the table on the Firebird server and insert two rows: ```sql CREATE TABLE T_PLAIN (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(20)); INSERT INTO T_PLAIN (ID, NAME) VALUES (6901, 'before'); INSERT INTO T_PLAIN (ID, NAME) VALUES (6902, 'after'); ``` 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 DbKeyPseudoColumns { // 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 insert the rows of step 1 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 6901 AND 6909"); executeOnServer("INSERT INTO T_PLAIN (ID, NAME) VALUES (6901, 'before')"); executeOnServer("INSERT INTO T_PLAIN (ID, NAME) VALUES (6902, 'after')"); try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { // Firebird server: "SELECT ID, OCTET_LENGTH(RDB$DB_KEY) FROM T_PLAIN WHERE ID = 6901 -> ID INTEGER, OCTET_LENGTH INTEGER: 6901, 8"; // proxy: "SELECT ID, OCTET_LENGTH(RDB$DB_KEY) FROM T_PLAIN WHERE ID = 6901 -> error: Unknown column 'RDB$DB_KEY' in 'field list'. ..." execute(connection, "SELECT ID, OCTET_LENGTH(RDB$DB_KEY) FROM T_PLAIN WHERE ID = 6901"); // Firebird server: "SELECT COUNT(*) FROM T_PLAIN WHERE ID BETWEEN 6901 AND 6909 AND RDB$RECORD_VERSION > 0 -> COUNT BIGINT: 2"; // proxy: "SELECT COUNT(*) FROM T_PLAIN WHERE ID BETWEEN 6901 AND 6909 AND RDB$RECORD_VERSION > 0 // -> error: Unknown column 'RDB$RECORD_VERSION' in 'where clause'. ..." execute(connection, "SELECT COUNT(*) FROM T_PLAIN WHERE ID BETWEEN 6901 AND 6909 AND RDB$RECORD_VERSION > 0"); } executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 6901 AND 6909"); } // 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]
