[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-7119) Hibernate filter's parameters are not populated when an entity's Collection is populated using a fetch mode of subselect.

[Gail Badner (JIRA)]

    [ https://hibernate.onjira.com/browse/HHH-7119?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=45760#comment-45760 ] 

Gail Badner commented on HHH-7119:
----------------------------------

Is this still an issue in 4.1.0?

> Hibernate filter's parameters are not populated when an entity's Collection is populated using a fetch mode of subselect.
> -------------------------------------------------------------------------------------------------------------------------
>
>                 Key: HHH-7119
>                 URL: https://hibernate.onjira.com/browse/HHH-7119
>             Project: Hibernate ORM
>          Issue Type: Bug
>    Affects Versions: 3.5.2
>            Reporter: Aaron Trewern
>
> I have a @Filter on a collection property that has @Fetch(FetchMode.SUBSELECT) when I query this entity with a simple list() I can see the filter is also being applied to SQL generated to retrieve the collection property but the "?" positional parameter is not being set in the subquery. For the purposes of illustration I have simplified the Filter from the actual IN clause to an equality test.
> A similar problem was reported here https://forum.hibernate.org/viewtopic.php?f=1&t=1007658&start=0&sid=a797c0a8c616e5be87be2fc4f7493ff1
> The entity in question has a number of collection properties all of which were fetched with a Fetchmode.SUBSELECT, after adding the @Filter I was only able to make the query work with a FetchMode.JOIN or SELECT. But the query seemed much slower.
> In the code box blow you can see Hibernate correctly binding the userId parameter to the query for retrieving the main entity:
> {code}
> 27.02.2012 17:02:17 DEBUG [http-8080-1] (SQLStatementLogger:logStatement) - select job0_.id as id1_, job0_.ENTITY_UID as ENTITY2_1_, job0_.version as version1_, job0_.created_by_user_id as created4_1_, job0_.created_on as created5_1_, job0_.internal_notes as internal6_1_, job0_.job_status_id as job7_1_, job0_.job_status_updated as job8_1_, job0_.job_status_user_id as job9_1_, job0_.job_type_id as job10_1_, job0_.name as name1_, job0_.number as number1_, job0_1_.assigned_to_employee_id as assigned1_2_, job0_1_.is_active as is2_2_, job0_1_.owner_party_id as owner3_2_, job0_2_.city as city6_, job0_2_.country as country6_, job0_2_.county as county6_, job0_2_.line1 as line4_6_, job0_2_.line2 as line5_6_, job0_2_.state as state6_, job0_2_.zipcode as zipcode6_, job0_2_.building_size as building8_6_, job0_2_.building_size_unit_id as building9_6_, job0_2_.building_type_id as building10_6_, job0_3_.annual_salary as annual1_17_, job0_3_.employed_by_department_id as employed2_17_, job0_3_.employee_number as employee3_17_, job0_3_.hourly_rate as hourly4_17_, job0_3_.last_hire_date as last5_17_, job0_3_.person_id as person6_17_, job0_4_.asset_id as asset1_48_, job0_4_.bid_submission_deadline as bid2_48_, job0_4_.customer_party_id as customer3_48_, job0_4_.original_contract_amount as original4_48_, job0_4_.payment_application_day as payment5_48_, job0_4_.requested_completion_date as requested6_48_, job0_4_.requested_start_date as requested7_48_, job0_5_.credit_limit as credit1_52_, job0_5_.party_id as party2_52_, case when job0_2_.id is not null then 2 when job0_1_.id is not null then 1 when job0_3_.id is not null then 3 when job0_4_.id is not null then 4 when job0_5_.id is not null then 5 when job0_.id is not null then 0 end as clazz_ from edrjob job0_ left outer join edrassetjob job0_1_ on job0_.id=job0_1_.id left outer join edrbuildingjob job0_2_ on job0_.id=job0_2_.id left outer join edremployeejob job0_3_ on job0_.id=job0_3_.id left outer join edrprojectjob job0_4_ on job0_.id=job0_4_.id left outer join edrrelationshipjob job0_5_ on job0_.id=job0_5_.id where ? = 113 and (job0_.id in (select department1_.job_id from edrdepartmentjoblink department1_ where department1_.department_id=2)) limit ?
> 27.02.2012 17:02:17 TRACE [http-8080-1] (NullableType:nullSafeSet) - binding '113' to parameter: 1
> {code}
> In code box below you can see Hibernate failing to set the parameter for the subselect query used to retrieve the collection property details:
> {code}
> 27.02.2012 17:02:32 DEBUG [http-8080-1] (SQLStatementLogger:logStatement) - select parties0_.job_id as job7_1_1_, parties0_.id as id1_, parties0_.id as id32_0_, parties0_.ENTITY_UID as ENTITY2_32_0_, parties0_.version as version32_0_, parties0_.description as descript4_32_0_, parties0_.job_id as job7_32_0_, parties0_.party_id as party5_32_0_, parties0_.party_type_id as party6_32_0_ from edrjobpartylink parties0_ where parties0_.job_id in (select job0_.id from edrjob job0_ left outer join edrassetjob job0_1_ on job0_.id=job0_1_.id left outer join edrbuildingjob job0_2_ on job0_.id=job0_2_.id left outer join edremployeejob job0_3_ on job0_.id=job0_3_.id left outer join edrprojectjob job0_4_ on job0_.id=job0_4_.id left outer join edrrelationshipjob job0_5_ on job0_.id=job0_5_.id where ? = 113 and (job0_.id in (select department1_.job_id from edrdepartmentjoblink department1_ where department1_.department_id=2)))
> 27.02.2012 17:02:32  WARN [http-8080-1] (JDBCExceptionReporter:logExceptions) - SQL Error: 0, SQLState: 07001
> 27.02.2012 17:02:32 ERROR [http-8080-1] (JDBCExceptionReporter:logExceptions) - No value specified for parameter 1
> {code}
> {code:title=Job.java|borderStyle=solid}
> @FilterDefs({
> 	@FilterDef(
> 		name = "filterJobsByUserId",
> 		parameters = {
> 			@ParamDef (name = "userId", type = "java.lang.Integer")
> 		}
> 	)
> })
> @Entity
> @Filters({
>  @Filter(name="filterJobsByUserId", condition = ":userId = 113")
> })
> public class Job extends LockableBaseEntity implements Auditable {
> 	...
> 	@OneToMany(mappedBy="job", targetEntity=JobParty.class, fetch=FetchType.EAGER, cascade=CascadeType.ALL, orphanRemoval=true)
> 	@Fetch(FetchMode.SUBSELECT)
> 	private Set<JobParty> parties = new HashSet<JobParty>();
> 	public Set<JobParty> getParties() { return this.parties; }
> 	public void setParties (Set<JobParty> parties) { this.parties = parties; }	
> 	...
> }
> {code}

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira