'Select from' using 'With' clause and static literals CTE

Oleksandr_Drozdiuk · February 15, 2024, 3:31pm

Hello, i’m currently using 6.4.1.Final version, and want to interpret next query into CriteriaQuery:

with HT_q(col_1, col_2) as materialized

(

select

     temporary_data.column1::uuid as col_1,

     ...

     temporary_data.column2::uuid as col_2,


     from (

         values('any uuid 1','any uuid 2'),
         ...
         values('any uuid 3','any uuid 4'),

) as temporary_data

)   

select rt1_0."id",rt1_0."real_column_1",rt1_0."real_column_2"

from RealTable rt1_0

join HT_q on (

        rt1_0."real_column_1" = HT_q.col_1

        ...

        and rt1_0."real_column_2" = HT_q.col_2

    )

In this case I have static literals inside values, and they are sourced from memory.

Is there a way, using CTE, to select multiple rows with literals, and then use them later for operations?

Of course, I want to avoid using native queries.

Any examples are welcome.

Thank you.

beikov · February 16, 2024, 7:04am

Yes, you can use union all like e.g.

JpaCriteriaQuery<Tuple> data1 = cb.createTupleQuery();
data1.multiselect( cb.literal( "any uuid 1" ).alias( "col1" ), cb.literal( "any uuid 2" ).alias( "col2" ) );
List<JpaCriteriaQuery<Tuple>> dataN = new ArrayList<>();
JpaCriteriaQuery<Tuple> data2 = cb.createTupleQuery();
data2.multiselect( cb.literal( "any uuid 3" ).alias( "col1" ), cb.literal( "any uuid 4" ).alias( "col2" ) );
dataN.add( data2 );

JpaCriteriaQuery<RealTable> cq = cb.createQuery( RealTable.class );
JpaCteCriteria<Tuple> dataCte = cq.with( cb.unionAll( data1, dataN.toArray( new JpaCriteriaQuery[0] ) ) );
JpaRoot<RealTable> root = cq.from( RealTable.class );
JpaJoin<Tuple> cteJoin = root.join( dataCte );
cteJoin.on( cb.and( cb.equal( root.get( "col1" ), cteJoin.get( "col1" ) ), cb.equal( root.get( "col2" ), cteJoin.get( "col2" ) ) ) );

Oleksandr_Drozdiuk · February 16, 2024, 10:50am

Thanks for answer!

This line

JpaCteCriteria<Tuple> dataCte = cq.with( cb.unionAll( data1, dataN.toArray( new JpaCriteriaQuery[0] ) ) );

Leads to next exception:

java.lang.IllegalStateException: Query group can't be treated as query spec. Use JpaSelectCriteria#getQueryPart to access query group details
	at org.hibernate.query.sqm.tree.select.SqmSelectStatement.getQuerySpec(SqmSelectStatement.java:158) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.cte.SqmCteTable.createStatementTable(SqmCteTable.java:60) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.cte.SqmCteStatement.<init>(SqmCteStatement.java:64) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.withInternal(AbstractSqmSelectQuery.java:173) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.select.AbstractSqmSelectQuery.with(AbstractSqmSelectQuery.java:115) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]

During debuging I figured out that SqmSelectQuery selectStatement cannot get selectStatement.getQuerySpec() since cb.unionAll returns Query Group!

Oleksandr_Drozdiuk · February 16, 2024, 10:51am

One more image attached

beikov · February 16, 2024, 11:25am

Please try to create a reproducer with our test case template and if you are able to reproduce the issue, create a bug ticket in our issue tracker and attach that reproducer.

In the meantime, you can try nest the query group in a subquery.

