[
https://issues.apache.org/jira/browse/OPENJPA-2895?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pawel Veselov updated OPENJPA-2895:
-----------------------------------
Summary: Predicates largely can not be used as value expressions (was:
Predicates largely can not be used as expressions)
> Predicates largely can not be used as value 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)
