[
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 expressions (was: Derby(?)
COALESCE functions are translated incorrectly)
> 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
>
> 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...
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
