oracle分页索引失效

[AH2000141]

• [ ] 我已在 issues 搜索类似问题，并且不存在相同的问题.

异常模板

使用环境

springboot2.4.11

• PageHelper 版本: 5.3.0
• 数据库类型和版本:oracle 11g
• JDBC_URL: jdbc:oracle:thin:@xxx

SQL 解析错误

分页参数

PageHelper.startPage(1, 10);
xxMapper.select(model);

原 SQL

SELECT *
FROM (
SELECT TMP_PAGE.*, ROWNUM as PAGEHELPER_ROW_ID
FROM (
/* com.asus.ucs.elm.inventory.repository.mapper.SoReceiveCustomDOMapper.getSoReceiveList */
SELECT ip.profile_id, ip.cust_code, ip.region_id, ip.ticket_seq, ip.local_site_id
, ip.local_profile_id, ip.local_order_type, ip.local_order_code, ip.local_src_from_type, ip.local_status
, ip.local_domain, ip.local_dept, ip.local_op_company, ip.local_fm_warehouse, ip.local_fm_location
, ip.local_fm_lot, ip.local_to_warehouse, ip.local_to_location, ip.local_to_lot, ip.detail_src_flag
, ip.active, ip.local_memo, ip.reason_code, ip.tiptop_id, ip.order_status
, ip.local_fm_profile_id, ip.create_date, ip.create_by, ip.modify_date, ip.modify_by
, ip.modify_memo, cp1.name AS profile_name, cp2.name AS local_profile_name, cp3.name AS local_fm_profile_name
FROM cc.wm_od_inv_proc ip, cc.pf_cpy_profile cp1, cc.pf_cpy_profile cp2, cc.pf_cpy_profile cp3
WHERE ip.profile_id = cp1.id(+)
AND ip.local_profile_id = cp2.id(+)
AND ip.local_fm_profile_id = cp3.id(+)
AND ip.active = 'Y'
) TMP_PAGE
)
WHERE PAGEHELPER_ROW_ID > 0
and PAGEHELPER_ROW_ID<=10

期望的结果：

SELECT *
FROM (
SELECT TMP_PAGE.*, ROWNUM as PAGEHELPER_ROW_ID
FROM (
/* com.asus.ucs.elm.inventory.repository.mapper.SoReceiveCustomDOMapper.getSoReceiveList */
SELECT ip.profile_id, ip.cust_code, ip.region_id, ip.ticket_seq, ip.local_site_id
, ip.local_profile_id, ip.local_order_type, ip.local_order_code, ip.local_src_from_type, ip.local_status
, ip.local_domain, ip.local_dept, ip.local_op_company, ip.local_fm_warehouse, ip.local_fm_location
, ip.local_fm_lot, ip.local_to_warehouse, ip.local_to_location, ip.local_to_lot, ip.detail_src_flag
, ip.active, ip.local_memo, ip.reason_code, ip.tiptop_id, ip.order_status
, ip.local_fm_profile_id, ip.create_date, ip.create_by, ip.modify_date, ip.modify_by
, ip.modify_memo, cp1.name AS profile_name, cp2.name AS local_profile_name, cp3.name AS local_fm_profile_name
FROM cc.wm_od_inv_proc ip, cc.pf_cpy_profile cp1, cc.pf_cpy_profile cp2, cc.pf_cpy_profile cp3
WHERE ip.profile_id = cp1.id(+)
AND ip.local_profile_id = cp2.id(+)
AND ip.local_fm_profile_id = cp3.id(+)
AND ip.active = 'Y'
) TMP_PAGE
where ROWNUM <= 10
)
WHERE PAGEHELPER_ROW_ID > 5

完整异常信息

异常信息放在这里

其他类型的错误

功能建议

详细说明，尽可能提供(伪)代码示例。

[abel533]

配置 dialectAlias=oracle=com.github.pagehelper.dialect.helper.Oracle9iDialect。

[zttdog]

非常感谢，解决了我的大问题惹