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

Commitecfed4a

Browse files
committed
Improve error handling of column references in expression transformation
Column references are not allowed in default expressions and partitionbound expressions, and are restricted as such once the transformation oftheir expressions is done. However, trying to use more complex columnreferences can lead to confusing error messages. For example, trying touse a two-field column reference name for default expressions andpartition bounds leads to "missing FROM-clause entry for table", whichmakes no sense in their respective context.In order to make the errors generated more useful, this commit adds moreverbose messages when transforming column references depending on thecontext. This has a little consequence though: for example anexpression using an aggregate with a column reference as argument wouldcause an error to be generated for the column reference, while theaggregate was the problem reported before this commit because columnreferences get transformed first.The confusion exists for default expressions for a long time, and theproblem is new as of v12 for partition bounds. Still per the lack ofcomplaints on the matter no backpatch is done.The patch has been written by Amit Langote and me, and Tom Lane hasprovided the improvement of the documentation for default expressions onthe CREATE TABLE page.Author: Amit Langote, Michael PaquierReviewed-by: Tom LaneDiscussion:https://postgr.es/m/20190326020853.GM2558@paquier.xyz
1 parentd2fd7f7 commitecfed4a

File tree

6 files changed

+147
-34
lines changed

6 files changed

+147
-34
lines changed

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -783,10 +783,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
783783
<para>
784784
The <literal>DEFAULT</literal> clause assigns a default data value for
785785
the column whose column definition it appears within. The value
786-
is any variable-free expression (subqueries and cross-references
787-
to other columns in the current table are not allowed).The
788-
data type of the default expression must match thedata type of the
789-
column.
786+
is any variable-free expression (in particular, cross-references
787+
to other columns in the current table are not allowed).Subqueries
788+
are not allowed either. Thedata type of the default expression must
789+
match the data type of thecolumn.
790790
</para>
791791

792792
<para>

‎src/backend/catalog/heap.c

Lines changed: 4 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -2939,19 +2939,11 @@ cookDefault(ParseState *pstate,
29392939
expr=transformExpr(pstate,raw_default,EXPR_KIND_COLUMN_DEFAULT);
29402940

29412941
/*
2942-
* Make sure default expr does not refer to any vars (we need this check
2943-
* since the pstate includes the target table).
2944-
*/
2945-
if (contain_var_clause(expr))
2946-
ereport(ERROR,
2947-
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
2948-
errmsg("cannot use column references in default expression")));
2949-
2950-
/*
2951-
* transformExpr() should have already rejected subqueries, aggregates,
2952-
* window functions, and SRFs, based on the EXPR_KIND_ for a default
2953-
* expression.
2942+
* transformExpr() should have already rejected column references,
2943+
* subqueries, aggregates, window functions, and SRFs, based on the
2944+
* EXPR_KIND_ for a default expression.
29542945
*/
2946+
Assert(!contain_var_clause(expr));
29552947

29562948
/*
29572949
* Coerce the expression to the correct type and typmod, if given. This

‎src/backend/parser/parse_expr.c

Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -520,6 +520,79 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
520520
CRERR_WRONG_DB,
521521
CRERR_TOO_MANY
522522
}crerr=CRERR_NO_COLUMN;
523+
constchar*err;
524+
525+
/*
526+
* Check to see if the column reference is in an invalid place within the
527+
* query. We allow column references in most places, except in default
528+
* expressions and partition bound expressions.
529+
*/
530+
err=NULL;
531+
switch (pstate->p_expr_kind)
532+
{
533+
caseEXPR_KIND_NONE:
534+
Assert(false);/* can't happen */
535+
break;
536+
caseEXPR_KIND_OTHER:
537+
caseEXPR_KIND_JOIN_ON:
538+
caseEXPR_KIND_JOIN_USING:
539+
caseEXPR_KIND_FROM_SUBSELECT:
540+
caseEXPR_KIND_FROM_FUNCTION:
541+
caseEXPR_KIND_WHERE:
542+
caseEXPR_KIND_POLICY:
543+
caseEXPR_KIND_HAVING:
544+
caseEXPR_KIND_FILTER:
545+
caseEXPR_KIND_WINDOW_PARTITION:
546+
caseEXPR_KIND_WINDOW_ORDER:
547+
caseEXPR_KIND_WINDOW_FRAME_RANGE:
548+
caseEXPR_KIND_WINDOW_FRAME_ROWS:
549+
caseEXPR_KIND_WINDOW_FRAME_GROUPS:
550+
caseEXPR_KIND_SELECT_TARGET:
551+
caseEXPR_KIND_INSERT_TARGET:
552+
caseEXPR_KIND_UPDATE_SOURCE:
553+
caseEXPR_KIND_UPDATE_TARGET:
554+
caseEXPR_KIND_GROUP_BY:
555+
caseEXPR_KIND_ORDER_BY:
556+
caseEXPR_KIND_DISTINCT_ON:
557+
caseEXPR_KIND_LIMIT:
558+
caseEXPR_KIND_OFFSET:
559+
caseEXPR_KIND_RETURNING:
560+
caseEXPR_KIND_VALUES:
561+
caseEXPR_KIND_VALUES_SINGLE:
562+
caseEXPR_KIND_CHECK_CONSTRAINT:
563+
caseEXPR_KIND_DOMAIN_CHECK:
564+
caseEXPR_KIND_FUNCTION_DEFAULT:
565+
caseEXPR_KIND_INDEX_EXPRESSION:
566+
caseEXPR_KIND_INDEX_PREDICATE:
567+
caseEXPR_KIND_ALTER_COL_TRANSFORM:
568+
caseEXPR_KIND_EXECUTE_PARAMETER:
569+
caseEXPR_KIND_TRIGGER_WHEN:
570+
caseEXPR_KIND_PARTITION_EXPRESSION:
571+
caseEXPR_KIND_CALL_ARGUMENT:
572+
caseEXPR_KIND_COPY_WHERE:
573+
/* okay */
574+
break;
575+
576+
caseEXPR_KIND_COLUMN_DEFAULT:
577+
err=_("cannot use column reference in DEFAULT expression");
578+
break;
579+
caseEXPR_KIND_PARTITION_BOUND:
580+
err=_("cannot use column reference in partition bound expression");
581+
break;
582+
583+
/*
584+
* There is intentionally no default: case here, so that the
585+
* compiler will warn if we add a new ParseExprKind without
586+
* extending this switch. If we do see an unrecognized value at
587+
* runtime, the behavior will be the same as for EXPR_KIND_OTHER,
588+
* which is sane anyway.
589+
*/
590+
}
591+
if (err)
592+
ereport(ERROR,
593+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
594+
errmsg_internal("%s",err),
595+
parser_errposition(pstate,cref->location)));
523596

