Generating countQuery for queries with CTE fails with actual pagination

[bountin]

I rechecked the "Automatic countQuery with CTE" case from #3504 and found another adjacent issue. In the other bug report I've used Pageable.unpaged() which apparently doesn't actually execute a count because the query itself already returns all elements. I've updated the repository with a constrained page request and now I have another exception from Hibernate not accepting the generated query:

org.hibernate.query.SemanticException: The derived SqmFrom[id, number] can not be used in a context where the expression needs to be expanded to identifying parts, because a derived model part does not have identifying parts. Replace uses of the root with paths instead e.g. `derivedRoot.get("alias1")` or `derivedRoot.alias1`

The generated countQuery is WITH entities AS (SELECT e.id as id, e.number as number FROM TestEntity e) SELECT count(c) FROM entities c.

See again here: https://github.com/bountin/spring-data-jpa-issue-3504

[bountin]

Ah, my current workaround is to use an explicit countQuery (see below) but I'd expect Spring Data to either block the invalid JQL generation with an explaining exception or actually support this usecase :)

    private static final String CTE_QUERY = "WITH foo AS (......)"
    private static final String SELECT = "SELECT field1, ... FROM foo foo"
    private static final String COUNT = "SELECT count(*) FROM foo foo"

    @Query(value = CTE_QUERY + SELECT, countQuery = CTE_QUERY + COUNT)

[nexus061]

Ah, my current workaround is to use an explicit countQuery (see below) but I'd expect Spring Data to either block the invalid JQL generation with an explaining exception or actually support this usecase :)

    private static final String CTE_QUERY = "WITH foo AS (......)"
    private static final String SELECT = "SELECT field1, ... FROM foo foo"
    private static final String COUNT = "SELECT count(*) FROM foo foo"

    @Query(value = CTE_QUERY + SELECT, countQuery = CTE_QUERY + COUNT)

I get it too, I managed to make it work also using countProjection.