openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bjorn Beskow (JIRA)" <j...@apache.org>
Subject [jira] Reopened: (OPENJPA-728) SubQuery with Exists generates invalid SQL
Date Tue, 23 Sep 2008 12:04:44 GMT

     [ 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.


Mime
View raw message