tuohai666 opened a new issue #6976: URL: https://github.com/apache/shardingsphere/issues/6976
## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview). Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will **close it**. Please answer these questions before submitting your issue. Thanks! ### Which version of ShardingSphere did you use? 5.0.0-RC1-SNAPSHOT ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? Both ### Expected behavior SQL 92 parser test pass ### Actual behavior ``` [INFO] --- maven-surefire-plugin:2.22.0:test (default-test) @ shardingsphere-sql-parser-test --- [INFO] [INFO] ------------------------------------------------------- [INFO] T E S T S [INFO] ------------------------------------------------------- [INFO] Running org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest line 1:7 no viable alternative at input 'CREATEINDEX' line 1:7 no viable alternative at input 'CREATEINDEX' line 1:7 no viable alternative at input 'CREATEINDEX' line 1:7 no viable alternative at input 'CREATEINDEX' line 1:145 no viable alternative at input 'HASH' line 1:145 mismatched input 'HASH' expecting {LINEAR, LIST, RANGE} line 1:7 no viable alternative at input 'CREATEUNIQUE' line 1:7 no viable alternative at input 'CREATEUNIQUE' line 1:7 no viable alternative at input 'CREATEUSER' line 1:7 no viable alternative at input 'CREATEUSER' line 1:7 no viable alternative at input 'CREATEUSER' line 1:7 no viable alternative at input 'CREATEUSER' line 1:0 no viable alternative at input 'TRUNCATE' line 1:0 mismatched input 'TRUNCATE' expecting {SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, SET, COMMIT, ROLLBACK} [ERROR] Tests run: 1899, Failures: 7, Errors: 6, Skipped: 0, Time elapsed: 7.002 s <<< FAILURE! - in org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest [ERROR] assertSupportedSQL[create_composite_index (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.007 s <<< ERROR! org.apache.shardingsphere.sql.parser.exception.SQLParsingException: Unsupported SQL of `CREATE INDEX order_index ON t_order (order_id, user_id, status)` at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:93) [ERROR] assertSupportedSQL[create_index (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< ERROR! org.apache.shardingsphere.sql.parser.exception.SQLParsingException: Unsupported SQL of `CREATE INDEX t_log_index ON t_log (id)` at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:93) [ERROR] assertSupportedSQL[create_table_with_out_of_line_check (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.017 s <<< FAILURE! java.lang.AssertionError: SQL Case ID : create_table_with_out_of_line_check SQL : CREATE TABLE t_order (order_id INT, user_id INT, status VARCHAR(10), column1 VARCHAR(10), column2 VARCHAR(10), column3 VARCHAR(10), CHECK (order_id > 0)) Constraint definitions size assertion error: Expected: is <1> but: was <0> at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94) [ERROR] assertSupportedSQL[create_unique_index (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< ERROR! org.apache.shardingsphere.sql.parser.exception.SQLParsingException: Unsupported SQL of `CREATE UNIQUE INDEX t_log_index ON t_log (id)` at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:93) [ERROR] assertSupportedSQL[create_user_with_password (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< ERROR! org.apache.shardingsphere.sql.parser.exception.SQLParsingException: Unsupported SQL of `CREATE USER user1 IDENTIFIED BY RANDOM password default role role1` at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:93) [ERROR] assertSupportedSQL[create_user_with_password_expire_lock (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< ERROR! org.apache.shardingsphere.sql.parser.exception.SQLParsingException: Unsupported SQL of `CREATE USER user1 IDENTIFIED BY RANDOM password default role role1 PASSWORD EXPIRE ACCOUNT LOCK` at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:93) [ERROR] assertSupportedSQL[insert_on_duplicate_key_update_with_placeholders (Placeholder) -> MySQL](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< FAILURE! java.lang.AssertionError: SQL Case ID : insert_on_duplicate_key_update_with_placeholders SQL : INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE status = ? SQL Params : [1, 1, 'init', 'init'] Parameter markers count assertion error: Expected: is <4> but: was <5> at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94) [ERROR] assertSupportedSQL[select_count_with_sub_with_whitespace (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0 s <<< FAILURE! java.lang.AssertionError: SQL Case ID : select_count_with_sub_with_whitespace SQL : SELECT COUNT(*) AS orders_count FROM t_order WHERE order_id > 1 - 1 Common expression text assertion error: Expected: is "1 - 1" but: was "1-1" at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94) [ERROR] assertSupportedSQL[select_distinct_function (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0 s <<< FAILURE! java.lang.AssertionError: SQL Case ID : select_distinct_function SQL : SELECT DISTINCT(item_id) FROM t_order_item ORDER BY item_id Projection type assertion error: Expected: an instance of org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.expression.ExpectedExpressionProjection but: <org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.column.ExpectedColumnProjection@163c7925> is a org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.column.ExpectedColumnProjection at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94) [ERROR] assertSupportedSQL[truncate_table (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< ERROR! org.apache.shardingsphere.sql.parser.exception.SQLParsingException: Unsupported SQL of `TRUNCATE TABLE t_log` at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:93) [ERROR] assertSupportedSQL[update_with_case_when (Placeholder) -> MySQL](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< FAILURE! java.lang.AssertionError: SQL Case ID : update_with_case_when SQL : update stock_freeze_detail set row_status=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end, update_user=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end, update_time=case WHEN (id=?) THEN ? end where tenant_id = ? SQL Params : [3, 2, 4, 2, 10, 2, 3, 'll', 4, 'll', 10, 'll', 3, '2020-08-10T17:15:25.979+0800', 'jd'] Parameter markers count assertion error: Expected: is <15> but: was <1> at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94) [ERROR] assertSupportedSQL[update_with_where_calculation (Literal) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< FAILURE! java.lang.AssertionError: SQL Case ID : update_with_where_calculation SQL : UPDATE t_order SET status = 1 WHERE order_id = order_id - 2 AND user_id = 3 Common expression text assertion error: Expected: is "order_id - 2" but: was "order_id-2" at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94) [ERROR] assertSupportedSQL[update_with_where_calculation (Placeholder) -> SQL92](org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest) Time elapsed: 0.001 s <<< FAILURE! java.lang.AssertionError: SQL Case ID : update_with_where_calculation SQL : UPDATE t_order SET status = ? WHERE order_id = order_id - ? AND user_id = ? SQL Params : [1, 2, 3] Common expression text assertion error: Expected: is "order_id - ?" but: was "order_id-?" at org.apache.shardingsphere.sql.parser.integrate.engine.SQLParserParameterizedTest.assertSupportedSQL(SQLParserParameterizedTest.java:94) [INFO] Running org.apache.shardingsphere.sql.parser.integrate.engine.UnsupportedSQLParserParameterizedTest [INFO] Tests run: 0, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.006 s - in org.apache.shardingsphere.sql.parser.integrate.engine.UnsupportedSQLParserParameterizedTest [INFO] Running org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.SQLCasesLoaderTest [INFO] Tests run: 6, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0 s - in org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.SQLCasesLoaderTest [INFO] [INFO] Results: [INFO] [ERROR] Failures: [ERROR] SQLParserParameterizedTest.assertSupportedSQL:94 SQL Case ID : create_table_with_out_of_line_check SQL : CREATE TABLE t_order (order_id INT, user_id INT, status VARCHAR(10), column1 VARCHAR(10), column2 VARCHAR(10), column3 VARCHAR(10), CHECK (order_id > 0)) Constraint definitions size assertion error: Expected: is <1> but: was <0> [ERROR] SQLParserParameterizedTest.assertSupportedSQL:94 SQL Case ID : insert_on_duplicate_key_update_with_placeholders SQL : INSERT INTO t_order (order_id, user_id, status) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE status = ? SQL Params : [1, 1, 'init', 'init'] Parameter markers count assertion error: Expected: is <4> but: was <5> [ERROR] SQLParserParameterizedTest.assertSupportedSQL:94 SQL Case ID : select_count_with_sub_with_whitespace SQL : SELECT COUNT(*) AS orders_count FROM t_order WHERE order_id > 1 - 1 Common expression text assertion error: Expected: is "1 - 1" but: was "1-1" [ERROR] SQLParserParameterizedTest.assertSupportedSQL:94 SQL Case ID : select_distinct_function SQL : SELECT DISTINCT(item_id) FROM t_order_item ORDER BY item_id Projection type assertion error: Expected: an instance of org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.expression.ExpectedExpressionProjection but: <org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.column.ExpectedColumnProjection@163c7925> is a org.apache.shardingsphere.sql.parser.integrate.jaxb.cases.domain.segment.impl.projection.impl.column.ExpectedColumnProjection [ERROR] SQLParserParameterizedTest.assertSupportedSQL:94 SQL Case ID : update_with_case_when SQL : update stock_freeze_detail set row_status=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end, update_user=case WHEN (id=?) THEN ? WHEN (id=?) THEN ? WHEN (id=?) THEN ? end, update_time=case WHEN (id=?) THEN ? end where tenant_id = ? SQL Params : [3, 2, 4, 2, 10, 2, 3, 'll', 4, 'll', 10, 'll', 3, '2020-08-10T17:15:25.979+0800', 'jd'] Parameter markers count assertion error: Expected: is <15> but: was <1> [ERROR] SQLParserParameterizedTest.assertSupportedSQL:94 SQL Case ID : update_with_where_calculation SQL : UPDATE t_order SET status = 1 WHERE order_id = order_id - 2 AND user_id = 3 Common expression text assertion error: Expected: is "order_id - 2" but: was "order_id-2" [ERROR] SQLParserParameterizedTest.assertSupportedSQL:94 SQL Case ID : update_with_where_calculation SQL : UPDATE t_order SET status = ? WHERE order_id = order_id - ? AND user_id = ? SQL Params : [1, 2, 3] Common expression text assertion error: Expected: is "order_id - ?" but: was "order_id-?" [ERROR] Errors: [ERROR] SQLParserParameterizedTest.assertSupportedSQL:93 » SQLParsing Unsupported SQL ... [ERROR] SQLParserParameterizedTest.assertSupportedSQL:93 » SQLParsing Unsupported SQL ... [ERROR] SQLParserParameterizedTest.assertSupportedSQL:93 » SQLParsing Unsupported SQL ... [ERROR] SQLParserParameterizedTest.assertSupportedSQL:93 » SQLParsing Unsupported SQL ... [ERROR] SQLParserParameterizedTest.assertSupportedSQL:93 » SQLParsing Unsupported SQL ... [ERROR] SQLParserParameterizedTest.assertSupportedSQL:93 » SQLParsing Unsupported SQL ... [INFO] [ERROR] Tests run: 1905, Failures: 7, Errors: 6, Skipped: 0 ``` ### Reason analyze (If you can) ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. In SQLCasesLoader.java: Modify the method to ``` private static Collection<String> getALlDatabaseTypes() { return Arrays.asList("H2", "MySQL", "PostgreSQL", "Oracle", "SQLServer", "SQL92"); } ``` ### Example codes for reproduce this issue (such as a github link). ---------------------------------------------------------------- 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. For queries about this service, please contact Infrastructure at: [email protected]
