sharding 5.4.1 oracle查询不能同时支持分页与分组
使用环境：
sharding:
org.apache.shardingsphere
shardingsphere-jdbc-core
5.4.1
问题：
有分页无分组时将分页起始位置改了0：
-
Logic SQL: SELECT * FROM (SELECT ROW_.*, ROWNUM rn FROM (SELECT CUST_ID CUST_ID, TRANS_TIME TRANS_TIME, OPER_AMT OPER_AMT$SUM, OPER_CNT OPER_CNT$SUM FROM (SELECT CUST_ID, TO_CHAR(OPER_TIME, ‘YYYY-MM-DD’) AS “TRANS_TIME”, sum(OPER_AMOUNT) AS “OPER_AMT”, count(SERIAL_ID) AS “OPER_CNT” FROM BANK_OPER WHERE OPER_TIME >= ? AND OPER_TIME < ? GROUP BY TO_CHAR(OPER_TIME, ‘YYYY-MM-DD’), CUST_ID) RPT_TRUST_OPER_INFO ORDER BY CUST_ID ASC ) ROW_ WHERE ROWNUM <= 4) WHERE rn > 1 ORDER BY “CUST_ID” ASC
-
Actual SQL: sharding ::: SELECT * FROM (SELECT ROW_.*, ROWNUM rn FROM (SELECT CUST_ID CUST_ID, TRANS_TIME TRANS_TIME, OPER_AMT OPER_AMT$SUM, OPER_CNT OPER_CNT$SUM FROM (SELECT CUST_ID, TO_CHAR(OPER_TIME, ‘YYYY-MM-DD’) AS “TRANS_TIME”, sum(OPER_AMOUNT) AS “OPER_AMT”, count(SERIAL_ID) AS “OPER_CNT” FROM BANK_OPER_202301_0 WHERE OPER_TIME >= ? AND OPER_TIME < ? GROUP BY TO_CHAR(OPER_TIME, ‘YYYY-MM-DD’), CUST_ID) RPT_TRUST_OPER_INFO ORDER BY CUST_ID ASC ) ROW_ WHERE ROWNUM <= 4) WHERE rn > 0 ORDER BY “CUST_ID” ASC ::: [2023-01-01T00:00:00.000+0800, 2023-01-30T23:13:55.000+0800]
分页与分组并存时未修改分页起始位置：
-
Logic SQL: SELECT “RISK_TIME” , “OPER_PROV” , “OPER_CITY” , “BIZ_CODE” , SUM(“OPER_AMT$SUM”) AS “OPER_AMT$SUM” , SUM(“OPER_CNT$SUM”) AS “OPER_CNT$SUM” FROM (SELECT ROW_.*, ROWNUM rn FROM (SELECT TO_CHAR(OPER_TIME, ‘YYYY-MM-DD HH24’) RISK_TIME, OPER_PROV OPER_PROV, OPER_CITY OPER_CITY, BIZ_CODE BIZ_CODE, SUM(OPER_AMOUNT) OPER_AMT$SUM, COUNT(1) OPER_CNT$SUM FROM BANK_OPER REPORT_MGR_RISK_SURVEY WHERE OPER_TIME >= ? AND OPER_TIME < ? GROUP BY TO_CHAR(OPER_TIME, ‘YYYY-MM-DD HH24’), OPER_PROV, OPER_CITY, BIZ_CODE ORDER BY BIZ_CODE ) ROW_ WHERE ROWNUM <= 4) WHERE rn > 1 GROUP BY “RISK_TIME”,“OPER_PROV”,“OPER_CITY”,“BIZ_CODE” ORDER BY “BIZ_CODE” ASC
-
Actual SQL: sharding ::: SELECT “RISK_TIME” , “OPER_PROV” , “OPER_CITY” , “BIZ_CODE” , SUM(“OPER_AMT$SUM”) AS “OPER_AMT$SUM” , SUM(“OPER_CNT$SUM”) AS “OPER_CNT$SUM” FROM (SELECT ROW_.*, ROWNUM rn FROM (SELECT TO_CHAR(OPER_TIME, ‘YYYY-MM-DD HH24’) RISK_TIME, OPER_PROV OPER_PROV, OPER_CITY OPER_CITY, BIZ_CODE BIZ_CODE, SUM(OPER_AMOUNT) OPER_AMT$SUM, COUNT(1) OPER_CNT$SUM FROM BANK_OPER_202301_0 REPORT_MGR_RISK_SURVEY WHERE OPER_TIME >= ? AND OPER_TIME < ? GROUP BY TO_CHAR(OPER_TIME, ‘YYYY-MM-DD HH24’), OPER_PROV, OPER_CITY, BIZ_CODE ORDER BY BIZ_CODE ) ROW_ WHERE ROWNUM <= 4) WHERE rn > 1 GROUP BY “RISK_TIME”,“OPER_PROV”,“OPER_CITY”,“BIZ_CODE” ORDER BY “BIZ_CODE” ASC ::: [2023-01-01T00:00:00.000+0800, 2023-01-30T23:13:55.000+0800]
导致返回的数据条数与预期不一致