makssent opened a new issue, #40277: URL: https://github.com/apache/shardingsphere/issues/40277
### 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 and return [`CASE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L13588-L13599), [unary minus](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L8880-L8916) and [`DISTINCT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L6038-L6041) with a column in parentheses in the select list as the Firebird server does. ### Actual behavior The proxy describes these statements without output columns, so the client gets no result set. The same statements were executed directly on the Firebird server and through the proxy: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SELECT CASE WHEN 1 > 0 THEN 'a' ELSE 'b' END FROM RDB$DATABASE` | Column `CASE` of type [CHAR](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L67), value a | No result set, update count 0 | | `SELECT -RDB$RELATION_ID FROM RDB$DATABASE` | Column without a name of type [SMALLINT](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L69), value -N | No result set, update count 0 | | `SELECT DISTINCT(RDB$RELATION_ID) FROM RDB$DATABASE` | Column `RDB$RELATION_ID` of type [SMALLINT](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L69), value N | No result set, update count 0 | | With auto-commit off, `INSERT INTO T_PLAIN (ID, NAME) VALUES (9501, 'before')` | Success | Success | | `SELECT CASE WHEN 1 > 0 THEN 'a' ELSE 'b' END FROM RDB$DATABASE` | Column `CASE` of type [CHAR](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L67), value a | Jaybird error [`jb_closeCursorBeforeCount`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/gds/ng/AbstractFbStatement.java#L615-L617) "Cursor still open, fetch all rows or close cursor before fetching SQL counts" | So through the proxy an application gets no row for these statements, and in an open transaction [`Statement.execute`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jdbc/FBStatement.java#L534-L538) fails. ### Reason analysis - [`FirebirdStatementVisitor#visitSimpleExpr`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/FirebirdStatementVisitor.java#L383-L431) has no branch for [`CASE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L13588-L13599) and [unary minus](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L8880-L8916) and returns a [`CommonExpressionSegment`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/expr/complex/CommonExpressionSegment.java#L28) for them ([line 430](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/eng ine/firebird/visitor/statement/FirebirdStatementVisitor.java#L430)). For [`DISTINCT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L6038-L6041)`(RDB$RELATION_ID)` it returns the [`ColumnSegment`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/column/ColumnSegment.java#L38) in parentheses ([lines 413-419](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/FirebirdStatementVisitor.java#L413-L419)). [`FirebirdDMLStatementVisitor#createProjection`](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/FirebirdDMLS tatementVisitor.java#L451-L462) wraps both into an [`ExpressionProjectionSegment`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/item/ExpressionProjectionSegment.java#L35). - [`FirebirdPrepareStatementCommandExecutor#processExpressionProjection`](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#L408-L432) handles only [`FunctionSegment`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/expr/FunctionSegment.java#L36), [`BinaryOperationExpression`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/expr/BinaryOperationExpression.java#L30) and [`LiteralExpressionSegment`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/s tatement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/segment/dml/expr/simple/LiteralExpressionSegment.java#L28) and adds no column for other expressions, so the statement is described without output columns, and Jaybird creates no result set ([`hasFields`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/gds/ng/AbstractFbStatement.java#L904-L907)). ### 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. The value N of `RDB$RELATION_ID` depends on the database. "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 ExpressionDescribe { // 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 delete the rows of the example 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 9501 AND 9509"); try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { // Firebird server: "SELECT CASE WHEN 1 > 0 THEN 'a' ELSE 'b' END FROM RDB$DATABASE -> CASE CHAR(1): a"; // proxy: "SELECT CASE WHEN 1 > 0 THEN 'a' ELSE 'b' END FROM RDB$DATABASE -> update count 0" execute(connection, "SELECT CASE WHEN 1 > 0 THEN 'a' ELSE 'b' END FROM RDB$DATABASE"); // The column has no name, N is the value of RDB$RELATION_ID. // Firebird server: "SELECT -RDB$RELATION_ID FROM RDB$DATABASE -> SMALLINT: -N"; // proxy: "SELECT -RDB$RELATION_ID FROM RDB$DATABASE -> update count 0" execute(connection, "SELECT -RDB$RELATION_ID FROM RDB$DATABASE"); // Firebird server: "SELECT DISTINCT(RDB$RELATION_ID) FROM RDB$DATABASE -> RDB$RELATION_ID SMALLINT: N"; // proxy: "SELECT DISTINCT(RDB$RELATION_ID) FROM RDB$DATABASE -> update count 0" execute(connection, "SELECT DISTINCT(RDB$RELATION_ID) FROM RDB$DATABASE"); connection.setAutoCommit(false); // Firebird server and proxy: "INSERT INTO T_PLAIN (ID, NAME) VALUES (9501, 'before') -> update count 1" execute(connection, "INSERT INTO T_PLAIN (ID, NAME) VALUES (9501, 'before')"); // Firebird server: "SELECT CASE WHEN 1 > 0 THEN 'a' ELSE 'b' END FROM RDB$DATABASE -> CASE CHAR(1): a"; // proxy: "SELECT CASE WHEN 1 > 0 THEN 'a' ELSE 'b' END FROM RDB$DATABASE -> error: Cursor still open, fetch all rows or close cursor before fetching SQL counts ..." execute(connection, "SELECT CASE WHEN 1 > 0 THEN 'a' ELSE 'b' END FROM RDB$DATABASE"); connection.commit(); } executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 9501 AND 9509"); } // 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]
