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

Commitea5ae4c

Browse files
committed
Fix inability to reference CYCLE column from inside its CTE.
Such references failed with "cache lookup failed for type 0"because we didn't resolve the type of the CYCLE column until afteranalyzing the CTE's query. We can just move that processingto before the recursive parse_sub_analyze call, though.While here, invent a couple of local variables to make thiscode less egregiously wider-than-80-columns.Per bug #17723 from Vik Fearing. Back-patch to v14 wherethe CYCLE feature was added.Discussion:https://postgr.es/m/17723-2c4985ff111e7bba@postgresql.org
1 parent171d774 commitea5ae4c

File tree

3 files changed

+129
-73
lines changed

3 files changed

+129
-73
lines changed

‎src/backend/parser/parse_cte.c‎

Lines changed: 97 additions & 73 deletions
Original file line numberDiff line numberDiff line change
@@ -241,10 +241,76 @@ static void
241241
analyzeCTE(ParseState*pstate,CommonTableExpr*cte)
242242
{
243243
Query*query;
244+
CTESearchClause*search_clause=cte->search_clause;
245+
CTECycleClause*cycle_clause=cte->cycle_clause;
244246

245247
/* Analysis not done already */
246248
Assert(!IsA(cte->ctequery,Query));
247249

250+
/*
251+
* Before analyzing the CTE's query, we'd better identify the data type of
252+
* the cycle mark column if any, since the query could refer to that.
253+
* Other validity checks on the cycle clause will be done afterwards.
254+
*/
255+
if (cycle_clause)
256+
{
257+
TypeCacheEntry*typentry;
258+
Oidop;
259+
260+
cycle_clause->cycle_mark_value=
261+
transformExpr(pstate,cycle_clause->cycle_mark_value,
262+
EXPR_KIND_CYCLE_MARK);
263+
cycle_clause->cycle_mark_default=
264+
transformExpr(pstate,cycle_clause->cycle_mark_default,
265+
EXPR_KIND_CYCLE_MARK);
266+
267+
cycle_clause->cycle_mark_type=
268+
select_common_type(pstate,
269+
list_make2(cycle_clause->cycle_mark_value,
270+
cycle_clause->cycle_mark_default),
271+
"CYCLE",NULL);
272+
cycle_clause->cycle_mark_value=
273+
coerce_to_common_type(pstate,
274+
cycle_clause->cycle_mark_value,
275+
cycle_clause->cycle_mark_type,
276+
"CYCLE/SET/TO");
277+
cycle_clause->cycle_mark_default=
278+
coerce_to_common_type(pstate,
279+
cycle_clause->cycle_mark_default,
280+
cycle_clause->cycle_mark_type,
281+
"CYCLE/SET/DEFAULT");
282+
283+
cycle_clause->cycle_mark_typmod=
284+
select_common_typmod(pstate,
285+
list_make2(cycle_clause->cycle_mark_value,
286+
cycle_clause->cycle_mark_default),
287+
cycle_clause->cycle_mark_type);
288+
289+
cycle_clause->cycle_mark_collation=
290+
select_common_collation(pstate,
291+
list_make2(cycle_clause->cycle_mark_value,
292+
cycle_clause->cycle_mark_default),
293+
true);
294+
295+
/* Might as well look up the relevant <> operator while we are at it */
296+
typentry=lookup_type_cache(cycle_clause->cycle_mark_type,
297+
TYPECACHE_EQ_OPR);
298+
if (!OidIsValid(typentry->eq_opr))
299+
ereport(ERROR,
300+
errcode(ERRCODE_UNDEFINED_FUNCTION),
301+
errmsg("could not identify an equality operator for type %s",
302+
format_type_be(cycle_clause->cycle_mark_type)));
303+
op=get_negator(typentry->eq_opr);
304+
if (!OidIsValid(op))
305+
ereport(ERROR,
306+
errcode(ERRCODE_UNDEFINED_FUNCTION),
307+
errmsg("could not identify an inequality operator for type %s",
308+
format_type_be(cycle_clause->cycle_mark_type)));
309+
310+
cycle_clause->cycle_mark_neop=op;
311+
}
312+
313+
/* Now we can get on with analyzing the CTE's query */
248314
query=parse_sub_analyze(cte->ctequery,pstate,cte, false, true);
249315
cte->ctequery= (Node*)query;
250316

@@ -339,7 +405,10 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
339405
elog(ERROR,"wrong number of output columns in WITH");
340406
}
341407

342-
if (cte->search_clause||cte->cycle_clause)
408+
/*
409+
* Now make validity checks on the SEARCH and CYCLE clauses, if present.
410+
*/
411+
if (search_clause||cycle_clause)
343412
{
344413
Query*ctequery;
345414
SetOperationStmt*sos;
@@ -386,12 +455,12 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
386455
errmsg("with a SEARCH or CYCLE clause, the right side of the UNION must be a SELECT")));
387456
}
388457

