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

Commit1b4f7f9

Browse files
committed
Allow empty target list in SELECT.
This fixes a problem noted as a followup to bug #8648: if a query has asemantically-empty target list, e.g. SELECT * FROM zero_column_table,ruleutils.c will dump it as a syntactically-empty target list, which wasnot allowed. There doesn't seem to be any reliable way to fix this byhacking ruleutils (note in particular that the originally zero-column tablemight since have had columns added to it); and even if we had such a fix,it would do nothing for existing dump files that might contain bad syntax.The best bet seems to be to relax the syntactic restriction.Also, add parse-analysis errors for SELECT DISTINCT with no columns (after*-expansion) and RETURNING with no columns. These cases previouslyproduced unexpected behavior because the parsed Query looked like it hadno DISTINCT or RETURNING clause, respectively. If anyone ever offersa plausible use-case for this, we could work a bit harder on making thesituation distinguishable.Arguably this is a bug fix that should be back-patched, but I'm worriedthat there may be client apps or PLs that expect "SELECT ;" to throw asyntax error. The issue doesn't seem important enough to risk changingbehavior in minor releases.
1 parentc03ad56 commit1b4f7f9

File tree

6 files changed

+73
-33
lines changed

6 files changed

+73
-33
lines changed

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

Lines changed: 21 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -34,7 +34,7 @@ PostgreSQL documentation
3434
<synopsis>
3535
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
3636
SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replaceable> [, ...] ) ] ]
37-
* | <replaceable class="parameter">expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...]
37+
[* | <replaceable class="parameter">expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...]]
3838
[ FROM <replaceable class="parameter">from_item</replaceable> [, ...] ]
3939
[ WHERE <replaceable class="parameter">condition</replaceable> ]
4040
[ GROUP BY <replaceable class="parameter">expression</replaceable> [, ...] ]
@@ -1740,13 +1740,27 @@ SELECT 2+2;
17401740
following query is invalid:
17411741
<programlisting>
17421742
SELECT distributors.* WHERE distributors.name = 'Westward';
1743-
</programlisting><productname>PostgreSQL</productname> releases prior to
1743+
</programlisting>
1744+
<productname>PostgreSQL</productname> releases prior to
17441745
8.1 would accept queries of this form, and add an implicit entry
17451746
to the query's <literal>FROM</literal> clause for each table
17461747
referenced by the query. This is no longer allowed.
17471748
</para>
17481749
</refsect2>
17491750

1751+
<refsect2>
1752+
<title>Empty <literal>SELECT</literal> Lists</title>
1753+
1754+
<para>
1755+
The list of output expressions after <literal>SELECT</literal> can be
1756+
empty, producing a zero-column result table.
1757+
This is not valid syntax according to the SQL standard.
1758+
<productname>PostgreSQL</productname> allows it to be consistent with
1759+
allowing zero-column tables.
1760+
However, an empty list is not allowed when <literal>DISTINCT</> is used.
1761+
</para>
1762+
</refsect2>
1763+
17501764
<refsect2>
17511765
<title>Omitting the <literal>AS</literal> Key Word</title>
17521766

@@ -1809,10 +1823,6 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
18091823
<productname>PostgreSQL</productname> treats <literal>UNNEST()</> the
18101824
same as other set-returning functions.
18111825
</para>
1812-
1813-
<para>
1814-
<literal>ROWS FROM( ... )</> is an extension of the SQL standard.
1815-
</para>
18161826
</refsect2>
18171827

18181828
<refsect2>
@@ -1910,9 +1920,13 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
19101920
<title>Nonstandard Clauses</title>
19111921

19121922
<para>
1913-
The clause<literal>DISTINCT ON</literal> isnot defined in the
1923+
<literal>DISTINCT ON ( ... )</literal> isan extension of the
19141924
SQL standard.
19151925
</para>
1926+
1927+
<para>
1928+
<literal>ROWS FROM( ... )</> is an extension of the SQL standard.
1929+
</para>
19161930
</refsect2>
19171931
</refsect1>
19181932
</refentry>

‎src/backend/parser/analyze.c

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2018,6 +2018,19 @@ transformReturningList(ParseState *pstate, List *returningList)
20182018
/* transform RETURNING identically to a SELECT targetlist */
20192019
rlist=transformTargetList(pstate,returningList,EXPR_KIND_RETURNING);
20202020

2021+
/*
2022+
* Complain if the nonempty tlist expanded to nothing (which is possible
2023+
* if it contains only a star-expansion of a zero-column table). If we
2024+
* allow this, the parsed Query will look like it didn't have RETURNING,
2025+
* with results that would probably surprise the user.
2026+
*/
2027+
if (rlist==NIL)
2028+
ereport(ERROR,
2029+
(errcode(ERRCODE_SYNTAX_ERROR),
2030+
errmsg("RETURNING must have at least one column"),
2031+
parser_errposition(pstate,
2032+
exprLocation(linitial(returningList)))));
2033+
20212034
/* mark column origins */
20222035
markTargetListOrigins(pstate,rlist);
20232036

