Re: Join Correlation Name
On 30/10/2019 09:04, Fabien COELHO wrote: > >> I think possibly what the spec says (and that neither my patch nor >> Peter's implements) is assigning the alias just to the > list>. > > I think you are right, the alias is only on the identical columns. > > It solves the issue I raised about inaccessible attributes, and > explains why it is only available with USING and no other join variants. > >> So my original example query should actually be: >> >> SELECT a.x, b.y, j.z FROM a INNER JOIN b USING (z) AS j; > > Yep, only z should be in j, it is really just about the USING clause. My reading of SQL:2016-2 7.10 SR 11.a convinces me that this is the case. My reading of transformFromClauseItem() convinces me that this is way over my head and I have to abandon it. :-(
Re: Join Correlation Name
Bonjour Vik, Is quoting the spec good enough? SQL:2016 Part 2 Foundation Section 7.10 : Ah, this is the one information I did not have when reviewing Peter's patch. ::= USING[ AS ] ::= I think possibly what the spec says (and that neither my patch nor Peter's implements) is assigning the alias just to the . I think you are right, the alias is only on the identical columns. It solves the issue I raised about inaccessible attributes, and explains why it is only available with USING and no other join variants. So my original example query should actually be: SELECT a.x, b.y, j.z FROM a INNER JOIN b USING (z) AS j; Yep, only z should be in j, it is really just about the USING clause. -- Fabien.
Re: Join Correlation Name
On 29/10/2019 15:20, Tom Lane wrote: > Vik Fearing writes: >> On 29/10/2019 12:24, Isaac Morland wrote: >>> If you need to refer specifically to the non-qualified version in a >>> different part of the query, you can give an alias to the result of >>> the join: >>> ... (a join b using (z)) as t ... >> Yes, this is about having standard SQL syntax for that. > Please present an argument why this proposal is standard SQL syntax. Is quoting the spec good enough? SQL:2016 Part 2 Foundation Section 7.10 : ::= | ::= ON ::= USING[ AS ] ::= > I see no support for it in the spec. AFAICS this proposal is just an > inconsistent wart; it makes it possible to write > > (a join b using (z) as q) as t > > and then what do you do? Moreover, why should you be able to > attach an alias to a USING join but not other sorts of joins? I think possibly what the spec says (and that neither my patch nor Peter's implements) is assigning the alias just to the . So my original example query should actually be: SELECT a.x, b.y, j.z FROM a INNER JOIN b USING (z) AS j; > After digging around in the spec for awhile, it seems like > there actually isn't any way to attach an alias to a join > per spec. > > According to SQL:2011 7.6 , you can attach an > AS clause to every variant of *except* the > variant. And there's nothing > about AS clauses in 7.7 , which is where it would > have to be mentioned if this proposal were spec-compliant. > > What our grammar effectively does is to allow an AS clause to be > attached to as well, which seems > like the most natural thing to do if the committee ever decide > to rectify the shortcoming. > > Anyway, we already have the functionality covered, and I don't > think we need another non-spec, non-orthogonal way to do it. I think the issue here is you're looking at SQL:2011 whereas I am looking at SQL:2016. -- Vik Fearing
Re: Join Correlation Name
Vik Fearing writes: > On 29/10/2019 12:24, Isaac Morland wrote: >> If you need to refer specifically to the non-qualified version in a >> different part of the query, you can give an alias to the result of >> the join: >> ... (a join b using (z)) as t ... > Yes, this is about having standard SQL syntax for that. Please present an argument why this proposal is standard SQL syntax. I see no support for it in the spec. AFAICS this proposal is just an inconsistent wart; it makes it possible to write (a join b using (z) as q) as t and then what do you do? Moreover, why should you be able to attach an alias to a USING join but not other sorts of joins? After digging around in the spec for awhile, it seems like there actually isn't any way to attach an alias to a join per spec. According to SQL:2011 7.6 , you can attach an AS clause to every variant of *except* the variant. And there's nothing about AS clauses in 7.7 , which is where it would have to be mentioned if this proposal were spec-compliant. What our grammar effectively does is to allow an AS clause to be attached to as well, which seems like the most natural thing to do if the committee ever decide to rectify the shortcoming. Anyway, we already have the functionality covered, and I don't think we need another non-spec, non-orthogonal way to do it. regards, tom lane
Re: Join Correlation Name
On 29/10/2019 12:24, Isaac Morland wrote: > If you need to refer specifically to the non-qualified version in a > different part of the query, you can give an alias to the result of > the join: > > ... (a join b using (z)) as t ... Yes, this is about having standard SQL syntax for that.
Re: Join Correlation Name
On 29/10/2019 12:05, Peter Eisentraut wrote: > On 2019-10-29 11:47, Vik Fearing wrote: >> When joining tables with USING, the listed columns are merged and no >> longer belong to either the left or the right side. That means they can >> no longer be qualified which can often be an inconvenience. >> >> >> SELECT a.x, b.y, z FROM a INNER JOIN b USING (z); >> >> >> The SQL standard provides a workaround for this by allowing an alias on >> the join clause. ( in section 7.10) >> >> >> SELECT j.x, j.y, j.z FROM a INNER JOIN b USING (z) AS j; >> >> >> Attached is a patch (based on 517bf2d910) adding this feature. > > Is this the same as https://commitfest.postgresql.org/25/2158/ ? Crap. Yes, it is.
Re: Join Correlation Name
On Tue, 29 Oct 2019 at 07:05, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 2019-10-29 11:47, Vik Fearing wrote: > > When joining tables with USING, the listed columns are merged and no > > longer belong to either the left or the right side. That means they can > > no longer be qualified which can often be an inconvenience. > > > > > > SELECT a.x, b.y, z FROM a INNER JOIN b USING (z); > I'm confused. As far as I can tell you can qualify the join columns if you want: odyssey=> select exam_id, sitting_id, room_id, exam_exam_sitting.exam_id from exam_exam_sitting join exam_exam_sitting_room using (exam_id, sitting_id) limit 5; exam_id | sitting_id | room_id | exam_id -++-+- 22235 | 23235 | 22113 | 22235 22237 | 23237 | 22113 | 22237 23101 | 21101 | 22215 | 23101 23101 | 21101 | 22216 | 23101 23101 | 21101 | 4 | 23101 (5 rows) odyssey=> In the case of a non-inner join it can make a difference whether you use the left side, right side, or non-qualified version. If you need to refer specifically to the non-qualified version in a different part of the query, you can give an alias to the result of the join: ... (a join b using (z)) as t ... > The SQL standard provides a workaround for this by allowing an alias on > > the join clause. ( in section 7.10) > > > > > > SELECT j.x, j.y, j.z FROM a INNER JOIN b USING (z) AS j; > What I would like is to be able to use both USING and ON in the same join; I more often than I would like find myself saying things like ON ((l.a, l.b, lc.) = (r.a, r.b, r.c) AND l.ab = r.bb). Also I would like to be able to use and rename differently-named fields in a USING clause, something like USING (a, b, c=d as f). A bit of thought convinces me that these are both essentially syntactic changes; I think it's already possible to represent these in the existing internal representation, they just aren't supported by the parser.
Re: Join Correlation Name
On 2019-10-29 11:47, Vik Fearing wrote: When joining tables with USING, the listed columns are merged and no longer belong to either the left or the right side. That means they can no longer be qualified which can often be an inconvenience. SELECT a.x, b.y, z FROM a INNER JOIN b USING (z); The SQL standard provides a workaround for this by allowing an alias on the join clause. ( in section 7.10) SELECT j.x, j.y, j.z FROM a INNER JOIN b USING (z) AS j; Attached is a patch (based on 517bf2d910) adding this feature. Is this the same as https://commitfest.postgresql.org/25/2158/ ? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Join Correlation Name
When joining tables with USING, the listed columns are merged and no
longer belong to either the left or the right side. That means they can
no longer be qualified which can often be an inconvenience.
SELECT a.x, b.y, z FROM a INNER JOIN b USING (z);
The SQL standard provides a workaround for this by allowing an alias on
the join clause. ( in section 7.10)
SELECT j.x, j.y, j.z FROM a INNER JOIN b USING (z) AS j;
Attached is a patch (based on 517bf2d910) adding this feature.
--
Vik Fearing
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 22252556be..e4b17698d9 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -248,7 +248,7 @@ FROM table_reference , table_r
T1 { INNER | { LEFT | RIGHT | FULL } OUTER } JOIN T2 ON boolean_expression
-T1 { INNER | { LEFT | RIGHT | FULL } OUTER } JOIN T2 USING ( join column list )
+T1 { INNER | { LEFT | RIGHT | FULL } OUTER } JOIN T2 USING ( join column list ) [ AS alias ]
T1 NATURAL { INNER | { LEFT | RIGHT | FULL } OUTER } JOIN T2
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 06d611b64c..fcb474aaee 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -620,7 +620,7 @@ TABLE [ ONLY ] table_name [ * ]
- USING ( join_column [, ...] )
+ USING ( join_column [, ...] ) [ AS alias ]
A clause of the form USING ( a, b, ... ) is
@@ -629,6 +629,8 @@ TABLE [ ONLY ] table_name [ * ]
USING implies that only one of each pair of
equivalent columns will be included in the join output, not
both.
+An alias may be provided to reference these columns. In this
+case, the AS keyword is required.
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3f67aaf30e..34a4e0fa57 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -12004,7 +12004,11 @@ joined_table:
n->larg = $1;
n->rarg = $4;
if ($5 != NULL && IsA($5, List))
- n->usingClause = (List *) $5; /* USING clause */
+ {
+ /* USING clause */
+ n->usingClause = linitial_node(List, castNode(List, $5));
+ n->alias = lsecond_node(Alias, castNode(List, $5));
+ }
else
n->quals = $5; /* ON clause */
$$ = n;
@@ -12018,7 +12022,11 @@ joined_table:
n->larg = $1;
n->rarg = $3;
if ($4 != NULL && IsA($4, List))
- n->usingClause = (List *) $4; /* USING clause */
+ {
+ /* USING clause */
+ n->usingClause = linitial_node(List, castNode(List, $4));
+ n->alias = lsecond_node(Alias, castNode(List, $4));
+ }
else
n->quals = $4; /* ON clause */
$$ = n;
@@ -12126,9 +12134,14 @@ join_outer: OUTER_P { $$ = NULL; }
* ON expr allows more general qualifications.
*
* We return USING as a List node, while an ON-expr will not be a List.
+ *
+ * Since the USING clause merges the columns, they no longer belong to either
+ * the left or the right table. SQL allows an alias to be assigned to the JOIN
+ * so that the columns can be qualified.
*/
-join_qual: USING '(' name_list ')' { $$ = (Node *) $3; }
+join_qual: USING '(' name_list ')' { $$ = (Node *) (list_make2($3, NULL)); }
+ | USING '(' name_list ')' AS ColId { $$ = (Node *) (list_make2($3, makeAlias($6, NIL))); }
| ON a_expr{ $$ = $2; }
;
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index b58d560163..9694688077 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -1621,6 +1621,30 @@ SELECT '' AS "xxx", *
| 4 | 1 | one | 2
(4 rows)
+-- Test naming the join result, first with postgres syntax...
+SELECT '' AS "xxx", *
+ FROM (J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (d, b) USING (b)) AS j
+ ORDER BY j.b, j.c, j.d;
+ xxx | b | a | c | d
+-+---+---+---+---
+ | 0 | 5 | five |
+ | 0 | | zero |
+ | 2 | 3 | three | 2
+ | 4 | 1 | one | 2
+(4 rows)
+
+-- ...then with standard SQL syntax.
+SELECT '' AS "xxx", *
+ FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (d, b) USING (b) AS j
+ ORDER BY j.b, j.c, j.d;
+ xxx | b | a | c | d
+-+---+---+---+---
+ | 0 | 5 | five |
+ | 0 | | zero |
+ | 2 | 3 | three | 2
+ | 4 | 1 | one | 2
+(4 rows)
+
--
-- NATURAL JOIN
-- Inner equi-join on all columns with the same name
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 57481d0411..aa8a5771c5 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -126,6 +126,16 @@ SELECT '' AS "xxx", *
FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b)
ORDER BY b, t1.a;
+-- Test naming the join result, first with postgres syntax...
+SELECT '' AS "xxx", *
+ FROM (J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (d, b) USING (b)) AS j
+ ORDER BY j.b, j.c, j.d;
+
+-- ...then with standard SQL
