[
https://issues.apache.org/jira/browse/OPENJPA-728?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Bjorn Beskow reopened OPENJPA-728:
----------------------------------
Yes, you are right, the testcase was sloppy in that it did not set the inverse
side of the relationship. But that would only affect the result set. I'm afraid
that is unrelated to the real problem: The JPQL query causes *syntactically*
invalid SQL to be generated (using a table alias which is not listed in the
FROM part):
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 EXISTS (SELECT t2.number FROM Address t1, PhoneNumber t2 WHERE
(t2.owner_id = t0.id))) ORDER BY t0.name ASC] {SELECT t0.name, t3.city FROM
Employee t0 INNER JOIN Address t1 ON t0.address_id = t1.id WHERE (t0.name LIKE
? ESCAPE '\' AND EXISTS (SELECT t2.number FROM Address t1, PhoneNumber t2 WHERE
(t2.owner_id = t0.id))) ORDER BY t0.name ASC} [code=-28, state=S0022]
The table alias t3 generated in the SELECT part (t3.city) does not exist in the
from clause. t3.city is wrong, it should really be t1.city.
I have updated the test project, to show that the problem still exists.
> SubQuery with Exists generates invalid SQL
> ------------------------------------------
>
> Key: OPENJPA-728
> URL: https://issues.apache.org/jira/browse/OPENJPA-728
> Project: OpenJPA
> Issue Type: Bug
> Components: query
> Affects Versions: 1.0.2, 1.0.3, 1.1.0, 1.2.0
> Environment: Windows XP, JDK 1.6.0_07, Derby and HSQL.
> Reporter: Bjorn Beskow
> Assignee: Michael Dick
> Priority: Critical
> Attachments: openjpa-bug.zip
>
>
> 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)
> 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))))]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