‎src/backend/parser/gram.y

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -334,7 +334,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
334334
name_listfrom_clausefrom_listopt_array_bounds
335335
qualified_name_listany_nameany_name_list
336336
any_operatorexpr_listattrs
337-
target_listinsert_column_listset_target_list
337+
target_listopt_target_listinsert_column_listset_target_list
338338
set_clause_listset_clausemultiple_set_clause
339339
ctext_expr_listctext_rowdef_listindirectionopt_indirection
340340
reloption_listgroup_clauseTriggerFuncArgsselect_limit
@@ -9259,7 +9259,7 @@ select_clause:
92599259
* However, this is not checked by the grammar; parse analysis must check it.
92609260
*/
92619261
simple_select:
9262-
SELECTopt_distincttarget_list
9262+
SELECTopt_distinctopt_target_list
92639263
into_clausefrom_clausewhere_clause
92649264
group_clausehaving_clausewindow_clause
92659265
{
@@ -12215,6 +12215,10 @@ ctext_row: '(' ctext_expr_list ')'{ $$ = $2; }
1221512215
*
1221612216
*****************************************************************************/
1221712217

12218+
opt_target_list: target_list{ $$ = $1; }
12219+
|/* EMPTY*/{ $$ = NIL; }
12220+
;
12221+
1221812222
target_list:
1221912223
target_el{ $$ =list_make1($1); }
1222012224
| target_list',' target_el{ $$ =lappend($1, $3); }

‎src/backend/parser/parse_clause.c

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2011,6 +2011,20 @@ transformDistinctClause(ParseState *pstate,
20112011
true);
20122012
}
20132013

2014+
/*
2015+
* Complain if we found nothing to make DISTINCT. Returning an empty list
2016+
* would cause the parsed Query to look like it didn't have DISTINCT, with
2017+
* results that would probably surprise the user. Note: this case is
2018+
* presently impossible for aggregates because of grammar restrictions,
2019+
* but we check anyway.
2020+
*/
2021+
if (result==NIL)
2022+
ereport(ERROR,
2023+
(errcode(ERRCODE_SYNTAX_ERROR),
2024+
is_agg ?
2025+
errmsg("an aggregate with DISTINCT must have at least one argument") :
2026+
errmsg("SELECT DISTINCT must have at least one column")));
2027+
20142028
returnresult;
20152029
}
20162030

@@ -2115,6 +2129,11 @@ transformDistinctOnClause(ParseState *pstate, List *distinctlist,
21152129
true);
21162130
}
21172131

2132+
/*
2133+
* An empty result list is impossible here because of grammar restrictions.
2134+
*/
2135+
Assert(result!=NIL);
2136+
21182137
returnresult;
21192138
}
21202139

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

Lines changed: 8 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -15,26 +15,24 @@ select 1;
1515
--
1616
--
1717
-- SELECT
18-
--missing relation name
18+
--this used to be a syntax error, but now we allow an empty target list
1919
select;
20-
ERROR: syntax error at or near ";"
21-
LINE 1: select;
22-
^
20+
--
21+
(1 row)
22+
2323
-- no such relation
2424
select * from nonesuch;
2525
ERROR: relation "nonesuch" does not exist
2626
LINE 1: select * from nonesuch;
2727
^
28-
-- missing target list
29-
select from pg_database;
30-
ERROR: syntax error at or near "from"
31-
LINE 1: select from pg_database;
32-
^
3328
-- bad name in target list
3429
select nonesuch from pg_database;
3530
ERROR: column "nonesuch" does not exist
3631
LINE 1: select nonesuch from pg_database;
3732
^
33+
-- empty distinct list isn't OK
34+
select distinct from pg_database;
35+
ERROR: SELECT DISTINCT must have at least one column
3836
-- bad attribute name on lhs of operator
3937
select * from pg_database where nonesuch = pg_database.datname;
4038
ERROR: column "nonesuch" does not exist
@@ -45,12 +43,7 @@ select * from pg_database where pg_database.datname = nonesuch;
4543
ERROR: column "nonesuch" does not exist
4644
LINE 1: ...ect * from pg_database where pg_database.datname = nonesuch;
4745
^
48-
-- bad select distinct on syntax, distinct attribute missing
49-
select distinct on (foobar) from pg_database;
50-
ERROR: syntax error at or near "from"
51-
LINE 1: select distinct on (foobar) from pg_database;
52-
^
53-
-- bad select distinct on syntax, distinct attribute not in target list
46+
-- bad attribute name in select distinct on
5447
select distinct on (foobar) * from pg_database;
5548
ERROR: column "foobar" does not exist
5649
LINE 1: select distinct on (foobar) * from pg_database;

‎src/test/regress/sql/errors.sql

Lines changed: 6 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -16,28 +16,25 @@ select 1;
1616
--
1717
-- SELECT
1818

19-
--missing relation name
19+
--this used to be a syntax error, but now we allow an empty target list
2020
select;
2121

2222
-- no such relation
2323
select*from nonesuch;
2424

25-
-- missing target list
26-
selectfrom pg_database;
2725
-- bad name in target list
2826
select nonesuchfrom pg_database;
27+
28+
-- empty distinct list isn't OK
29+
select distinctfrom pg_database;
30+
2931
-- bad attribute name on lhs of operator
3032
select*from pg_databasewhere nonesuch=pg_database.datname;
3133

3234
-- bad attribute name on rhs of operator
3335
select*from pg_databasewherepg_database.datname= nonesuch;
3436

35-
36-
-- bad select distinct on syntax, distinct attribute missing
37-
select distincton (foobar)from pg_database;
38-
39-
40-
-- bad select distinct on syntax, distinct attribute not in target list
37+
-- bad attribute name in select distinct on
4138
select distincton (foobar)*from pg_database;
4239

4340

0 commit comments

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