SQL generated by HQL update query has the wrong aliases. Causeing GenericADO Exception

Description

I have a largish HQL query that I am trying to execute (table names have been changed to protect the innocent). When I call ExecuteUpdate on the below HQL I get a GenericADOException. The inner exception states "The multi-part identifier "memberinfo0_.MemberID" could not be bound."

UPDATE MemberInfo as memberInfo
SET memberInfo.Drafted = raftedVal
WHERE memberInfo.ManagedTeamID = :teamID
AND (memberInfo.ManagedMemberID IN (SELECT distinct NFLElig.MemberInfo.ManagedMemberID as memberId
FROM NFLEligibilityInfo NFLElig
WHERE NFLElig.ManagedPlayOptionInfo.ManagedApplicationID in ( :applicationApproved0))
OR memberInfo.ManagedMemberID NOT IN
(SELECT distinct NFLElig.MemberInfo.ManagedMemberID as memberId
FROM NFLEligibilityInfo NFLElig))

AND memberInfo.ManagedMemberID NOT IN
(SELECT distinct NFLElig.MemberInfo.ManagedMemberID as memberId
FROM NFLEligibilityInfo NFLElig
WHERE NFLElig.ManagedPlayOptionInfo.ManagedApplicationID in ( :applicationUnApproved0))

AND (memberInfo.ManagedMemberID IN (SELECT distinct CoachElig.ManagedMemberID as memberId
FROM CoachEligibilityInfo CoachElig
WHERE CoachElig.ManagedPlayOpID in ( layApproved0))
OR memberInfo.ManagedMemberID NOT IN
(SELECT distinct CoachElig.ManagedMemberID as memberId
FROM CoachEligibilityInfo CoachElig))

AND memberInfo.ManagedMemberID NOT IN
(SELECT distinct CoachElig.ManagedMemberID as memberId
FROM CoachEligibilityInfo CoachElig
WHERE CoachElig.ManagedPlayOpID in ( layUnApproved0))

AND (LOWER(memberInfo.SSN) LIKE :searchText
OR LOWER(memberInfo.HICN) LIKE :searchText
OR LOWER(memberInfo.ContractID) LIKE :searchText
OR LOWER(memberInfo.FirstName) LIKE :searchText
OR LOWER(memberInfo.LastName) LIKE :searchText
OR LOWER(memberInfo.LastName + ' ' + memberInfo.FirstName) LIKE :searchText
OR LOWER(memberInfo.FirstName + ' ' + memberInfo.LastName) LIKE :searchText
OR LOWER(memberInfo.LastName + ', ' + memberInfo.FirstName) LIKE :searchText
OR LOWER(memberInfo.Tags) LIKE :searchText
OR memberInfo.ManagedMemberID LIKE :searchText)

AND memberInfo.Drafted >= 0

Here is the SQL that it's spitting out along with the error.

update _Players
set Drafted=?
where TeamID=?

and (MemberID in (select distinct nfleligibi1_.MemberID
from NFL_EligPeriods nfleligibi1, PlayOp playopt2
where nfleligibi1_.PlayOpID=playopt2_.PlayOpID
and (playopt2_.ApplicationID in ))
or MemberID not in
(select distinct nfleligibi3_.MemberID
from NFL_EligPeriods nfleligibi3))

and (memberinfo0_.MemberID not in
(select distinct nfleligibi4_.MemberID
from NFL_EligPeriods nfleligibi4, PlayOp playopt5
where nfleligibi4_.PlayOpID=playopt5_.PlayOpID
and (playopt5_.ApplicationID in )))

and (MemberID in (select distinct coacheligib6_.MemberID
from Coach_EligPeriods coacheligib6
where coacheligib6_.PlayOpID in )
or MemberID not in
(select distinct coacheligib7_.MemberID
from Coach_EligPeriods coacheligib7))

and (memberinfo0_.MemberID not in
(select distinct coacheligib8_.MemberID
from Coach_EligPeriods coacheligib8
where coacheligib8_.PlayOpID in ))

and (lower(PlayerNum) like ?
or lower(PlayerICN) like ?
or lower(PlayerContractID) like ?
or lower(FName) like ?
or lower(LName) like ?
or lower(LName+' '+FName) like ?
or lower(FName+' '+LName) like ?
or lower(LName+', '+FName) like ?
or lower(Tags) like ?
or MemberID like ?)

and Drafted>=0

It looks like this is caused by the HQL parser using the alias for _Players inconsistently. The piece that leads me to believe this is an HQL bug is that if I re-order my sub-queries the alias issue doesn't stay with the same sub-queries.... Hard to word that but basically the bad alias will end up on the third sub-query consistently instead of following 1 sub-query when I move it.

In trying different iterations of my HQL it seems like this problem only comes up when I am using a number of sub queries.

The only workaround that I have been able to find is to stop using the alias in my HQL and instead replace the entity's property with the SQL column name that it is mapped to.

If you need clarification please ask, or if you spot the obvious mistake I am making please let me know.

Environment

None

Activity

Show:
Oskar Berggren
November 14, 2012 at 12:06 PM

Closed - not productive to analyze without minimal test case from user.

Oskar Berggren
April 26, 2012 at 6:13 PM

It would be really helpful if you could create a test case that reproduces the problem and which is suitable to integrate in the NH test suite.

Incomplete

Details

Assignee

Reporter

Labels

Components

Affects versions

Priority

Who's Looking?

Open Who's Looking?
Created April 26, 2012 at 5:27 PM
Updated November 14, 2012 at 12:06 PM
Resolved November 14, 2012 at 12:06 PM
Who's Looking?