Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Fri, 9 Dec 2011 03:01:41 +0000 (UTC)
From: "Mamta A. Satoor (Commented) (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: 
 <788139360.56201.1323399701886.JavaMail.tomcat@hel.zones.apache.org>
Subject: [jira] [Commented] (DERBY-4631) Wrong join column returned by right
 outer join with NATURAL or USING and territory-based collation
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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

Mamta A. Satoor commented on DERBY-4631:
----------------------------------------

As mentioned earlier in this jira, the SQL spec 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. Derby implements the SQL spec by using left table's column value when doing natural left outer join. For natural right outer join, Derby uses the right
table's join column value. This logic correctly implements the SQL spec requirements for both left and right outer joins for non-territory based database and for left outer join for territory based database but the logic for the natural right outer join doesn't work for territory based database. Taking the example from earlier in this jira
create table big(x varchar(5)); 
insert into big values 'A','B'; 
create table small(x varchar(5)); 
insert into small values 'b','c'; 

For this data, following shows that selecting the join column doesn't give the same results as coalesce(t1.x, t2.x)
select x, t1.x t1x, t2.x t2x, coalesce(t1.x, t2.x) cx from small t1 natural right outer join big t2;
X    |T1X  |T2X  |CX   
--------------------------------------------------------------------
A    |NULL |A    |A    
B    |b         |B    |b    

For the 1st row above, coalesce(t1.x, t2.x) will return 'A' and that is what we got for the join column in that row, so we are good for the 1st row. But for the 2nd row, coalesce(t1.x, t2.x) will return 'b' whereas the join column for that row shows 'B'. This is because as per Derby's implementation, for natural right outer join, we just pick the value from the right table row which for the 2nd row happens to be 'B'. 

We can leave the logic as it is for natural left outer joins since it works fine for both territory and non-terrtory based databases. We can also leave the logic untouched for natural right outer joins for non-territory based databases. The only broken case is natural right outer join in case of territory based database. For this specific case, we can generate a project restrict resultset which will pick the join column's value based on following logic
1)if the left table's column value is null then pick up the right table's column's value. 
2)If the left table's column value is non-null, then pick up that value

I have not done much work in code generation and hence wanted to run this logic by the community to see if anyone has any feedback and if this looks like the correct approach to solve the problem. Any suggestions on alternative/better fix?

                
> 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
>              Labels: derby_triage10_8
>
> 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