Pawel Veselov created OPENJPA-2895:
--------------------------------------
Summary: Derby(?) COALESCE functions are translated incorrectly
Key: OPENJPA-2895
URL: https://issues.apache.org/jira/browse/OPENJPA-2895
Project: OpenJPA
Issue Type: Bug
Components: criteria, jdbc
Affects Versions: 3.2.0, 3.1.2
Reporter: Pawel Veselov
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.
This is standing in the way of my testing OPENJPA-2876, as COALESCE is the only
boolean function I could think of that Derby has...
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
