makssent opened a new issue, #40273: URL: https://github.com/apache/shardingsphere/issues/40273
### 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 and return [numeric](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7731-L7750), [boolean](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7793-L7797) and [hexadecimal string](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/Parser.cpp#L608-L619) literals in the select list as the Firebird server does. ### Actual behavior The proxy names these columns `CONSTANT`, as the Firebird server does, but describes them with other types, so the client gets wrong values, no row or a broken connection. The same statements were executed directly on the Firebird server and through the proxy: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SELECT 1.5 FROM RDB$DATABASE` | Column `CONSTANT` of type [NUMERIC](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L80), value 1.5 | Column `CONSTANT` of type [NUMERIC](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L80), value 1 | | `SELECT 1.5e2 FROM RDB$DATABASE` | Column `CONSTANT` of type [DOUBLE PRECISION](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L72), value 150.0 | Column `CONSTANT` of type [NUMERIC](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L80), value 4639481672377565184 | | `SELECT FALSE FROM RDB$DATABASE` | Column `CONSTANT` of type [BOOLEAN](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L88), value false | Column `CONSTANT` of type [NULL](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L89), value NULL | | `SELECT X'4142' FROM RDB$DATABASE` | Column `CONSTANT` of type [CHAR](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L67), value 0x4142 | No result set, update count 0 | | `SELECT TRUE FROM RDB$DATABASE` | Column `CONSTANT` of type [BOOLEAN](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L88), value true | Column `CONSTANT` of type [NULL](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L89), then Jaybird error [`isc_net_read_err`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/gds/ng/FbExceptionBuilder.java#L301-L305) "Error reading data from the connection." | | `DELETE FROM T_PLAIN WHERE ID BETWEEN 9101 AND 9109` | 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 reads wrong values without an error, gets no row for a [hexadecimal string literal](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/Parser.cpp#L608-L619) and loses the connection on [`TRUE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7796). The open PR #39759 changes how literal columns are described, but does not fix these cases. With its head, `1.5` is still read as 1, `1.5e2` fails with [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown exception.", and Jaybird cannot read the result of `SELECT FALSE`. ### Reason analysis - [`FirebirdPrepareStatementCommandExecutor#processExpressionProjection`](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#L424-L437) chooses the type of a literal column by the Java class of the value. `1.5` and `1.5e2` are both [`BigDecimal`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/value/literal/impl/NumberLiteralValue.java#L39-L46) values and get [`Types.NUMERIC`](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#L433-L434), and a [`Boolean`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/value/literal/impl/BooleanLiteralValue.java#L36-L38) value keeps the initial [`Types.NULL`](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#L426). - [`FirebirdReturnColumnPacket#write`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/prepare/FirebirdReturnColumnPacket.java#L76-L77) always sends scale 0, and [`FirebirdInt8BinaryProtocolValue#write`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/execute/protocol/FirebirdInt8BinaryProtocolValue.java#L37-L38) sends a [`BigDecimal`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/value/literal/impl/NumberLiteralValue.java#L44) value as its [`longValue()`](https://github.com/apache/shardingsphere/blob/8a10a2f4822 841b45561796ad02c4709ae69e2e2/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/execute/protocol/FirebirdInt8BinaryProtocolValue.java#L38), so `1.5` comes as 1. - [`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#L46) encodes the row by the type of the backend result, not by the described type. The [DOUBLE PRECISION](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L72) value 150.0 is sent as the 8 bytes of a double and is read as the described [NUMERIC](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L80), which gives 4639481672377565184. The [BOOLEAN](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L88) values are sent under the described [NULL](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/sqlda_pub.h#L89) type, so the client gets NULL for [`FALSE`](https://github .com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7795) and cannot read the row for [`TRUE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7796). - [`FirebirdStatementVisitor#visitHexadecimalLiterals`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/java/org/apache/shardingsphere/sql/parser/engine/firebird/visitor/statement/FirebirdStatementVisitor.java#L178-L181) returns [`OtherLiteralValue`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/value/literal/impl/OtherLiteralValue.java#L29), which [`SQLUtils#createLiteralExpression`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/statement/core/src/main/java/org/apache/shardingsphere/sql/parser/statement/core/util/SQLUtils.java#L293-L295) turns into a [`CommonExpressionSegment`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/statement/core/src/main/java/org/apache/sha rdingsphere/sql/parser/statement/core/segment/dml/expr/complex/CommonExpressionSegment.java#L31). [`processExpressionProjection`](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#L414-L439) adds no column for it, 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. "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; import java.util.Properties; public class LiteralDescribe { // 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 { Properties properties = new Properties(); properties.setProperty("user", USER); properties.setProperty("password", PASSWORD); // Through the proxy Jaybird takes the value of SELECT TRUE for the length of a 16 MB value and waits for it; // the read timeout of 15 seconds ends the wait, without it the program hangs properties.setProperty("soTimeout", "15000"); try (Connection connection = DriverManager.getConnection(URL, properties)) { // Firebird server: "SELECT 1.5 FROM RDB$DATABASE -> CONSTANT NUMERIC: 1.5"; proxy: "SELECT 1.5 FROM RDB$DATABASE -> CONSTANT NUMERIC: 1" execute(connection, "SELECT 1.5 FROM RDB$DATABASE"); // Firebird server: "SELECT 1.5e2 FROM RDB$DATABASE -> CONSTANT DOUBLE PRECISION: 150.0"; // proxy: "SELECT 1.5e2 FROM RDB$DATABASE -> CONSTANT NUMERIC: 4639481672377565184" execute(connection, "SELECT 1.5e2 FROM RDB$DATABASE"); // Firebird server: "SELECT FALSE FROM RDB$DATABASE -> CONSTANT BOOLEAN: false"; proxy: "SELECT FALSE FROM RDB$DATABASE -> CONSTANT NULL: null" execute(connection, "SELECT FALSE FROM RDB$DATABASE"); // Firebird server: "SELECT X'4142' FROM RDB$DATABASE -> CONSTANT CHAR: [B@...", the value is a byte array with the bytes 0x41 0x42; // proxy: "SELECT X'4142' FROM RDB$DATABASE -> update count 0" execute(connection, "SELECT X'4142' FROM RDB$DATABASE"); // Firebird server: "SELECT TRUE FROM RDB$DATABASE -> CONSTANT BOOLEAN: true"; // proxy: "SELECT TRUE FROM RDB$DATABASE -> error: Error reading data from the connection. ..." execute(connection, "SELECT TRUE FROM RDB$DATABASE"); // Firebird server: "DELETE FROM T_PLAIN WHERE ID BETWEEN 9101 AND 9109 -> update count 0"; // proxy: "DELETE FROM T_PLAIN WHERE ID BETWEEN 9101 AND 9109 -> error: The connection is closed ..." execute(connection, "DELETE FROM T_PLAIN WHERE ID BETWEEN 9101 AND 9109"); } } // 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]
