[using hibernate 3.2, mysql 5]
I'm trying to use a union select in a query to avoid some index penalties that crop up when using an OR statement in the WHERE. (ie. breaking the query into 2 selects allows Mysql to use the correct index for each select instead of using one thats wrong for half of the conditions ) My my case, the conditions on each side of the OR are mirror images of each other (my class is a combination of 2 identical parts... UID0 and UID0Flag0-7 and UID1 and UID1Flag0-7. I'm querying on the UID and flags, but need to search both UID0 and UID1 for my value.
But, it seems like hibernate is throwing away everything after the "UNION SELECT":
Code:
select link from BiLink link where ( link.UID0 = :srcUID and link.UID0Flag1 = true) union select link2 from BiLink link2 where ( link2.UID1 = :srcUID and link2.UID1Flag1 = true)
gets translated into:
Code:
select <snipped_field_list> where hibernateb0_.UID0=? and hibernateb0_.UID0FLAG1=1 limit ?
where its missing the second select with its WHERE UID1=? and UID1FLAG1=1.
BTW, my original query only differed in that there was an "OR" in place of the
"union select from where" clause:
Code:
select link from BiLink link where ( link.UID0 = :srcUID and link.UID0Flag1 = true) or ( link2.UID1 = :srcUID and link2.UID1Flag1 = true)
but this wasn't handled well by Mysql 5.
Does Hibernate allow union selects? If so, how do I write it?
-Trevor
Login
FAQ
Search