Is UNION (in EJBQL) supported in Wildfly 10

[prasanth_jboss]

We had a query that uses UNION and worked fine until Wildfly 9.0.2. We recently migrated to 10.1.0 and the query execution fails with the following error. Tried the same in 9.0.2 and it works fine. Has something changed in Wildfly 10?

Thanks,

Prasanth

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: UNION near line 1, column 86 [SELECT a.pID FROM Arrangement a WHERE a.pID=30947 AND a.effectiveDate = '08/21/2001' UNION SELECT e.yID FROM Event e WHERE e.policyID=30947 AND e.proceedsDate = '08/20/2001']

at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)

at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91)

at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:288)

at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:187)

at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:142)

at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)

at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:76)

at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:150)

at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:302)

at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:240)

at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1894)

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291)

... 89 more

[ctomc]

That is more question for Hibernate guys, as only difference in this area was version upgrade of hibernate.

maybe sebersole smarlow knows more

[mayerw01]

I got the same issue in Wildfly 10 when using Hibernate (EclipseLink seems to works). Are you sure that Hibernate union works in WildFly 9? In my tests Hibernate did not produce any error but just ignored the 'union table' (which is even worse).

But using 'createNativeQuery' should work. Maybe you could also use a database view instead.

[prasanth_jboss]

Turned on statement logging in the database and ran the same code in 9.0.2 the statement in the log doesn't show the sql after union. As you said hibernate might have been ignoring it and that is scary.