spring-data-jpa: Sorting doesn't work when using an alias on two or more functions
As per the comments on this closed issue, I’m opening a new issue, because sorting still doesn’t work for aliases in all cases.
The regex patterns in org.springframework.data.jpa.repository.query.QueryUtils are not broad enough.
The FUNCTION_PATTERN in QueryUtils looks like this:
builder = new StringBuilder(); // any function call including parameters within the brackets builder.append("\\w+\\s*\\([\\w\\.,\\s'=]+\\)"); // the potential alias builder.append("\\s+[as|AS]+\\s+(([\\w\\.]+))"); FUNCTION_PATTERN = compile(builder.toString());which only considers a single pair of parenthesis, but not two or more. So, for example, the earlierstBundleStart is not detected in this query:
SELECT DISTINCT(event.id) as id, event.name as name, event.top_event as topEvent, event.ranking as ranking, MIN(bundle.base_price_amount) as cheapestBundlePrice, MIN(DATE(bundle.start)) as earliestBundleStart, ..
There was also a question on SO about this here, where many people state that they are facing the same issue.
There are and have been several issues (at least https://github.com/spring-projects/spring-data-jpa/issues/1404, https://github.com/spring-projects/spring-data-jpa/issues/1724, https://github.com/spring-projects/spring-data-jpa/issues/1919) with the regex patterns in QueryUtils. I’m wondering, isn’t there any more reliable way to parse the queries than with a regex (possibly an AST that is used also to execute the query)?
Edit: here’s another closely related issue: https://github.com/spring-projects/spring-data-jpa/issues/2079
About this issue
- Original URL
- State: closed
- Created 3 years ago
- Comments: 15 (6 by maintainers)
Commits related to this issue
- Introduce support for ordering by aliased columns. If a projection of an HQL query is aliased, be that a function call or a simply alias, apply sorting should NOT result in that order parameter havin... — committed to spring-projects/spring-data-jpa by gregturn a year ago
- Introduce support for ordering by aliased columns. If a projection of an HQL query is aliased, be that a function call or a simply alias, apply sorting should NOT result in that order parameter havin... — committed to spring-projects/spring-data-jpa by gregturn a year ago
- Introduce support for ordering by aliased columns. If a projection of an HQL query is aliased, be that a function call or a simply alias, apply sorting should NOT result in that order parameter havin... — committed to spring-projects/spring-data-jpa by gregturn a year ago
- Introduce support for ordering by aliased columns. If a projection of either an HQL or JPQL query is aliased, applied sorting should NOT result in that order parameter having the primary FROM clause'... — committed to spring-projects/spring-data-jpa by gregturn a year ago
- Introduce support for ordering by aliased columns. If a projection of either an HQL or JPQL query is aliased, do NOT prefix the FROM clause's alias prefix to any relevant applied sorting. Same for fu... — committed to spring-projects/spring-data-jpa by gregturn a year ago
- Introduce support for ordering by aliased columns. If a projection of either an HQL or JPQL query is aliased, do NOT prefix the FROM clause's alias prefix to any relevant applied sorting. Same for fu... — committed to spring-projects/spring-data-jpa by gregturn a year ago
Ok I see, the QueryUtils.STARTS_WITH_PAREN does not handle newlines in subselects. I’ll try to make a MR for that.
private static final Pattern PARENS_TO_REMOVE = Pattern.compile("(\\(.*\\bfrom\\b[^)]+\\))", CASE_INSENSITIVE | DOTALL);the DOTALL needs to be added thereit looks like there is a missing test case in commit mentioned by @gregturn , in my case i have such SQL query:
select u from UserAccountEntity u join fetch u.lossInspectorLimitConfiguration lil join fetch u.companyTeam ct where exists (select iu from UserAccountEntity iu join iu.roles u2r join u2r.role r join r.rights r2r join r2r.right rt where rt.code = :rightCode and iu = u ) and ct.id = :teamIdand if I add sort by id:
Sort.by(ASC, UserAccountEntity_.LAST_NAME, UserAccountEntity_.FIRST_NAME, UserAccountEntity_.ID)it tries to sort by iu instead of u.The commit mentioned by @gregturn does not cover a select in where nor a select in join.
The issue happens after updating to spring boot 2.7.0
No news that I know of. If you’re constructing the Pageable manually, you can try this as a workaround:
JpaSort.unsafe(Sort.Direction.DESC, "(id)"). This has been working for us, although, I don’t know if it works with a wildcard in select, you might needt1.id AS idfor that to work.