I am using Hibernate 3.0.5 with MySQL and I have found something very strange in a query I am using. I am also using Spring Framework 1.2 to manage the Hibernate sessions.

I have 3 tables: (Patch, Build, Bug). A Patch contains Builds, a Build contains bugs and bugs have a value for the build the bug was found in and the build the bug was fixed in (the build fixed in might be null).

I have an HQL query that is supposed to retrieve all bugs for a particular patch (so any bugs that were found or fixed in any builds in a specified patch).
Here is the query:
"from Bug bug where (bug.buildFixedIn.patch.patchId = :patchId) OR (bug.buildFoundIn.patch.patchId = :patchId)"

Here is the generated SQL Hibernate makes:


Hibernate: select bug0_.bugId as bugId, bug0_.buildFixedIn as buildFix2_9_, bug0_.buildFoundIn as buildFou3_9_, bug0_.dateAdded as dateAdded9_, bug0_.dateFixed as dateFixed9_, bug0_.dateUpdated as dateUpda6_9_, bug0_.description as descript7_9_, bug0_.title as title9_, bug0_.userId as userId9_, bug0_.impact as impact9_, bug0_.resolution as resolution9_, bug0_.status as status9_, bug0_.assignedUserId as assigne13_9_, bug0_.patchId as patchId9_ from BUG bug0_, BUILD build1_, BUILD build2_ where bug0_.buildFoundIn=build2_.buildId and bug0_.buildFixedIn=build1_.buildId and (build1_.patchId=? or build2_.patchId=?)

The generated SQL has a WHERE clause that uses an AND where it should use an OR, that is what I can't figure out. This generated query is getting all bugs that are found and fixed in patch X, not all bugs that are found or fixed in patch X.

I can't figure out why this is happening. BTW, I tried playing with the query and I found that if I make the query search for all bugs found in patch X or all bugs found in patch Y, it works properly. Just when I do the found in or fixed in does it not work.

Thanks,
Julian

looks like the "and"ing is happening for reasons of "bug.buildFixedIn.patch.patchId" where it has to join bug and build tables by their relationship...
(same w/ bug.buildFoundIn.patch.patchId).

jwerfel wrote:

Did you try setting up the inverse relation from Build to Bug w/ <one-to-many and query from Build where patchid = ?

also what if you did something like:

select bug where bug.buildFixedIn in (select patchid from build where patchid=?) or
bug.buildFoundIn in (select patchid from build where patchid=?)

Thanks for your help. I am using MySQL 4.0.16 and I don't think it supports sub-queries.

I did think about your other method, of querying on the builds for a specific patch, and then getting all the bugs for those builds. the problem is 1 don't want bugs listed twice. If a bug is found in a build 2 of patch A, and fixed in build 3 of patch A, I only wanted it listed once. Originally my query had a GROUP BY bug.bugId so that I would have a unique set of bugs (I took out that part for debugging purposes). I could easily write one query that gets all the bugs found in a patch, and all the bugs fixed in a patch, and then join the lists, but that would give me multiples of the same bug. I thought I could avoid that in my query.

If you have any other thoughts, I would welcome hearing them.

Thanks,
Julian