Bad Subselect SQL for BETWEEN ----------------------------- Key: OPENJPA-82 URL: http://issues.apache.org/jira/browse/OPENJPA-82 Project: OpenJPA Issue Type: Bug Components: site Environment: openJPA 0.9.7 MySQL 5.0.15 Reporter: Jakob Braeuchi
the following query generates an sql with no table in the FROM-clause of the subselct: em.createQuery("select k from Kauf k where " + "((select sum(p.betrag) from Posten p where p.kauf = k) between :betrVon and :betrBis) " + "order by k.datum asc"); SELECT t1.id, t1.bemerkung, t1.datum, t2.id, t2.idEinkauferAlt, t2.name FROM ekv2kauf t1 LEFT OUTER JOIN ekv2einkaufer t2 ON t1.idEinkaufer = t2.id WHERE ((SELECT SUM(t0.betrag) FROM WHERE (t0.idKauf = t1.id)) >= ? AND (SELECT SUM(t0.betrag) FROM WHERE (t0.idKauf = t1.id)) <= ?) ORDER BY t1.datum ASC [params=(double) 1800.0, (double) 3000.0] when i use the query without BETWEEN it works: em.createQuery("select k from Kauf k where " + "((select sum(p.betrag) from Posten p where p.kauf = k) > :betr) " + "order by k.datum asc"); SELECT t1.id, t1.bemerkung, t1.datum, t2.id, t2.idEinkauferAlt, t2.name FROM ekv2kauf t1 LEFT OUTER JOIN ekv2einkaufer t2 ON t1.idEinkaufer = t2.id WHERE ((SELECT SUM(t0.betrag) FROM ekv2posten t0 WHERE (t0.idKauf = t1.id)) > ?) ORDER BY t1.datum ASC [params=(double) 1800.0] when i rewrite the query using groupby / having it also works, but the generated sql does not use BETWEEN: em.createQuery("select p.kauf from Posten p " + "group by p.kauf " + "having sum(p.betrag) between :betrVon and :betrBis " + "order by p.kauf.datum asc"); SELECT t1.id, t1.bemerkung, t1.datum, t1.idEinkaufer FROM ekv2posten t0 INNER JOIN ekv2kauf t1 ON t0.idKauf = t1.id GROUP BY t1.id, t1.bemerkung, t1.datum, t1.idEinkaufer HAVING SUM(t0.betrag) >= ? AND SUM(t0.betrag) <= ? ORDER BY t1.datum ASC [params=(double) 1800.0, (double) 3000.0] -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira