StringQuery incorrect alias definition for complex native queries [DATAJPA-1613]

[spring-projects-issues]

vyemialyanchyk opened DATAJPA-1613 and commented

select (@row_number\:=@row_number+1) as id , ref_id , external , some_app_id , message , creation_date , ack , xxx_abc_id , is_responded , response_attempts , orm_ack_n , oru_ack_n from ( select * from (( select some_request.id as ref_id , true as external , some_request.some_app_id , some_request.serialized_object as message , some_request.creation_date , some_request.ack , some_request.xxx_abc_id , some_request.is_responded , some_request.response_attempts , count(orm_ack.id) as orm_ack_n , count(oru_ack.id) as oru_ack_n from some_request left join orm_ack on orm_ack.order_id=some_request.xxx_abc_id left join oru_ack on oru_ack.order_id=some_request.xxx_abc_id group by some_request.id ) union (select some_forward.id as ref_id , false as external , some_forward.some_app_id , some_forward.message as message , some_forward.creation_date , some_forward.ack , some_forward.xxx_abc_id , (some_forward.ack is not null) as is_responded , if(some_forward.ack is not null, 1, 0) as response_attempts , count(orm_ack.id) as orm_ack_n , count(oru_ack.id) as oru_ack_n from some_forward left join orm_ack on orm_ack.order_id=some_forward.xxx_abc_id left join oru_ack on oru_ack.order_id=some_forward.xxx_abc_id group by some_forward.id )) as union_orf, (select @row_number\:=0) as init_row_number_var ) as whole_orf where whole_orf.some_app_id=:someAppId

this is query (defined as native JPA).

 

org.springframework.data.jpa.repository.query.StringQuery:

StringQuery(String query) {

 Assert.hasText(query, "Query must not be null or empty!");

 this.bindings = new ArrayList<>();
 this.containsPageableInSpel = query.contains("#pageable");

 Metadata queryMeta = new Metadata();
 this.query = ParameterBindingParser.INSTANCE.parseParameterBindingsOfQueryIntoBindingsAndReturnCleanedQuery(query,
 this.bindings, queryMeta);

 this.usesJdbcStyleParameters = queryMeta.usesJdbcStyleParameters;
 this.alias = QueryUtils.detectAlias(query); <== here query used and alias detected incorrectly as 'left'
 this.hasConstructorExpression = QueryUtils.hasConstructorExpression(query);
}

 

QueryUtils.ALIAS_MATCH

(?<=from)(?:\s)+([._[\P\\{Z}&&\P\\{Cc}&&\P\\{Cf}&&\P\\{P}]]+)(?:\sas)*(?:\s)+(?!(?:where|group\s*by|order\s*by))(\w+)

=>

this RegExp can't cover all possible variants of native queries..

for example in provided sql is left join and finally 'left' interpreted by this RegExp as alias...

---

Affects: 2.1.11 (Lovelace SR11), 2.2 GA (Moore)

Issue Links:

• DATAJPA-1406 NativeQuery pageable sort with existing inner ORDER BY

[spring-projects-issues]

vyemialyanchyk commented

https://stackoverflow.com/questions/50475700/spring-jpa-native-query-sorting-adding-prefix-to-order-by-field-name

[gregturn]

As hinted at in the opening comment, when it comes to native queries, we rely upon QueryUtils to handle everything. And a regex has limits on parsing native queries that can really be just about anything.

This situation appears too complicated for QueryUtils to handle, and we aren't likely to approve a change there that would meet your needs. Your best best moving forward may be to implement a custom implementation. Check out https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations for more details on how to hook such a solution into your repository.

The alternative would be to rewrite your query using HQL and leveraging our new query parser. That might also better support your need for dynamically applied ordering.