no viable alternative at input 'WHERE'

[GGQJavakf]

Bug Report

Which version of ShardingSphere did you use?

V 5.3.2

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

ShardingSphere-JDBC

Expected behavior

1. dameng db and mybatis plus page query
2. sql is SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT T1.ID AS adminInfoId, T3.ROLE_NAME AS roleName, T3.ROLE_CODE AS roleCode, T1.USERNAME AS username, T1.EMAIL AS email, T2.ISSUER_CN AS issuerCn, T1.CREATE_AT AS createAt, T1.UPDATE_AT AS updateAt, T2.VALIDATE_FROM AS validateFrom, T2.VALIDATE_TO AS validateTo, T2.SIGN_CERT_SN AS signCertSn, T2.ENC_CERT_SN AS encCertSn, T1.PROVINCE AS province, T1.CITY AS city FROM T_ADMIN_INFO T1 LEFT JOIN T_ADMIN_CERT T2 ON T1.ID = T2.ADMIN_INFO_ID LEFT JOIN T_ROLE T3 ON T1.ROLE_CODE = T3.ROLE_CODE WHERE T1.IS_DELETE = 0 AND T3.ROLE_CODE IN ( ?, ?, ? ) ORDER BY T1.CREATE_AT DESC ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ?
3. Execute sql statements normally and output results

Actual behavior

Report an error: Caused by: org.apache.shardingsphere.sql.parser.exception.SQLParsingException: You have an error in your SQL syntax: SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT T1.ID AS adminInfoId, T3.ROLE_NAME AS roleName, T3.ROLE_CODE AS roleCode, T1.USERNAME AS username, T1.EMAIL AS email, T2.ISSUER_CN AS issuerCn, T1.CREATE_AT AS createAt, T1.UPDATE_AT AS updateAt, T2.VALIDATE_FROM AS validateFrom, T2.VALIDATE_TO AS validateTo, T2.SIGN_CERT_SN AS signCertSn, T2.ENC_CERT_SN AS encCertSn, T1.PROVINCE AS province, T1.CITY AS city FROM T_ADMIN_INFO T1 LEFT JOIN T_ADMIN_CERT T2 ON T1.ID = T2.ADMIN_INFO_ID LEFT JOIN T_ROLE T3 ON T1.ROLE_CODE = T3.ROLE_CODE WHERE T1.IS_DELETE = 0 AND T3.ROLE_CODE IN ( ? , ? , ? ) ORDER BY T1.CREATE_AT DESC ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ?, no viable alternative at input 'WHERE' at line 37, position 59, near [@178,1416:1420='WHERE',<89>,37:59] 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:145) at com.github.benmanes.caffeine.cache.BoundedLocalCache.lambda$doComputeIfAbsent$14(BoundedLocalCache.java:2406) at java.util.concurrent.ConcurrentHashMap.compute(ConcurrentHashMap.java:1853) at com.github.benmanes.caffeine.cache.BoundedLocalCache.doComputeIfAbsent(BoundedLocalCache.java:2404) at com.github.benmanes.caffeine.cache.BoundedLocalCache.computeIfAbsent(BoundedLocalCache.java:2387) at com.github.benmanes.caffeine.cache.LocalCache.computeIfAbsent(LocalCache.java:108) at com.github.benmanes.caffeine.cache.LocalLoadingCache.get(LocalLoadingCache.java:56) 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 org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:88) at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:90) ... 115 common frames omitted

Reason analyze (If you can)

mysql uses limit normally, dameng db If rowNUM row_id is used, an error occurs

[zhaojinchao95]

It looks like sql parser doesn't support, are you interested in fix it?

[GGQJavakf]

Upgrading to 5.4 can resolve this issue

[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.

[15620627645]

您好 您是怎么使用shardingJDBC 连接 达梦数据库 方便讲一下吗？

[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.