389-
if (cte->search_clause)
458+
if (search_clause)
390459
{
391460
ListCell*lc;
392461
List*seen=NIL;
393462

394-
foreach(lc,cte->search_clause->search_col_list)
463+
foreach(lc,search_clause->search_col_list)
395464
{
396465
Value*colname=lfirst(lc);
397466

@@ -400,33 +469,31 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
400469
(errcode(ERRCODE_SYNTAX_ERROR),
401470
errmsg("search column \"%s\" not in WITH query column list",
402471
strVal(colname)),
403-
parser_errposition(pstate,cte->search_clause->location)));
472+
parser_errposition(pstate,search_clause->location)));
404473

405474
if (list_member(seen,colname))
406475
ereport(ERROR,
407476
(errcode(ERRCODE_DUPLICATE_COLUMN),
408477
errmsg("search column \"%s\" specified more than once",
409478
strVal(colname)),
410-
parser_errposition(pstate,cte->search_clause->location)));
479+
parser_errposition(pstate,search_clause->location)));
411480
seen=lappend(seen,colname);
412481
}
413482

414-
if (list_member(cte->ctecolnames,makeString(cte->search_clause->search_seq_column)))
483+
if (list_member(cte->ctecolnames,makeString(search_clause->search_seq_column)))
415484
ereport(ERROR,
416485
errcode(ERRCODE_SYNTAX_ERROR),
417486
errmsg("search sequence column name \"%s\" already used in WITH query column list",
418-
cte->search_clause->search_seq_column),
419-
parser_errposition(pstate,cte->search_clause->location));
487+
search_clause->search_seq_column),
488+
parser_errposition(pstate,search_clause->location));
420489
}
421490

422-
if (cte->cycle_clause)
491+
if (cycle_clause)
423492
{
424493
ListCell*lc;
425494
List*seen=NIL;
426-
TypeCacheEntry*typentry;
427-
Oidop;
428495

429-
foreach(lc,cte->cycle_clause->cycle_col_list)
496+
foreach(lc,cycle_clause->cycle_col_list)
430497
{
431498
Value*colname=lfirst(lc);
432499

@@ -435,97 +502,54 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
435502
(errcode(ERRCODE_SYNTAX_ERROR),
436503
errmsg("cycle column \"%s\" not in WITH query column list",
437504
strVal(colname)),
438-
parser_errposition(pstate,cte->cycle_clause->location)));
505+
parser_errposition(pstate,cycle_clause->location)));
439506

440507
if (list_member(seen,colname))
441508
ereport(ERROR,
442509
(errcode(ERRCODE_DUPLICATE_COLUMN),
443510
errmsg("cycle column \"%s\" specified more than once",
444511
strVal(colname)),
445-
parser_errposition(pstate,cte->cycle_clause->location)));
512+
parser_errposition(pstate,cycle_clause->location)));
446513
seen=lappend(seen,colname);
447514
}
448515

449-
if (list_member(cte->ctecolnames,makeString(cte->cycle_clause->cycle_mark_column)))
516+
if (list_member(cte->ctecolnames,makeString(cycle_clause->cycle_mark_column)))
450517
ereport(ERROR,
451518
errcode(ERRCODE_SYNTAX_ERROR),
452519
errmsg("cycle mark column name \"%s\" already used in WITH query column list",
453-
cte->cycle_clause->cycle_mark_column),
454-
parser_errposition(pstate,cte->cycle_clause->location));
455-
456-
cte->cycle_clause->cycle_mark_value=transformExpr(pstate,cte->cycle_clause->cycle_mark_value,
457-
EXPR_KIND_CYCLE_MARK);
458-
cte->cycle_clause->cycle_mark_default=transformExpr(pstate,cte->cycle_clause->cycle_mark_default,
459-
EXPR_KIND_CYCLE_MARK);
520+
cycle_clause->cycle_mark_column),
521+
parser_errposition(pstate,cycle_clause->location));
460522

461-
if (list_member(cte->ctecolnames,makeString(cte->cycle_clause->cycle_path_column)))
523+
if (list_member(cte->ctecolnames,makeString(cycle_clause->cycle_path_column)))
462524
ereport(ERROR,
463525
errcode(ERRCODE_SYNTAX_ERROR),
464526
errmsg("cycle path column name \"%s\" already used in WITH query column list",
465-
cte->cycle_clause->cycle_path_column),
466-
parser_errposition(pstate,cte->cycle_clause->location));
527+
cycle_clause->cycle_path_column),
528+
parser_errposition(pstate,cycle_clause->location));
467529

