Saturday, 12 May 2007
Hibernate bug in subqueries
« Ode to Intel graphics or my downgrade from my MacBookPro | Main | Talk to BLU at MIT and PLUG-West at Unisys and Mike @ Man-LUG »Mikey logged this bug on Hibernate query generation. We've ended up writing a fair amount of native SQL for Meldware to deal with various limitations in Hibernate and/or MySQL or the sticky goo between them (generally Oracle is fine and frankly PostgreSQL is the most capable, easiest to work with database we test on). This latest bug affects when you have two subqueries and is a straight hibernate bug. If I say "delete from Foo as foo where foo.pid not in (select id from Bar) and foo.pid not in (select id from Goo)"... Hibernate generates something like "delete from FOO where pid not in (select id from BAR) and _foo0.pid not in (select id from GOO)" basically failing to declare its own alias _foo0 and thus erroring at least postgresql. This follows on a more serious Hibernate bug (really JavaAssist) which fortunately did not affect us. Hope the 2609 bug gets fixed soon as Mikey doesn't like it when he's writing native SQL instead of HQL.
Anyhow, M8 is coming soon... I realize it is very late, but it has a lot in it that we hadn't planned on but would have been silly not to do. Clustered IMAP IDLE notifications and nicely working WCAP + freebusy isn't bad for a month and some change huh? I'll have an announcement on Monday as well as some pictures of our official Bunisoft model for LinuxFest Northwest, Francesca...so stay tuned for that. I can't wait to blab on what I'm working on for M9...but I'll save that for even later ;-). I have to say as 1.0 approaches its hard not to start writing 2.0 in the middle of each milestone...but I resist.
Technorati Tags: mike barker jboss hibernate bug hql ejb3 java
[Trackback URL for this entry]
Sorry gavin it actually only affects deletes (I corrected the entry post-hoc). Mike got it right, I screwed it up. Stayed up too late watching Friday night fights on DVR... The real query can't be rewritten AFAICT:
DELETE FROM StoreItemMetaData s WHERE s.pid NOT IN (SELECT storeId FROM
MailBodyReference) AND s.pid NOT IN (SELECT bodyId FROM MessageBody)
That's correct, it only affects deletes. I have tried the query using a couple of variations, including the one above (its in the bug report).
On another note, we probably won't have IMAP IDLE until M9. The jgroups notification bus is in place though.
I imagine it will work if you use:
from Foo as foo where foo.pid not in (select b.id from Bar b) and foo.pid not in (select g.id from Goo g)