[ https://issues.apache.org/jira/browse/OPENJPA-83?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Patrick Linskey updated OPENJPA-83: ----------------------------------- Fix Version/s: 0.9.7 > Bad SQL for Subselect BETWEEN > ----------------------------- > > Key: OPENJPA-83 > URL: https://issues.apache.org/jira/browse/OPENJPA-83 > Project: OpenJPA > Issue Type: Bug > Components: query > Environment: openJPA 0.9.7 > MySQL 5.0.15 > Reporter: Jakob Braeuchi > Fix For: 0.9.7 > > > 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. - You can reply to this email to add a comment to the issue online.