Re: Minutes: JDO TCK Conference Call Friday July 29, 9 AM Pacific Daylight Time (PDT)

[Craig Russell]

Hi Andy,

> On Jul 29, 2016, at 11:53 PM, Andy Jefferson  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!

Re: Minutes: JDO TCK Conference Call Friday July 29, 9 AM Pacific Daylight Time (PDT)

[Andy Jefferson]

> 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)

Minutes: JDO TCK Conference Call Friday July 29, 9 AM Pacific Daylight Time (PDT)

[Craig Russell]

Attendees: Michael Bouschen, Tilmann Zäschke, Craig Russell

Agenda:

1. New patch on JDO-751 "Support for Java8 Optional" 
https://issues.apache.org/jira/browse/JDO-751

Waiting on experiments with queries. See #3 below.

2. JDO-747 "Behavior of delete() with multiple concurrent Transactions" 
https://issues.apache.org/jira/browse/JDO-747

Tilmann plans to investigate datanucleus implementation of refresh. Datanucleus 
prepares objects before loading from the datastore, so this preparation needs 
to be moved to after loading so the implementation knows whether the object 
exists in the datastore or not.

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?

@Michael: investigate the != null in addition to == null and see what SQL is 
generated. In particular there is a difference between !(x == null) and x != 
null. For example this.manager.manager != null versus !(this.manager.manager == 
null). Also look at navigating relationships where the foreign key is on the 
other side. Also look at how fetch plans impact the generated SQL. 

4. JDO 3.1: Need to go through change lists in JIRA for 3.1 RC1 and 3.1 to 
prepare JCP Change Log

5. Other issues

Action Items from weeks past:
[Oct 30 2015] AI Craig: File a maintenance review with JCP
[May 15 2015] AI Craig Spec change for roll back an active transaction when 
closing a persistence manager (JDO-735)  
[Apr 17 2015] AI Craig: Oracle spec page on JDO need to be updated once the JCP 
Maintenance Release for JDO 3.1 is published
[Oct 17 2014] AI Matthew any updates for "Modify specification to address NoSQL 
datastores": https://issues.apache.org/jira/browse/JDO-651?
[Feb 28 2014] AI Everyone: take a look at 
https://issues.apache.org/jira/browse/JDO-712
[Feb 28 2014] AI Everyone: take a look at 
https://issues.apache.org/jira/browse/JDO-625
[Dec 13 2013] AI Craig file a JIRA for java.sql.Blob and java.sql.Clob as 
persistent field types
[Aug 24 2012] AI Craig update the JIRAs JDO-689 JDO-690 and JDO-692 about 
JDOHelper methods. In process.

Craig L Russell
Architect
craig.russ...@oracle.com
P.S. A good JDO? O, Gasp!