[ 
https://issues.apache.org/jira/browse/OPENJPA-2895?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Pawel Veselov updated OPENJPA-2895:
-----------------------------------
    Description: 
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...

  was:
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...


> 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.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)

Reply via email to