db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lily Wei (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4471) Left outer join reassociation rewrite gives wrong result
Date Thu, 26 Aug 2010 17:45:54 GMT

    [ 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.


Mime
View raw message