[hibernate-dev] Detached Query causes stale query cache entries

[Lei Gu]

Hibernate version: 
3.2.6 

We ran into an issue where detached query causes stale query cache
entries. Our example is to retrieve a list of our favorite dogs. We use
a separate table called favorite_animal to store our favorite animals,
including dogs. 

For example, our main query is the following, 

from Dog 

And we use a detached query to select our favorite Dog, 
select fa.animal_id from favoriate_animal fa where fa.user_id = :user_id


The combined SQL looks like the following, 
from Dog dog where dog.id in (select fa.animal_id from favoriate_animal
fa where fa.user_id = :user_id) 

There is our problem. We execute the above query once and get a list of
dogs back. We then remove one dog from our favorite list and then run
the above query again. We are expected to see one less dog in our result
but we got the same list as before! 

It turns out that the query cache entry was not invalidated when we
removed one dog from the favorite list. I dug a little and it seems like
the query spaces used to look up invalidated entity names are only built
from associated criteria, but not from detached query or detached query
based criterion. 

In our example, the query spaces will only contain [DOG] entity. 

I had made some changes to CriteriaQueryTranslator to include additional
query spaces from detached queries in getQuerySpaces method in
CriteriaQueryTranslator but want to make sure that I am on the right
track. 

After my fix, the query spaces will contain [DOG,FAVORITE_ANIMAL]. 

I am attaching a patch as well.

Thanks for your feedback and comments. 

-- Lei

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-dev/attachments/20080818/9407dce4/attachment.html