makssent opened a new issue, #40274: URL: https://github.com/apache/shardingsphere/issues/40274
### 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 execute the [string concatenation `||`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L4013-L4033) in the select list as the Firebird server does. ### Actual behavior The proxy answers [`op_prepare_statement`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5262-L5264) of a statement with [`||`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L4013-L4033) in the select list with an error. The same statements were executed directly on the Firebird server and through the proxy: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SELECT 'a' \|\| 'b' FROM RDB$DATABASE` | Column `CONCATENATION` of type [VARCHAR](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L68) with length 2, value ab | 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 exception." | | `SELECT NAME \|\| '-x' FROM T_PLAIN WHERE ID = -1` | Column `CONCATENATION` of type [VARCHAR](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L68) with length 22, no rows | 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 exception." | So through the proxy an application cannot prepare or execute a statement that concatenates strings in the select list. The open PR #39759 removes this error. With its head, however, the proxy describes both columns as [VARCHAR](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L68) with length 255 instead of 2 and 22, so the length of a concatenation still does not match the Firebird server. ### Reason analysis - [`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#L414-L417) describes a [`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) with [`||`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L4013-L4033) as a string column without a table. [`getOperationType`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/Fire birdPrepareStatementCommandExecutor.java#L461-L468) returns [`12`](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#L463-L464), the JDBC code of a variable length string, and [`processCustomColumn`](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#L470-L475) creates the column without a table, so [`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/FirebirdPrepareStatementCommandExecuto r.java#L506-L512) finds no column size and returns `null`. - [`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 of the [VARCHAR](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L68) column 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-L71), which takes an `int`, so the unboxing throws [`NullPointerException`](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#L84). - [`FirebirdPacketCodecEngine#encode`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/codec/FirebirdPacketCodecEngine.java#L209-L216) answers the exception with an error. [`SQLExceptionTransformEngine#toSQLException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/exception/core/src/main/java/org/apache/shardingsphere/database/exception/core/SQLExceptionTransformEngine.java#L68) turns it into [`UnknownSQLException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/common/src/main/java/org/apache/shardingsphere/infra/exception/generic/UnknownSQLException.java#L30-L32) "Unknown exception." with the error code 0, and [`FirebirdErrorSegmentResolver#resolve`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/database/p rotocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/err/FirebirdErrorSegmentResolver.java#L55-L58) sends the code 0 as [`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). ### 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" 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 ConcatDescribe { // 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 'a' || 'b' FROM RDB$DATABASE -> CONCATENATION VARCHAR(2): ab"; // proxy: "SELECT 'a' || 'b' FROM RDB$DATABASE -> error: Unknown exception. ..." execute(connection, "SELECT 'a' || 'b' FROM RDB$DATABASE"); // Firebird server: "SELECT NAME || '-x' FROM T_PLAIN WHERE ID = -1 -> CONCATENATION VARCHAR(22): no rows"; // proxy: "SELECT NAME || '-x' FROM T_PLAIN WHERE ID = -1 -> error: Unknown exception. ..." execute(connection, "SELECT NAME || '-x' FROM T_PLAIN WHERE ID = -1"); } } // 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]