468-
if (strcmp(cte->cycle_clause->cycle_mark_column,
469-
cte->cycle_clause->cycle_path_column)==0)
530+
if (strcmp(cycle_clause->cycle_mark_column,
531+
cycle_clause->cycle_path_column)==0)
470532
ereport(ERROR,
471533
errcode(ERRCODE_SYNTAX_ERROR),
472534
errmsg("cycle mark column name and cycle path column name are the same"),
473-
parser_errposition(pstate,cte->cycle_clause->location));
474-
475-
cte->cycle_clause->cycle_mark_type=select_common_type(pstate,
476-
list_make2(cte->cycle_clause->cycle_mark_value,
477-
cte->cycle_clause->cycle_mark_default),
478-
"CYCLE",NULL);
479-
cte->cycle_clause->cycle_mark_value=coerce_to_common_type(pstate,
480-
cte->cycle_clause->cycle_mark_value,
481-
cte->cycle_clause->cycle_mark_type,
482-
"CYCLE/SET/TO");
483-
cte->cycle_clause->cycle_mark_default=coerce_to_common_type(pstate,
484-
cte->cycle_clause->cycle_mark_default,
485-
cte->cycle_clause->cycle_mark_type,
486-
"CYCLE/SET/DEFAULT");
487-
488-
cte->cycle_clause->cycle_mark_typmod=select_common_typmod(pstate,
489-
list_make2(cte->cycle_clause->cycle_mark_value,
490-
cte->cycle_clause->cycle_mark_default),
491-
cte->cycle_clause->cycle_mark_type);
492-
493-
cte->cycle_clause->cycle_mark_collation=select_common_collation(pstate,
494-
list_make2(cte->cycle_clause->cycle_mark_value,
495-
cte->cycle_clause->cycle_mark_default),
496-
true);
497-
498-
typentry=lookup_type_cache(cte->cycle_clause->cycle_mark_type,TYPECACHE_EQ_OPR);
499-
if (!typentry->eq_opr)
500-
ereport(ERROR,
501-
errcode(ERRCODE_UNDEFINED_FUNCTION),
502-
errmsg("could not identify an equality operator for type %s",
503-
format_type_be(cte->cycle_clause->cycle_mark_type)));
504-
op=get_negator(typentry->eq_opr);
505-
if (!op)
506-
ereport(ERROR,
507-
errcode(ERRCODE_UNDEFINED_FUNCTION),
508-
errmsg("could not identify an inequality operator for type %s",
509-
format_type_be(cte->cycle_clause->cycle_mark_type)));
510-
511-
cte->cycle_clause->cycle_mark_neop=op;
535+
parser_errposition(pstate,cycle_clause->location));
512536
}
513537

514-
if (cte->search_clause&&cte->cycle_clause)
538+
if (search_clause&&cycle_clause)
515539
{
516-
if (strcmp(cte->search_clause->search_seq_column,
517-
cte->cycle_clause->cycle_mark_column)==0)
540+
if (strcmp(search_clause->search_seq_column,
541+
cycle_clause->cycle_mark_column)==0)
518542
ereport(ERROR,
519543
errcode(ERRCODE_SYNTAX_ERROR),
520544
errmsg("search sequence column name and cycle mark column name are the same"),
521-
parser_errposition(pstate,cte->search_clause->location));
545+
parser_errposition(pstate,search_clause->location));
522546

523-
if (strcmp(cte->search_clause->search_seq_column,
524-
cte->cycle_clause->cycle_path_column)==0)
547+
if (strcmp(search_clause->search_seq_column,
548+
cycle_clause->cycle_path_column)==0)
525549
ereport(ERROR,
526550
errcode(ERRCODE_SYNTAX_ERROR),
527551
errmsg("search sequence column name and cycle path column name are the same"),
528-
parser_errposition(pstate,cte->search_clause->location));
552+
parser_errposition(pstate,search_clause->location));
529553
}
530554
}
531555

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

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1221,6 +1221,29 @@ select * from test;
12211221
0 | t | {(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(0)}
12221222
(11 rows)
12231223

1224+
with recursive test as (
1225+
select 0 as x
1226+
union all
1227+
select (x + 1) % 10
1228+
from test
1229+
where not is_cycle -- redundant, but legal
1230+
) cycle x set is_cycle using path
1231+
select * from test;
1232+
x | is_cycle | path
1233+
---+----------+-----------------------------------------------
1234+
0 | f | {(0)}
1235+
1 | f | {(0),(1)}
1236+
2 | f | {(0),(1),(2)}
1237+
3 | f | {(0),(1),(2),(3)}
1238+
4 | f | {(0),(1),(2),(3),(4)}
1239+
5 | f | {(0),(1),(2),(3),(4),(5)}
1240+
6 | f | {(0),(1),(2),(3),(4),(5),(6)}
1241+
7 | f | {(0),(1),(2),(3),(4),(5),(6),(7)}
1242+
8 | f | {(0),(1),(2),(3),(4),(5),(6),(7),(8)}
1243+
9 | f | {(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)}
1244+
0 | t | {(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(0)}
1245+
(11 rows)
1246+
12241247
-- multiple CTEs
12251248
with recursive
12261249
graph(f, t, label) as (

‎src/test/regress/sql/with.sql‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -605,6 +605,15 @@ with recursive test as (
605605
) cycle xset is_cycle usingpath
606606
select*from test;
607607

608+
with recursive testas (
609+
select0as x
610+
union all
611+
select (x+1) %10
612+
from test
613+
where not is_cycle-- redundant, but legal
614+
) cycle xset is_cycle usingpath
615+
select*from test;
616+
608617
-- multiple CTEs
609618
with recursive
610619
graph(f, t, label)as (

0 commit comments

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