Hi,
I am facing the problem where for given HQL, incorrect SQL is generated.

An association betwwen classes is as follows:
1 . Participant has one-to-many ParticiapantMedicalIdentifier (UniDirection)
2. ParticiapantMedicalIdentifier has many-to-one Site. (UniDirection)


Hibernate version:3.0

[b]Mapping documents:


Code between sessionFactory.openSession() and session.close():

String hql1 = "From edu.wustl.catissuecore.domain.Participant as xxTargetAliasxx where xxTargetAliasxx.participantMedicalIdentifierCollection.id in (select id From edu.wustl.catissuecore.domain.ParticipantMedicalIdentifier where site.id in (select id From edu.wustl.catissuecore.domain.Site where name = 'Walt'))";

Full stack trace of any exception that occurs:

Name and version of the database you are using:
MYSQL 4.0.19

[b]The generated SQL (show_sql=true):

select participan0_.IDENTIFIER as IDENTIFIER, participan0_.LAST_NAME as LAST2_13_, participan0_.FIRST_NAME as FIRST3_13_, participan0_.MIDDLE_NAME as MIDDLE4_13_, participan0_.BIRTH_DATE as BIRTH5_13_, participan0_.GENDER as GENDER13_, participan0_.GENOTYPE as GENOTYPE13_, participan0_.ETHNICITY as ETHNICITY13_, participan0_.SOCIAL_SECURITY_NUMBER as SOCIAL9_13_, participan0_.ACTIVITY_STATUS as ACTIVITY10_13_, participan0_.DEATH_DATE as DEATH11_13_, participan0_.VITAL_STATUS as VITAL12_13_ from CATISSUE_PARTICIPANT participan0_, CATISSUE_PART_MEDICAL_ID participan1_ where participan0_.IDENTIFIER=participan1_.PARTICIPANT_ID and (participan1_.IDENTIFIER in (select participan2_.IDENTIFIER from CATISSUE_PART_MEDICAL_ID participan2_ where
participan2_.IDENTIFIER in (select site3_.IDENTIFIER from CATISSUE_SITE site3_ where site3_.NAME='Walt')))


Debug level Hibernate log excerpt:


When i execute the above HQL the SQL generated is incorrect. In above SQL you will see (last two lines) participan2_.IDENTIFIER is search in Site.IDENTIFIER where it should be participan2_.SITE_ID in (select site3_.IDENTIFIER from CATISSUE_SITE site3_ where site3_.NAME='Walt')))

Is the HQL i am executing not correct? Do anyone have confronted with such issue. Please help