I am mapping a legacy db with compound primary keys all over the place. The security requirements fit with hibernate filtering. I am hitting a SQLException because the SQL generated is invalid.

Is collection filtering not supported with compond primary keys, or am I doing something stupid?

The structure being mapped is:
Business Division 1-* ProcessArea 1-* Process

With filters applied on both the ProcessArea and the Process based on a group_areas table wich specifies which ProcessAreas and Processes each group gives access to. Unfortunatly the Primary Key of each of the ProcessArea and Process are compound. This seems to cause a problem in the SQL because it tries the following where (processes0_.business_division_id, processes0_.area_id) in (select processare0_.business_division_id, processare0_.process_area_id from business_div_process_areas processare0_


Hibernate version: 3.3.0.ga

Mapping documents:
The BusinessDivision->ProcessArea collection has a filter applied that looks like this:
@OneToMany
@JoinColumn(name = "business_division_id")
@IndexColumn(name = "display_id")
@Fetch(FetchMode.SUBSELECT)
@Filter(name = "icSelectFilter", condition = "process_area_id IN (SELECT ga.area_id FROM group_areas ga WHERE ga.group_id IN (:groupIds))")

and ProcessAre->Processes:

@OneToMany
@JoinColumns({@JoinColumn(name = "business_division_id"), @JoinColumn(name = "area_id")})
@OrderBy(value = "displayOrder")
@Fetch(FetchMode.SUBSELECT)
@Filter(name = "icSelectFilter", condition = "process_id IN (SELECT ga.process_id FROM group_areas ga WHERE ga.group_id IN (:groupIds))")



Code between sessionFactory.openSession() and session.close():
Filter filter=getSession().enableFilter("icSelectFilter");
Integer[] idArray = ids.toArray(new Integer[]{});
filter.setParameterList("groupIds", idArray);
return getSession.get(Process.class,id)

Full stack trace of any exception that occurs:
Caused by: com.inet.tds.SQLException: Msg 170, Level 15, State 1, Line 1, Sqlstate 01000
[IBLONPSD32X612\OI_DEV7]Line 1: Incorrect syntax near ','.
at com.inet.tds.a.a(Unknown Source)
at com.inet.tds.a.a(Unknown Source)
at com.inet.tds.c.new(Unknown Source)
at com.inet.tds.c.executeQuery(Unknown Source)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)

Name and version of the database you are using:
SQLServer 2000

The generated SQL (show_sql=true):
select processes0_.business_division_id as business2_3_, processes0_.area_id as area4_3_, processes0_.process_id as process3_3_, processes0_.business_division_id as business2_12_2_, processes0_.process_id as process3_12_2_, processes0_.area_id as area4_12_2_, processes0_.display_id as display1_12_2_, corporatep1_.process_area_id as process1_4_0_, corporatep1_.corporate_id as corporate4_4_0_, corporatep1_.last_updated as last2_4_0_, corporatep1_.process_area_name as process3_4_0_, corporatio2_.corporate_id as corporate1_7_1_, corporatio2_.display_id as display2_7_1_, corporatio2_.last_updated as last3_7_1_, corporatio2_.item_name as item4_7_1_ from business_div_processes processes0_ left outer join corporate_process_area corporatep1_ on processes0_.area_id=corporatep1_.process_area_id left outer join corporations corporatio2_ on corporatep1_.corporate_id=corporatio2_.corporate_id where (processes0_.business_division_id, processes0_.area_id) in (select processare0_.business_division_id, processare0_.process_area_id from business_div_process_areas processare0_ where processare0_.process_area_id IN (SELECT ga.area_id FROM group_areas ga WHERE ga.group_id IN (?, ?)) and processare0_.business_division_id=?)

