[OPENJPA-82] Bad Subselect SQL for BETWEEN

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Invalid
• Component/s: site
• Environment: openJPA 0.9.7; MySQL 5.0.15
• Assignee: Unassigned
• Reporter: Jakob Braeuchi
• Created: 15/Nov/06 19:39
• Updated: 09/Mar/10 18:35
• Resolved: 15/Nov/06 19:52

Description

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]