JpaCriteriaQuery<Tuple> cteSubquery = cb.createTupleQuery();
JpaSubQuery<Tuple> data1 = cteSubquery.subquery(Tuple.class);
data1.multiselect( cb.literal( "any uuid 1" ).alias( "col1" ), cb.literal( "any uuid 2" ).alias( "col2" ) );
List<JpaSubQuery<Tuple>> dataN = new ArrayList<>();
JpaSubQuery<Tuple> data2 = cteSubquery.subquery(Tuple.class);
data2.multiselect( cb.literal( "any uuid 3" ).alias( "col1" ), cb.literal( "any uuid 4" ).alias( "col2" ) );
dataN.add( data2 );

JpaCriteriaQuery<RealTable> cq = cb.createQuery( RealTable.class );
JpaRoot<Tuple> subqueryRoot = cteSubquery.from( cb.unionAll( data1, dataN.toArray( new JpaSubQuery[0] ) ) );
cteSubquery.multiselect( subqueryRoot.get("col1").alias("col1"), subqueryRoot.get("col2").alias("col2") );
JpaCteCriteria<Tuple> dataCte = cq.with( cteSubquery );
JpaRoot<RealTable> root = cq.from( RealTable.class );
JpaJoin<Tuple> cteJoin = root.join( dataCte );
cteJoin.on( cb.and( cb.equal( root.get( "col1" ), cteJoin.get( "col1" ) ), cb.equal( root.get( "col2" ), cteJoin.get( "col2" ) ) ) );

Oleksandr_Drozdiuk · February 16, 2024, 1:02pm

Here is the ticket:

https://hibernate.atlassian.net/browse/HHH-17744

Thanks for your time!

Oleksandr_Drozdiuk · February 20, 2024, 1:30pm

‘Nest the query group in a subquery’ throws exception also.

The query i was trying to build:

with HT_q(col_1, col_2) as materialized

(

select

     temporary_data.column1::uuid as col_1,

     ...

     temporary_data.column2::uuid as col_2,


     from (

     	 select ('any uuid 1','any uuid 2')
         union all
         select ('any uuid 3','any uuid 4')
         ...

) as temporary_data

)

Geting result like this:

    private List<RealEntity> methodExample() {

        var builder = entityManager.unwrap(Session.class).getCriteriaBuilder();
        var mainQuery = builder.createQuery(RealEntity.class);
        // Query that wrapps union all subquery
        var wrapperQuery = builder.createQuery(Tuple.class);
        var unionAllSubQuery = buildUnionAllSubquery(builder, wrapperQuery);
        var unionAllQueryRoot = wrapperQuery.from(unionAllSubQuery);

        wrapperQuery.multiselect(
                unionAllQueryRoot.get("col1").alias("col1"),
                unionAllQueryRoot.get("col2").alias("col2")
        );

        var dataCte = mainQuery.with(wrapperQuery);
        var mainQueryRoot = mainQuery.from(dataCte);

        mainQuery.multiselect(
                mainQueryRoot.get("col1").alias("col1"),
                mainQueryRoot.get("col2").alias("col2")
        );

        return entityManager.createQuery(mainQuery)
                  .getResultList();
    }

    private JpaSubQuery<Tuple> buildUnionAllSubquery(HibernateCriteriaBuilder builder,
                                                  JpaCriteriaQuery<Tuple> unionAllQuery) {

        JpaSubQuery<Tuple> q1 = unionAllQuery.subquery(Tuple.class);
        JpaSubQuery<Tuple> q2 = unionAllQuery.subquery(Tuple.class);

        q1.multiselect(
                builder.literal("123").alias("col1"),
                builder.literal("123").alias("col2")
        );

        q2.multiselect(
                builder.literal("123").alias("col1"),
                builder.literal("123").alias("col2")
        );

        return builder.unionAll(
                q1,
                q2
        );
    }

Exception :

