makssent opened a new issue, #40276: URL: https://github.com/apache/shardingsphere/issues/40276
### 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 describe a [scalar subquery](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L11238-L11245) and a [`UNION`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/pass1.cpp#L2550-L2565) column in the select list as the Firebird server does. ### Actual behavior The proxy describes a [scalar subquery](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L11238-L11245) and a [`UNION`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/pass1.cpp#L2550-L2565) column as [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70), and the [BIGINT](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L80) value of the [`UNION`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/pass1.cpp#L2550-L2565) breaks the connection. The same statements were executed directly on the Firebird server and through the proxy: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SELECT (SELECT NAME FROM T_PLAIN WHERE ID = -1) FROM RDB$DATABASE` | Column `NAME` of type [VARCHAR](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L68), value NULL | Column `(SELECT NAME FROM T_PLAIN WHERE ID = -1)` of type [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70), value NULL | | `SELECT ID FROM T_PLAIN WHERE ID = -1 UNION ALL SELECT CAST(1 AS BIGINT) FROM RDB$DATABASE` | Column `ID` of type [BIGINT](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L80), value 1 | Column `ID` of type [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70), then Jaybird error [`jb_unexpectedOperationCode`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/gds/ng/wire/AbstractWireOperations.java#L201-L205) "Unsupported or unexpected operation code 1 in processOperation" | | `DELETE FROM T_PLAIN WHERE ID BETWEEN 9401 AND 9409` | Success | Jaybird error [`jb_connectionClosed`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/gds/ng/FbExceptionBuilder.java#L313-L317) "The connection is closed" | So through the proxy an application gets an [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70) column instead of [VARCHAR](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L68) for a [scalar subquery](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L11238-L11245) and loses the connection on a [`UNION`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/pass1.cpp#L2550-L2565) of [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70) and [BIGINT](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L80). ### Reason analysis - [`FirebirdPrepareStatementCommandExecutor#processReturnValues`](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#L260-L262) describes every [`SubqueryProjection`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/impl/SubqueryProjection.java#L39) as [`Types.INTEGER`](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#L262) and names it by the text of the subquery ([`getColumnName`](https://github.com/apache/shardingsphere/blob/ 01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/impl/SubqueryProjection.java#L50-L58)). The open pull request #39759 rewrites this branch, but still describes a subquery as [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70). - [`FirebirdDMLStatementVisitor#visitCombineClause`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/type/FirebirdDMLStatementVisitor.java#L285) builds the [`UNION`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/pass1.cpp#L2550-L2565) statement with the projections of the first branch, and [`getProjections`](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#L267-L273) returns them for the description, so [`processReturnValues`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/fireb ird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L246-L254) gives the column the type of `T_PLAIN.ID`. - [`FirebirdBinaryRowBuilder#build`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/FirebirdBinaryRowBuilder.java#L46) encodes the row by the type of the backend result, so the 8-byte [BIGINT](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L80) value is sent under the described 4-byte [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70) column. Jaybird reads the rest of the value as the next operation code ([`processOperation`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/gds/ng/wire/AbstractWireOperations.java#L201-L205)) and closes the connection. ### 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. Execute the statements from the table under "Actual behavior" in this order 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 SubqueryDescribe { // 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 (SELECT NAME FROM T_PLAIN WHERE ID = -1) FROM RDB$DATABASE -> NAME VARCHAR(20): null"; // proxy: "SELECT (SELECT NAME FROM T_PLAIN WHERE ID = -1) FROM RDB$DATABASE -> (SELECT NAME FROM T_PLAIN WHERE ID = -1) INTEGER: null" execute(connection, "SELECT (SELECT NAME FROM T_PLAIN WHERE ID = -1) FROM RDB$DATABASE"); // Firebird server: "SELECT ID FROM T_PLAIN WHERE ID = -1 UNION ALL SELECT CAST(1 AS BIGINT) FROM RDB$DATABASE -> ID BIGINT: 1"; // proxy: "SELECT ID FROM T_PLAIN WHERE ID = -1 UNION ALL SELECT CAST(1 AS BIGINT) FROM RDB$DATABASE -> error: Unsupported or unexpected operation code 1 in processOperation ..." execute(connection, "SELECT ID FROM T_PLAIN WHERE ID = -1 UNION ALL SELECT CAST(1 AS BIGINT) FROM RDB$DATABASE"); // Firebird server: "DELETE FROM T_PLAIN WHERE ID BETWEEN 9401 AND 9409 -> update count 0"; // proxy: "DELETE FROM T_PLAIN WHERE ID BETWEEN 9401 AND 9409 -> error: The connection is closed ..." execute(connection, "DELETE FROM T_PLAIN WHERE ID BETWEEN 9401 AND 9409"); } } // 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]
