Howdy. I'm trying to do something fairly complex, and Hibernate isn't doing what I expect. It *looks* like it's producing the wrong SQL, but I may be misinterpreting, so I want to check before submitting a formal bug report. I'm also not sure whether this is legal HQL -- certainly it's more complex than anything in the docs -- so I'm looking for any and all guidance here.
I've got several places where I basically need to update a join table. The details vary, but in each case I need to post-hoc fill in any joins that may not be there yet. I'll be showing one example here; all of them seem to exhibit the same problem. I've been trying to do this with a subselect, because it's the only way I've figured out to say "insert this if it *doesn't* yet exist". If there is a better way to do this, I'm all ears.
The HQL query looks like this:
Code:
insert into ReadState
(viewer,conversation)
select me, conv
from Person me, Conversation conv
left join me.reading reading
where me.id = :me
and ((conv.creator = reading.friend and conv.community = null)
or conv.community = reading.community)
and not exists (
from ReadState state
where state.viewer = me
and state.conversation = conv
)
Narratively: Person is an individual; Conversation is a particular conversation. Person.reading is a list of contexts that this person is following -- either other Persons or Communities. ReadState represents the relationship between a Person and a Conversation that they are reading. So this query finds all Conversations that were created in a context I am reading, for which there doesn't already exist a ReadState, and inserts a ReadState if so.
The problem comes in the subselect. The actual SQL that is being produced is:
Code:
insert
into
ReadState
( personId, convId ) select
person0_.id as col_0_0_,
conversati1_.id as col_1_0_
from
Person person0_
left outer join
Reading reading2_
on person0_.id=reading2_.personId,
Conversation conversati1_
where
person0_.id=?
and (
conversati1_.creatorId=reading2_.friendId
and (
conversati1_.communityId is null
)
or conversati1_.communityId=reading2_.communityId
)
and not (exists (select
readstate3_.id
from
ReadState readstate3_
where
readstate3_.personId=id
and readstate3_.convId=id))
Notice those last two lines, which seem to be the heart of the problem. I had originally assumed that I was missing some subtlety of subselect syntax, but they do seem to be broken -- if I'm not mistaken, they *should* be
Code:
readstate3_.personId=person0_.id
and readstate3_.convId=conversati1_.id))
So: am I correct that this is a bug, or am I missing something? Is this even legal for HQL? And does anyone have a good suggestion of how to address it? My fallback is to take the above SQL, fix it and submit it as native SQL instead of HQL, but obviously that's not a great solution.
Hibernate version: Originally hit this problem with 3.2.5, but it still seems to be the case in 3.2.6
Mapping documents: Available if anybody thinks they're relevant, but given that this is occurring for several different tables, I suspect that they aren't.
Name and version of the database you are using: MySQL 5.0
Login
FAQ
Search