Thanks for looking into this, Thomas. Yes, I'll log it a JIRA issue a later today. I've not yet looked into any of the query code (or much outside of the TCK tests), so I'm not sure I can fix it. I can give it a try, though.
On Wednesday, January 16, 2013 at 5:16 AM, Thomas Mueller wrote: > Hi, > > I think you are right. Could you create an new issue about this? > > To be sure, I converted the queries in this test to SQL statements that can > be run against PostgreSQL. You can run them online using > http://sqlfiddle.com/ (see below). I can fix the bug in Oak, but I'm not very > familiar with this area in Jackrabbit 2.x. I guess the reason why Jackrabbit > 2.x is broken is the same as for Oak: a "property is not null" restriction is > pushed to the inner side of the outer join, which is wrong. > > Test case: > > -- schema > create table test(name varchar(255), prop1 int, prop2 int); > insert into test values('testroot/node1', 1, null); > insert into test values('testroot/node2', 1, 1); > insert into test values('testroot', null, null); > > -- testRightOuterJoin1: > select l.name (http://l.name) l, r.name (http://r.name) r > from test as l > right outer join test as r > on l.prop1 = r.prop2 > where r.name (http://r.name) like 'testroot/%'; > > -- testRightOuterJoin2: > select l.name (http://l.name) l, r.name (http://r.name) r > from test as l > right outer join test as r > on l.prop2 = r.prop1 > where r.name (http://r.name) like 'testroot/%'; > > -- testLeftOuterJoin1: > select l.name (http://l.name) l, r.name (http://r.name) r > from test as l > left outer join test as r > on l.prop1 = r.prop2 > where l.name (http://l.name) like 'testroot/%'; > > -- testLeftOuterJoin2: > select l.name (http://l.name) l, r.name (http://r.name) r > from test as l > left outer join test as r > on l.prop2 = r.prop1 > where l.name (http://l.name) like 'testroot/%'; > > > Regards, > Thomas > > > > From: Randall Hauch <[email protected] (mailto:[email protected])> > Reply-To: "[email protected] (mailto:[email protected])" > <[email protected] (mailto:[email protected])> > Date: Tuesday, January 15, 2013 7:33 PM > To: "[email protected] (mailto:[email protected])" > <[email protected] (mailto:[email protected])> > Subject: Outer join behavior in the TCK tests > > I've been looking into two OUTER JOIN tests in the TCK tests, and it appears > to me that the expected results are incorrect. The two tests are: > org.apache.jackrabbit.test.api.query.qom.EquiJoinConditionTest#testRightOuterJoin1 > org.apache.jackrabbit.test.api.query.qom.EquiJoinConditionTest#testLeftOuterJoin2 > > I'll do my best to explain my logic in a concise manner. > > Both tests are set up the same way: two nodes are created: > > /testroot/workarea/node1 {jcr:primaryType=nt:unstructured, prop1=yikqysrwur} > /testroot/workarea/node1/node2 {jcr:primaryType=nt:unstructured, > prop1=yikqysrwur, prop2=yikqysrwur, jcr:mixinTypes=[mix:referenceable], > jcr:uuid=c9118bb2-922e-4612-acd7-7152105f5684} > > where a single string is randomly generated and used for the values for > "prop1" and "prop2", and only the second node is made to be > "mix:referenceable". > > The "testRightOuterJoin1" test runs this query: > > SELECT * FROM [nt:unstructured] AS left > RIGHT OUTER JOIN [nt:unstructured] AS right > ON left.prop1 = right.prop2 > WHERE ISDESCENDANTNODE(right,'/testroot/workarea') > > The left side of the join has at least two tuples (one for "node1", one for > "node2", and other nodes which do not have a 'prop1' value), and column of > interest is the "prop1" column. Thus the left side tuples (or the parts we > care about for the join) look like: > > [ node1, yikqysrwur ] > [ node2, yikqysrwur ] > [ …, <null> ] > > The right side of the join has only two tuples ("node1" and "node2") because > of the "ISDESCENDANTNODE" criteria, and the only column of interest is the > "prop2" column. Thus, the right side tuples (or the parts we care about for > the join) look like: > > [ node1, <null> ] > [ node2, yikqysrwur ] > > When we perform a RIGHT OUTER JOIN, we have to **include all the tuples on > the right** even if they don't match a value on the left tuples. Thus, > "node1" must be included in the results, and because it has a null value for > the "prop2" column will not match any of the tuples on the left (since a null > value is not equal to another null value in the case of join criteria). So > the result set should contain these combinations of nodes: > > [ null, node1 ] > [ node1, node2 ] > [ node2, node2 ] > > However, the test expects the following result: > > [ node1, node2 ] > [ node2, node2 ] > > > This seems incorrect to me, because it is missing the [node1, null] tuple > that was on the right side of the join. Can anyone explain why my reasoning > is wrong, or do you agree that the test is incorrect? > > The "testLeftOuterJoin2" test uses a LEFT OUTER JOIN but instead reverses the > properties in the query, and thus fails for a similar reason. > > Best regards, > > Randall Hauch
