[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3807) Adding a restriction to a many-to-one entity in Criteria query causes Join fetching

[Gail Badner (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3807?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=32688#action_32688 ] 

Gail Badner commented on HHH-3807:
----------------------------------

Have you tried using dot notation instead of an alias?

Criteria criteria = persistenceService.getCriteria(MailingParcel.class)
.add(Restrictions.ge("mailingCampaign.id", 1))
.setMaxResults(10);

I know there is an optimization using a restriction on the "id" property for many-to-one associations using HQL that will return a proxy. This is described in http://www.hibernate.org/hib_docs/v3/reference/en-US/html_single/#queryhql-where . 

When using a Criteria with an alias in a restriction, the expected behavior is to join the tables. 

"Java Persistence with Hibernate" states in section 15.1.2 Joins and dynamic fetching:

"The second way to express inner joins with the Criteria API is to assign an
alias to the joined entity:

   session.createCriteria(Item.class)
            .createAlias("bids", "b")
            .add( Restrictions.like("description", "%Foo%") )
            .add( Restrictions.gt("b.amount", new BigDecimal(99) ) );

And the same for a restriction on a single-valued association, the seller:

   session.createCriteria(Item.class)
            .createAlias("seller", "s")
            .add( Restrictions.like("s.email", "%hibernate.org" ) );"

>From your test case, it looks like Hibernate is smart enough to not do the join when using an alias without a restriction.

> Adding a restriction to a many-to-one entity in Criteria query causes Join fetching
> -----------------------------------------------------------------------------------
>
>                 Key: HHH-3807
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3807
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-criteria
>    Affects Versions: 3.3.1
>         Environment: Hibernate 3.3.1GA
> Sql Server 2005
>            Reporter: Jonathan Gordon
>            Priority: Minor
>         Attachments: slsession.tgz
>
>
> When performing a criteria query that includes a restriction on a many-to-one entity, the associated entity is fetched eagerly, as if FetchMode were set to "JOIN". Explicitly setting the FetchMode to "SELECT" does not override this behavior. 
> For instance, this criteria query:
> 		Criteria criteria = persistenceService.getCriteria(MailingParcel.class)
> 				.createAlias("mailingCampaign", "mc")
> 				.add(Restrictions.ge("mc.id", 1))
> 				.setMaxResults(10);
> Yields the following sql:
> select
> top 10 this_.ID as ID17_1_,
> this_.KEYCODE as KEYCODE17_1_,
> this_.CAMPAIGN_ID as CAMPAIGN3_17_1_,
> this_.MAILING_LIST_MAILING_ID as MAILING4_17_1_,
> this_.COUNTRY_LE_ID as COUNTRY5_17_1_,
> this_.MATCH_ADDRESS as MATCH6_17_1_,
> this_.ADDRESS as ADDRESS17_1_,
> this_.MATCH_NAME as MATCH8_17_1_,
> this_.FIRST_NAME as FIRST9_17_1_,
> this_.LAST_NAME as LAST10_17_1_,
> this_.ZIP_CODE asZIP11_17_1_,
> this_.CITY as CITY17_1_,
> this_.STATE as STATE17_1_,
> this_.COMPANY as COMPANY17_1_,
> mc1_.ID as ID6_0_,
> mc1_.NOTE as NOTE6_0_,
> mc1_.NAME as NAME6_0_,
> mc1_.DATE_CREATED as DATE5_6_0_,
> mc1_.DATE_MODIFIED as DATE6_6_0_,
> mc1_.START_DATE as START7_6_0_,
> mc1_.SOURCE_FILE_NAME as SOURCE8_6_0_,
> mc1_.ORDINAL as ORDINAL6_0_,
> mc1_.KEYCODE_SUFFIX as KEYCODE10_6_0_,
> mc1_.MATCH_CATALOG_ID as MATCH11_6_0_,
> mc1_.ADDRESS_IMPORT_DATE as ADDRESS12_6_0_
> from MATCH_MAILING_CAMPAIGN this_
> inner join MATCH_CAMPAIGN_INFO mc1_ on this_.CAMPAIGN_ID=mc1_.ID
> where mc1_.ID>=1
> However, removing the restriction yields the following sql:
> select
> top 10 this_.ID as ID17_0_,
> this_.KEYCODE as KEYCODE17_0_,
> this_.CAMPAIGN_ID as CAMPAIGN3_17_0_,
> this_.MAILING_LIST_MAILING_ID as MAILING4_17_0_,
> this_.COUNTRY_LE_ID as COUNTRY5_17_0_,
> this_.MATCH_ADDRESS as MATCH6_17_0_,
> this_.ADDRESS as ADDRESS17_0_,
> this_.MATCH_NAME as MATCH8_17_0_,
> this_.FIRST_NAME as FIRST9_17_0_,
> this_.LAST_NAME as LAST10_17_0_,
> this_.ZIP_CODE as ZIP11_17_0_,
> this_.CITY as CITY17_0_,
> this_.STATE as STATE17_0_,
> this_.COMPANY as COMPANY17_0_
> from MATCH_MAILING_CAMPAIGN this_
> Performing the original query in its HQL analog does not have this problem.

-- 
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