[DATAJPA-1506] Wrong alias detection for native query Created: 21/Feb/19  Updated: 10/May/19  Resolved: 09/May/19

Status: Closed
Project: Spring Data JPA
Component/s: None
Affects Version/s: 1.11.18 (Ingalls SR18)
Fix Version/s: 1.11.21 (Ingalls SR21), 2.0.15 (Kay SR15), 2.1.7 (Lovelace SR7), 2.2 M4 (Moore)

Type: Bug Priority: Minor
Reporter: Tobat Assignee: Oliver Drotbohm
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Last updater: Oliver Drotbohm
Pull Request URL: https://github.com/spring-projects/spring-data-jpa/pull/379

 Description   

In a JpaRepository, the ORDER clause of a native query without WHERE clause can be interpreted as an alias.

 

@Query(nativeQuery = true,
value = "SELECT id, type_contact typecontact, libelle, caption  FROM view_ann_tous_contacts ORDER BY ?#{#pageable}",
countQuery = "SELECT COUNT(*) FROM view_ann_tous_contacts")
Page<Result> projectAll(Pageable page);

When the query is executed, the compiled query in logs is 

SELECT
        id,
        type_contact typecontact,
        libelle,
        caption  
    FROM
        view_ann_tous_contacts 
    ORDER BY
        ?,
        ORDER.libelle asc limit ?

In debug, with breakpoint in the constructor of the class org.springframework.data.jpa.repository.query.StringQuery, this part of code detect the ORDER clause as an alias : 

this.alias = QueryUtils.detectAlias(query);

And the pattern used to detect the alias is :

(?<=from)(?:\s)+([\p{Lu}\P{InBASIC_LATIN}\p{Alnum}._$]+)(?:\sas)*(?:\s)+(?!(?:where))(\w*)

It seems only the WHERE clause is taken in account to detect the end of the FROM part of the query.

And my query works if I change it to 

SELECT id, type_contact typecontact, libelle, caption FROM view_ann_tous_contacts WHERE 1=1 ORDER BY ?#{#pageable}

 



 Comments   
Comment by Oliver Drotbohm [ 09/May/19 ]

That's polished and merged, thanks!

Generated at Sat Jul 20 03:32:11 UTC 2019 using JIRA 7.9.2#79002-sha1:3bb15b68ecd99a30eb364c4c1a393359bcad6278.