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
