makssent opened a new issue, #40293: URL: https://github.com/apache/shardingsphere/issues/40293
### 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 [`8a10a2f4822`](https://github.com/apache/shardingsphere/commit/8a10a2f4822841b45561796ad02c4709ae69e2e2) ### ShardingSphere project ShardingSphere-Proxy ### Expected behavior The proxy should describe [`EXTRACT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L8198-L8200) in the select list as the Firebird server [does](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ExprNodes.cpp#L5459-L5485): `MILLISECOND` as NUMERIC(9,1), `TIMEZONE_NAME` as VARCHAR(32) and the other date parts as SMALLINT. ### Actual behavior The proxy describes [`EXTRACT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L8198-L8200) with the date parts `WEEKDAY`, `YEARDAY`, `MILLISECOND` and `TIMEZONE_NAME` as [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70), and the values of `MILLISECOND` and `TIMEZONE_NAME` break the connection. The same statements were executed directly on the Firebird server and through the proxy, each on a new connection: | Steps | Firebird server | Proxy | | --- | --- | --- | | `SELECT EXTRACT(WEEKDAY FROM CURRENT_DATE) FROM RDB$DATABASE` | Column `EXTRACT` of type [SMALLINT](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L69), the day of the week | Column `EXTRACT` of type [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70), the same value | | `SELECT EXTRACT(YEARDAY FROM CURRENT_DATE) FROM RDB$DATABASE` | Column `EXTRACT` of type [SMALLINT](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L69), the day of the year | Column `EXTRACT` of type [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70), the same value | | `SELECT EXTRACT(MILLISECOND FROM CURRENT_TIMESTAMP) FROM RDB$DATABASE` | Column `EXTRACT` of type NUMERIC(9,1), the milliseconds | Column `EXTRACT` 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 N in processOperation" | | `SELECT EXTRACT(TIMEZONE_NAME FROM CURRENT_TIMESTAMP) FROM RDB$DATABASE` | Column `EXTRACT` of type [VARCHAR](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L68)(32), the name of the time zone | Column `EXTRACT` 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 N in processOperation" | So through the proxy an application gets wrong column types for [`EXTRACT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L8198-L8200) and loses the connection on `EXTRACT(MILLISECOND …)` and `EXTRACT(TIMEZONE_NAME …)`. ### Reason analysis - The lexer of the Firebird grammar of ShardingSphere has no tokens for the date parts `WEEKDAY`, `YEARDAY`, `MILLISECOND` and `TIMEZONE_NAME`, so they are read as identifiers, which [`extractFunction`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L333-L335) accepts, and the statement is parsed as a function call. - [`FirebirdPrepareStatementCommandExecutor#getFunctionType`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L440-L456) has no branch for [`EXTRACT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L8198-L8200), so its [`default`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/prepare/FirebirdPrepareStatementCommandExecutor.java#L453-L454) branch describes the column as [INTEGER](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L70). - [`FirebirdBinaryRowBuilder#build`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/proxy/frontend/dialect/firebird/src/main/java/org/apache/shardingsphere/proxy/frontend/firebird/command/query/statement/FirebirdBinaryRowBuilder.java#L43-L49) encodes the row by the type of the backend result, so the NUMERIC value of `MILLISECOND` and the VARCHAR value of `TIMEZONE_NAME` are 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. - With the head of the open pull request #39759 the date parts are described as [SMALLINT](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L69), and the connection is not broken, but `EXTRACT(MILLISECOND …)` and `EXTRACT(TIMEZONE_NAME …)` fail with [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception." ### 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>?encoding=UTF8 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 ExtractDescribe { // The proxy; put the URL, user and password 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 { // D is the day of the week, Y the day of the year, M the milliseconds, Z the name of the time zone of the server, N a number // Firebird server: "SELECT EXTRACT(WEEKDAY FROM CURRENT_DATE) FROM RDB$DATABASE -> EXTRACT SMALLINT: D"; // proxy: "SELECT EXTRACT(WEEKDAY FROM CURRENT_DATE) FROM RDB$DATABASE -> EXTRACT INTEGER: D" executeOnNewConnection("SELECT EXTRACT(WEEKDAY FROM CURRENT_DATE) FROM RDB$DATABASE"); // Firebird server: "SELECT EXTRACT(YEARDAY FROM CURRENT_DATE) FROM RDB$DATABASE -> EXTRACT SMALLINT: Y"; // proxy: "SELECT EXTRACT(YEARDAY FROM CURRENT_DATE) FROM RDB$DATABASE -> EXTRACT INTEGER: Y" executeOnNewConnection("SELECT EXTRACT(YEARDAY FROM CURRENT_DATE) FROM RDB$DATABASE"); // Firebird server: "SELECT EXTRACT(MILLISECOND FROM CURRENT_TIMESTAMP) FROM RDB$DATABASE -> EXTRACT NUMERIC: M"; // proxy: "SELECT EXTRACT(MILLISECOND FROM CURRENT_TIMESTAMP) FROM RDB$DATABASE -> error: Unsupported or unexpected operation code N in processOperation // [SQLState:08000, ISC error code:337248276]" executeOnNewConnection("SELECT EXTRACT(MILLISECOND FROM CURRENT_TIMESTAMP) FROM RDB$DATABASE"); // Firebird server: "SELECT EXTRACT(TIMEZONE_NAME FROM CURRENT_TIMESTAMP) FROM RDB$DATABASE -> EXTRACT VARCHAR(32): Z"; // proxy: "SELECT EXTRACT(TIMEZONE_NAME FROM CURRENT_TIMESTAMP) FROM RDB$DATABASE -> error: Unsupported or unexpected operation code N in processOperation // [SQLState:08000, ISC error code:337248276]" executeOnNewConnection("SELECT EXTRACT(TIMEZONE_NAME FROM CURRENT_TIMESTAMP) FROM RDB$DATABASE"); } // A new connection for each statement: the proxy breaks the connection on some of them static void executeOnNewConnection(final String sql) throws SQLException { try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) { execute(connection, sql); } } // 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]
