You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Henno Vermeulen <he...@huizemolenaar.nl> on 2015/04/23 11:48:36 UTC
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
Re: criteria API generates a parameter for literal in group by but does not provide the value
Posted by Mark Struberg <st...@yahoo.de>.
Yes, we still have to get the docs and site done.
Txs for your unit test. Will try to give it a run in the next 2 days.
LieGrue,
strub
> Am 23.04.2015 um 16:07 schrieb Henno Vermeulen <he...@huizemolenaar.nl>:
>
> 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 value. (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 using 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 existing (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]
> Verzonden: donderdag 23 april 2015 15:42
> Aan: users
> Onderwerp: Re: criteria API generates a parameter for literal in group by but 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 <st...@yahoo.de> wrote:
>
>> 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 <he...@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
>>>
>>
>>
>
>
> --
> *Rick Curtis*
RE: criteria API generates a parameter for literal in group by but
does not provide the value
Posted by Henno Vermeulen <he...@huizemolenaar.nl>.
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 value. (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 using 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 existing (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]
Verzonden: donderdag 23 april 2015 15:42
Aan: users
Onderwerp: Re: criteria API generates a parameter for literal in group by but 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 <st...@yahoo.de> wrote:
> 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 <he...@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
> >
>
>
--
*Rick Curtis*
Re: criteria API generates a parameter for literal in group by but
does not provide the value
Posted by Rick Curtis <cu...@gmail.com>.
> 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 <st...@yahoo.de> wrote:
> 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 <he...@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
> >
>
>
--
*Rick Curtis*
Re: criteria API generates a parameter for literal in group by but does not provide the value
Posted by Mark Struberg <st...@yahoo.de>.
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 <he...@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
>
RE: criteria API generates a parameter for literal in group by but
does not provide the value
Posted by Henno Vermeulen <he...@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