Hi Andy, > On Jul 29, 2016, at 11:53 PM, Andy Jefferson <a...@datanucleus.org> wrote: > >> 3. Experiments with queries and multiple navigations >> >> Looks like there is a bug in datanucleus when translating JDOQL to SQL by >> using LEFT OUTER JOIN semantics when navigating a relationship. >> >> @Andy: is there a bug? Clearly, LEFT OUTER JOIN is needed when loading >> objects with a fetch plan but when doing a query maybe the join should be a >> straight inner join? > > As already stated on the issue about Optional, DataNucleus REQUIRES correct > complete Java syntax for the JDOQL. That is, it requires the NULL check to be > present in the JDOQL if navigating through relations, namely > > WHERE this.manager != null && this.manager.manager == null
Sadly, this is a long-standing bug in datanucleus since it is in conflict with the specification: • A14.6.2-9 [Navigation through a null-valued field, which would throw NullPointerException, is treated as if the subexpression returned false.] Similarly, a failed cast operation, which would throw ClassCastException, is treated as if the subexpression returned false. Other subexpressions or other values for variables might still qualify the candidate instance for inclusion in the result set. Not requiring the user to explicitly include the null check was the motivation for including this section. Unfortunately, the test case NavigationThroughANullValuedField does not adequately test this condition. > > > Regarding how to represent that as SQL, the problem is one of creating a > dynamic query mechanism that caters for all queries (and you would be > surprised at how elaborate people want to make their queries). If you have a > WHERE clause with an OR (applying to the expression requiring the join) you > to represent (1-1/N-1) relation navigation have to use LEFT OUTER JOIN since > in SQL if you use INNER JOIN then that will apply to the other branch of the > OR also, which would be wrong. Can you give a specific example where INNER JOIN would give the wrong answer? I can’t come up with one. Each navigation expression needs to have its own JOIN so I don’t see where two sides of the OR would interact. I do see the need for generating LEFT OUTER JOIN when implementing fetch plans but not for JDOQL queries. > Consequently with JDOQL the default join type is LEFT OUTER JOIN unless the > relation has "optional=false" (when it becomes INNER JOIN). Clearly, if > someone wanted to provide their time, the query creation mechanism could be > made more complex and work out "oh there is a navigation through a relation > but no OR therefore we'll use INNER JOIN", but I don't see people offering > their time. With this project especially, resources will always be an issue. > > Regarding DataNucleus supporting omission of the NULL check, again, that is > down to resource. DataNucleus has a generic query compiler that simply > converts the JDOQL into expression trees (before any SQL is even considered > ... since the generic query compilation can be used for non-RDBMS datastores > also). These expression trees could be analysed and the process could be > extended to add in the NULL check expression when not provided. So someone > could provide their time to do that, or you make it an optional feature for > now until resource is available. Looking at the generated SQL, I don’t see a need for the extra NULL check. The JDOQL NULL check corresponds to SQL IS/IS NOT NULL so if we can resolve the INNER vs LEFT OUTER JOIN question, this part is easy. Regards, Craig > > Regards > -- > Andy > DataNucleus (Web: http://www.datanucleus.org Twitter: @datanucleus) Craig L Russell Architect craig.russ...@oracle.com P.S. A good JDO? O, Gasp!
