[OPENJPA-1283] Incorrect SQL for JPQL query selecting Map value from an elementCollection of a Map and the map the value is an embeddable

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0.0-M2
• Fix Version/s: 2.0.0-M3
• Assignee: Catalina Wei
• Reporter: Catalina Wei
• Created: 02/Sep/09 21:14
• Updated: 09/Mar/10 18:31
• Resolved: 03/Sep/09 00:55

Description

SELECT VALUE(f) FROM Policy p, IN (p.funds) f WHERE f.fundName = :fName and p.annuityHolderId = :holderId

Where Policy has an ElementCollection as a Map:

@ElementCollection(targetClass = Fund.class, fetch=FetchType.EAGER)
@CollectionTable(name="POLICY_FUNDS",joinColumns=

{@JoinColumn(name="FK_POLICY_ID")}

)
@MapKeyColumn(name = "FUND_ID")
public Map<Integer, IFund> getFunds()

{ return Funds; }

The generated SQL was incorrect:

SELECT t2.id FROM INSPOLICY t0
INNER JOIN POLICY_FUNDS t1 ON t0.id = t1.FK_POLICY_ID,
INSPOLICY t2
WHERE (t1.fundName = ? AND t0.FK_ANNUITY_HOLDER_ID = ?)

The correct SQL should projecting out t1.*.