Major Description
Here is how I stumbled upon this:
Code:
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();
Result of q.toString(): (that looks quite OK)
SELECT o FROM Order o WHERE COALESCE(o.name,1 <> 1)
What gets sent to Derby (output of JPA query log):
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
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. Since cb.literal(true) is actual implemented using Expression.Equal, which is an instance of PredicateImpl, it's a predicate.
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.