[OPENJPA-728] SubQuery with Exists generates invalid SQL

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Duplicate
• Affects Version/s: 1.0.2, 1.0.3, 1.1.0, 1.2.0
• Component/s: query
• Environment: Windows XP, JDK 1.6.0_07, Derby and HSQL.
• Assignee: Michael Dick
• Reporter: Bjorn Beskow
• Created: 18/Sep/08 11:10
• Updated: 07/Apr/10 15:46
• Resolved: 23/Sep/08 20:58

Description

When using a subquery containing EXISTS, the generated SQL contains table aliases that does not exist in the select statement. For example, the following query:

SELECT new test.dto.EmployeeReportDTO(e.name, a.city)
FROM Employee e JOIN e.address a
WHERE e.name LIKE :name
AND EXISTS (SELECT p.number from PhoneNumber p WHERE p.owner = e)

will cause the following exception:

Column not found: T3.CITY in statement [SELECT t0.name, t3.city FROM Employee t0 INNER JOIN Address t1 ON t0.address_id = t1.id WHERE (t0.name LIKE ? ESCAPE '\' AND NOT (EXISTS (SELECT t2.number FROM Address t1, PhoneNumber t2 WHERE (t2.owner_id = t0.id))))]

Issue Links

• duplicates: OPENJPA-681 Subquery generates SQL with syntax error