CountQuery fails to be auto generated when not using explicit alias [DATAJPA-1737]

[spring-projects-issues]

Jonas Tänzer opened DATAJPA-1737 and commented

Consider the following repository: 

public interface BookErrorRepository extends Repository<BookError, Long> {

    @Query("FROM BookError b WHERE portal = :portal AND date >= :startDate AND date <= :endDate" +
           " AND (:search IS NULL OR keyword LIKE :search%)" +
           " AND (:state IS NULL OR errors = :#{#state?.shortMessage})")
    Page<BookError> findByPortalInDateRange(@Param("portal") Portal portal,
            @Param("startDate") LocalDate startDate,
            @Param("endDate") LocalDate endDate,
            @Param("search") Optional<String> search,
            @Param("state") Optional<State> state,
            Pageable pageable);

}

When changing this to 

FROM BookError WHERE portal [...]

the attached exception occurs on startup.

At first it seems like an issue with the HQL parser, but upon further inspection it seems that spring-data-jpa doesn't generate the count query correctly, because it recognizes WHERE as the alias. 

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: WHERE near line 1, column 14 [select count(WHERE) FROM com.example.project

This isn't a huge issue issue of course, but I figured I'd report to at least receive feedback anyway.

 

 

---

Attachments:

• count_query_error.txt (49.63 kB)

[jmax01]

My native query example:

Fails:

    String FIND_BY_ALL_ATTRIBUTES_QUERY = """

            SELECT
              *
            FROM
              #{#entityName}
            WHERE
              attributes @> CAST(?1 as jsonb)

            """;

Resulting bad query:

SELECT
   COUNT(WHERE)  
FROM
  someTable
WHERE
  attributes @> CAST(?1 as jsonb)

Adding the alias works:

    String FIND_BY_ALL_ATTRIBUTES_QUERY = """

            SELECT
              *
            FROM
              #{#entityName} t
            WHERE
              attributes @> CAST(?1 as jsonb)

            """;

[gregturn]

Part of this query's issue is that the LIKE clause details should be wrapped inside single-quotes. As for the rest, we're working up a solution to handle missing aliases.