db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (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 Fri, 09 Dec 2011 03:01:41 GMT

    [ 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

        

Mime
View raw message