[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-4631:
---
Attachment: releaseNote.html
Attaching release note
> 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.6.2.3, 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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-4631:
---
Attachment: (was: releaseNote.html)
> 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.6.2.3, 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
>
>
> 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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kathey Marsden updated DERBY-4631:
--
Issue & fix info: Release Note Needed,Repro attached (was: Repro
attached,Patch Available)
> 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.6.2.3, 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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
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.6.2.3
> 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.6.2.3, 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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
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.8.2.3
> 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.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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
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.9.0.0
> 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.9.0.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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-4631:
---
Attachment: DERBY_4631_patch7_diff.txt
I am attaching another patch (DERBY_4631_patch7_diff.txt) which is very similar
to previous patch(DERBY_4631_patch6_diff.txt) except that I do not have
instance of checks in ResultColumn to find JoinNodes from the FromList.
Instead, I have added a new method isJoinColumnForRightOuterJoin which will
allow HalfOuterJoinNode to see if the ResultColumn is a join column for a right
outer join with using/natural clause. This makes the code more readable and
removes the instanceof checkings. Please let me know if there are any comments
to this patch. Thanks
> 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,
> 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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-4631:
---
Attachment: releaseNote.html
Attaching the release note for the issue
> 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,
> DERBY_4631_patch5_diff.txt, DERBY_4631_patch6_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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-4631:
---
Attachment: DERBY_4631_patch6_diff.txt
I have the final patch which is ready for commit. I have added couple more
tests
1)have left or right table empty before doing joins
2)Do arithmetic operation on join columns.
I will commit it in a week's time but will appreciate any feedback on the patch
before that.
> 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,
> DERBY_4631_patch5_diff.txt, DERBY_4631_patch6_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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-4631:
---
Attachment: DERBY_4631_patch5_diff.txt
Hi Mike,
thanks for reviewing patch 4. Here are some comments to your feedback(Attaching
a new patch DERBY_4631_patch5_diff.txt which takes care of some of your
comments.)
1)ResultColumn:
Mike's comment - There is a lot of checking for instances here, is there anyway
to do this work in the affected nodes like HalfOuterJoinNode?
Yes, I am concerned about the instance checking too but I had researched into
putting the code in HalfOuterJoinNode and found that HalfOuterJoinNode does not
ever get to see the ResultColumns for the query and hence it has no way of
marking those ResultColumns as join columns.
Mike's comment - At end of ResultColumn changes why do you check and set for
one of the new fields and not the other?
I was trying to follow the existing code where the boolean kinds of fields are
first checked and then set to true if the check returned true.
rightOuterJoinUsingClause is a boolean field and hence I checked for the return
value and then set the new object's value to true. But joinResultSet is a
non-boolean field and hence I simply used it's value to set new object's
joinResultSet value. For clarity, I will go ahead and replace following
newResultColumn.setJoinResultset(getJoinResultSet());
with
if (getJoinResultSet() != null) {
newResultColumn.setJoinResultset(getJoinResultSet());
}
2)ResultColumnList.java:
Mike's comment - get rid of the commented out line of code that you fixed with
patch 4
Removed it.
Mike's comment - a comment in allExpressionsAreColumns explaining why returning
false for isRightOuterJoinUsingClause would be useful.
Added a comment.
Mike's comment - in mapSourceColumns() what does the -1 for right outer join
columns mean?
allExpressionsAreColumns() uses the -1 value set by mapSourceColumns() to
decide if there are columns which require special consideration during code
generation. When mapSourceColumns() assigns -1 to right outer join column,
allExpressionsAreColumns() will return false. This will allow Derby to later
generate code equivalent to COASLECE for right outer join columns(this code
generation happens in newly added code in ResultColumnList.generateCore.)
Mike's comment - typo - search for "righ "
Fixed it.
Mike's comment - would be nice to have comments for setJoinResultset and
setRightOuterJoinUsingClause, maybe something about what is expected to call
this routine and in what circumstances.
Added comments around for both those methods.
Additionally, took care of some indentation problems with the code.
> 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,
> DERBY_4631_patch5_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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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.)
> Ho
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-4631:
---
Attachment: DERBY_4631_patch4_diff.txt
I found the problem which was causing array index out of bound exception with
the previous patch(patch 3).
The query which was throwing the exception is as follows
INSERT INTO derby4631_t3
SELECT x,
'a'
FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1;
The columns in derby4631_t3 are named x1 and y1. The source for column x1 in
insert above is join column x. In the patch, when we look to determine if the
column is a join column, I was looking at column's external name rather than
it's base name. So instead of looking for column named 'x', the earlier patch
was looking for column 'x1' to see if it is a join column. Because of that, it
didn't identify join column x in the SELECT query. I have fixed one line of
code in ResultColumnList to fix the problem. So, instead of
if (joinColumn.getName().equals(rc.getName())) {
it now checks
if (joinColumn.getName().equals(rc.name)) {
I will run the derbyall and junit suite to make sure no existing tests fail
with this new patch. I will also continue writing few more tests for JOINs.
> 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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-4631:
---
Issue & fix info: Patch Available,Repro attached (was: Repro attached)
> 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
>
>
> 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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[ https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mamta A. Satoor updated DERBY-4631: --- Attachment: DERBY_4631_patch3_stat.txt DERBY_4631_patch3_diff.txt DERBY-4631 Wrong join column returned by right outer join with NATURAL or USING and territory-based collation I have a patch(DERBY_4631_patch3_diff.txt) which is ready for review and commit. To recap the issue, 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 Derby has it's on logic to retrieve the join column values. It always picks up join column's value from the left table when we are working with natural left outer join and it picks up the join column's value from the right table when we are working with natural right outer join. But this logic does not work for all cases for right outer join. The fix provided in this patch will pick the join column's value based on following logic(this logic mimics the functionality of COALESCE) 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 Following are the files impacted by this patch $ svn stat -q M java\engine\org\apache\derby\impl\sql\compile\ResultColumn.java M java\engine\org\apache\derby\impl\sql\compile\JoinNode.java M java\engine\org\apache\derby\impl\sql\compile\ResultColumnList.java M java\testing\org\apache\derbyTesting\functionTests\tests\lang\CollationTest.java The changes are as follows Two additional fields have been added to ResultColumn.java rightOuterJoinUsingClause and joinResultSet rightOuterJoinUsingClause will be set to true for following 2 cases 1)if this column represents the join column which is part of the SELECT list of a RIGHT OUTER JOIN with USING/NATURAL. eg select c from t1 right join t2 using (c) This case is talking about column c as in "select c" 2)if this column represents the join column from the right table for predicates generated for the USING/NATURAL of RIGHT OUTER JOIN eg select c from t1 right join t2 using (c) For "using(c)", a join predicate will be created as follows t1.c=t2.c This case is talking about column t2.c of the join predicate. joinResultSet will be non-null for the case 1) above. It will show the association of this result column to the join resultset created for the RIGHT OUTER JOIN with USING/NATURAL. This information along with rightOuterJoinUsingClause will be used during the code generation time. These 2 additional fields will be used to identify ResultColumn which belong to a join column in the SELECT list and identify ResultColumn which belong to right join column for the predicate generated for USING/NATURAL columns. Additionally, ResultColumn which belong to a join column in the SELECT list will also know about the JoinNode which they belong to. These 2 pieces of information will then be used at the code generation time for join column for RIGHT OUTER JOIN with USING/NATURAL 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 Changes in JoinNode.java just identifies the ResultColumn which represent the join column from the right table for predicates generated for the USING/NATURAL of RIGHT OUTER JOIN eg select c from t1 right join t2 using (c) For "using(c)", a join predicate will be created, t1.c=t2.c. JoinNode changes will set ResultColumn.rightOuterJoinUsingClause flag to true for t2.c The code generation changes have gone into ResultColumnList.java > 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 > > > 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, , syntax rules: > > 1) Let TR1 be the first , and let TR2 be the >
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-4631:
---
Attachment: DERBY_4631_not_for_commit_patch2_stat.txt
DERBY_4631_not_for_commit_patch2_diff.txt
Attaching an updated patch which is still not ready for commit but it fixes two
queries listed earlier which were failing with 1st patch,
With the query using column a+1 in the select query, I was looking for field
name in ResultColumn which would be null for an expression like a+1. Instead, I
need to use exposedName which will not be null for an expression. Instead, we
internally generate a name for such columns.
With the query using order by, I found that in the new code for code
generation, I need to let Derby do what it does today for ResultColumns with
VirtualColumnNode underneath. Such a case can happen for order by query where
we pull columns if needed for order by columns,
Next I plan to make the code changes less of a hack and then run the derbyall
and junit suite to see if we catch any failures. Another thing to do would be
to add more RIGHT OUTER JOIN test variations to see if the suggested code
changes work fine with it. I will appreciate any suggestion on what kind of
RIGHT OUTER JOIN tests can be added to test the functionality.
> 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
>
>
> 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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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
[jira] [Updated] (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[ https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mamta A. Satoor updated DERBY-4631: --- Attachment: DERBY_4631_not_for_commit_patch1_stat.txt DERBY_4631_not_for_commit_patch1_diff.txt I have a patch which is not ready for commit. It is a work in progress for the solution 1) proposed earlier in the jira which is as follows Solution 1) requires that we change the code generation to following logic. 1)if the left table's join column value is null then pick up the right table's join column's value. 2)If the left table's join column value is non-null, then pick up that value We should have the solution 1) kickin for only RIGHT OUTER JOIN with USING clause or NATURAL JOIN. LEFT OUTER JOINs and INNER JOINs with USING clause or NATURAL JOIN will work correctly with current Derby logic which is to always pickup the left table's join column value. This will work for LEFT OUTER JOINs and INNER JOINs with USING clause or NATURAL JOIN in both territory and non-territory based databases The attached patch now makes the following query return the correct results 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 FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1; ij> X - A b The patch in it's current state is quite a bit of hacking because currently a ResultColumn does not need to know if it belongs to a Join ResultSet. But with solution 1), if ResultColumn is a join column part of a RIGHT OUTER JOIN with USING/NATURAL JOIN, then we want the join column's value to be determined using the solution 1) described above. For that, a ResultColumn will now need to know if it is part of a RIGHT OUTER JOIN with USING/NATURAL JOIN and it will need to know the column positions of the left table's join column and rigt table's join colunm and it will need to know the resultset number of the resultset from which these join colunms's values will be extracted. I get these values in this patch by putting extra information related to joins in ResultColunm at bind time and using them at code generation time. This patch may break many other queries and I will continue to work on the patch to make it more stable but I wanted to put the general approach for this solution out sooner than later to see if community has any feedback on keeping the extra information on ResultColumn needed to implement solution 1). Although, all this additional information maintenance is pretty localized and not many files are impacted by this solution. Next I plan to work on the patch more to make it stable and do more testing with it to see how it will work for the rest of the queries. Alternative to this approach would be to introduce a new kind of compilation node which will be created during the bind phase(unlike most nodes which get created during parsing) when we find that the ResultColumn belongs to Join Node(which means we will still have to do the checking I do in this patch to see if ResultColumn is part of RIGHT OUTER JOIN with USING/NATURAL JOIN but at code generation time, we can have the new node do this special code generation which is how we handle all the other special nodes like Conditional If node, Coalesce node etc). This approach of adding new node will require us to somehow fire the binding of the new node after replacing the ResultColumn which was created during the Parse time. At this point, I am unfamiliar with how to replace a node during the bind time with some other node and make it go through the binding step. Also, replacing the ResultColumn with a new node might also impact queries like following where I think order by column from right table is associated with the join column in the select list SELECT x FROM derby4631_t2 NATURAL RIGHT OUTER JOIN derby4631_t1 ORDER BY derby4631_t1.x; The files changed by this patch are as follows $ svn stat -q M java\engine\org\apache\derby\impl\sql\compile\ResultColumn.java M java\engine\org\apache\derby\impl\sql\compile\ProjectRestrictNode.java M java\engine\org\apache\derby\impl\sql\compile\JoinNode.java M java\engine\org\apache\derby\impl\sql\compile\ResultColumnList.java M java\engine\org\apache\derby\impl\sql\compile\HashTableNode.java Following explains the changes in brief for the files touched by this patch ResultColumn.java has additioanl columns to keep RIGHT OUTER JOIN with USING/NATURAL JOIN information. These columns will get updated during bind time if ResultColumn is part of such a join. In my next patch, I think I should be able to remove virtualColumnIdLeftTable and virtualColumnIdRig
[jira] Updated: (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Dag H. Wanvik updated DERBY-4631:
-
Urgency: Normal
Labels: derby_triage10_8 (was: )
> 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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-4631:
-
Issue & fix info: [Repro attached]
> 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
>
> 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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-4631) Wrong join column returned by right outer join with NATURAL or USING and territory-based collation
[
https://issues.apache.org/jira/browse/DERBY-4631?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Knut Anders Hatlen updated DERBY-4631:
--
Bug behavior facts: [Deviation from standard, Wrong query result] (was:
[Deviation from standard])
> 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
>
> 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, , syntax rules:
> > 1) Let TR1 be the first , and let TR2 be the > reference> or that is the second operand of the
> > . 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 immediately
> > containing a is specified, then:
> (...)
> > d) If there is at least one corresponding join column, then let SLCC
> > be a of 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.
-
You can reply to this email to add a comment to the issue online.
