[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1742) SQL join is missed in Criteria queries for join/alias on foreign key as part of composite primary key (many-to-one association)

SQL join is missed in Criteria queries for join/alias on foreign key as part of composite primary key (many-to-one association) ------------------------------------------------------------------------------------------------------------------------------- Key: HHH-1742 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1742 Project: Hibernate3 Type: Bug

Components: query-criteria Versions: 3.2.0 cr1 Environment: Windows XP, Oracle 10g, HSQLDB Reporter: Viatcheslav Sysoltsev (Slavka) Attachments: HibernateTest.zip I've stuck with Criteria API on issue, that the join is sometimes missed from generated SQL query doing joining on foreign key. Here is the little example I've made to demonstrate the problem (also attached as standalone runnable eclipse project) There are two tables: FAMILY familyName as primary key familyProperty PERSON familyName as foreign key on FAMILY } the both fields make primary key personName } of PERSON Doing query on person like session.createCriteria(Person.class) .createCriteria("id.family") .add(Restrictions.eq("familyProperty", "whatever")) .list(); Causes query Hibernate: select this_.FAMILY_NAME as FAMILY1_1_0_, this_.PERSON_NAME as PERSON2_1_0_ from PERSON this_ where family1_.FAMILY_PROPERTY=? .. which is rather invalid The same by the way happens doing query session.createCriteria(Person.class) .createCriteria("id.family") .add(Restrictions.eq("familyName", "whatever")) .list(); The query select this_.FAMILY_NAME as FAMILY1_1_0_, this_.PERSON_NAME as PERSON2_1_0_ from PERSON this_ where family1_.FAMILY_NAME=? .. is invalid either though it can be made valid without join, because family1_.FAMILY_NAME can be accessed through this_.FAMILY_NAME The attachment is a whole eclipse project, pretty straightforward, with hsqldb, which demonstrates the SQL generated and the problem. I suspect this bug may be caused by the fix to HH-528 (component.manyToOne.id in HQL causes join). The priority should be high because session.createCriteria(Person.class) .createAlias("id.family", "family") .add(Restrictions.eq("family.familyProperty", "whatever")) .list(); doesn't work either. In effect there is no way to cause join on foreign key with Criteria API. Whatever in hibernate blocks join on foreign key rather zealous.