Hi Witold,
I filed an issue for this:
https://glassfish.dev.java.net/issues/show_bug.cgi?id=2867
Regards Michael
> Hi Witold,
>
> this sounds like a bug. Could you please file an issue at
> https://glassfish.dev.java.net/servlets/ProjectIssues using
> entity-persistence as the subcomponent. It would be great if you could
> also attach a small test case that allows us to reproduce the problem.
>
> Thanks!
>
> Regards Michael
>
>> I have tested that query on Sun Java System Application Server 9.1
>> Beta (build b33e-beta)
>> this time against JavaDB. SQL looks different (in previous email I was
>> at office using Oracle and SJAS 9.0 u1p1) but it is still incorrect -
>> TopLink still does not see the difference between "assents1" and
>> "assents2" :(
>>
>> My workaround for that was to use second query for counting executed
>> assents, but if this is a bug, I wanted you to know about it, so you
>> could create a test case for that for future releases. That issue
>> could be a huge problem if that'd be a subquery...
>>
>>
>> 2007/4/6, Witold Szczerba <pljosh.mail_at_gmail.com>:
>>> Hi there,
>>> I have a JPA QL query:
>>> Object[] result = (Object[]) em.createQuery("SELECT " +
>>> "COUNT(assents1.id), " +
>>> "COUNT(assents2.id), " +
>>> "status.id, " +
>>> "adviser.id, " +
>>> "p.id " +
>>> "FROM Proposal p " +
>>> "LEFT JOIN p.proposalStatus status " +
>>> "LEFT JOIN p.adviser adviser " +
>>> "LEFT JOIN p.assents assents1 " +
>>> "LEFT JOIN p.assents assents2 " +
>>> "WHERE p = ?1 AND assents2.executedBy IS NULL " +
>>> "GROUP BY p.id, status.id, adviser.id ")
>>> .setParameter(1, proposalRef)
>>> .getSingleResult();
>>>
>>> And this is how it was translated by TopLink Essentials (from
>>> Glassfish V1 u1p1)
>>>
>>> SELECT
>>> COUNT(t0.ID),
>>> COUNT(t0.ID),
>>> t2.ID,
>>> t3.USERNAME,
>>> t1.ID
>>> FROM APPLICATIONUSER t3,
>>> DICTPROPOSALSTATUS t2,
>>> PROPOSAL t1,
>>> PROPOSALASSENT t0
>>> WHERE (((? = t1.ID) AND (t0.EXECUTEDBY_USERNAME IS NULL)) AND
>>> (((t0.PROPOSAL_ID (+) = t1.ID) AND
>>> (t2.ID (+) = t1.PROPOSALSTATUS_ID)) AND
>>> (t3.USERNAME (+) = t1.ADVISER_USERNAME)))
>>> GROUP BY t1.ID, t2.ID, t3.USERNAME
>>>
>>>
>>> It is clear, that for TopLink "assents1" and "assents2" are just the
>>> same, but these are two different relations, as "assents1" means ANY
>>> ASSENT for given Proposal, and "assents2" means ANY NOT EXECUTED
>>> ASSENT for given proposal...
>>>
>>> Is this a TopLink Essentials bug?
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
>> For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
> For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>