Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.2.6

Name and version of the database you are using: Derby, SQLServer


BACKGROUND
==========

I am using a Collection of primitive types which hibernate allows - I have defined a Map<String, String> entity with appropriate hibernate annotations as follows -

@CollectionOfElements(fetch=FetchType.EAGER)
@JoinTable(name = "MY_MAP", joinColumns = @JoinColumn(name = "OWNER_ID"))
@MapKey(columns={@Column(name="MAP_KEY", nullable = false)})
@Column(name = "MAP_VALUE", nullable = false)
private Map<String,String> myMap;

Hibernate also allows querying this map using indexes in HQL, which is the basis of my query

For eg, I can do
from OwnerTable where myMap['key1']='value1' AND myMap['key2']='value2'

This works and Hibernate automatically constructs a SQL query that will do an inner join with two map tables in the above example.

eg - (simplified for readability) generated SQL will look as follows

select * from OwnerTable n, myMap m1, myMap m2, where
n.id = m1.owner_id
and n.id = m2.owner_id
and m1.map_key='key1'
and m2.map_key='key2'
and m1.map_value='value1'
and m2.map_value='value2'


PROBLEM
==========

Issue arises if I switch the condition in my query from AND to OR - i.e.;

from OwnerTable where myMap['key1']='value1' OR myMap['key2']='value2'

Hibernate parses this as follows

select * from OwnerTable n, myMap m1, myMap m2, where
n.id = m1.owner_id
and n.id = m2.owner_id
and m1.map_key='key1'
and m2.map_key='key2'
and
(m1.map_value='value1'
OR m2.map_value='value2')


The right query, I believe, should be


select * from OwnerTable n, myMap m1, myMap m2, where
n.id = m1.owner_id
and n.id = m2.owner_id
and
((m1.map_key='key1' and m1.map_value='value1')
OR
(m1.map_key='key2' and m1.map_value='value2'))


Please correct me if I am wrong. It seems to me that the second query presented here is a correcter reading of the HQL OR query using indexes.

There is a subtle difference between the two queries.
As an example, assume that the map has a value for "key1" but no entry for "key2". In this case, the first query will incorrectly return 0 rows, while the second one will return a row.

The second query might have performance issues but it achieves the right goal.
Please let me know if there is a mistake in my reading here. If not, is this a bug ?

I believe the Hibernate query is better. The first part just selects the right m1 and m2 from the table. The right condition is then applied to their values. With your version you may not properly fix m1 / m2 and get a cartesian join. No?

sorry - i tend to disagree

I do not want to fix the keys. The way I read the HQL query is -

"fetch me any object for which there exists key k1 with value v1 or key k2 with value v2"

iF k2 key does not exist at all, the hibernate query is incorrect but the second one will work.

Also, note that the second query works for all cases that the first query works for (such as ones with AND condition or ones with OR condition with k2 key existing)