java.lang.NullPointerException: Cannot invoke "org.hibernate.query.sqm.tree.from.SqmFromClause.visitRoots(java.util.function.Consumer)" because "fromClause" is null
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitFromClause(BaseSemanticQueryWalker.java:247) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQuerySpec(BaseSemanticQueryWalker.java:234) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:125) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryGroup(BaseSemanticQueryWalker.java:224) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.select.SqmQueryGroup.accept(SqmQueryGroup.java:96) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitSubQueryExpression(BaseSemanticQueryWalker.java:877) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.select.SqmSubQuery.accept(SqmSubQuery.java:694) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.consumeFromClauseRoot(BaseSemanticQueryWalker.java:253) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) ~[na:na]
	at org.hibernate.query.sqm.tree.from.SqmFromClause.visitRoots(SqmFromClause.java:80) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitFromClause(BaseSemanticQueryWalker.java:247) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQuerySpec(BaseSemanticQueryWalker.java:234) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:125) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryPart(BaseSemanticQueryWalker.java:218) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitSelectStatement(BaseSemanticQueryWalker.java:131) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitSelectQuery(BaseSemanticQueryWalker.java:201) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitCteStatement(BaseSemanticQueryWalker.java:195) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.cte.SqmCteStatement.accept(SqmCteStatement.java:321) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitCteContainer(BaseSemanticQueryWalker.java:211) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitSelectStatement(BaseSemanticQueryWalker.java:130) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.select.SqmSelectStatement.accept(SqmSelectStatement.java:228) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.jpa.ParameterCollector.collectParameters(ParameterCollector.java:55) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.internal.SqmUtil.resolveParameters(SqmUtil.java:548) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.tree.select.SqmSelectStatement.resolveParameters(SqmSelectStatement.java:223) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.internal.DomainParameterXref.from(DomainParameterXref.java:44) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.query.sqm.internal.QuerySqmImpl.<init>(QuerySqmImpl.java:246) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createCriteriaQuery(AbstractSharedSessionContract.java:1407) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:1367) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:133) ~[hibernate-core-6.4.1.Final.jar:6.4.1.Final]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
	at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:364) ~[spring-orm-6.1.2.jar:6.1.2]
	at jdk.proxy2/jdk.proxy2.$Proxy198.createQuery(Unknown Source) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
	at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:319) ~[spring-orm-6.1.2.jar:6.1.2]
	at jdk.proxy2/jdk.proxy2.$Proxy198.createQuery(Unknown Source) ~[na:na]

I think I’m doing something wrong, could you please take me through this.

Thanks in advance!

Oleksandr_Drozdiuk · February 20, 2024, 1:36pm

It seems to me, that subquery must have From statement, but I want them to select literals!

beikov · February 20, 2024, 4:21pm

That NPE is a bug, please try to create a reproducer with our test case template and if you are able to reproduce the issue, create a bug ticket in our issue tracker and attach that reproducer.

In the meantime, you should be able to get around this by setting the SqmFromClause explicitly.

((SqmSubQuery) q1).getQuerySpec().setFromClause(new SqmFromClause());
((SqmSubQuery) q2).getQuerySpec().setFromClause(new SqmFromClause());

Oleksandr_Drozdiuk · February 22, 2024, 3:20pm

Hello, thank you so much!

It worked, as expected.

Sincerely, Oleksandr.

Borys_Makhlin · February 23, 2024, 7:31pm

We’ve created a bug ticket for this issue,
Thanks for your time and guidance.

Topic		Replies	Views
Select from array of literals using CTE Hibernate ORM	1	312	February 16, 2024
How can I create SELECT FROM SELECT query with JPA and Hibernate? Hibernate ORM	2	11476	August 8, 2018
Select item at position 1 in select list has no alias (aliases are required in CTEs and in subqueries occurring in from clause) Hibernate ORM	1	2033	May 24, 2024
Recursive CTE possible in Subselects or Joins? Hibernate ORM	3	561	December 8, 2023
Temptable in Hibernate, Criteria JPA Hibernate ORM	2	80	August 27, 2024

'Select from' using 'With' clause and static literals CTE

Related topics