openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jakob Braeuchi (JIRA)" <j...@apache.org>
Subject [jira] Created: (OPENJPA-82) Bad Subselect SQL for BETWEEN
Date Wed, 15 Nov 2006 19:39:37 GMT
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

        

Mime
View raw message