openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Struberg <strub...@yahoo.de>
Subject Re: criteria API generates a parameter for literal in group by but does not provide the value
Date Thu, 23 Apr 2015 11:43:42 GMT
Thanks Henno!

Not quite sure if this workaround is good enough or whether we should try to solve this properly.
I plan to do a follow up release for 2.4.0 rather soonish. So thanks for your test case.

Did you already look at the OpenJPA codebase? Are you interested in 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 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 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 the generated parameter
in the group by clause. The query fails with a SQL 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 the average length grouped by the person's
age / 10:
> 
>                        CriteriaBuilder cb = em.getCriteriaBuilder();
>                        CriteriaQuery<Double> query = cb.createQuery(Double.class);
>                        Root<Person> person = query.from(Person.class);
> 
>                        Expression<Double> averageLength = cb.avg(person.<Integer>
get("length"));
>                        CriteriaQuery<Double> select = query.select(averageLength);
> 
>                        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(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 entities.Person; query: null
> 1108  testPU  TRACE  [main] openjpa.jdbc.SQL - <t 5763249, conn 7326702> executing
prepstmnt 26531336 SELECT AVG(t0.length) FROM Person t0 WHERE (t0.age > ?) GROUP BY (t0.age
/ ?) [params=(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 there 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
> 


Mime
View raw message