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

**URL:** <https://discourse.hibernate.org/t/select-from-using-with-clause-and-static-literals-cte/9037>\
**Category:** Hibernate ORM\
**Created:** [February 15, 2024, 3:31pm UTC](https://discourse.hibernate.org/t/select-from-using-with-clause-and-static-literals-cte/9037 "2024-02-15T15:31:16Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Oleksandr\_Drozdiuk](https://avatars.discourse-cdn.com/v4/letter/o/90ced4/32.png) [@Oleksandr\_Drozdiuk](https://discourse.hibernate.org/u/Oleksandr_Drozdiuk)\
**Post date:** [February 15, 2024, 3:31pm UTC](https://discourse.hibernate.org/t/select-from-using-with-clause-and-static-literals-cte/9037/1 "2024-02-15T15:31:16Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [February 16, 2024, 7:04am UTC](https://discourse.hibernate.org/t/select-from-using-with-clause-and-static-literals-cte/9037/2 "2024-02-16T07:04:36Z")

</div>

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

```java
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" ) ) ) );

```

---

<div class="post-metadata">

**Author:** ![Oleksandr\_Drozdiuk](https://avatars.discourse-cdn.com/v4/letter/o/90ced4/32.png) [@Oleksandr\_Drozdiuk](https://discourse.hibernate.org/u/Oleksandr_Drozdiuk)\
**Post date:** [February 16, 2024, 10:50am UTC](https://discourse.hibernate.org/t/select-from-using-with-clause-and-static-literals-cte/9037/3 "2024-02-16T10:50:48Z")

</div>

Thanks for answer!

This line

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

```

Leads to next exception:

```auto
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!

 ![image](https://canada1.discourse-cdn.com/flex035/uploads/hibernate/original/2X/6/60a69109c62ac95463893a3a762cb54865682939.png)

---

<div class="post-metadata">

**Author:** ![Oleksandr\_Drozdiuk](https://avatars.discourse-cdn.com/v4/letter/o/90ced4/32.png) [@Oleksandr\_Drozdiuk](https://discourse.hibernate.org/u/Oleksandr_Drozdiuk)\
**Post date:** [February 16, 2024, 10:51am UTC](https://discourse.hibernate.org/t/select-from-using-with-clause-and-static-literals-cte/9037/4 "2024-02-16T10:51:23Z")

</div>

One more image attached

 ![image](https://canada1.discourse-cdn.com/flex035/uploads/hibernate/original/2X/1/118579b3d7109b18b56e6b50c5c93cdc5490eaa2.png)

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [February 16, 2024, 11:25am UTC](https://discourse.hibernate.org/t/select-from-using-with-clause-and-static-literals-cte/9037/5 "2024-02-16T11:25:58Z")

</div>

Please try to create a reproducer with our [test case template](https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java) and if you are able to reproduce the issue, create a bug ticket in our [issue tracker](https://hibernate.atlassian.net) and attach that reproducer.

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

```java
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" ) ) ) );

```

---

<div class="post-metadata">

**Author:** ![Oleksandr\_Drozdiuk](https://avatars.discourse-cdn.com/v4/letter/o/90ced4/32.png) [@Oleksandr\_Drozdiuk](https://discourse.hibernate.org/u/Oleksandr_Drozdiuk)\
**Post date:** [February 16, 2024, 1:02pm UTC](https://discourse.hibernate.org/t/select-from-using-with-clause-and-static-literals-cte/9037/6 "2024-02-16T13:02:24Z")

</div>

Here is the ticket:

[https://hibernate.atlassian.net/browse/HHH-17744](https://hibernate.atlassian.net/browse/HHH-17744)

Thanks for your time!

---

<div class="post-metadata">

**Author:** ![Oleksandr\_Drozdiuk](https://avatars.discourse-cdn.com/v4/letter/o/90ced4/32.png) [@Oleksandr\_Drozdiuk](https://discourse.hibernate.org/u/Oleksandr_Drozdiuk)\
**Post date:** [February 20, 2024, 1:30pm UTC](https://discourse.hibernate.org/t/select-from-using-with-clause-and-static-literals-cte/9037/7 "2024-02-20T13:30:20Z")

</div>

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

**The query i was trying to build:**

```auto
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:**

```auto
    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 :**

```auto
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!**

---

<div class="post-metadata">

**Author:** ![Oleksandr\_Drozdiuk](https://avatars.discourse-cdn.com/v4/letter/o/90ced4/32.png) [@Oleksandr\_Drozdiuk](https://discourse.hibernate.org/u/Oleksandr_Drozdiuk)\
**Post date:** [February 20, 2024, 1:36pm UTC](https://discourse.hibernate.org/t/select-from-using-with-clause-and-static-literals-cte/9037/8 "2024-02-20T13:36:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [February 20, 2024, 4:21pm UTC](https://discourse.hibernate.org/t/select-from-using-with-clause-and-static-literals-cte/9037/9 "2024-02-20T16:21:19Z")

</div>

That NPE is a bug, please try to create a reproducer with our [test case template](https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java) and if you are able to reproduce the issue, create a bug ticket in our [issue tracker](https://hibernate.atlassian.net) and attach that reproducer.

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

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

```

---

<div class="post-metadata">

**Author:** ![Oleksandr\_Drozdiuk](https://avatars.discourse-cdn.com/v4/letter/o/90ced4/32.png) [@Oleksandr\_Drozdiuk](https://discourse.hibernate.org/u/Oleksandr_Drozdiuk)\
**Post date:** [February 22, 2024, 3:20pm UTC](https://discourse.hibernate.org/t/select-from-using-with-clause-and-static-literals-cte/9037/10 "2024-02-22T15:20:53Z")

</div>

Hello, thank you so much!

It worked, as expected.

Sincerely, Oleksandr.

---

<div class="post-metadata">

**Author:** ![Borys\_Makhlin](https://avatars.discourse-cdn.com/v4/letter/b/a8b319/32.png) [@Borys\_Makhlin](https://discourse.hibernate.org/u/Borys_Makhlin)\
**Post date:** [February 23, 2024, 7:31pm UTC](https://discourse.hibernate.org/t/select-from-using-with-clause-and-static-literals-cte/9037/11 "2024-02-23T19:31:01Z")

</div>

We’ve created [a bug](https://hibernate.atlassian.net/jira/software/c/projects/HHH/boards/31?selectedIssue=HHH-17776) ticket for this issue,  
Thanks for your time and guidance.