524597
/*
525598
* Give the PreParseColumnRefHook, if any, first shot. If it returns

‎src/backend/parser/parse_utilcmd.c

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -3926,12 +3926,12 @@ transformPartitionBoundValue(ParseState *pstate, Node *val,
39263926
if (!IsA(value,Const))
39273927
value= (Node*)expression_planner((Expr*)value);
39283928

3929-
/* Make sure the expression does not refer to any vars. */
3930-
if (contain_var_clause(value))
3931-
ereport(ERROR,
3932-
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
3933-
errmsg("cannot use column references in partition bound expression"),
3934-
parser_errposition(pstate,exprLocation(value))));
3929+
/*
3930+
* transformExpr() should have already rejected column references,
3931+
* subqueries, aggregates, window functions, and SRFs, based on the
3932+
* EXPR_KIND_ for a default expression.
3933+
*/
3934+
Assert(!contain_var_clause(value));
39353935

39363936
/*
39373937
* Evaluate the expression, assigning the partition key's collation to the

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

Lines changed: 46 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -297,6 +297,40 @@ ERROR: tables declared WITH OIDS are not supported
297297
-- but explicitly not adding oids is still supported
298298
CREATE TEMP TABLE withoutoid() WITHOUT OIDS; DROP TABLE withoutoid;
299299
CREATE TEMP TABLE withoutoid() WITH (oids = false); DROP TABLE withoutoid;
300+
-- check restriction with default expressions
301+
-- invalid use of column reference in default expressions
302+
CREATE TABLE default_expr_column (id int DEFAULT (id));
303+
ERROR: cannot use column reference in DEFAULT expression
304+
LINE 1: CREATE TABLE default_expr_column (id int DEFAULT (id));
305+
^
306+
CREATE TABLE default_expr_column (id int DEFAULT (bar.id));
307+
ERROR: cannot use column reference in DEFAULT expression
308+
LINE 1: CREATE TABLE default_expr_column (id int DEFAULT (bar.id));
309+
^
310+
CREATE TABLE default_expr_agg_column (id int DEFAULT (avg(id)));
311+
ERROR: cannot use column reference in DEFAULT expression
312+
LINE 1: ...TE TABLE default_expr_agg_column (id int DEFAULT (avg(id)));
313+
^
314+
-- invalid column definition
315+
CREATE TABLE default_expr_non_column (a int DEFAULT (avg(non_existent)));
316+
ERROR: cannot use column reference in DEFAULT expression
317+
LINE 1: ...TABLE default_expr_non_column (a int DEFAULT (avg(non_existe...
318+
^
319+
-- invalid use of aggregate
320+
CREATE TABLE default_expr_agg (a int DEFAULT (avg(1)));
321+
ERROR: aggregate functions are not allowed in DEFAULT expressions
322+
LINE 1: CREATE TABLE default_expr_agg (a int DEFAULT (avg(1)));
323+
^
324+
-- invalid use of subquery
325+
CREATE TABLE default_expr_agg (a int DEFAULT (select 1));
326+
ERROR: cannot use subquery in DEFAULT expression
327+
LINE 1: CREATE TABLE default_expr_agg (a int DEFAULT (select 1));
328+
^
329+
-- invalid use of set-returning function
330+
CREATE TABLE default_expr_agg (a int DEFAULT (generate_series(1,3)));
331+
ERROR: set-returning functions are not allowed in DEFAULT expressions
332+
LINE 1: CREATE TABLE default_expr_agg (a int DEFAULT (generate_serie...
333+
^
300334
--
301335
-- Partitioned tables
302336
--
@@ -491,23 +525,23 @@ Partitions: part_null FOR VALUES IN (NULL),
491525

492526
-- forbidden expressions for partition bound with list partitioned table
493527
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename);
494-
ERROR: column"somename" does not exist
528+
ERROR:cannot usecolumnreference in partition bound expression
495529
LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN (somename);
496530
^
497531
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename.somename);
498-
ERROR:missing FROM-clause entry for table "somename"
532+
ERROR:cannot use column reference in partition bound expression
499533
LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN (somename.s...
500534
^
501535
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a);
502-
ERROR: cannot use columnreferences in partition bound expression
536+
ERROR: cannot use columnreference in partition bound expression
503537
LINE 1: ..._bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a);
504538
^
505539
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(a));
506-
ERROR:aggregate functions are not allowedin partition bound
540+
ERROR:cannot use column referencein partition bound expression
507541
LINE 1: ...s_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(a));
508-
^
542+
^
509543
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(somename));
510-
ERROR: column"somename" does not exist
544+
ERROR:cannot usecolumnreference in partition bound expression
511545
LINE 1: ..._fail PARTITION OF list_parted FOR VALUES IN (sum(somename))...
512546
^
513547
CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(1));
@@ -573,27 +607,27 @@ CREATE TABLE range_parted (
573607
-- forbidden expressions for partition bounds with range partitioned table
574608
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
575609
FOR VALUES FROM (somename) TO ('2019-01-01');
576-
ERROR: column"somename" does not exist
610+
ERROR:cannot usecolumnreference in partition bound expression
577611
LINE 2: FOR VALUES FROM (somename) TO ('2019-01-01');
578612
^
579613
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
580614
FOR VALUES FROM (somename.somename) TO ('2019-01-01');
581-
ERROR:missing FROM-clause entry for table "somename"
615+
ERROR:cannot use column reference in partition bound expression
582616
LINE 2: FOR VALUES FROM (somename.somename) TO ('2019-01-01');
583617
^
584618
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
585619
FOR VALUES FROM (a) TO ('2019-01-01');
586-
ERROR: cannot use columnreferences in partition bound expression
620+
ERROR: cannot use columnreference in partition bound expression
587621
LINE 2: FOR VALUES FROM (a) TO ('2019-01-01');
588622
^
589623
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
590624
FOR VALUES FROM (max(a)) TO ('2019-01-01');
591-
ERROR:aggregate functions are not allowedin partition bound
625+
ERROR:cannot use column referencein partition bound expression
592626
LINE 2: FOR VALUES FROM (max(a)) TO ('2019-01-01');
593-
^
627+
^
594628
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
595629
FOR VALUES FROM (max(somename)) TO ('2019-01-01');
596-
ERROR: column"somename" does not exist
630+
ERROR:cannot usecolumnreference in partition bound expression
597631
LINE 2: FOR VALUES FROM (max(somename)) TO ('2019-01-01');
598632
^
599633
CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted

‎src/test/regress/sql/create_table.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -304,6 +304,20 @@ CREATE TABLE withoid() WITH (oids = true);
304304
CREATE TEMP TABLE withoutoid() WITHOUT OIDS;DROPTABLEwithoutoid;
305305
CREATE TEMP TABLE withoutoid() WITH (oids= false);DROPTABLEwithoutoid;
306306

307+
-- check restriction with default expressions
308+
-- invalid use of column reference in default expressions
309+
CREATETABLEdefault_expr_column (idint DEFAULT (id));
310+
CREATETABLEdefault_expr_column (idint DEFAULT (bar.id));
311+
CREATETABLEdefault_expr_agg_column (idint DEFAULT (avg(id)));
312+
-- invalid column definition
313+
CREATETABLEdefault_expr_non_column (aint DEFAULT (avg(non_existent)));
314+
-- invalid use of aggregate
315+
CREATETABLEdefault_expr_agg (aint DEFAULT (avg(1)));
316+
-- invalid use of subquery
317+
CREATETABLEdefault_expr_agg (aint DEFAULT (select1));
318+
-- invalid use of set-returning function
319+
CREATETABLEdefault_expr_agg (aint DEFAULT (generate_series(1,3)));
320+
307321
--
308322
-- Partitioned tables
309323
--

0 commit comments

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