Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2af1e13

Browse files
deanrasheedpull[bot]
authored andcommitted
Make subquery aliases optional in the FROM clause.
This allows aliases for sub-SELECTs and VALUES clauses in the FROMclause to be omitted.This is an extension of the SQL standard, supported by some otherdatabase systems, and so eases the transition from such systems, aswell as removing the minor inconvenience caused by requiring thesealiases.Patch by me, reviewed by Tom Lane.Discussion:https://postgr.es/m/CAEZATCUCGCf82=hxd9N5n6xGHPyYpQnxW8HneeH+uP7yNALkWA@mail.gmail.com
1 parent22f940d commit2af1e13

File tree

10 files changed

+164
-81
lines changed

10 files changed

+164
-81
lines changed

‎doc/src/sgml/ref/select.sgml

Lines changed: 13 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -51,7 +51,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
5151

5252
[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
5353
[ TABLESAMPLE <replaceable class="parameter">sampling_method</replaceable> ( <replaceable class="parameter">argument</replaceable> [, ...] ) [ REPEATABLE ( <replaceable class="parameter">seed</replaceable> ) ] ]
54-
[ LATERAL ] ( <replaceable class="parameter">select</replaceable> ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ]
54+
[ LATERAL ] ( <replaceable class="parameter">select</replaceable> ) [[AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
5555
<replaceable class="parameter">with_query_name</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
5656
[ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] )
5757
[ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
@@ -490,8 +490,8 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
490490
output were created as a temporary table for the duration of
491491
this single <command>SELECT</command> command. Note that the
492492
sub-<command>SELECT</command> must be surrounded by
493-
parentheses, and an alias<emphasis>must</emphasis> be
494-
provided for it. A
493+
parentheses, and an aliascan be provided in the same way as for a
494+
table. A
495495
<link linkend="sql-values"><command>VALUES</command></link> command
496496
can also be used here.
497497
</para>
@@ -2041,6 +2041,16 @@ SELECT 2+2;
20412041
</para>
20422042
</refsect2>
20432043

2044+
<refsect2>
2045+
<title>Omitting sub-<command>SELECT</command> aliases in <literal>FROM</literal></title>
2046+
2047+
<para>
2048+
According to the SQL standard, a sub-<command>SELECT</command> in the
2049+
<literal>FROM</literal> list must have an alias. In
2050+
<productname>PostgreSQL</productname>, this alias may be omitted.
2051+
</para>
2052+
</refsect2>
2053+
20442054
<refsect2>
20452055
<title><literal>ONLY</literal> and Inheritance</title>
20462056

‎src/backend/parser/analyze.c

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -3291,7 +3291,7 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc,
32913291
foreach(rt,qry->rtable)
32923292
{
32933293
RangeTblEntry*rte= (RangeTblEntry*)lfirst(rt);
3294-
char*rtename;
3294+
char*rtename=rte->eref->aliasname;
32953295

32963296
++i;
32973297
if (!rte->inFromCl)
@@ -3302,15 +3302,22 @@ transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc,
33023302
* name and needs to be skipped (otherwise it might hide a
33033303
* base relation with the same name), except if it has a USING
33043304
* alias, which *is* visible.
3305+
*
3306+
* Subquery and values RTEs without aliases are never visible
3307+
* as relation names and must always be skipped.
33053308
*/
3306-
if (rte->rtekind==RTE_JOIN&&rte->alias==NULL)
3309+
if (rte->alias==NULL)
33073310
{
3308-
if (rte->join_using_alias==NULL)
3311+
if (rte->rtekind==RTE_JOIN)
3312+
{
3313+
if (rte->join_using_alias==NULL)
3314+
continue;
3315+
rtename=rte->join_using_alias->aliasname;
3316+
}
3317+
elseif (rte->rtekind==RTE_SUBQUERY||
3318+
rte->rtekind==RTE_VALUES)
33093319
continue;
3310-
rtename=rte->join_using_alias->aliasname;
33113320
}
3312-
else
3313-
rtename=rte->eref->aliasname;
33143321

33153322
if (strcmp(rtename,thisrel->relname)==0)
33163323
{

‎src/backend/parser/gram.y

Lines changed: 0 additions & 44 deletions
Original file line numberDiff line numberDiff line change
@@ -13379,33 +13379,6 @@ table_ref:relation_expr opt_alias_clause
1337913379
n->lateral =false;
1338013380
n->subquery =$1;
1338113381
n->alias =$2;
13382-
/*
13383-
* The SQL spec does not permit a subselect
13384-
* (<derived_table>) without an alias clause,
13385-
* so we don't either. This avoids the problem
13386-
* of needing to invent a unique refname for it.
13387-
* That could be surmounted if there's sufficient
13388-
* popular demand, but for now let's just implement
13389-
* the spec and see if anyone complains.
13390-
* However, it does seem like a good idea to emit
13391-
* an error message that's better than "syntax error".
13392-
*/
13393-
if ($2 ==NULL)
13394-
{
13395-
if (IsA($1, SelectStmt) &&
13396-
((SelectStmt *)$1)->valuesLists)
13397-
ereport(ERROR,
13398-
(errcode(ERRCODE_SYNTAX_ERROR),
13399-
errmsg("VALUES in FROM must have an alias"),
13400-
errhint("For example, FROM (VALUES ...) [AS] foo."),
13401-
parser_errposition(@1)));
13402-
else
13403-
ereport(ERROR,
13404-
(errcode(ERRCODE_SYNTAX_ERROR),
13405-
errmsg("subquery in FROM must have an alias"),
13406-
errhint("For example, FROM (SELECT ...) [AS] foo."),
13407-
parser_errposition(@1)));
13408-
}
1340913382
$$ = (Node *) n;
1341013383
}
1341113384
|LATERAL_Pselect_with_parensopt_alias_clause
@@ -13415,23 +13388,6 @@ table_ref:relation_expr opt_alias_clause
1341513388
n->lateral =true;
1341613389
n->subquery =$2;
1341713390
n->alias =$3;
13418-
/* same comment as above*/
13419-
if ($3 ==NULL)
13420-
{
13421-
if (IsA($2, SelectStmt) &&
13422-
((SelectStmt *)$2)->valuesLists)
13423-
ereport(ERROR,
13424-
(errcode(ERRCODE_SYNTAX_ERROR),
13425-
errmsg("VALUES in FROM must have an alias"),
13426-
errhint("For example, FROM (VALUES ...) [AS] foo."),
13427-
parser_errposition(@2)));
13428-
else
13429-
ereport(ERROR,
13430-
(errcode(ERRCODE_SYNTAX_ERROR),
13431-
errmsg("subquery in FROM must have an alias"),
13432-
errhint("For example, FROM (SELECT ...) [AS] foo."),
13433-
parser_errposition(@2)));
13434-
}
1343513391
$$ = (Node *) n;
1343613392
}
1343713393
|joined_table

‎src/backend/parser/parse_clause.c

Lines changed: 6 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -403,16 +403,6 @@ transformRangeSubselect(ParseState *pstate, RangeSubselect *r)
403403
{
404404
Query*query;
405405

406-
/*
407-
* We require user to supply an alias for a subselect, per SQL92. To relax
408-
* this, we'd have to be prepared to gin up a unique alias for an
409-
* unlabeled subselect. (This is just elog, not ereport, because the
410-
* grammar should have enforced it already. It'd probably be better to
411-
* report the error here, but we don't have a good error location here.)
412-
*/
413-
if (r->alias==NULL)
414-
elog(ERROR,"subquery in FROM must have an alias");
415-
416406
/*
417407
* Set p_expr_kind to show this parse level is recursing to a subselect.
418408
* We can't be nested within any expression, so don't need save-restore
@@ -430,10 +420,14 @@ transformRangeSubselect(ParseState *pstate, RangeSubselect *r)
430420
pstate->p_lateral_active=r->lateral;
431421

432422
/*
433-
* Analyze and transform the subquery.
423+
* Analyze and transform the subquery. Note that if the subquery doesn't
424+
* have an alias, it can't be explicitly selected for locking, but locking
425+
* might still be required (if there is an all-tables locking clause).
434426
*/
435427
query=parse_sub_analyze(r->subquery,pstate,NULL,
436-
isLockedRefname(pstate,r->alias->aliasname),
428+
isLockedRefname(pstate,
429+
r->alias==NULL ?NULL :
430+
r->alias->aliasname),
437431
true);
438432

439433
/* Restore state */

‎src/backend/parser/parse_relation.c

Lines changed: 22 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1577,7 +1577,11 @@ addRangeTableEntryForRelation(ParseState *pstate,
15771577
* Then, construct and return a ParseNamespaceItem for the new RTE.
15781578
*
15791579
* This is much like addRangeTableEntry() except that it makes a subquery RTE.
1580-
* Note that an alias clause *must* be supplied.
1580+
*
1581+
* If the subquery does not have an alias, the auto-generated relation name in
1582+
* the returned ParseNamespaceItem will be marked as not visible, and so only
1583+
* unqualified references to the subquery columns will be allowed, and the
1584+
* relation name will not conflict with others in the pstate's namespace list.
15811585
*/
15821586
ParseNamespaceItem*
15831587
addRangeTableEntryForSubquery(ParseState*pstate,
@@ -1587,22 +1591,22 @@ addRangeTableEntryForSubquery(ParseState *pstate,
15871591
boolinFromCl)
15881592
{
15891593
RangeTblEntry*rte=makeNode(RangeTblEntry);
1590-
char*refname=alias->aliasname;
15911594
Alias*eref;
15921595
intnumaliases;
15931596
List*coltypes,
15941597
*coltypmods,
15951598
*colcollations;
15961599
intvarattno;
15971600
ListCell*tlistitem;
1601+
ParseNamespaceItem*nsitem;
15981602

15991603
Assert(pstate!=NULL);
16001604

16011605
rte->rtekind=RTE_SUBQUERY;
16021606
rte->subquery=subquery;
16031607
rte->alias=alias;
16041608

1605-
eref=copyObject(alias);
1609+
eref=alias ?copyObject(alias) :makeAlias("unnamed_subquery",NIL);
16061610
numaliases=list_length(eref->colnames);
16071611

16081612
/* fill in any unspecified alias columns, and extract column type info */
@@ -1634,7 +1638,7 @@ addRangeTableEntryForSubquery(ParseState *pstate,
16341638
ereport(ERROR,
16351639
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
16361640
errmsg("table \"%s\" has %d columns available but %d columns specified",
1637-
refname,varattno,numaliases)));
1641+
eref->aliasname,varattno,numaliases)));
16381642

16391643
rte->eref=eref;
16401644

@@ -1665,8 +1669,15 @@ addRangeTableEntryForSubquery(ParseState *pstate,
16651669
* Build a ParseNamespaceItem, but don't add it to the pstate's namespace
16661670
* list --- caller must do that if appropriate.
16671671
*/
1668-
returnbuildNSItemFromLists(rte,list_length(pstate->p_rtable),
1669-
coltypes,coltypmods,colcollations);
1672+
nsitem=buildNSItemFromLists(rte,list_length(pstate->p_rtable),
1673+
coltypes,coltypmods,colcollations);
1674+
1675+
/*
1676+
* Mark it visible as a relation name only if it had a user-written alias.
1677+
*/
1678+
nsitem->p_rel_visible= (alias!=NULL);
1679+
1680+
returnnsitem;
16701681
}
16711682

16721683
/*
@@ -2520,6 +2531,10 @@ addRangeTableEntryForENR(ParseState *pstate,
25202531
* This is used when we have not yet done transformLockingClause, but need
25212532
* to know the correct lock to take during initial opening of relations.
25222533
*
2534+
* Note that refname may be NULL (for a subquery without an alias), in which
2535+
* case the relation can't be locked by name, but it might still be locked if
2536+
* a locking clause requests that all tables be locked.
2537+
*
25232538
* Note: we pay no attention to whether it's FOR UPDATE vs FOR SHARE,
25242539
* since the table-level lock is the same either way.
25252540
*/
@@ -2544,7 +2559,7 @@ isLockedRefname(ParseState *pstate, const char *refname)
25442559
/* all tables used in query */
25452560
return true;
25462561
}
2547-
else
2562+
elseif (refname!=NULL)
25482563
{
25492564
/* just the named tables */
25502565
ListCell*l2;

‎src/backend/utils/adt/ruleutils.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11725,7 +11725,11 @@ get_from_clause_item(Node *jtnode, Query *query, deparse_context *context)
1172511725
elseif (rte->rtekind==RTE_SUBQUERY||
1172611726
rte->rtekind==RTE_VALUES)
1172711727
{
11728-
/* Alias is syntactically required for SUBQUERY and VALUES */
11728+
/*
11729+
* For a subquery, always print alias. This makes the output SQL
11730+
* spec-compliant, even though we allow the alias to be omitted on
11731+
* input.
11732+
*/
1172911733
printalias= true;
1173011734
}
1173111735
elseif (rte->rtekind==RTE_CTE)

‎src/include/parser/parse_node.h

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -246,8 +246,11 @@ struct ParseState
246246
* visible for qualified references) but it does hide their columns
247247
* (unqualified references to the columns refer to the JOIN, not the member
248248
* tables, so we must not complain that such a reference is ambiguous).
249-
* Various special RTEs such as NEW/OLD for rules may also appear with only
250-
* one flag set.
249+
* Conversely, a subquery without an alias does not hide the columns selected
250+
* by the subquery, but it does hide the auto-generated relation name (so the
251+
* subquery columns are visible for unqualified references only). Various
252+
* special RTEs such as NEW/OLD for rules may also appear with only one flag
253+
* set.
251254
*
252255
* While processing the FROM clause, namespace items may appear with
253256
* p_lateral_only set, meaning they are visible only to LATERAL

‎src/interfaces/ecpg/preproc/ecpg.addons

Lines changed: 0 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -449,12 +449,6 @@ ECPG: into_clauseINTOOptTempTableName block
449449
$$= cat2_str(mm_strdup("into"), $2);
450450
}
451451
| ecpg_into { $$ = EMPTY; }
452-
ECPG: table_refselect_with_parensopt_alias_clause addon
453-
if ($2 == NULL)
454-
mmerror(PARSE_ERROR, ET_ERROR, "subquery in FROM must have an alias");
455-
ECPG: table_refLATERAL_Pselect_with_parensopt_alias_clause addon
456-
if ($3 == NULL)
457-
mmerror(PARSE_ERROR, ET_ERROR, "subquery in FROM must have an alias");
458452
ECPG: TypenameSimpleTypenameopt_array_bounds block
459453
{$$ = cat2_str($1, $2.str); }
460454
ECPG: TypenameSETOFSimpleTypenameopt_array_bounds block

‎src/test/regress/expected/subselect.out

Lines changed: 69 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,12 @@ SELECT * FROM ((SELECT 1 AS x)) ss;
3030
1
3131
(1 row)
3232

33+
SELECT * FROM ((SELECT 1 AS x)), ((SELECT * FROM ((SELECT 2 AS y))));
34+
x | y
35+
---+---
36+
1 | 2
37+
(1 row)
38+
3339
(SELECT 2) UNION SELECT 2;
3440
?column?
3541
----------
@@ -196,6 +202,69 @@ SELECT f1 AS "Correlated Field"
196202
3
197203
(5 rows)
198204

205+
-- Subselects without aliases
206+
SELECT count FROM (SELECT COUNT(DISTINCT name) FROM road);
207+
count
208+
-------
209+
2911
210+
(1 row)
211+
212+
SELECT COUNT(*) FROM (SELECT DISTINCT name FROM road);
213+
count
214+
-------
215+
2911
216+
(1 row)
217+
218+
SELECT * FROM (SELECT * FROM int4_tbl), (VALUES (123456)) WHERE f1 = column1;
219+
f1 | column1
220+
--------+---------
221+
123456 | 123456
222+
(1 row)
223+
224+
CREATE VIEW view_unnamed_ss AS
225+
SELECT * FROM (SELECT * FROM (SELECT abs(f1) AS a1 FROM int4_tbl)),
226+
(SELECT * FROM int8_tbl)
227+
WHERE a1 < 10 AND q1 > a1 ORDER BY q1, q2;
228+
SELECT * FROM view_unnamed_ss;
229+
a1 | q1 | q2
230+
----+------------------+-------------------
231+
0 | 123 | 456
232+
0 | 123 | 4567890123456789
233+
0 | 4567890123456789 | -4567890123456789
234+
0 | 4567890123456789 | 123
235+
0 | 4567890123456789 | 4567890123456789
236+
(5 rows)
237+
238+
\sv view_unnamed_ss
239+
CREATE OR REPLACE VIEW public.view_unnamed_ss AS
240+
SELECT unnamed_subquery.a1,
241+
unnamed_subquery_1.q1,
242+
unnamed_subquery_1.q2
243+
FROM ( SELECT unnamed_subquery_2.a1
244+
FROM ( SELECT abs(int4_tbl.f1) AS a1
245+
FROM int4_tbl) unnamed_subquery_2) unnamed_subquery,
246+
( SELECT int8_tbl.q1,
247+
int8_tbl.q2
248+
FROM int8_tbl) unnamed_subquery_1
249+
WHERE unnamed_subquery.a1 < 10 AND unnamed_subquery_1.q1 > unnamed_subquery.a1
250+
ORDER BY unnamed_subquery_1.q1, unnamed_subquery_1.q2
251+
DROP VIEW view_unnamed_ss;
252+
-- Test matching of locking clause to correct alias
253+
CREATE VIEW view_unnamed_ss_locking AS
254+
SELECT * FROM (SELECT * FROM int4_tbl), int8_tbl AS unnamed_subquery
255+
WHERE f1 = q1
256+
FOR UPDATE OF unnamed_subquery;
257+
\sv view_unnamed_ss_locking
258+
CREATE OR REPLACE VIEW public.view_unnamed_ss_locking AS
259+
SELECT unnamed_subquery.f1,
260+
unnamed_subquery_1.q1,
261+
unnamed_subquery_1.q2
262+
FROM ( SELECT int4_tbl.f1
263+
FROM int4_tbl) unnamed_subquery,
264+
int8_tbl unnamed_subquery_1
265+
WHERE unnamed_subquery.f1 = unnamed_subquery_1.q1
266+
FOR UPDATE OF unnamed_subquery_1
267+
DROP VIEW view_unnamed_ss_locking;
199268
--
200269
-- Use some existing tables in the regression test
201270
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp