[
https://issues.apache.org/jira/browse/DERBY-4471?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12902954#action_12902954
]
Lily Wei commented on DERBY-4471:
---------------------------------
I will go for option c). It gives you the freedom to recognize the expected
behavior with a warning. Also, it leaves room for lots of comments for further
development down the road. And, we will have computer to do the filter work for
us. However, the warning could be over look.
> Left outer join reassociation rewrite gives wrong result
> --------------------------------------------------------
>
> Key: DERBY-4471
> URL: https://issues.apache.org/jira/browse/DERBY-4471
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1,
> 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0,
> 10.5.1.1, 10.5.2.0, 10.5.3.0
> Reporter: Dag H. Wanvik
> Assignee: Dag H. Wanvik
> Attachments: derby-4471-1a.diff, derby-4471-1a.stat,
> derby-4471-1b.diff, derby-4471-1b.stat, derby-4471-1c.diff,
> derby-4471-1c.stat, derby-4471-junit-repro.diff, query_plan_derby_4471.pdf
>
>
> The following script and output shows the problem:
> > create table r(c1 char(1));
> > create table s(c1 char(1), c2 char(1));
> > create table t(c1 char(1));
> > insert into r values 'a';
> > insert into s values ('b', default);
> > insert into t values ('c');
> > select * from s left outer join t on s.c2=t.c1 or s.c2 is null;
> C1 |C2 |C1
> --------------
> b |NULL|c
> > select * from r left outer join s on r.c1=s.c1;
> C1 |C1 |C2
> --------------
> a |NULL|NULL
> > select * from (r left outer join s on r.c1=s.c1) left outer join t on
> > s.c2=t.c1 or s.c2 is null;
> C1 |C1 |C2 |C1
> -------------------
> a |NULL|NULL|c
> > select * from r left outer join (s left outer join t on s.c2=t.c1 or s.c2
> > is null) on r.c1=s.c1;
> C1 |C1 |C2 |C1
> -------------------
> a |NULL|NULL|c
> The last result is wrong. The correct answer should be:
> C1 |C1 |C2 |C1
> -------------------
> a |NULL|NULL|NULL
> since in the last form, the left table r has the value 'a', which does
> not match any row in result of the compound inner given the join
> predicate ("r.c1=s.c1"), so all nulls should be appended to the 'a'
> from the outer table r.
> This happens because internally the last form is rewritten to the
> second but the last form (left-deep), but this rewrite is not
> justified here unless the join predicate on s rejects null, which the
> present one explicitly does not ("or s.c2 is null"). Cf. for example
> [1], page 52, which describes this transform and its prerequisite
> condition as indentity #7.
> [1] Galindo-Legaria, C. & Rosenthal, A.: "Outerjoin simplification and
> reordering for query optimization", ACM Transactions on Database
> Systems, Vol 22, No 1, March 1997.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
