GGQJavakf opened a new issue, #28155:
URL: https://github.com/apache/shardingsphere/issues/28155
## 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
--
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]
