[jira] Updated: (OPENJPA-83) Bad SQL for Subselect BETWEEN

["Patrick Linskey (JIRA)" <ji...@apache.org>]

     [ 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.