Uploaded image for project: 'Spring Data JPA'
  1. Spring Data JPA
  2. DATAJPA-1506

Wrong alias detection for native query

    Details

      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}
      

       

        Attachments

          Activity

            People

            • Assignee:
              olivergierke Oliver Drotbohm
              Reporter:
              tobat50 Tobat
              Last updater:
              Oliver Drotbohm
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: