[
https://issues.apache.org/jira/browse/OPENJPA-2895?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pawel Veselov updated OPENJPA-2895:
-----------------------------------
Description:
Here is how I stumbled upon this:
Code:
{code:java}
CriteriaQuery<Order> q = cb.createQuery(Order.class);
Root<Order> o = q.from(Order.class);
q.where(cb.function("COALESCE", Boolean.class, o.get(Order_.name),
cb.literal(false)));
q.select(o);
List<Order> orders = em.createQuery(q).getResultList();
{code}
Result of {{q.toString():}} (that looks quite OK)
{code:sql}
SELECT o FROM Order o WHERE COALESCE(o.name,1 <> 1)
{code}
What gets sent to Derby (output of JPA query log):
{noformat}
1162 test TRACE [main] openjpa.jdbc.SQL - <t 402978317, conn 981487964>
executing prepstmnt 1437983537 SELECT t0.id, t0.CNT, t1.id, t1.accountNum,
t2.id, t2.city, t2.country, t2.county, t2.state, t2.street, t3.userid,
t3.DTYPE, t3.age, t3.compName, t3.creditRating, t3.name, t2.zipCode,
t1.balanceOwed, t1.creditRating, t1.filledOrderCount, t1.firstName,
t1.lastName, t1.name, t1.status, t0.delivered, t0.name, t0.orderTs,
t0.quantity, t0.totalCost FROM CR_ODR t0 LEFT OUTER JOIN CR_CUST t1 ON
t0.CUSTOMER_ID = t1.id LEFT OUTER JOIN CR_ADDR t2 ON t1.ADDRESS_ID = t2.id LEFT
OUTER JOIN CompUser t3 ON t2.id = t3.ADD_ID WHERE (COALESCE(t0.name, ?) = ?)
[params=(int) 1, (int) 1]
1162 test TRACE [main] openjpa.jdbc.SQL - <t 402978317, conn 981487964> [0
ms] spent
{noformat}
Obviously, the query is wrong, as it inverted the result.
This is standing in the way of my testing OPENJPA-2894, as COALESCE is the only
boolean function I could think of that Derby has...
Mind you, the analysis below is over 3.1.2 code base, but so far that's the
version I'm sticking to.
The root cause is that the function argument is an expression, specifically, in
this case, a predicate. Because it's a function argument, it needs to be
translated to {{Value}}. There is a special method,
{{PredicateImpl.toValue()}}, which only works for a handful of predicates. It
definitely doesn't work on most binary operators.
{{PredicateImpl.toValue()}} returns TRUE (which is what happens here), if the
predicate has no parts. Otherwise, it throws a "not implemented" error.
However, a lot of predicates, at least {{BinaryLogicalExpression}}, which
{{Equals}}, and others, extend from, do not "add" their sub-expressions to
{{_exps}} list, and don't implement {{toValue{}}} method. Not only that's not
caught, it also causes bugs like this.
was:
I can't find the piece of code does this translation. But:
Code:
{code:java}
CriteriaQuery<Order> q = cb.createQuery(Order.class);
Root<Order> o = q.from(Order.class);
q.where(cb.function("COALESCE", Boolean.class, o.get(Order_.name),
cb.literal(false)));
q.select(o);
List<Order> orders = em.createQuery(q).getResultList();
{code}
Result of {{q.toString():}} (that looks quite OK)
{code:sql}
SELECT o FROM Order o WHERE COALESCE(o.name,1 <> 1)
{code}
What gets sent to Derby (output of JPA query log):
{noformat}
1162 test TRACE [main] openjpa.jdbc.SQL - <t 402978317, conn 981487964>
executing prepstmnt 1437983537 SELECT t0.id, t0.CNT, t1.id, t1.accountNum,
t2.id, t2.city, t2.country, t2.county, t2.state, t2.street, t3.userid,
t3.DTYPE, t3.age, t3.compName, t3.creditRating, t3.name, t2.zipCode,
t1.balanceOwed, t1.creditRating, t1.filledOrderCount, t1.firstName,
t1.lastName, t1.name, t1.status, t0.delivered, t0.name, t0.orderTs,
t0.quantity, t0.totalCost FROM CR_ODR t0 LEFT OUTER JOIN CR_CUST t1 ON
t0.CUSTOMER_ID = t1.id LEFT OUTER JOIN CR_ADDR t2 ON t1.ADDRESS_ID = t2.id LEFT
OUTER JOIN CompUser t3 ON t2.id = t3.ADD_ID WHERE (COALESCE(t0.name, ?) = ?)
[params=(int) 1, (int) 1]
1162 test TRACE [main] openjpa.jdbc.SQL - <t 402978317, conn 981487964> [0
ms] spent
{noformat}
Obviously, the query is wrong, as it inverted the result. IDK if this
reproduces on other DBs, but it might since I don't see any special code to
handle COALESCE in Derby/DB2 DB dictionary.
This is standing in the way of my testing OPENJPA-2894, as COALESCE is the only
boolean function I could think of that Derby has...
> Predicates largely can not be used as expressions
> -------------------------------------------------
>
> Key: OPENJPA-2895
> URL: https://issues.apache.org/jira/browse/OPENJPA-2895
> Project: OpenJPA
> Issue Type: Bug
> Components: criteria, jdbc
> Affects Versions: 3.1.2, 3.2.0
> Reporter: Pawel Veselov
> Priority: Major
>
> Here is how I stumbled upon this:
> Code:
> {code:java}
> CriteriaQuery<Order> q = cb.createQuery(Order.class);
> Root<Order> o = q.from(Order.class);
> q.where(cb.function("COALESCE", Boolean.class,
> o.get(Order_.name), cb.literal(false)));
> q.select(o);
> List<Order> orders = em.createQuery(q).getResultList();
> {code}
> Result of {{q.toString():}} (that looks quite OK)
> {code:sql}
> SELECT o FROM Order o WHERE COALESCE(o.name,1 <> 1)
> {code}
> What gets sent to Derby (output of JPA query log):
> {noformat}
> 1162 test TRACE [main] openjpa.jdbc.SQL - <t 402978317, conn 981487964>
> executing prepstmnt 1437983537 SELECT t0.id, t0.CNT, t1.id, t1.accountNum,
> t2.id, t2.city, t2.country, t2.county, t2.state, t2.street, t3.userid,
> t3.DTYPE, t3.age, t3.compName, t3.creditRating, t3.name, t2.zipCode,
> t1.balanceOwed, t1.creditRating, t1.filledOrderCount, t1.firstName,
> t1.lastName, t1.name, t1.status, t0.delivered, t0.name, t0.orderTs,
> t0.quantity, t0.totalCost FROM CR_ODR t0 LEFT OUTER JOIN CR_CUST t1 ON
> t0.CUSTOMER_ID = t1.id LEFT OUTER JOIN CR_ADDR t2 ON t1.ADDRESS_ID = t2.id
> LEFT OUTER JOIN CompUser t3 ON t2.id = t3.ADD_ID WHERE (COALESCE(t0.name, ?)
> = ?) [params=(int) 1, (int) 1]
> 1162 test TRACE [main] openjpa.jdbc.SQL - <t 402978317, conn 981487964> [0
> ms] spent
> {noformat}
> Obviously, the query is wrong, as it inverted the result.
> This is standing in the way of my testing OPENJPA-2894, as COALESCE is the
> only boolean function I could think of that Derby has...
> Mind you, the analysis below is over 3.1.2 code base, but so far that's the
> version I'm sticking to.
> The root cause is that the function argument is an expression, specifically,
> in this case, a predicate. Because it's a function argument, it needs to be
> translated to {{Value}}. There is a special method,
> {{PredicateImpl.toValue()}}, which only works for a handful of predicates. It
> definitely doesn't work on most binary operators.
> {{PredicateImpl.toValue()}} returns TRUE (which is what happens here), if the
> predicate has no parts. Otherwise, it throws a "not implemented" error.
> However, a lot of predicates, at least {{BinaryLogicalExpression}}, which
> {{Equals}}, and others, extend from, do not "add" their sub-expressions to
> {{_exps}} list, and don't implement {{toValue{}}} method. Not only that's not
> caught, it also causes bugs like this.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
