makssent opened a new issue, #40256: URL: https://github.com/apache/shardingsphere/issues/40256
### 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 execute statements in the SQL dialect of the client ([`sqlDialect`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jaybird/props/PropertyNames.java#L69) in Jaybird, sent in [`op_prepare_statement`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L5262-L5264)) as the Firebird server [does](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/remote/server/server.cpp#L4963-L4966). ### Actual behavior The proxy executes the statements of a dialect 1 client as dialect 3 statements, so statements that the Firebird server executes fail. The same statements were executed directly on the Firebird server and through the proxy, each on its own connection with [`sqlDialect`](https://github.com/FirebirdSQL/jaybird/blob/v6.0.7/src/main/org/firebirdsql/jaybird/props/PropertyNames.java#L69)`=1`, and the rows were read directly from the server: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SELECT "abc" FROM RDB$DATABASE` | Success, the column CONSTANT of type CHAR with the value abc | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown column 'abc' in 'field list'." | | `SELECT CAST('2026-10-15 10:11:12' AS DATE) FROM RDB$DATABASE` | Success, the column CAST of type [TIMESTAMP](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4811-L4818) with the value 2026-10-15 10:11:12 | Error [`isc_convert_error`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/common/cvt.cpp#L2282) "conversion error from string "2026-10-15 10:11:12"" | | `INSERT INTO T_PLAIN (ID, NAME) VALUES (7302, "after")` | Success, the row is saved | Error [`isc_random`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/include/firebird/impl/msg/jrd.h#L63) "Unknown column 'after' in 'unknown clause'.", the row is lost | So an application that works in SQL dialect 1 cannot use string constants in double quotes and the dialect 1 [`DATE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4806-L4827) type through the proxy, although the Firebird server executes these statements. ### Reason analysis - [`FirebirdPrepareStatementPacket`](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/FirebirdPrepareStatementPacket.java#L58) reads the dialect of the client into [`sqlDialect`](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/FirebirdPrepareStatementPacket.java#L42), but nothing in the Firebird protocol and frontend modules uses it. - In dialect 1 the Firebird server reads a string in double quotes as a string constant ([lexer](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/Parser.cpp#L519-L577)). The Firebird lexer of ShardingSphere reads it only as an identifier ([`IDENTIFIER_`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/Literals.g4#L22-L25)), whatever the dialect of the client. So [`ColumnSegmentBinder#bind`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/ColumnSegmentBinder.java#L111-L112) looks for the columns abc and after and throws [`ColumnNotFoundException`](https://github.com/apache/shardingsphere/blob/01446d4fad48450e7fc3eb3b3631b25d6bf411df/infra/common/src/main/java/org/apache/shardingsphere/infra/exception/kernel/metadata/Column NotFoundException.java#L31). - The proxy executes the statement on its backend connection, which has the default dialect 3 of Jaybird ([`DEFAULT_DIALECT`](https://github.com/FirebirdSQL/jaybird/blob/v5.0.13/src/main/org/firebirdsql/jaybird/props/PropertyConstants.java#L46)). In dialect 3 [`DATE`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L4821-L4825) is a date without time, and a date [cannot have](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/common/cvt.cpp#L908-L913) a time part, so the Firebird server rejects the cast. ### 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 All cases on Jaybird 6.0.7, one method per case. The comment above each method 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.util.Properties; public class ClientDialectOne { // 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 read the rows 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 Exception { executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 7301 AND 7309"); doubleQuotedString(); castAsDate(); insertDoubleQuotedString(); executeOnServer("DELETE FROM T_PLAIN WHERE ID BETWEEN 7301 AND 7309"); } // Firebird server: "CONSTANT (CHAR): abc"; proxy: "Unknown column 'abc' in 'field list'." static void doubleQuotedString() { printQuery("SELECT \"abc\" FROM RDB$DATABASE"); } // Firebird server: "CAST (TIMESTAMP): 2026-10-15 10:11:12"; proxy: "conversion error from string "2026-10-15 10:11:12"" static void castAsDate() { printQuery("SELECT CAST('2026-10-15 10:11:12' AS DATE) FROM RDB$DATABASE"); } // Firebird server: "INSERT: 1 row", "ID = 7302: the row is saved"; // proxy: "Unknown column 'after' in 'unknown clause'.", "ID = 7302: the row is lost" static void insertDoubleQuotedString() throws SQLException { try (Connection connection = connect(); Statement statement = connection.createStatement()) { System.out.println("INSERT: " + statement.executeUpdate("INSERT INTO T_PLAIN (ID, NAME) VALUES (7302, \"after\")") + " row"); } catch (final SQLException ex) { System.out.println(ex.getMessage()); } printRow(7302); } // Prints the label, the type and the value of the first column static void printQuery(final String sql) { try (Connection connection = connect(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql)) { resultSet.next(); ResultSetMetaData metaData = resultSet.getMetaData(); System.out.println(metaData.getColumnLabel(1) + " (" + metaData.getColumnTypeName(1) + "): " + resultSet.getString(1)); } catch (final SQLException ex) { System.out.println(ex.getMessage()); } } // A new connection with SQL dialect 1, auto-commit on static Connection connect() throws SQLException { Properties properties = new Properties(); properties.setProperty("user", USER); properties.setProperty("password", PASSWORD); // Jaybird sends the dialect in op_prepare_statement properties.setProperty("sqlDialect", "1"); return DriverManager.getConnection(URL, properties); } // Reads the row directly on the Firebird server static void printRow(final int id) throws SQLException { try (Connection connection = DriverManager.getConnection(SERVER_URL, SERVER_USER, SERVER_PASSWORD); ResultSet resultSet = connection.createStatement().executeQuery("SELECT NAME FROM T_PLAIN WHERE ID = " + id)) { System.out.println("ID = " + id + ": " + (resultSet.next() ? "the row is saved" : "the row is lost")); } } 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]
