Skip to content

Commit ea5ae4c

Browse files
tglsfdctglsfdc
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 after analyzing the CTE's query. We can just move that processing to before the recursive parse_sub_analyze call, though. While here, invent a couple of local variables to make this code less egregiously wider-than-80-columns. Per bug #17723 from Vik Fearing. Back-patch to v14 where the CYCLE feature was added. Discussion: https://postgr.es/m/17723-2c4985ff111e7bba@postgresql.org
1 parent 171d774 commit ea5ae4c

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+
Oid op;
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-
Oid op;
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 x set is_cycle using path
606606
select * from test;
607607

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

0 commit comments

Comments
 (0)