makssent opened a new issue, #40278: URL: https://github.com/apache/shardingsphere/issues/40278
### 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 [`AVG`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/AggNodes.cpp#L511-L557), [`MIN`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/AggNodes.cpp#L1320-L1323) and [`MAX`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/AggNodes.cpp#L1320-L1323) in the select list as the Firebird server does. ### Actual behavior The proxy describes [`AVG`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/AggNodes.cpp#L511-L557), [`MIN`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/AggNodes.cpp#L1320-L1323) and [`MAX`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/AggNodes.cpp#L1320-L1323) as [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70) whatever the type of the argument, so the client gets a number instead of a date or loses the connection. The same statements were executed directly on the Firebird server and through the proxy: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SELECT MIN(CURRENT_DATE) FROM RDB$DATABASE` | Column `MIN` of type [DATE](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L79), value 2026-10-15 | Column `MIN` of type [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70), value 61328 | | `SELECT MAX(RDB$CHARACTER_SET_NAME) FROM RDB$DATABASE` | Column `MAX` of type [CHAR](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L67), value UTF8 | Column `MAX` 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 1431586360 in processOperation" | | `DELETE FROM T_PLAIN WHERE ID BETWEEN 9601 AND 9609` | 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" | | In a new connection, `SELECT AVG(RDB$RELATION_ID) FROM RDB$DATABASE` | Column `AVG` of type [BIGINT](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L80), value N | 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 N in processOperation", where N is the value of the average | So through the proxy an application gets 61328, the number of days since 1858-11-17, instead of the date 2026-10-15, and [`MAX`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/AggNodes.cpp#L1320-L1323) of a string and [`AVG`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/AggNodes.cpp#L511-L557) close the connection, so an open transaction is lost. The open PR #39759 changes the types of [`AVG`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/AggNodes.cpp#L511-L557), [`MIN`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/AggNodes.cpp#L1320-L1323) and [`MAX`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/AggNodes.cpp#L1320-L1323) and fixes `MIN(CURRENT_DATE)`. With its head, however, `AVG` is described as [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70) instead of [BIGINT](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L80), and `MAX(RDB$CHARACTER_SET_NAME)` is still described as [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70) and fails with [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception.". ### 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#L257-L259) describes an [`AggregationProjection`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/context/segment/select/projection/impl/AggregationProjection.java#L44) by the function name only. [`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) has no case for `avg`, `min` and `max`, so they get the [default](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#L447-L448) [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70) type whatever the type of the argument. - [`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. The [DATE](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L79) value is read as the described [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70), and the [CHAR](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L67) and [BIGINT](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L80) values take more bytes than the described [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70), so Jaybird reads the rest of the value as the next opera tion 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. The operation code 1431586360 is the bytes of `UTF8`, and 191 is the value of [`AVG`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/AggNodes.cpp#L511-L557). ### 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 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 AggregateDescribe { // 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)) { // The value is the current date. Firebird server: "SELECT MIN(CURRENT_DATE) FROM RDB$DATABASE -> MIN DATE: 2026-10-15"; // proxy: "SELECT MIN(CURRENT_DATE) FROM RDB$DATABASE -> MIN INTEGER: 61328" execute(connection, "SELECT MIN(CURRENT_DATE) FROM RDB$DATABASE"); // Firebird server: "SELECT MAX(RDB$CHARACTER_SET_NAME) FROM RDB$DATABASE -> MAX CHAR(63): UTF8" with spaces up to 63 characters; // proxy: "SELECT MAX(RDB$CHARACTER_SET_NAME) FROM RDB$DATABASE -> error: Unsupported or unexpected operation code 1431586360 in processOperation ..." execute(connection, "SELECT MAX(RDB$CHARACTER_SET_NAME) FROM RDB$DATABASE"); // Firebird server: "DELETE FROM T_PLAIN WHERE ID BETWEEN 9601 AND 9609 -> update count 0"; // proxy: "DELETE FROM T_PLAIN WHERE ID BETWEEN 9601 AND 9609 -> error: The connection is closed ..." execute(connection, "DELETE FROM T_PLAIN WHERE ID BETWEEN 9601 AND 9609"); } // A new connection: through the proxy the first one is closed try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { // N is the value of RDB$RELATION_ID. Firebird server: "SELECT AVG(RDB$RELATION_ID) FROM RDB$DATABASE -> AVG BIGINT: N"; // proxy: "SELECT AVG(RDB$RELATION_ID) FROM RDB$DATABASE -> error: Unsupported or unexpected operation code N in processOperation ..." execute(connection, "SELECT AVG(RDB$RELATION_ID) 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]
