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