Community
Participate
Working Groups
Build ID: N/A Steps To Reproduce: When running this SQL: SELECT Cause.causingCostCentreId, COUNT(DISTINCT DiscoveredMistake.mistakeId) FROM Complaint JOIN ComplaintSubject LEFT JOIN Clarification LEFT JOIN DiscoveredMistake LEFT JOIN Cause LEFT JOIN SeizedMeasure LEFT JOIN OccuredCost GROUP BY Cause.causingCostCentreId There is this result: 'AAA': 1 'Support': 0 (NULL): 6 'BBB': 0 When running the equivalent JPA QL: SELECT ca.causingCostCentre.pk.id, COUNT(DISTINCT dm.mistake.pk.id) FROM Complaint cp JOIN cp.complaintSubject cs LEFT JOIN cp.clarifications cf LEFT JOIN cf.discoveredMistakes dm LEFT JOIN dm.causes ca LEFT JOIN ca.seizedMeasures sm LEFT JOIN sm.occuredCosts oc GROUP BY ca.causingCostCentre.pk.id Then the result is just one line: 'AAA': 1 I wonder why the lines 'Support: 0' and 'BBB: 0' are not provided? More information: Bug originally reported to TopLink: https://glassfish.dev.java.net/issues/show_bug.cgi?id=5644
Hello, Can you please provide the SQL generated from the JPA QL, and give either a test case or describe the relationships used in the joins so that we can reproduce the problem for testing. As you filed this originally on TopLink Essentials, can you please also confirm that it exists in EclipseLink?
Yes the problem also occurs in EclipseLink. The SQL showed that the problem is that a INNER JOIN is created that "eats up" NULL rows, leading to the discrepancy between SQL and JPA QL result lists. I have drilled down the problem to the core and opened another bug report describing the cause of the problem. Please see bug report #246211.
closing as a duplicate of 246211. In this case, dm.mistake.pk.id is specifying an Inner join between DM and Mistake. This is why null mistake values will not be included in the results. *** This bug has been marked as a duplicate of bug 246211 ***
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
