openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Dick (JIRA)" <j...@apache.org>
Subject [jira] Resolved: (OPENJPA-728) SubQuery with Exists generates invalid SQL
Date Thu, 18 Sep 2008 14:52:44 GMT

     [ https://issues.apache.org/jira/browse/OPENJPA-728?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Michael Dick resolved OPENJPA-728.
----------------------------------

    Resolution: Invalid

Hi Bjorn,

The problem here is that you haven't set the relationship on the owning side. In your example
PhoneNumber is the owner of a bidirectional relationship with Employee, the testcase never
sets this side of the relationship and therefore the relationship is never stored in the database
(and is unavailable for your query). 

Adding something like this will resolve the problem : 
        . . .
        e1.getPhoneNumbers().add(phoneNumber1);
        e1.getPhoneNumbers().add(phoneNumber2);
        
        phoneNumber1.setOwner(e1);
        phoneNumber2.setOwner(e1);
        
        em.persist(e1);
        e2 = new Employee();
        . . .



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