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?
>