Hi Tom,
I was able to reproduce issue 592 using a TopLink UpdateAllQuery. Here
is the sample code:
UpdateAllQuery update = new UpdateAllQuery(Customer.class);
update.setShouldDeferExecutionInUOW(false);
ExpressionBuilder cBuilder = new ExpressionBuilder(Customer.class);
update.setExpressionBuilder(cBuilder);
update.addUpdate(cBuilder.get("name"), new
ExpressionBuilder().value("CHANGED"));
ReportQuery subquery = new ReportQuery(Customer.class, cBuilder);
subquery.addCount("COUNT", cBuilder.anyOf("orders"), Long.class);
update.setSelectionCriteria(cBuilder.subQuery(subquery).greaterThan(0));
Object result = em.getActiveSession().executeQuery(update);
It results in the same SQL as the JPQL query from 592:
UPDATE Customer c SET c.name = 'CHANGED'
WHERE (SELECT COUNT(o) FROM c.orders o) > 0
Regards Michael
> Hi Tom,
>
> I closed the NPE issue (572) and filed a new bug for the issue with
> the wrong number of updated rows:
> https://glassfish.dev.java.net/issues/show_bug.cgi?id=592
>
> I just noticed that the new entity-persistence-tests JPQL test class
> included in the attached jar of my previous mail still prints some
> debug messages to show the above issue.
>
> Regards Michael
>
>> Hi Tom,
>>
>>> Hi Michael,
>>>
>>> Your changes look good. Go ahead and check in.
>>
>>
>> thanks, I checked them into the trunk.
>>
>> I looked at adding a new JPQL test class to entity-persistence-tests
>> running UPDATE and DELETE queries in order to verify my fix for issue
>> 572. While doing this I ran into a problem with the generated SQL for
>> an JPQL UPDATE query having a subquery: it updates too many rows.
>> Here is a sample JPQL and the generated SQL:
>>
>> JPQL: UPDATE Customer c SET c.name = 'CHANGED'
>> WHERE (SELECT COUNT(o) FROM c.orders o) > 0
>>
>> SQL: UPDATE CUSTOMER_TABLE SET VERSION = (VERSION + ?), NAME = ?
>> WHERE EXISTS(
>> SELECT t0.ID FROM CUSTOMER_TABLE t0
>> WHERE ((
>> SELECT COUNT(*) FROM CUSTOMER_TABLE t2, ORDER_TABLE t1
>> WHERE (t1.CUST_ID = t2.ID)) > ?)
>> AND t0.ID = CUSTOMER_TABLE.ID)
>>
>> Adding a clause joining t0 and t2 would return the expected result.
>> Then the WHERE clause of the innermost query would become:
>> WHERE (t1.CUST_ID = t2.ID) AND (t2.ID = t0.ID)) > ?)
>>
>> We had a similar issue with subqueries in SELECT. We decided that
>> until issue 197 (duplicated tables in generated SQL) is not fixed,
>> the JPQL compiler will connect the ExpressionBuilders for t0 and t2
>> which then generates the join clause. In the case of the UPDATE
>> query, the toplink queryframework generates the EXISTS query, so the
>> JPQL compiler cannot connect t0 and t2. Do you have an idea?
>>
>> I propose I close issue 572 as fixed and file a new glassfish bug for
>> the above issue with the unexpected UPDATE result. You find my new
>> entity-persistence-tests JPQL test class in the attached jar.
>>
>> Regards Michael
>>
>>>
>>> -Tom
>>>
>>> Michael Bouschen wrote:
>>>
>>>> Hi Tom,
>>>>
>>>> attached you find my changes to fix the NPE in the JPQL compiler
>>>> when compiling an UPDATE query including a subquery.
>>>>
>>>> The SubqueryNode creates a ReportQuery instance for the subquery
>>>> and then calls the subQuery method on the query's base expression
>>>> passing the ReportQuery. The issue is that the base expression is
>>>> only set for a SELECT query.
>>>>
>>>> I added some code initializing the base expression for UPDATE and
>>>> DELETE queries. Most of the code to be changed/added is identical
>>>> for UPDATE and DELETE, so I decided to add a new superclass
>>>> ModifyNode for UpdateNode and DeleteNode. I also added a new method
>>>> to ModifyAllQuery allowing to set the query's ExpressionBuilder.
>>>>
>>>> Please have a look at the attached changes. Thanks!
>>>>
>>>> Regards Michael
>>>>
>>>>
>>