Community
Participate
Working Groups
Build ID: N/A Steps To Reproduce: I found out that when doing "GROUP BY state_field_path_expression" I am getting a result that has a NULL group (i. e. all NULLs build one group), while I am getting NO NULL group (i. e. all NULLs are filtered out) when I am doing "GROUP BY single_valued_association_path_expression). The reason seams to be simple: single_valued_association_path_expression necessarily contains a dot operator, which is treated like an INNER JOIN. Unfortunately, this leads to the fact, that it is impossible get a NULL group as long as the 'X' of "GROUP BY X" is not a state field. In fact, that it not acceptable, since there is no workaround. You just lose all NULLs. Neither does the specification clearly tell that in the GROUP BY description, nor can you do anything to actually get the NULL group. But getting the NULL group is essential in lots of business use cases. Example: If you store the cause for a problem, it might be the case that you just do not know the cause, so the reason can be NULL. So you would add a relationship from problem to reason that accepts NULL. Now the boss wants to know the percentage of reasons, so you do GROUP BY problem.reason. Since reason is a relation, and due to this reported bug, your boss will never know that for 95% of all problems you do not know the reasons. As you can see, getting the NULL group is essential. For us, this is a big problem. More information: Bug originall reported to TopLink, see: https://glassfish.dev.java.net/issues/show_bug.cgi?id=5678
Changed severity to major since our application needs the NULL groups essentially, we cannot change the app since it is not EclipseLink specific but vendor transparent, and since executing a second JPA QL command for SOLELY retrieving the NULLs is an unacceptable performance penalty.
see also bug report #246211
This is a result of path expressions (A.B) using inner joins. You can specify that an outer join be used in the form of: "Select ... FROM A a LEFT OUTER JOIN a.b b ... GROUP BY B" The above statment will use a left outer join from A->B. Closing as a duplicate of 246211 *** This bug has been marked as a duplicate of bug 246211 ***
(In reply to comment #3) > This is a result of path expressions (A.B) using inner joins. You can specify > that an outer join be used in the form of: > "Select ... FROM A a LEFT OUTER JOIN a.b b ... GROUP BY B" > The above statment will use a left outer join from A->B. > > Closing as a duplicate of 246211 > > *** This bug has been marked as a duplicate of bug 246211 *** > Just wanted to confirm that this idea solved our problem pretty well. The NULL groups are contained in the result as soon as adding a trailing "LEFT JOIN a.b b" that overrides the implicit inner join. Thanks for this tip, it is a pretty cool workaround! :-) Unfortunately it implies more complexity for the reader of the code and less performance of the DBMS server, compared to the solution implied in #246211 (not joining the grouped, referenced table at all but GROUP directly over the foreign key which is already joined). Thanks! Markus
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
