Add CTE support

[beikov]

Now that we have implemented support for subqueries in the from clause in 6.1 as part of #3768 and https://hibernate.atlassian.net/browse/HHH-3356, it is time to think about CTE support in HQL and Criteria as well.

The HQL part is pretty simple IMO. I suggest we just do what SQL does here and allow specifying a WITH clause for queryExpression. The syntax is something like this:

queryExpression
	: withClause? orderedQuery				# SimpleQueryGroup
	| withClause? orderedQuery (setOperator orderedQuery)+	# SetQueryGroup
	;

withClause
	: WITH cte (COMMA cte)*
	;

cte
	: identifier AS (NOT? MATERIALIZED)? LEFT_PAREN queryExpression RIGHT_PAREN searchClause? cycleClause?
	;

cteAttributes
	: identifier (COMMA identifier)*
	;

searchClause
	: SEARCH (BREADTH|DEPTH) FIRST BY searchSpecifications SET identifier
	;

searchSpecifications
	: searchSpecification (COMMA searchSpecification)*
	;

searchSpecification
	: identifier sortDirection? nullsPrecedence?
	;

cycleClause
	: CYCLE cteAttributes SET identifier (TO literal DEFAULT literal)? (USING identifier)?
	;

The search and cycle clause are applicable to recursive CTEs, i.e. ones that refer to the CTE itself in the second orderedQuery when the structure is orderedQuery UNION ALL? orderedQuery.

The SQM API for this is pretty simple:

HibernateCriteriaBuilder cb = session.getCriteriaBuilder();

// Create the femaleContacts CTE body
JpaCriteriaQuery<Tuple> cte = cb.createTupleQuery();
JpaRoot<Contact> cteRoot = cte.from( Contact.class );
cte.multiselect( cteRoot.get( "id" ).alias( "id" ), cteRoot.get( "name" ).alias( "name" ) );
cte.where( cb.equal( cteRoot.get( "gender" ), Contact.Gender.FEMALE ) );

// Create the main query and add the femaleContacts CTE through the "with" method
final JpaCriteriaQuery<Tuple> cq = cb.createTupleQuery();
// The returned object can then be used in `from` or `join` and can be thought of like a "named subquery"
final JpaCteCriteria<Tuple> femaleContacts = cq.with( cte );

final JpaRoot<Tuple> root = cq.from( femaleContacts );

cq.multiselect( root.get( "id" ), root.get( "name" ) );

which would be the way to write this HQL query:

with femaleContacts as (
	select c.id id, c.name name from Contact c where c.gender = FEMALE
)
select c.id, c.name from femaleContacts c

My intention is to add this to the next feature release 6.2 as part of https://hibernate.atlassian.net/browse/HHH-15328. Let's move further discussions to the PR that implements this.

[gavinking]

@beikov this looks sensible, but could you please add a couple of example queries showing how this would be used?

[gavinking]

What I was trying to get at is what is it in terms of the type system of HQL. I think it's just a "tuple".

[beikov]

It's a named tuple, yes

[sebersole]

It's a named tuple, yes

Not a TableGroup / TableReference? Some form of ColumnReferenceQualifier.

[gavinking]

I guess strictly-speaking it's a record type, not a tuple type; my bad.

[beikov]

Not a TableGroup / TableReference

Ultimately, there will be a TableGroup / TableReference, but we also need some kind of SqmPathSource which roughly is like a named tuple/record type.