Return-Path: 
 <derby-dev-return-97869-apmail-db-derby-dev-archive=db.apache.org@db.apache.org>
X-Original-To: apmail-db-derby-dev-archive@www.apache.org
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id 3A396D195
	for <apmail-db-derby-dev-archive@www.apache.org>;
 Fri, 14 Sep 2012 15:24:08 +0000 (UTC)
Received: (qmail 48674 invoked by uid 500); 14 Sep 2012 15:24:08 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 48654 invoked by uid 500); 14 Sep 2012 15:24:08 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:derby-dev-help@db.apache.org>
List-Unsubscribe: <mailto:derby-dev-unsubscribe@db.apache.org>
List-Post: <mailto:derby-dev@db.apache.org>
List-Id: <derby-dev.db.apache.org>
Reply-To: <derby-dev@db.apache.org>
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 48645 invoked by uid 99); 14 Sep 2012 15:24:08 -0000
Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 14 Sep 2012 15:24:08 +0000
Date: Sat, 15 Sep 2012 02:24:07 +1100 (NCT)
From: "Mamta A. Satoor (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <422632472.80756.1347636248009.JavaMail.jiratomcat@arcas>
Subject: [jira] [Updated] (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
X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394


     [ https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor updated DERBY-4631:
-----------------------------------

    Fix Version/s: 10.7.1.4
    
> 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
>             Fix For: 10.7.1.4, 10.8.2.3, 10.9.1.0
>
>         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, DERBY_4631_patch5_diff.txt, DERBY_4631_patch6_diff.txt, DERBY_4631_patch7_diff.txt, releaseNote.html
>
>
> 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
For more information on JIRA, see: http://www.atlassian.com/software/jira