[Hibernate-issues] [Hibernate-JIRA] Created: (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)

[Viatcheslav (JIRA) <no...@at...>]

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


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