It appears that I have developed a mapping, where certain HQL queries result in problematic SQL code.
Using hibernate 2.1.4 I have the following (shortened) mapping:
Code:
<class name="de.innovationgate.webgate.api.jdbc.Content" table="CONTENT">
<id name="cuid" unsaved-value="null" type="string" length="32">
<generator class="uuid.hex" />
</id>
<map name="items" inverse="true" cascade="all-delete-orphan" lazy="true">
<key column="cuid"/>
<index column="name" type="string" />
<one-to-many class="de.innovationgate.webgate.api.jdbc.ContentItem" />
</map>
</class>
<class name="de.innovationgate.webgate.api.jdbc.ContentItem" table="CONTENT_ITEMS">
<composite-id unsaved-value="any">
<key-many-to-one name="parentcontent" class="de.innovationgate.webgate.api.jdbc.Content" column="cuid"/>
<key-property name="name" length="100" type="string"/>
</composite-id>
<list name="strings" table="CONTENT_ITEMS_STRINGVALUES" cascade="all" lazy="true">
<key>
<column name="cuid"/>
<column name="name"/>
</key>
<index column="idx"/>
<element column="stringvalue" type="text"/>
</list>
</class>
As you can see there are two entities. The entity "Content" holds a list of entities of type "ContentItem". ContentItem itself holds a list to string values.
When querying in HQL it is very common to search for contents, whose content items contain certain strings in their string list. The problematic situation appears, when we test for two strings combined by OR:
Code:
from Content as content
where content.items['body'].strings[0] = "A"
or content.items['_shortstory'].strings[0] = "B"
This HQL gets translated into the following SQL:
Code:
select
content0_.cuid as x0_0_ from CONTENT content0_,
CONTENT_ITEMS items1_,
CONTENT_ITEMS_STRINGVALUES strings2_,
CONTENT_ITEMS items3_,
CONTENT_ITEMS_STRINGVALUES strings4_
where
(strings2_.stringvalue = 'A' and content0_.cuid=items1_.cuid and items1_.name = 'body' and items1_.cuid=strings2_.cuid and items1_.name=strings2_.name and strings2_.idx = 0) OR
(strings4_.stringvalue = 'A' and content0_.cuid=items3_.cuid and items3_.name = '_shortstory' and items3_.cuid=strings4_.cuid and items3_.name=strings4_.name and strings4_.idx = 0)
I tested this SQL on mySQL 4.0, Firebird 1.5 and DB2 8.1. It results in the SQL server taking 100% CPU and not returning from the query.
I must admit, I have not completely understood the reason for this behaviour (since I am a SQL/Hibernate newbie) but the reason has to do with this theta style inner join, that is doubled in the where clause.
This following SQL code, which simply puts the inner join on the top level of the where clause (and cleans up the table doubles in the select clause) functions correctly:
Code:
select
content0_.cuid as x0_0_ from CONTENT content0_,
CONTENT_ITEMS items1_,
CONTENT_ITEMS_STRINGVALUES strings2_
where
(content0_.cuid=items1_.cuid) and
(items1_.cuid=strings2_.cuid) and
(items1_.name=strings2_.name) and
(
(strings2_.stringvalue = 'A' and items1_.name = 'body' and strings2_.idx = 0) OR
(strings2_.stringvalue = 'A' and items1_.name = '_shortstory' and strings2_.idx = 0)
)
Maybe I have done something that shouldn't be done to SQL/Hibernate/HQL, so I'm far from blaming anyone but me. I have just a few simple questions:
a) Can I force/hint HQL in any way to build these joins differently so that the resulting SQL is not problematic
b) Are plans on Hibernate/HQL undergoing to change this behaviour
c) If a) and b) are to be negated, any hint about what you would do in my place to solve this problem without completely dropping HQL would be very much appreciated.
So thanks for any help!
Oliver
Login
FAQ
Search