2

I've written a JPQL statement that unions multiple select statements, each of which selects a different string constant. A simplified version of the query would look something like this:

SELECT DISTINCT 'A' AS widget_type
  FROM WidgetA widgetA
  WHERE widgetA.creationTimestamp > :cutoff
UNION SELECT DISTINCT 'B' AS widget_type
  FROM WidgetB widgetB
  WHERE widgetB.creationTimestamp > :cutoff

The query doesn't cause any errors but I'm not getting the result I expect. I see that the generated SQL doesn't have any unions - it only queries the table from the first select:

select distinct 'A' as col_0_0_ 
from widget_a widget0_
where widget0_.creation_timestamp>?

Is there an obvious reason why JPA would disregard everything after the first select statement? If it makes any difference, I am using Hibernate 4.1.9 as the JPA implementation, with a MySQL database.

spaaarky21
  • 6,524
  • 7
  • 52
  • 65

1 Answers1

3

JPQL has no such concept as UNION. So consequently any "query" that has it is not JPQL and so ought to be rejected as invalid JPQL (which DataNucleus JPA certainly would do)

DataNucleus
  • 15,497
  • 3
  • 32
  • 37
  • Interesting. I figured Hibernate would have complained at me. If JPQL doesn't handle unions, are there alternatives that might serve a similar purpose? – spaaarky21 Feb 25 '13 at 14:56
  • are "WidgetA" and "WidgetB" in the same inheritance tree? If so then do a JPQL of the common superclass, and you may get a UNION SQL statement issued. Otherwise just issue two JPQL queries and merge the results in your code – DataNucleus Feb 25 '13 at 15:16
  • Thanks for setting me straight on unions in queries and for the suggestions. I have other questions now but they are a little off topic for this question. I figured it was better to post them separately. http://stackoverflow.com/questions/15080555/jpql-equivalent-of-sql-query-using-unions-and-selecting-constants – spaaarky21 Feb 26 '13 at 03:10
  • 1
    EclipseLink supports UNION in JPQL, http://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/j_union.htm#union – James Mar 21 '13 at 13:53