[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13187459#comment-13187459
]
Mamta A. Satoor commented on DERBY-4631:
----------------------------------------
********************************************************
Mike's comment
Also can you explicitly give some queries that currently work today that will
not after your change, so that the compatibility impact of your solutions can
be understood".
********************************************************
Following queries will be fixed by both the solutions.
java -Dij.exceptionTrace=true org.apache.derby.tools.ij
connect
'jdbc:derby:db1;create=true;territory=en_US;collation=TERRITORY_BASED:SECONDARY';
CREATE TABLE derby4631_t1(x varchar(5));
INSERT INTO derby4631_t1 VALUES 'A','B';
CREATE TABLE derby4631_t2(x varchar(5));
INSERT INTO derby4631_t2 VALUES 'b','c';
SELECT x, coalesce(derby4631_t2.x, derby4631_t1.x) FROM derby4631_t2 NATURAL
RIGHT OUTER JOIN derby4631_t1;
select x, coalesce(derby4631_t2.x, derby4631_t1.x) from derby4631_t2 RIGHT
OUTER JOIN derby4631_t1 USING(x);
The 2 select queries above return following today(which is incorrect)
X |2
-----------
A |A
B |b
2 rows selected
With both the solutions proposed in this jira, they will return
following(correct results)
X |2
-----------
A |A
b |b
2 rows selected
Additionally, each of the 2 proposed solutions affect few other queries, but
unfortunately not the same way.
Here is example query that will be affected by solution 1).
SELECT x FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1 group by
derby4631_t1.x;
Since join column x can have different results with solution 1(compared to
existing behavior), the group by query below will result in different results
too. This is kind of obvious but still wanted to point out how different
results for join columns can affect join queries with group by/having clause etc
With solution 2), the group by query above will fail because column
derby4631_t1.x is not part of the SELECT columns list. With solution 2), join
column x is not going to be assoicated with left table(in case of left outer
join and inner joins) or right table(in case of right outer join). Because of
that, the group by will result in compilation error but column derby4631_t1.x
is not in the SELECT list.
> 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
>
> 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
