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?