[Hibernate-JIRA] Created: (HHH-3807) Adding a restriction to a many-to-one entity in Criteria query causes Join fetching
11:36 a.m.
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
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
12:38 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3807) Adding a restriction to a many-to-one entity in Criteria query causes Join fetching
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3807?page=c...
]
Gail Badner commented on HHH-3807:
----------------------------------
Please attach a runnable (Java + mapping) that reproduces this issue.
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
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
10:20 p.m.
New subject: [Hibernate-JIRA] Updated: (HHH-3807) Adding a restriction to a many-to-one entity in Criteria query causes Join fetching
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3807?page=c...
]
Jonathan Gordon updated HHH-3807:
---------------------------------
Attachment: slsession.tgz
I've attached some runnable code that shows the problem. To run:
tar xzvf slsession.tgz
cd slsession
ant
That will compile and run five junit tests, two of which currently fail because of this
problem. I've created five unit test methods in a class named AppTest that query the
db in different ways (via criteria and hql) for an object (DomainObject) that has two
many-to-one entities (RelationA and RelationB). Three of those queries return results that
allow the entity to be lazy loaded (select fetching), but the two that use the criteria
api and have a restriction based on the entity fail, loading the entity using join
fetching.
testCriteriaWithRestrictions
testCriteriaWithRestrictionsAndFetchModeOfSelect
I've gone ahead and step-through debugged the running query and the problem seems to
be on lines 120-124 of org.hibernate.loader.AbstractEntityJoinWalker:
{{
.setSelectClause(
projection == null ?
persister.selectFragment( alias, suffixes[joins] ) + selectString( associations )
:
projection
)
}}
When using the criteria api, it appears that if there are any associations found, which
will be the case if a restriction is placed on a many-to-one entity, the are automatically
included in the sql.
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
12:54 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-3807) Adding a restriction to a many-to-one entity in Criteria query causes Join fetching
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3807?page=c...
]
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/#queryhq... .
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
7:59 a.m.
New subject: [Hibernate-JIRA] Closed: (HHH-3807) Adding a restriction to a many-to-one entity in Criteria query causes Join fetching
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-3807?page=c...
]
Steve Ebersole closed HHH-3807.
-------------------------------
Resolution: Duplicate
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
4988
days inactive
5768
days old
hibernate-issues@lists.jboss.org
4 comments
3 participants
participants (3)
-
Gail Badner (JIRA) -
Jonathan Gordon (JIRA)
-
Steve Ebersole (JIRA)