db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (Commented) (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
Date Thu, 23 Feb 2012 17:44:48 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13214878#comment-13214878
] 

Mike Matrigali commented on DERBY-4631:
---------------------------------------

from review of latest patch.  

ResultColumn:
It would be good to get a review of this with someone with more expertise in this area of
the code.  The placement
of the code at 672 just seems strange given how simple the logic use to be.  There is a lot
of checking for instances
here, is there anyway to do this work in the affected nodes like HalfOuterJoinNode.

At end of ResultColumn changes why do you check and set for one of the new fields and not
the other?
+       if (isRightOuterJoinUsingClause()) {
+           newResultColumn.setRightOuterJoinUsingClause(true);
+       }
+
+       newResultColumn.setJoinResultset(getJoinResultSet());
+

ResultColumnList.java:
1266: get rid of the commented out line of code that you fixed with patch 4

a comment in allExpressionsAreColumns explaining why returning false for isRightOuterJoinUsingClause
would be useful.

in mapSourceColumns() what does the -1 for right outer join columns mean?  

nits:
typo - search for "righ "
would be nice to have comments for setJoinResultset and setRightOuterJoinUsingClause, maybe
something about what is expected
to call this routine and in what circumstances.
would be nice if code was 80 columns, critical bug at line 672 of ResultColumn is unreadable
at 80.

JoinNode change:
inconsistent bracket use in if/then/else

ResultColumnList:
more > 80 lines

indentation looks wrong for this code, maybe editor got confused by commented out if block:
                        if (joinColumn.getName().equals(rc.name)) {
                                if (joinColumn.isRightOuterJoinUsingClause())
                                    virtualColumnIdRightTable = joinColumn.getVirtualColumnId();
                                else
                                    virtualColumnIdLeftTable = joinColumn.getVirtualColumnId();
                        }

                
> Wrong join column returned by right outer join with NATURAL or USING and territory-based
collation
> --------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4631
>                 URL: https://issues.apache.org/jira/browse/DERBY-4631
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.6.1.0
>            Reporter: Knut Anders Hatlen
>            Assignee: Mamta A. Satoor
>              Labels: derby_triage10_8
>         Attachments: DERBY_4631_not_for_commit_patch1_diff.txt, DERBY_4631_not_for_commit_patch1_stat.txt,
DERBY_4631_not_for_commit_patch2_diff.txt, DERBY_4631_not_for_commit_patch2_stat.txt, DERBY_4631_patch3_diff.txt,
DERBY_4631_patch3_stat.txt, DERBY_4631_patch4_diff.txt
>
>
> SQL:2003 says that the join columns in a natural join or in a named
> columns join should be added to the select list by coalescing the
> column from the left table with the column from the right table.
> Section 7.7, <joined table>, syntax rules:
> > 1) Let TR1 be the first <table reference>, and let TR2 be the <table
> > reference> or <table factor> that is the second operand of the
> > <joined table>. Let RT1 and RT2 be the row types of TR1 and TR2,
> > respectively. Let TA and TB be the range variables of TR1 and TR2,
> > respectively. (...)
> and
> > 7) If NATURAL is specified or if a <join specification> immediately
> > containing a <named columns join> is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a <select list> of <derived column>s of the form
> >
> > COALESCE ( TA.C, TB.C ) AS C
> >
> > for every column C that is a corresponding join column, taken in
> > order of their ordinal positions in RT1.
> For a right outer join, Derby doesn't use COALESCE(TA.C, TB.C), but
> rather just TB.C (the column in the right table) directly.
> This is in most cases OK, because COALESCE(TA.C, TB.C) = TB.C is an
> invariant in a right outer join. (Because TA.C is either NULL or equal
> to TB.C.)
> However, in a database with territory-based collation, equality
> between two values does not mean they are identical, especially now
> that the strength of the collator can be specified (DERBY-1748).
> Take for instance this join:
> ij> connect 'jdbc:derby:testdb;create=true;territory=en_US;collation=TERRITORY_BASED:SECONDARY';
> ij> create table big(x varchar(5));
> 0 rows inserted/updated/deleted
> ij> insert into big values 'A','B','C';
> 3 rows inserted/updated/deleted
> ij> create table small(x varchar(5));
> 0 rows inserted/updated/deleted
> ij> insert into small values 'b','c','d';
> 3 rows inserted/updated/deleted
> ij> select x, t1.x, t2.x, coalesce(t1.x, t2.x) from small t1 natural right outer join
big t2;
> X    |X    |X    |4    
> -----------------------
> A    |NULL |A    |A    
> B    |b    |B    |b    
> C    |c    |C    |c    
> 3 rows selected
> I believe that the expected result from the above query is that the
> first column should have the same values as the last column. That is,
> the first column should contain {'A', 'b', 'c'}, not {'A', 'B', 'C'}.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message