makssent opened a new issue, #40258: URL: https://github.com/apache/shardingsphere/issues/40258
### 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 execute statements with these built-in functions and predicates as the Firebird server does. ### Actual behavior The proxy rejects each of these statements with a syntax error. The same statements were executed directly on the Firebird server and through the proxy: | Statement | Firebird server | Proxy | | --- | --- | --- | | `SELECT EXTRACT(YEAR FROM TS) FROM T_TS WHERE ID = -1` | Success, no rows | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'EXTRACT(YEAR'" | | `SELECT DATEADD(1 DAY TO TS) FROM T_TS WHERE ID = -1` | Success, no rows | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DATEADD(1DAY'" | | `SELECT DATEADD(DAY, 1, TS) FROM T_TS WHERE ID = -1` | Success, no rows | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DATEADD(DAY'" | | `SELECT DATEDIFF(DAY, TS, TS) FROM T_TS WHERE ID = -1` | Success, no rows | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DATEDIFF(DAY'" | | `SELECT DATEDIFF(DAY FROM TS TO TS) FROM T_TS WHERE ID = -1` | Success, no rows | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DATEDIFF(DAY'" | | `SELECT LEFT(NAME, 2) FROM T_PLAIN WHERE ID = -1` | Success, no rows | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'LEFT'" | | `SELECT RIGHT(NAME, 2) FROM T_PLAIN WHERE ID = -1` | Success, no rows | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'RIGHT'" | | `SELECT POSITION('a', NAME) FROM T_PLAIN WHERE ID = -1` | Success, no rows | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'POSITION('a','" | | `SELECT OVERLAY(NAME PLACING 'x' FROM 1) FROM T_PLAIN WHERE ID = -1` | Success, no rows | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'OVERLAY(NAMEPLACING'" | | `SELECT LIST(DISTINCT NAME) FROM T_PLAIN WHERE ID = -1` | One row: NULL | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'LIST(DISTINCT'" | | `SELECT COUNT(*) FILTER (WHERE ID > 0) FROM T_PLAIN WHERE ID = -1` | One row: 0 | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: SELECT COUNT(*) FILTER (WHERE ID > 0) FROM T_PLAIN WHERE ID = -1, null" | | `SELECT 1 FROM RDB$DATABASE WHERE NEXT VALUE FOR S_AUDIT_SEQ > 0` | One row: 1 | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'NEXT'" | | `SELECT ID FROM T_PLAIN WHERE NAME CONTAINING 'x' AND ID = -1` | Success, no rows | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'NAMECONTAINING'" | | `SELECT ID FROM T_PLAIN WHERE SINGULAR (SELECT 1 FROM RDB$DATABASE) AND ID = -1` | Success, no rows | Error [`isc_dsql_error`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/database/exception/dialect/firebird/src/main/java/org/apache/shardingsphere/database/exception/firebird/mapper/FirebirdDialectExceptionMapper.java#L95-L97) "Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'SINGULAR(SELECT'" | So through the proxy an application cannot use these built-in functions and predicates of Firebird. ### Reason analysis The Firebird grammar of ShardingSphere does not have these forms: - [`extractFunction`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L333-L335) expects an [`identifier`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L65-L67) for the date part, but the ShardingSphere lexer reads [`YEAR`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/Keyword.g4#L390-L392), [`DAY`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/Keyword.g4#L406-L408) and the other [date parts](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L8741-L8757) of [`EXTRACT`](https://github.com/FirebirdSQL/firebird/blo b/v5.0.4/src/dsql/parse.y#L8198-L8200) as keywords; - [`specialFunction`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L306-L315) and [`regularFunctionName`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L355-L361) have no [`DATEADD`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L8307-L8318) and [`DATEDIFF`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L8319-L8330) with a date part, no [`OVERLAY`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L8359-L8370), no [`NEXT VALUE FOR`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L8726-L8732), and no [`LEFT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L8265) and [`RIGHT`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/ parse.y#L8284), which the ShardingSphere lexer reads as [keywords](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/Keyword.g4#L198-L204); - [`positionFunction`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L325-L327) accepts only `POSITION(a IN b)`, not the form with a comma ([`POSITION`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L8376-L8377)); - [`aggregationFunctionName`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L298-L300) has no [`LIST`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7996-L7999), and [`aggregationFunction`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L294-L296) has no [`FILTER`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7946) clause; - [`predicate`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L246-L255) has no [`CONTAINING`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7111-L7113) and [`SINGULAR`](https://github.com/FirebirdSQL/firebird/blob/v5.0.4/src/dsql/parse.y#L7171-L7174). The open PR #39759 adds `LEFT` and `RIGHT` to [`regularFunctionName`](https://github.com/apache/shardingsphere/blob/8a10a2f4822841b45561796ad02c4709ae69e2e2/parser/sql/engine/dialect/firebird/src/main/antlr4/imports/firebird/BaseRule.g4#L355-L361). It does not cover the other functions and predicates in the table. ### Steps to reproduce #### Environment - Firebird server 5.0. - Client: Jaybird 6.0.7. #### Steps 1. Create the objects on the Firebird server: ```sql CREATE TABLE T_PLAIN (ID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(20)); CREATE TABLE T_TS (ID INTEGER NOT NULL PRIMARY KEY, TS TIMESTAMP); CREATE SEQUENCE S_AUDIT_SEQ; ``` 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 BuiltinExpressionSyntax { // 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 EXTRACT(YEAR FROM TS) FROM T_TS WHERE ID = -1 -> EXTRACT SMALLINT: no rows"; // proxy: "SELECT EXTRACT(YEAR FROM TS) FROM T_TS WHERE ID = -1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'EXTRACT(YEAR' ..." execute(connection, "SELECT EXTRACT(YEAR FROM TS) FROM T_TS WHERE ID = -1"); // Firebird server: "SELECT DATEADD(1 DAY TO TS) FROM T_TS WHERE ID = -1 -> DATEADD TIMESTAMP: no rows"; // proxy: "SELECT DATEADD(1 DAY TO TS) FROM T_TS WHERE ID = -1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DATEADD(1DAY' ..." execute(connection, "SELECT DATEADD(1 DAY TO TS) FROM T_TS WHERE ID = -1"); // Firebird server: "SELECT DATEADD(DAY, 1, TS) FROM T_TS WHERE ID = -1 -> DATEADD TIMESTAMP: no rows"; // proxy: "SELECT DATEADD(DAY, 1, TS) FROM T_TS WHERE ID = -1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DATEADD(DAY' ..." execute(connection, "SELECT DATEADD(DAY, 1, TS) FROM T_TS WHERE ID = -1"); // Firebird server: "SELECT DATEDIFF(DAY, TS, TS) FROM T_TS WHERE ID = -1 -> DATEDIFF BIGINT: no rows"; // proxy: "SELECT DATEDIFF(DAY, TS, TS) FROM T_TS WHERE ID = -1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DATEDIFF(DAY' ..." execute(connection, "SELECT DATEDIFF(DAY, TS, TS) FROM T_TS WHERE ID = -1"); // Firebird server: "SELECT DATEDIFF(DAY FROM TS TO TS) FROM T_TS WHERE ID = -1 -> DATEDIFF BIGINT: no rows"; // proxy: "SELECT DATEDIFF(DAY FROM TS TO TS) FROM T_TS WHERE ID = -1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'DATEDIFF(DAY' ..." execute(connection, "SELECT DATEDIFF(DAY FROM TS TO TS) FROM T_TS WHERE ID = -1"); // Firebird server: "SELECT LEFT(NAME, 2) FROM T_PLAIN WHERE ID = -1 -> LEFT VARCHAR(20): no rows"; // proxy: "SELECT LEFT(NAME, 2) FROM T_PLAIN WHERE ID = -1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'LEFT' ..." execute(connection, "SELECT LEFT(NAME, 2) FROM T_PLAIN WHERE ID = -1"); // Firebird server: "SELECT RIGHT(NAME, 2) FROM T_PLAIN WHERE ID = -1 -> RIGHT VARCHAR(20): no rows"; // proxy: "SELECT RIGHT(NAME, 2) FROM T_PLAIN WHERE ID = -1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'RIGHT' ..." execute(connection, "SELECT RIGHT(NAME, 2) FROM T_PLAIN WHERE ID = -1"); // Firebird server: "SELECT POSITION('a', NAME) FROM T_PLAIN WHERE ID = -1 -> POSITION INTEGER: no rows"; // proxy: "SELECT POSITION('a', NAME) FROM T_PLAIN WHERE ID = -1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'POSITION('a',' ..." execute(connection, "SELECT POSITION('a', NAME) FROM T_PLAIN WHERE ID = -1"); // Firebird server: "SELECT OVERLAY(NAME PLACING 'x' FROM 1) FROM T_PLAIN WHERE ID = -1 -> OVERLAY VARCHAR(21): no rows"; // proxy: "SELECT OVERLAY(NAME PLACING 'x' FROM 1) FROM T_PLAIN WHERE ID = -1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'OVERLAY(NAMEPLACING' ..." execute(connection, "SELECT OVERLAY(NAME PLACING 'x' FROM 1) FROM T_PLAIN WHERE ID = -1"); // Firebird server: "SELECT LIST(DISTINCT NAME) FROM T_PLAIN WHERE ID = -1 -> LIST BLOB SUB_TYPE TEXT: null"; // proxy: "SELECT LIST(DISTINCT NAME) FROM T_PLAIN WHERE ID = -1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'LIST(DISTINCT' ..." execute(connection, "SELECT LIST(DISTINCT NAME) FROM T_PLAIN WHERE ID = -1"); // Firebird server: "SELECT COUNT(*) FILTER (WHERE ID > 0) FROM T_PLAIN WHERE ID = -1 -> COUNT BIGINT: 0"; // proxy: "SELECT COUNT(*) FILTER (WHERE ID > 0) FROM T_PLAIN WHERE ID = -1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: SELECT COUNT(*) FILTER (WHERE ID > 0) FROM T_PLAIN WHERE ID = -1, null ..." execute(connection, "SELECT COUNT(*) FILTER (WHERE ID > 0) FROM T_PLAIN WHERE ID = -1"); // Firebird server: "SELECT 1 FROM RDB$DATABASE WHERE NEXT VALUE FOR S_AUDIT_SEQ > 0 -> CONSTANT INTEGER: 1"; // proxy: "SELECT 1 FROM RDB$DATABASE WHERE NEXT VALUE FOR S_AUDIT_SEQ > 0 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'NEXT' ..." execute(connection, "SELECT 1 FROM RDB$DATABASE WHERE NEXT VALUE FOR S_AUDIT_SEQ > 0"); // Firebird server: "SELECT ID FROM T_PLAIN WHERE NAME CONTAINING 'x' AND ID = -1 -> ID INTEGER: no rows"; // proxy: "SELECT ID FROM T_PLAIN WHERE NAME CONTAINING 'x' AND ID = -1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'NAMECONTAINING' ..." execute(connection, "SELECT ID FROM T_PLAIN WHERE NAME CONTAINING 'x' AND ID = -1"); // Firebird server: "SELECT ID FROM T_PLAIN WHERE SINGULAR (SELECT 1 FROM RDB$DATABASE) AND ID = -1 -> ID INTEGER: no rows"; // proxy: "SELECT ID FROM T_PLAIN WHERE SINGULAR (SELECT 1 FROM RDB$DATABASE) AND ID = -1 -> error: Dynamic SQL Error; You have an error in your SQL syntax: no viable alternative at input 'SINGULAR(SELECT' ..." execute(connection, "SELECT ID FROM T_PLAIN WHERE SINGULAR (SELECT 1 FROM RDB$DATABASE) AND 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]
