Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (athena.apache.org: message received from 54.191.145.13
 which is an MX secondary for users@openjpa.apache.org)
From: Henno Vermeulen <henno@huizemolenaar.nl>
To: "users@openjpa.apache.org" <users@openjpa.apache.org>
Subject: RE: criteria API generates a parameter for literal in group by but
 does not provide the value
Thread-Topic: criteria API generates a parameter for literal in group by but
 does not provide the value
Thread-Index: AdB9qPU9Gs3qPgB/Q2GliVCdp7iTPQAB5BKw
Date: Thu, 23 Apr 2015 10:32:34 +0000
Message-ID: <2FFAFD73C8FCEA4FB07C5E997664B1A039CE10CA@HMS11.hm.local>
References: <2FFAFD73C8FCEA4FB07C5E997664B1A039CE10AD@HMS11.hm.local>
In-Reply-To: <2FFAFD73C8FCEA4FB07C5E997664B1A039CE10AD@HMS11.hm.local>
Accept-Language: nl-NL, en-US
Content-Language: nl-NL
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

One addition (my question is still open).

I can confirm that a valid workaround for this problem is to use setHint("o=
penjpa.hint.UseLiteralInSQL", "true") and updating to OpenJPA 2.4.0 which i=
s available in Maven central since a few days.

Henno

-----Oorspronkelijk bericht-----
Van: Henno Vermeulen [mailto:henno@huizemolenaar.nl]=20
Verzonden: donderdag 23 april 2015 11:49
Aan: users@openjpa.apache.org
Onderwerp: criteria API generates a parameter for literal in group by but d=
oes not provide the value

Hello,

I have a query created using the criteria API where I group by an expressio=
n that contains a small calculation using literal values.

OpenJPA generates the correct SQL but does not provide the value of the gen=
erated parameter in the group by clause. The query fails with a SQL excepti=
on "The value is not set for the parameter number 9.".

I can reproduce the issue with a minimal example. Suppose we have a person =
class with integer age and length columns and we wish to select the average=
 length grouped by the person's age / 10:

                        CriteriaBuilder cb =3D em.getCriteriaBuilder();
                        CriteriaQuery<Double> query =3D cb.createQuery(Doub=
le.class);
                        Root<Person> person =3D query.from(Person.class);

                        Expression<Double> averageLength =3D cb.avg(person.=
<Integer> get("length"));
                        CriteriaQuery<Double> select =3D query.select(avera=
geLength);

                        select.groupBy(cb.quot(person.<Integer> get("age"),=
 cb.literal(10)));
                        // optional where, useful to ensure parameters are =
logged
                        select.where(cb.gt(person.<Integer> get("age"), cb.=
literal(20)));

                        System.out.println("result: " + em.createQuery(quer=
y).getResultList());

Whe running this query with trace and displaying parameters on I get:

1067  testPU  TRACE  [main] openjpa.Query - Executing query: Query: org.apa=
che.openjpa.kernel.QueryImpl@be4f81; candidate class: class entities.Person=
; query: null
1108  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 5763249, conn 7326702> ex=
ecuting prepstmnt 26531336 SELECT AVG(t0.length) FROM Person t0 WHERE (t0.a=
ge > ?) GROUP BY (t0.age / ?) [params=3D(int) 20]

You can clearly see that the query has two parameter placeholders but only =
one value is provided.
Shall I report this as a bug or am I doing something wrong in my code?

(As a workaround I can call setHint("openjpa.hint.UseLiteralInSQL", "true")=
 on em.createQuery(query). This doesn't work in my application because ther=
e is a bug where boolean literals aren't correctly handled: https://issues.=
apache.org/jira/browse/OPENJPA-2534. I think this is solved in the upcoming=
 release.)

Thank you,
Henno