You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik (JIRA)" <ji...@apache.org> on 2010/07/10 00:32:51 UTC
[jira] Commented: (DERBY-4471) Left outer join reassociation
rewrite gives wrong result
[ https://issues.apache.org/jira/browse/DERBY-4471?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12886924#action_12886924 ]
Dag H. Wanvik commented on DERBY-4471:
--------------------------------------
A query in DERBY-4436 shows another example of wrong outer join reordering.
https://issues.apache.org/jira/browse/DERBY-4736?focusedCommentId=12886105&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_12886105
select * from (t2 left join (t3 left outer join t4 on 1=1) on t2.x = t3.x)
loj t2.x=t3.x
/ \
/ \
t2 loj 1=1
/ \
/ \
t3 t4
is rewritten to:
select * from ((t2 left join t3 on t2.x = t3.x) left join t4 on 1=1);
loj 1=1
/ \
/ t4
/
loj t2.x=t3.x
/ \
/ \
t2 t3
which also gives a wrong result, cf. analysis in DERBY-4736.
> 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
> Attachments: derby-4471-junit-repro.diff
>
>
> 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.