Debug level Hibernate log excerpt:
<2008-04-28 17:36:04,027> <DEBUG> <main> <engine.TwoPhaseLoad> <done materializing entity [com.dkib.radar.domain.ProcessArea#component[businessDivision,corporateProcessArea]{corporateProcessArea=com.dkib.radar.domain.CorporateProcessArea#23, businessDivision=com.dkib.radar.domain.BusinessDivision#2}]>
<2008-04-28 17:36:04,027> <DEBUG> <main> <loading.CollectionLoadContext> <1 collections were found in result set for role: com.dkib.radar.domain.BusinessDivision.processAreas>
<2008-04-28 17:36:04,027> <DEBUG> <main> <loading.CollectionLoadContext> <collection fully initialized: [com.dkib.radar.domain.BusinessDivision.processAreas#2]>
<2008-04-28 17:36:04,027> <DEBUG> <main> <loading.CollectionLoadContext> <1 collections initialized for role: com.dkib.radar.domain.BusinessDivision.processAreas>
<2008-04-28 17:36:04,027> <DEBUG> <main> <engine.StatefulPersistenceContext> <initializing non-lazy collections>
<2008-04-28 17:36:04,027> <DEBUG> <main> <loader.Loader> <done loading collection>
<2008-04-28 17:36:04,042> <DEBUG> <main> <collection.OneToManyLoader> <Static select for one-to-many com.dkib.radar.domain.ProcessArea.processes: select processes0_.business_division_id as business2_3_, processes0_.area_id as area4_3_, processes0_.process_id as process3_3_, processes0_.business_division_id as business2_12_2_, processes0_.process_id as process3_12_2_, processes0_.area_id as area4_12_2_, processes0_.display_id as display1_12_2_, corporatep1_.process_area_id as process1_4_0_, corporatep1_.corporate_id as corporate4_4_0_, corporatep1_.last_updated as last2_4_0_, corporatep1_.process_area_name as process3_4_0_, corporatio2_.corporate_id as corporate1_7_1_, corporatio2_.display_id as display2_7_1_, corporatio2_.last_updated as last3_7_1_, corporatio2_.item_name as item4_7_1_ from business_div_processes processes0_ left outer join corporate_process_area corporatep1_ on processes0_.area_id=corporatep1_.process_area_id left outer join corporations corporatio2_ on corporatep1_.corporate_id=corporatio2_.corporate_id where (processes0_.business_division_id, processes0_.area_id) in (select processare0_.business_division_id, processare0_.process_area_id from business_div_process_areas processare0_ where processare0_.process_area_id IN (SELECT ga.area_id FROM group_areas ga WHERE ga.group_id IN (?, ?)) and processare0_.business_division_id=?)>
<2008-04-28 17:36:04,058> <DEBUG> <main> <jdbc.AbstractBatcher> <about to open PreparedStatement (open PreparedStatements: 0, globally: 0)>
<2008-04-28 17:36:04,058> <DEBUG> <main> <hibernate.SQL> <select processes0_.business_division_id as business2_3_, processes0_.area_id as area4_3_, processes0_.process_id as process3_3_, processes0_.business_division_id as business2_12_2_, processes0_.process_id as process3_12_2_, processes0_.area_id as area4_12_2_, processes0_.display_id as display1_12_2_, corporatep1_.process_area_id as process1_4_0_, corporatep1_.corporate_id as corporate4_4_0_, corporatep1_.last_updated as last2_4_0_, corporatep1_.process_area_name as process3_4_0_, corporatio2_.corporate_id as corporate1_7_1_, corporatio2_.display_id as display2_7_1_, corporatio2_.last_updated as last3_7_1_, corporatio2_.item_name as item4_7_1_ from business_div_processes processes0_ left outer join corporate_process_area corporatep1_ on processes0_.area_id=corporatep1_.process_area_id left outer join corporations corporatio2_ on corporatep1_.corporate_id=corporatio2_.corporate_id where (processes0_.business_division_id, processes0_.area_id) in (select processare0_.business_division_id, processare0_.process_area_id from business_div_process_areas processare0_ where processare0_.process_area_id IN (SELECT ga.area_id FROM group_areas ga WHERE ga.group_id IN (?, ?)) and processare0_.business_division_id=?)>
Hibernate: select processes0_.business_division_id as business2_3_, processes0_.area_id as area4_3_, processes0_.process_id as process3_3_, processes0_.business_division_id as business2_12_2_, processes0_.process_id as process3_12_2_, processes0_.area_id as area4_12_2_, processes0_.display_id as display1_12_2_, corporatep1_.process_area_id as process1_4_0_, corporatep1_.corporate_id as corporate4_4_0_, corporatep1_.last_updated as last2_4_0_, corporatep1_.process_area_name as process3_4_0_, corporatio2_.corporate_id as corporate1_7_1_, corporatio2_.display_id as display2_7_1_, corporatio2_.last_updated as last3_7_1_, corporatio2_.item_name as item4_7_1_ from business_div_processes processes0_ left outer join corporate_process_area corporatep1_ on processes0_.area_id=corporatep1_.process_area_id left outer join corporations corporatio2_ on corporatep1_.corporate_id=corporatio2_.corporate_id where (processes0_.business_division_id, processes0_.area_id) in (select processare0_.business_division_id, processare0_.process_area_id from business_div_process_areas processare0_ where processare0_.process_area_id IN (SELECT ga.area_id FROM group_areas ga WHERE ga.group_id IN (?, ?)) and processare0_.business_division_id=?)
<2008-04-28 17:36:04,074> <DEBUG> <main> <jdbc.AbstractBatcher> <about to close PreparedStatement (open PreparedStatements: 1, globally: 1)>
<2008-04-28 17:36:04,089> <DEBUG> <main> <util.JDBCExceptionReporter> <could not load collection by subselect: [com.dkib.radar.domain.ProcessArea.processes#<component[businessDivision,corporateProcessArea]{corporateProcessArea=com.dkib.radar.domain.CorporateProcessArea#17, businessDivision=com.dkib.radar.domain.BusinessDivision#2}, component[businessDivision,corporateProcessArea]{corporateProcessArea=com.dkib.radar.domain.CorporateProcessArea#11, businessDivision=com.dkib.radar.domain.BusinessDivision#2}, component[businessDivision,corporateProcessArea]{corporateProcessArea=com.dkib.radar.domain.CorporateProcessArea#9, businessDivision=com.dkib.radar.domain.BusinessDivision#2}, component[businessDivision,corporateProcessArea]{corporateProcessArea=com.dkib.radar.domain.CorporateProcessArea#12, businessDivision=com.dkib.radar.domain.BusinessDivision#2}, component[businessDivision,corporateProcessArea]{corporateProcessArea=com.dkib.radar.domain.CorporateProcessArea#14, businessDivision=com.dkib.radar.domain.BusinessDivision#2}, component[businessDivision,corporateProcessArea]{corporateProcessArea=com.dkib.radar.domain.CorporateProcessArea#16, businessDivision=com.dkib.radar.domain.BusinessDivision#2}, component[businessDivision,corporateProcessArea]{corporateProcessArea=com.dkib.radar.domain.CorporateProcessArea#10, businessDivision=com.dkib.radar.domain.BusinessDivision#2}, component[businessDivision,corporateProcessArea]{corporateProcessArea=com.dkib.radar.domain.CorporateProcessArea#20, businessDivision=com.dkib.radar.domain.BusinessDivision#2}, component[businessDivision,corporateProcessArea]{corporateProcessArea=com.dkib.radar.domain.CorporateProcessArea#23, businessDivision=com.dkib.radar.domain.BusinessDivision#2}, component[businessDivision,corporateProcessArea]{corporateProcessArea=com.dkib.radar.domain.CorporateProcessArea#18, businessDivision=com.dkib.radar.domain.BusinessDivision#2}, component[businessDivision,corporateProcessArea]{corporateProcessArea=com.dkib.radar.domain.CorporateProcessArea#13, businessDivision=com.dkib.radar.domain.BusinessDivision#2}>] [select processes0_.business_division_id as business2_3_, processes0_.area_id as area4_3_, processes0_.process_id as process3_3_, processes0_.business_division_id as business2_12_2_, processes0_.process_id as process3_12_2_, processes0_.area_id as area4_12_2_, processes0_.display_id as display1_12_2_, corporatep1_.process_area_id as process1_4_0_, corporatep1_.corporate_id as corporate4_4_0_, corporatep1_.last_updated as last2_4_0_, corporatep1_.process_area_name as process3_4_0_, corporatio2_.corporate_id as corporate1_7_1_, corporatio2_.display_id as display2_7_1_, corporatio2_.last_updated as last3_7_1_, corporatio2_.item_name as item4_7_1_ from business_div_processes processes0_ left outer join corporate_process_area corporatep1_ on processes0_.area_id=corporatep1_.process_area_id left outer join corporations corporatio2_ on corporatep1_.corporate_id=corporatio2_.corporate_id where (processes0_.business_division_id, processes0_.area_id) in (select processare0_.business_division_id, processare0_.process_area_id from business_div_process_areas processare0_ where processare0_.process_area_id IN (SELECT ga.area_id FROM group_areas ga WHERE ga.group_id IN (?, ?)) and processare0_.business_division_id=?)]>
com.inet.tds.SQLException: Msg 170, Level 15, State 1, Line 1, Sqlstate 01000
[IBLONPSD32X612\OI_DEV7]Line 1: Incorrect syntax near ','.