[jira] [Created] (OPENJPA-2895) Derby(?) COALESCE functions are translated incorrectly

["Pawel Veselov (Jira)" <ji...@apache.org>]

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)