|
Consider the following HQL query:
FROM things.Club c
WHERE EXISTS
(SELECT 1 FROM c.members m
WHERE EXISTS
(SELECT 1 FROM m.favoriteColors fc WHERE fc='Red')
)
i.e. A Club has many Members and each Member can have many favouriteColors (Strings). So, my HQL is looking for Clubs that have a member who likes red.
The DB schema looks like this:
CREATE TABLE [dbo].[CLUB](
[CLUB_ID] [numeric](19, 0) NULL,
[NAME] [nvarchar](200) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[MEMBER](
[MEMBER_ID] [numeric](19, 0) NULL,
[NAME] [nvarchar](200) NULL,
[CLUB_MEMBERS_ID] [numeric](19, 0) NULL,
[CLUB_MEMBERS_IDX] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[FAV_COLOR](
[MEMBER_FC_ID] [numeric](19, 0) NULL,
[MEMBER_FC_IDX] [int] NULL,
[COLOR] [nvarchar](200) NULL
) ON [PRIMARY]
The Hibernate mappings look like this:
<class name="things.Club" entity-name="things.Club" table="CLUB">
<id name="id" column="CLUB_ID" type="long">
<generator class="assigned"/>
</id>
<property name="name" column="NAME" type="string"/>
<list name="members" lazy="true" cascade="all,delete-orphan">
<key update="true">
<column name="CLUB_MEMBERS_ID" not-null="false" unique="false"/>
</key>
<list-index column="CLUB_MEMBERS_IDX"/>
<one-to-many entity-name="things.Member"/>
</list>
</class>
<class name="things.Member" entity-name="things.Member" table="MEMBER">
<id name="id" column="MEMBER_ID" type="long">
<generator class="assigned"/>
</id>
<property name="name" column="NAME" type="string"/>
<list name="favoriteColors" table="FAV_COLOR" lazy="true" cascade="all,delete-orphan">
<key update="true">
<column name="MEMBER_FC_ID" not-null="true" unique="false"/>
</key>
<list-index column="MEMBER_FC_IDX"/>
<element type="java.lang.String" not-null="false" unique="false">
<column not-null="false" unique="false" name="COLOR" length="400"/>
</element>
</list>
</class>
I'm seeing a problem when SQL is generated from the HQL:
select club0_.CLUB_ID as CLUB_ID1_0_, club0_.NAME as NAME2_0_ from CLUB club0_
where exists (select 1 from where
club0_.CLUB_ID=members1_.CLUB_MEMBERS_ID
and (exists (select 1 from FAV_COLOR favoriteco2_
where members1_.MEMBER_ID=favoriteco2_.MEMBER_FC_ID
and favoriteco2_.COLOR='Red')))
Notice the 'from where' in the first sub-query. The 'from' clause is empty. it should be 'from MEMBER members1_'.
The attached file contains everything needed to reproduce the problem. I've not included the 'lib' folder, into which you need to place the Hibernate JARs and JDBC driver.
|
