blaze-persistence
blaze-persistence copied to clipboard
Blazebit
Oracle CTE pagination uses ROWNUM instead of ROW_NUMBER() causing invalid column index SQLException
[Environment]
- Spring Boot: 3.1.12
- Hibernate ORM Core: 6.2.25.Final
- Hibernate Envers: 6.2.13.Final
- Blaze Persistence: 1.6.9 → 1.6.17 (upgraded to try to resolve the issue, but no difference)
- QueryDSL: 5.0.0
- JDK: 17
- Database: Oracle 19c
[Problem Summary] When using CTE (WITH RECURSIVE), pagination uses ROWNUM instead of ROW_NUMBER(), and the number of parameter bindings differs, causing "invalid column index" SQLException.
[Code 1 - Working Fine (without CTE)]
BlazeJPAQuery<Group> q = new BlazeJPAQuery<>(entityManager, criteriaBuilderFactory);
q.select(group).from(group)
.orderBy(group.id.asc())
.offset(2)
.limit(3L);
List<Group> groupList = q.fetch();
[Generated Query 1 - Working]
select * from (
select g1_0.*,
row_number() over(order by g1_0.group_id) rn
from tb_group g1_0
) r_0_
where r_0_.rn<=?+?
and r_0_.rn>?
order by r_0_.rn
As shown in Code 1, simple queries are parsed using window functions for pagination. The issue I'm experiencing is related to CTEs.
[Code 2 - Problem Occurs (with CTE)]
BlazeJPAQuery<GroupVO> query = new BlazeJPAQuery<>(entityManager, criteriaBuilderFactory);
query.withRecursive(groupCTE, recursive query generation method)
.select(Projections.constructor(ResultVO.class, ...))
.from(group)
.join(groupCTE).on(group.id.eq(groupCTE.id))
.groupBy(group.id)
.orderBy(group.id.asc())
.offset(2)
.limit(3L);
[Generated Query 2 - Problem]
select * from (
with GroupCTE(...)
select ...
from tb_group g1_0
join GroupCTE ...
)
where rownum<=? -- Only 1 parameter (SQLException occurs due to parameter count mismatch)
[Error]
Caused by: java.sql.SQLException: invalid column index
[Questions]
- Is it possible to make pagination use ROW_NUMBER() method like Code 1 when using CTE?
- Is there a setting to force ROW_NUMBER() instead of ROWNUM?
- Is this a known issue?
[Additional Information]
- Works fine when offset/limit are removed.
- Pagination is required, so a workaround is needed.
