HQL union query with null values in select part

Hibernate ORM
1

I create union query with two tables. This two tables have set of identical columns and set of not identical columns. I want that null values is selected for not identical columns.
I create query as follow (simplified, other columns were ommited):

 List<UnitedAlarmDto> resultList = entityManager.createQuery("""
                SELECT
                ja.jobId as jobId
                from JobAlarm ja
                union all
                SELECT
                null as jobId
                from SystemAlarm sa
                """, Tuple.class)
            .getResultList()
            .stream()
            .map(AlarmService::fromTuple).toList();

This query throws exception:

Caused by: org.hibernate.query.SemanticException: Select items of the same index must have the same java type across all query parts
	at org.hibernate.query.sqm.tree.select.SqmQueryGroup.validateQueryGroupFetchStructure(SqmQueryGroup.java:174) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
	at org.hibernate.query.sqm.tree.select.SqmQueryGroup.validateQueryStructureAndFetchOwners(SqmQueryGroup.java:154) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitStatement(SemanticQueryBuilder.java:403) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.buildSemanticModel(SemanticQueryBuilder.java:311) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
	at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:71) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:165) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:147) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:790) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:840) ~[hibernate-core-6.4.4.Final.jar:6.4.4.Final]
	... 251 common frames omitted

What is right way to create such query? Is it possible?
Tested on hibernate version: 6.4.4

2

I guess the validation is a bit too strict. 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.

3

Was this fixed? We are seeing the same exception happen. We are using hibernate-core 6.5.3. Our query looks something like this:

SELECT new Result('Type1', 'A', sfpv.id.propertyValueId, pv.propertyTypeId)
FROM Filter sf
LEFT JOIN Table1 sfpv ON sfpv.id.filterId = sf.id
LEFT JOIN sfpv.propertyValue pv
WHERE sf.id = :filterId

UNION ALL

SELECT new Result('Type2', 'B', sfs.id.subjectId, null)
FROM Filter sf
LEFT JOIN Table2 sfs ON sfs.id.filterId = sf.id
WHERE sf.id = :filterId

UNION ALL

SELECT new Result('Type3', 'C', sfst.id.subjectTypeId, null)
FROM Filter sf
LEFT JOIN Table3 sfst ON sfst.id.filterId = sf.id
WHERE sf.id = :filterId

We are creating a typed query from this and Result is a record with a string, an enumeration and two UUIDs.

It only happens once and the next time the query seems to work just fine.

1 Like
4

Since @slavick didn’t post a link to a Jira issue here it’s hard to say. Either way, try updating to the latest version (6.6.1.Final at the time of writing) to see if that fixes your issues.
If not, please create a Jira issue and attach a reproducer as well as post the link here for others to have a reference.

5

Yes, well the trouble is that we cannot reproduce it in a test. It only happens the first time after our Spring Boot application has started up. All subsequent times that the query is used (which is a lot), the exception is not raised and it works fine. Does this ring a bell perhaps? That it happens right after startup?

6

Unfortunately, no, that doesn’t ring a bell. Please share once you are able to reproduce.

7

Thanks for your quick replies, beikov!

I tried all evening to create a test project for our case and failed. However, I could easily get the code of slavick to show the issue. I created an issue with a test project. See [HHH-18720] Type check on select columns in union all gives SemanticException when there is a null column - Hibernate JIRA (atlassian.net)

1 Like