[
https://issues.apache.org/jira/browse/JCR-3493?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13556366#comment-13556366
]
Randall Hauch commented on JCR-3493:
------------------------------------
Thomas was kind enough to verify my assertions. Here's his response to original
email inquiry (see [this
thread|http://jackrabbit.510166.n4.nabble.com/Outer-join-behavior-in-the-TCK-tests-tt4657345.html]):
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 l, r.name r
from test as l
right outer join test as r
on l.prop1 = r.prop2
where r.name like 'testroot/%';
-- testRightOuterJoin2:
select l.name l, r.name r
from test as l
right outer join test as r
on l.prop2 = r.prop1
where r.name like 'testroot/%';
-- testLeftOuterJoin1:
select l.name l, r.name r
from test as l
left outer join test as r
on l.prop1 = r.prop2
where l.name like 'testroot/%';
-- testLeftOuterJoin2:
select l.name l, r.name r
from test as l
left outer join test as r
on l.prop2 = r.prop1
where l.name like 'testroot/%';
> OUTER JOIN tests expect incorrect results, which means that Jackrabbit
> excludes some valid results for OUTER JOINS
> ------------------------------------------------------------------------------------------------------------------
>
> Key: JCR-3493
> URL: https://issues.apache.org/jira/browse/JCR-3493
> Project: Jackrabbit Content Repository
> Issue Type: Bug
> Components: jackrabbit-jcr-tests
> Affects Versions: 2.5.2
> Reporter: Randall Hauch
>
> Two of the OUTER JOIN tests appears to expect incorrect results:
> -
> org.apache.jackrabbit.test.api.query.qom.EquiJoinConditionTest#testRightOuterJoin1
> -
> org.apache.jackrabbit.test.api.query.qom.EquiJoinConditionTest#testLeftOuterJoin2
> 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}
> 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 is incorrect to me, because it is missing the [node1, null] tuple that
> was on the right side of the join.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
