[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1650) specific criteria generates incorrect SQL command (missing join)

[Alex Borshik (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1650?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_28828 ] 

Alex Borshik commented on HHH-1650:
-----------------------------------

I managed to work around this issue by setting FetchMode.JOIN on the associations:

session.createCriteria(Message.class) 
.add(Restrictions.eq("language", language))
.setFetchMode("messageKey", FetchMode.JOIN)
.createCriteria("messageKey")
.add(Restrictions.isNotNull("key"))
.list();

> specific criteria generates incorrect SQL command (missing join)
> ----------------------------------------------------------------
>
>                 Key: HHH-1650
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1650
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 3.1.3
>         Environment: 3.1.3, Oracle 9.1
>            Reporter: Martin Zdila
>            Priority: Minor
>
> Hello
> Mapping:
> <hibernate-mapping>
> 	<class name="MessageKey" table="KFE_MESSAGE_KEY">
> 		<id name="id" column="MESSAGE_KEY_ID" node="@id">
> 			<generator class="native">
> 				<param name="sequence">KFE_MESSAGE_KEY_ID_SEQ</param>
> 			</generator>
> 		</id>
> 		
> 		<property name="key" column="MESSAGE_KEY" not-null="false" />
> 		
> 		<map name="messages" cascade="all" inverse="true" order-by="LANGUAGE_ID" embed-xml="false">
> 			<key column="MESSAGE_KEY_ID" />
> 			<map-key type="java.lang.String" formula="LANGUAGE_ID" />
> 			<one-to-many class="Message" />
> 		</map>
> 	</class>
> 	<class name="Message" table="KFE_MESSAGE">
> 		<composite-id>
> 			<key-many-to-one name="language" column="LANGUAGE_ID" class="Language" />
> 			<key-many-to-one name="messageKey" column="MESSAGE_KEY_ID" class="MessageKey" />
> 		</composite-id>
> 		<property name="value" column="MESSAGE_VALUE" length="255" />
> 	</class>
> </hibernate-mapping>
> This doesn't work:
> session.createCriteria(Message.class)	.add(Restrictions.eq("language", language)).createCriteria("messageKey").add(Restrictions.isNotNull("key")).list();
> Generated incorrect SQL command:
> select this_.LANGUAGE_ID as LANGUAGE1_2_0_, this_.MESSAGE_KEY_ID as MESSAGE2_2_0_, this_.MESSAGE_VALUE as MESSAGE3_2_0_ from KFE_MESSAGE this_ where this_.LANGUAGE_ID=? and messagekey1_.MESSAGE_KEY is not null
> Error: java.sql.SQLException: ORA-00904: "MESSAGEKEY1_"."MESSAGE_KEY": invalid identifier
> Workaround using HQL works:
> session.createQuery("FROM Message AS msg WHERE msg.messageKey.key IS NOT NULL AND msg.language = :lang").setParameter("lang", language).list();
> Tanks in advance for fixing

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira