SQLParsingException: no viable alternative at input

[1a23-pangqiu]

Bug Report

Which version of ShardingSphere did you use?

5.3.2

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Expected behavior

sql correct

Actual behavior

sql error

Reason analyze (If you can)

SQLParsingException

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Caused by: org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax: select count(0) from (select test_plan_test_case.id as id, test_case.id as caseId, test_case.name, test_case.priority,
        test_case.type,test_case.test_id as testId,test_case.node_id, test_case.tags, test_case.maintainer,
        test_case.custom_fields,u.name as maintainerName,us.name as executorName,
        test_case.node_path, test_case.method, if(project.custom_num = 0, cast(test_case.num as char),
        test_case.custom_num) as customNum, test_plan_test_case.executor, test_plan_test_case.status,
        test_plan_test_case.actual_result,test_plan_test_case.execute_time as executeTime,
        test_plan_test_case.update_time, test_plan_test_case.`order`, test_plan_test_case.create_time,test_case_node.name as model, project.name as
        projectName,
        test_plan_test_case.plan_id as planId
        from test_plan_test_case
        inner join test_case on test_plan_test_case.case_id = test_case.id
        left join test_case_node on test_case_node.id = test_case.node_id
        inner join project on project.id = test_case.project_id
        left join user u on u.id = test_case.maintainer
        left join user us on us.id = test_plan_test_case.executor
         WHERE (test_case.status != 'Trash' or test_case.status is null)
                and test_plan_test_case.plan_id = ? 
            order by
                        test_plan_test_case.order desc) tmp_count, no viable alternative at input 'selectcount(0)from(selecttest_plan_test_case.idasid,test_case.idascaseId,test_case.name,test_case.priority,test_case.type,test_case.test_idastestId,test_case.node_id,test_case.tags,test_case.maintainer,test_case.custom_fields,u.nameasmaintainerName,us.nameasexecutorName,test_case.node_path,test_case.method,if(project.custom_num=0,cast(test_case.numaschar),test_case.custom_num)ascustomNum,test_plan_test_case.executor,test_plan_test_case.status,test_plan_test_case.actual_result,test_plan_test_case.execute_timeasexecuteTime,test_plan_test_case.update_time,test_plan_test_case.`order`,test_plan_test_case.create_time,test_case_node.nameasmodel,project.nameasprojectName,test_plan_test_case.plan_idasplanIdfromtest_plan_test_caseinnerjointest_caseontest_plan_test_case.case_id=test_case.idleftjointest_case_nodeontest_case_node.id=test_case.node_idinnerjoinprojectonproject.id=test_case.project_idleftjoinuseruonu.id=test_case.maintainerleftjoinuserusonus.id=test_plan_test_case.executorWHERE(test_case.status!='Trash'ortest_case.statusisnull)andtest_plan_test_case.plan_id=?orderbytest_plan_test_case.order' at line 31, position 44, near [@228,1529:1533='order',<486>,31:44]
    at org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.twoPhaseParse(SQLParserExecutor.java:68)
    at org.apache.shardingsphere.sql.parser.core.database.parser.SQLParserExecutor.parse(SQLParserExecutor.java:47)
    at org.apache.shardingsphere.sql.parser.api.SQLParserEngine.parse(SQLParserEngine.java:47)
    at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserExecutor.parse(SQLStatementParserExecutor.java:48)
    at org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:41)
    at org.apache.shardingsphere.infra.parser.cache.SQLStatementCacheLoader.load(SQLStatementCacheLoader.java:30)
    at com.github.benmanes.caffeine.cache.LocalLoadingCache.lambda$newMappingFunction$2(LocalLoadingCache.java:141)
    at com.github.benmanes.caffeine.cache.BoundedLocalCache.lambda$doComputeIfAbsent$14(BoundedLocalCache.java:2405)
    at java.util.concurrent.ConcurrentHashMap.compute(ConcurrentHashMap.java:1853)
    at com.github.benmanes.caffeine.cache.BoundedLocalCache.doComputeIfAbsent(BoundedLocalCache.java:2403)
    at com.github.benmanes.caffeine.cache.BoundedLocalCache.computeIfAbsent(BoundedLocalCache.java:2386)
    at com.github.benmanes.caffeine.cache.LocalCache.computeIfAbsent(LocalCache.java:108)
    at com.github.benmanes.caffeine.cache.LocalLoadingCache.get(LocalLoadingCache.java:54)
    at org.apache.shardingsphere.infra.parser.sql.SQLStatementParserEngine.parse(SQLStatementParserEngine.java:47)
    at org.apache.shardingsphere.infra.parser.ShardingSphereSQLParserEngine.parse(ShardingSphereSQLParserEngine.java:58)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:205)
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:171)
    at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:87)
    at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:337)
    at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)
    at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
    ... 170 common frames omitted

Example codes for reproduce this issue (such as a github link).

Mysql5.7 runs fine, but sharding jdbc doesn’t work. Does it have the same name as the sharding jdbc keyword? The project has been running for 2 years, and it’s impossible to change the field

[FlyingZC]

order by test_plan_test_case.order desc @1a23-pangqiu Hi, the order is the keyword of MySQL, this sql can not be executed in mysql too.

[1a23-pangqiu]

