openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Albert Lee (JIRA)" <j...@apache.org>
Subject [jira] [Created] (OPENJPA-2289) Additional SQL alias generated for query with subquery causes incorrect # of rows returned - Oracle only
Date Wed, 31 Oct 2012 18:42:11 GMT
Albert Lee created OPENJPA-2289:
-----------------------------------

             Summary: Additional SQL alias generated for query with subquery causes incorrect
# of rows returned - Oracle only
                 Key: OPENJPA-2289
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2289
             Project: OpenJPA
          Issue Type: Bug
          Components: sql
    Affects Versions: 2.2.0, 2.1.1, 2.0.1, 2.3.0
            Reporter: Albert Lee
            Assignee: Albert Lee


                .createQuery("SELECT e FROM MaxQueryEntity e, MaxQueryMapEntity map "
                    + "WHERE map.selectCriteria = 'B3' AND map.refEntity = e "
                    + "  AND e.revision = ( SELECT MAX(e_.revision)"
                    + "                     FROM MaxQueryEntity e_"
                    + "                     WHERE e_.domainId = e.domainId )"
                    + "  AND map.revision = ( SELECT MAX(map_.revision)"
                    + "                       FROM MaxQueryMapEntity map_"
                    + "                       WHERE map_.refEntity = map.refEntity )");  
     


On Oracle we generate SQL like this on 2.0.x+:

SELECT t1.id, t1.domainId, t1.revision FROM OPENJPA_MAXQUERY_MAPENTITY t0, OPENJPA_MAXQUERY_ENTITY
t1, OPENJPA_MAXQUERY_MAPENTITY t4 WHERE (t0.selectCriteria = ? AND t0.refEntity = t1.id AND
t1.revision = (SELECT MAX(t2.revision) FROM OPENJPA_MAXQUERY_ENTITY t2 WHERE (t2.domainId
= t1.domainId)) AND t0.revision = (SELECT MAX(t3.revision) FROM OPENJPA_MAXQUERY_MAPENTITY
t3 WHERE (t3.refEntity = t4.refEntity))) [params=(String) B3]

The additional alias "OPENJPA_MAXQUERY_MAPENTITY t4" caused more unexpected rows to return.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message