> 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 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. 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. 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. Regards -- Andy DataNucleus (Web: http://www.datanucleus.org Twitter: @datanucleus)
