Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (nike.apache.org: message received from 54.76.25.247 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///y7ACAACElAP//2kbg
Date: Thu, 23 Apr 2015 14:07:17 +0000
Message-ID: <2FFAFD73C8FCEA4FB07C5E997664B1A039CE112C@HMS11.hm.local>
References: <2FFAFD73C8FCEA4FB07C5E997664B1A039CE10AD@HMS11.hm.local>
	<2FFAFD73C8FCEA4FB07C5E997664B1A039CE10CA@HMS11.hm.local>
	<C9FB0D6F-7BE6-436A-B317-8434EE06989C@yahoo.de>
 <CAP=qHCusLDxrAyMDz6asrJq8OqU4ZzPbX0jnLaR5L_sBSdhgRg@mail.gmail.com>
In-Reply-To: 
 <CAP=qHCusLDxrAyMDz6asrJq8OqU4ZzPbX0jnLaR5L_sBSdhgRg@mail.gmail.com>
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

Sorry, I haven't looked at the code base, I'm still an OpenJPA "user" not a=
 developer.

I submitted a unit test in the bug report OPENJPA-2578 criteria API "group =
by" creates SQL with a parameter for a literal but does not provide it's va=
lue. (Requires a trivial adjustment to be an official OpenJPA unit test).

The workaround works for me but I guess this should still be fixed because =
that forces you to use the mentioned query hint and OpenJPA 2.4.0 when usin=
g boolean literals.

I downloaded OpenJPA 2.4.0 today through Maven although the website doesn't=
 yet show it as released.
Now I can move forwards to using Java 8. Good to see that all of my existin=
g (Java 7) tests that passed on 2.3.0 also pass in 2.4.0.

Henno


-----Oorspronkelijk bericht-----
Van: Rick Curtis [mailto:curtisr7@gmail.com]=20
Verzonden: donderdag 23 april 2015 15:42
Aan: users
Onderwerp: Re: criteria API generates a parameter for literal in group by b=
ut does not provide the value

> Shall I report this as a bug or am I doing something wrong in my code?
I vote bug

On Thu, Apr 23, 2015 at 6:43 AM, Mark Struberg <struberg@yahoo.de> wrote:

> Thanks Henno!
>
> Not quite sure if this workaround is good enough or whether we should=20
> try to solve this properly.
> I plan to do a follow up release for 2.4.0 rather soonish. So thanks=20
> for your test case.
>
> Did you already look at the OpenJPA codebase? Are you interested in=20
> turning this sample code into a unit test?
>
>
> txs and LieGrue,
> strub
>
>
>
> > Am 23.04.2015 um 12:32 schrieb Henno Vermeulen <henno@huizemolenaar.nl>=
:
> >
> > One addition (my question is still open).
> >
> > I can confirm that a valid workaround for this problem is to use
> setHint("openjpa.hint.UseLiteralInSQL", "true") and updating to=20
> OpenJPA
> 2.4.0 which is available in Maven central since a few days.
> >
> > Henno
> >
> > -----Oorspronkelijk bericht-----
> > Van: Henno Vermeulen [mailto:henno@huizemolenaar.nl]
> > Verzonden: donderdag 23 april 2015 11:49
> > Aan: users@openjpa.apache.org
> > Onderwerp: criteria API generates a parameter for literal in group=20
> > by
> but does not provide the value
> >
> > Hello,
> >
> > I have a query created using the criteria API where I group by an
> expression that contains a small calculation using literal values.
> >
> > OpenJPA generates the correct SQL but does not provide the value of=20
> > the
> generated parameter in the group by clause. The query fails with a SQL=20
> exception "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=20
> the average length grouped by the person's age / 10:
> >
> >                        CriteriaBuilder cb =3D em.getCriteriaBuilder();
> >                        CriteriaQuery<Double> query =3D
> cb.createQuery(Double.class);
> >                        Root<Person> person =3D=20
> > query.from(Person.class);
> >
> >                        Expression<Double> averageLength =3D
> cb.avg(person.<Integer> get("length"));
> >                        CriteriaQuery<Double> select =3D
> query.select(averageLength);
> >
> >                        select.groupBy(cb.quot(person.<Integer>
> get("age"), cb.literal(10)));
> >                        // optional where, useful to ensure=20
> > parameters
> are logged
> >                        select.where(cb.gt(person.<Integer>=20
> > get("age"),
> cb.literal(20)));
> >
> >                        System.out.println("result: " +
> em.createQuery(query).getResultList());
> >
> > Whe running this query with trace and displaying parameters on I get:
> >
> > 1067  testPU  TRACE  [main] openjpa.Query - Executing query: Query:
> org.apache.openjpa.kernel.QueryImpl@be4f81; candidate class: class=20
> entities.Person; query: null
> > 1108  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 5763249, conn=20
> > 7326702>
> executing prepstmnt 26531336 SELECT AVG(t0.length) FROM Person t0=20
> WHERE (t0.age > ?) GROUP BY (t0.age / ?) [params=3D(int) 20]
> >
> > You can clearly see that the query has two parameter placeholders=20
> > 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=20
> because there is a bug where boolean literals aren't correctly handled:
> https://issues.apache.org/jira/browse/OPENJPA-2534. I think this is=20
> solved in the upcoming release.)
> >
> > Thank you,
> > Henno
> >
>
>


--
*Rick Curtis*