order by test_plan_test_case.order desc @1a23-pangqiu Hi, the order is the keyword of MySQL, this sql can not be executed in mysql too.

the order is the keyword of MySQL, this sql can be executed in mysql, but this sql can not be executed in shardingsphere.

[FlyingZC]

I tested that this sql cannot be executed in mysql5.7.

[1a23-pangqiu]

我测试了这个sql在mysql5.7中无法执行。 -- metersphere_prod_and_corp.test_case definition

CREATE TABLE `test_case` (
  `id` varchar(50) NOT NULL COMMENT 'Test case ID',
  `node_id` varchar(50) NOT NULL COMMENT 'Node ID this case belongs to',
  `test_id` varchar(2000) DEFAULT NULL,
  `node_path` varchar(999) NOT NULL COMMENT 'Node path this case belongs to',
  `project_id` varchar(50) NOT NULL COMMENT 'Project ID this test belongs to',
  `name` varchar(255) NOT NULL COMMENT 'Test case name',
  `type` varchar(25) DEFAULT NULL COMMENT 'Test case type',
  `maintainer` varchar(50) DEFAULT NULL COMMENT 'Test case maintainer',
  `priority` varchar(50) DEFAULT NULL COMMENT 'Test case priority',
  `method` varchar(15) DEFAULT NULL COMMENT 'Test case method type',
  `caseabstract` text COMMENT '用例摘要',
  `prerequisite` text COMMENT 'Test case prerequisite condition',
  `remark` text COMMENT 'Test case remark',
  `steps` text COMMENT 'Test case steps (JSON format)',
  `create_time` bigint(13) NOT NULL COMMENT 'Create timestamp',
  `update_time` bigint(13) NOT NULL COMMENT 'Update timestamp',
  `sort` int(11) DEFAULT NULL COMMENT 'Import test case sort',
  `num` int(11) DEFAULT NULL COMMENT 'Manually controlled growth identifier',
  `other_test_name` varchar(200) DEFAULT NULL,
  `review_status` varchar(25) DEFAULT NULL,
  `tags` varchar(1000) DEFAULT NULL,
  `demand_id` varchar(120) DEFAULT NULL,
  `demand_name` varchar(999) DEFAULT NULL,
  `follow_people` varchar(100) DEFAULT NULL,
  `status` varchar(25) DEFAULT NULL,
  `step_description` text,
  `expected_result` text,
  `custom_fields` text COMMENT 'CustomField',
  `step_model` varchar(10) DEFAULT NULL COMMENT 'Test case step model',
  `custom_num` varchar(64) DEFAULT NULL COMMENT 'custom num',
  `create_user` varchar(100) DEFAULT NULL,
  `original_status` varchar(50) DEFAULT NULL,
  `delete_time` bigint(13) DEFAULT NULL COMMENT 'Delete timestamp',
  `delete_user_id` varchar(64) DEFAULT NULL COMMENT 'Delete user id',
  `order` bigint(20) NOT NULL COMMENT '自定义排序，间隔5000',
  `case_public` tinyint(1) DEFAULT NULL COMMENT '是否是公共用例',
  `version_id` varchar(50) DEFAULT NULL COMMENT '版本ID',
  `ref_id` varchar(50) DEFAULT NULL COMMENT '指向初始版本ID',
  `latest` tinyint(1) DEFAULT '0' COMMENT '是否为最新版本 0:否，1:是',
  `automation` tinyint(1) DEFAULT NULL COMMENT '是否自动化 0:否, 1:是',
  PRIMARY KEY (`id`),
  KEY `test_case_version_id_index` (`version_id`),
  KEY `test_case_ref_id_index` (`ref_id`),
  KEY `test_case_node_id_IDX` (`node_id`) USING HASH,
  KEY `test_case_project_id_IDX` (`project_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

I tested that this sql can be executed in Mysql5.7.34

[FlyingZC]

Can you try to change order to order

[1a23-pangqiu]

Can you try to change order to order

Because the order is dynamically passed in as a String type, no matter whether the passed value is "order" or "'order'", this sql can not be executed in shardingsphere

[FlyingZC]

I think this is not a problem with ShardingSphere, can you execute select * from test order by order; on mysql successfully?

[1a23-pangqiu]

I think this is not a problem with ShardingSphere, can you execute select * from test order by order; on mysql successfully?

I can't execute select * from test order by order; on mysql successfully, but I can execute select * from test order by test.order; on mysql 5.7.34 successfully, the following is a successful screenshot. Unfortunately, some join table sql does not work in shardingsphere, so obviously this is a bug.I think it may not be the keyword conflict of mysql, but the keyword conflict of shardingsphere.

single table sql

join table sql

[FlyingZC]

Ok, thanks, it's a bug.

[1a23-pangqiu]

Ok, thanks, it's a bug.

Another unrelated question, will 5.4.0 read-write separation support multiple masters?

[FlyingZC]

@1a23-pangqiu will not support in 5.4.0.

[StackRui]

:)

[wgy8283335]

Will this issue be completed before June 15 which is the final date of version 5.4.0?

[boyjoy1127]

Will this issue be completed before June 15 which is the final date of version 5.4.0?

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[1a23-pangqiu]

@1a23-pangqiu will not support in 5.4.0.

This bug still exists in version 5.4.0，when will it be fixed？

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.