Support of CTE with Pageable

[bountin]

I've eagerly tried the CTE support with Hibernate but was quickly limited by Spring Data JPA's support of it. Here is a repository illustrating all of the three points below. There are workarounds for the issues with Pageable but they involve processing the client's PageRequest manually, and a bunch of query duplications.

Two of the following three issues persist in 3.3.0, one was fixed (accidentally?) after 3.2.5. All of the queries below are annotated on a repository method with @Query.

Simple CTE with Pageable

WITH entities AS (
    SELECT e.id as id, e.number as number
    FROM TestEntity e
)
SELECT new com.example.demo.Result('X', c.id, c.number)
FROM entities c

This HQL snippet (with a Pageable sort and paging) failed up to 3.2.5 but was fixed in 3.2.6 and 3.3.0. Unfortunately, I could not find any related ticket or release note entry.

Because it is already fixed, I won't write much more here :)

Automatic countQuery with CTE

If one leaves the countQuery parameter empty, so that Spring Data generates one for a query with a CTE, it will fail with the following stack trace:

Caused by: org.hibernate.query.SemanticException: Select item at position 1 in select list has no alias (aliases are required in CTEs and in subqueries occurring in from clause)
	at org.hibernate.query.derived.AnonymousTupleType.<init>(AnonymousTupleType.java:62)
	at org.hibernate.query.sqm.tree.cte.SqmCteTable.<init>(SqmCteTable.java:40)
	at org.hibernate.query.sqm.tree.cte.SqmCteTable.createStatementTable(SqmCteTable.java:65)
	at org.hibernate.query.sqm.tree.cte.SqmCteStatement.<init>(SqmCteStatement.java:64)
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.withInternal(AbstractSqmSelectQuery.java:175)
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.with(AbstractSqmSelectQuery.java:136)
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitCte(SemanticQueryBuilder.java:885)
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWithClause(SemanticQueryBuilder.java:757)
	at org.hibernate.grammars.hql.HqlParser$WithClauseContext.accept(HqlParser.java:1405)
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:1018)
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:275)
	at org.hibernate.grammars.hql.HqlParser$SimpleQueryGroupContext.accept(HqlParser.java:2003)
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectStatement(SemanticQueryBuilder.java:490)
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitStatement(SemanticQueryBuilder.java:449)
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.buildSemanticModel(SemanticQueryBuilder.java:322)
	at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:71)
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:145)
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:132)
	at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:802)
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:852)

Essentially, Spring Data generates the following HQL that of course is not a correct count query:

WITH entities AS(
    SELECT count(e) FROM TestEntity e
) SELECT count(e) FROM entities c

CTE and UNION ALL

A query as follows will again fail with a sorting/paging Pageable parameter:

WITH entities AS (
    SELECT e.id as id, e.number as number FROM TestEntity e
),
combined AS (
    SELECT 'A' as source, a.id as id, a.number as number
    FROM entities as a

    UNION ALL

    SELECT 'B' as source, b.id as id, b.number as number
    FROM entities as b
)
SELECT new com.example.demo.Result(c.source, c.id, c.number)
FROM combined as c

This time, Spring Data fails to generate correct HQL. It again pushes the order into the UNION CTE - which is superfluous - and misses to add a space after the ASC key word as seen in the following part of the generated query: ... order by number ascUNION ALL SELECT 'B' as source, ...

[christophstrobl]

@bountin thanks for reporting & sharing a sample project. The part with the missing space for UNION has already been reported via #3427.
We'll look into the count issue you described.

Related to: #2981