Skip to content

Commit 935277b

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 b059a24 commit 935277b

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
@@ -248,10 +248,76 @@ static void
248248
analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
249249
{
250250
Query *query;
251+
CTESearchClause *search_clause = cte->search_clause;
252+
CTECycleClause *cycle_clause = cte->cycle_clause;
251253

252254
/* Analysis not done already */
253255
Assert(!IsA(cte->ctequery, Query));
254256

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

@@ -346,7 +412,10 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
346412
elog(ERROR, "wrong number of output columns in WITH");
347413
}
348414

349-
if (cte->search_clause || cte->cycle_clause)
415+
/*
416+
* Now make validity checks on the SEARCH and CYCLE clauses, if present.
417+
*/
418+
if (search_clause || cycle_clause)
350419
{
351420
Query *ctequery;
352421
SetOperationStmt *sos;
@@ -393,12 +462,12 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
393462
errmsg("with a SEARCH or CYCLE clause, the right side of the UNION must be a SELECT")));
394463
}
395464

396-
if (cte->search_clause)
465+
if (search_clause)
397466
{
398467
ListCell *lc;
399468
List *seen = NIL;
400469

401-
foreach(lc, cte->search_clause->search_col_list)
470+
foreach(lc, search_clause->search_col_list)
402471
{
403472
String *colname = lfirst_node(String, lc);
404473

@@ -407,33 +476,31 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
407476
(errcode(ERRCODE_SYNTAX_ERROR),
408477
errmsg("search column \"%s\" not in WITH query column list",
409478
strVal(colname)),
410-
parser_errposition(pstate, cte->search_clause->location)));
479+
parser_errposition(pstate, search_clause->location)));
411480

412481
if (list_member(seen, colname))
413482
ereport(ERROR,
414483
(errcode(ERRCODE_DUPLICATE_COLUMN),
415484
errmsg("search column \"%s\" specified more than once",
416485
strVal(colname)),
417-
parser_errposition(pstate, cte->search_clause->location)));
486+
parser_errposition(pstate, search_clause->location)));
418487
seen = lappend(seen, colname);
419488
}
420489

421-
if (list_member(cte->ctecolnames, makeString(cte->search_clause->search_seq_column)))
490+
if (list_member(cte->ctecolnames, makeString(search_clause->search_seq_column)))
422491
ereport(ERROR,
423492
errcode(ERRCODE_SYNTAX_ERROR),
424493
errmsg("search sequence column name \"%s\" already used in WITH query column list",
425-
cte->search_clause->search_seq_column),
426-
parser_errposition(pstate, cte->search_clause->location));
494+
search_clause->search_seq_column),
495+
parser_errposition(pstate, search_clause->location));
427496
}
428497

429-
if (cte->cycle_clause)
498+
if (cycle_clause)
430499
{
431500
ListCell *lc;
432501
List *seen = NIL;
433-
TypeCacheEntry *typentry;
434-
Oid op;
435502

436-
foreach(lc, cte->cycle_clause->cycle_col_list)
503+
foreach(lc, cycle_clause->cycle_col_list)
437504
{
438505
String *colname = lfirst_node(String, lc);
439506

@@ -442,97 +509,54 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte)
442509
(errcode(ERRCODE_SYNTAX_ERROR),
443510
errmsg("cycle column \"%s\" not in WITH query column list",
444511
strVal(colname)),
445-
parser_errposition(pstate, cte->cycle_clause->location)));
512+
parser_errposition(pstate, cycle_clause->location)));
446513

447514
if (list_member(seen, colname))
448515
ereport(ERROR,
449516
(errcode(ERRCODE_DUPLICATE_COLUMN),
450517
errmsg("cycle column \"%s\" specified more than once",
451518
strVal(colname)),
452-
parser_errposition(pstate, cte->cycle_clause->location)));
519+
parser_errposition(pstate, cycle_clause->location)));
453520
seen = lappend(seen, colname);
454521
}
455522

456-
if (list_member(cte->ctecolnames, makeString(cte->cycle_clause->cycle_mark_column)))
523+
if (list_member(cte->ctecolnames, makeString(cycle_clause->cycle_mark_column)))
457524
ereport(ERROR,
458525
errcode(ERRCODE_SYNTAX_ERROR),
459526
errmsg("cycle mark column name \"%s\" already used in WITH query column list",
460-
cte->cycle_clause->cycle_mark_column),
461-
parser_errposition(pstate, cte->cycle_clause->location));
462-
463-
cte->cycle_clause->cycle_mark_value = transformExpr(pstate, cte->cycle_clause->cycle_mark_value,
464-
EXPR_KIND_CYCLE_MARK);
465-
cte->cycle_clause->cycle_mark_default = transformExpr(pstate, cte->cycle_clause->cycle_mark_default,
466-
EXPR_KIND_CYCLE_MARK);
527+
cycle_clause->cycle_mark_column),
528+
parser_errposition(pstate, cycle_clause->location));
467529

468-
if (list_member(cte->ctecolnames, makeString(cte->cycle_clause->cycle_path_column)))
530+
if (list_member(cte->ctecolnames, makeString(cycle_clause->cycle_path_column)))
469531
ereport(ERROR,
470532
errcode(ERRCODE_SYNTAX_ERROR),
471533
errmsg("cycle path column name \"%s\" already used in WITH query column list",
472-
cte->cycle_clause->cycle_path_column),
473-
parser_errposition(pstate, cte->cycle_clause->location));
534+
cycle_clause->cycle_path_column),
535+
parser_errposition(pstate, cycle_clause->location));
474536

475-
if (strcmp(cte->cycle_clause->cycle_mark_column,
476-
cte->cycle_clause->cycle_path_column) == 0)
537+
if (strcmp(cycle_clause->cycle_mark_column,
538+
cycle_clause->cycle_path_column) == 0)
477539
ereport(ERROR,
478540
errcode(ERRCODE_SYNTAX_ERROR),
479541
errmsg("cycle mark column name and cycle path column name are the same"),
480-
parser_errposition(pstate, cte->cycle_clause->location));
481-
482-
cte->cycle_clause->cycle_mark_type = select_common_type(pstate,
483-
list_make2(cte->cycle_clause->cycle_mark_value,
484-
cte->cycle_clause->cycle_mark_default),
485-
"CYCLE", NULL);
486-
cte->cycle_clause->cycle_mark_value = coerce_to_common_type(pstate,
487-
cte->cycle_clause->cycle_mark_value,
488-
cte->cycle_clause->cycle_mark_type,
489-
"CYCLE/SET/TO");
490-
cte->cycle_clause->cycle_mark_default = coerce_to_common_type(pstate,
491-
cte->cycle_clause->cycle_mark_default,
492-
cte->cycle_clause->cycle_mark_type,
493-
"CYCLE/SET/DEFAULT");
494-
495-
cte->cycle_clause->cycle_mark_typmod = select_common_typmod(pstate,
496-
list_make2(cte->cycle_clause->cycle_mark_value,
497-
cte->cycle_clause->cycle_mark_default),
498-
cte->cycle_clause->cycle_mark_type);
499-
500-
cte->cycle_clause->cycle_mark_collation = select_common_collation(pstate,
501-
list_make2(cte->cycle_clause->cycle_mark_value,
502-
cte->cycle_clause->cycle_mark_default),
503-
true);
504-
505-
typentry = lookup_type_cache(cte->cycle_clause->cycle_mark_type, TYPECACHE_EQ_OPR);
506-
if (!typentry->eq_opr)
507-
ereport(ERROR,
508-
errcode(ERRCODE_UNDEFINED_FUNCTION),
509-
errmsg("could not identify an equality operator for type %s",
510-
format_type_be(cte->cycle_clause->cycle_mark_type)));
511-
op = get_negator(typentry->eq_opr);
512-
if (!op)
513-
ereport(ERROR,
514-
errcode(ERRCODE_UNDEFINED_FUNCTION),
515-
errmsg("could not identify an inequality operator for type %s",
516-
format_type_be(cte->cycle_clause->cycle_mark_type)));
517-
518-
cte->cycle_clause->cycle_mark_neop = op;
542+
parser_errposition(pstate, cycle_clause->location));
519543
}
520544

521-
if (cte->search_clause && cte->cycle_clause)
545+
if (search_clause && cycle_clause)
522546
{
523-
if (strcmp(cte->search_clause->search_seq_column,
524-
cte->cycle_clause->cycle_mark_column) == 0)
547+
if (strcmp(search_clause->search_seq_column,
548+
cycle_clause->cycle_mark_column) == 0)
525549
ereport(ERROR,
526550
errcode(ERRCODE_SYNTAX_ERROR),
527551
errmsg("search sequence column name and cycle mark column name are the same"),
528-
parser_errposition(pstate, cte->search_clause->location));
552+
parser_errposition(pstate, search_clause->location));
529553

530-
if (strcmp(cte->search_clause->search_seq_column,
531-
cte->cycle_clause->cycle_path_column) == 0)
554+
if (strcmp(search_clause->search_seq_column,
555+
cycle_clause->cycle_path_column) == 0)
532556
ereport(ERROR,
533557
errcode(ERRCODE_SYNTAX_ERROR),
534558
errmsg("search sequence column name and cycle path column name are the same"),
535-
parser_errposition(pstate, cte->search_clause->location));
559+
parser_errposition(pstate, search_clause->location));
536560
}
537561
}
538562

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

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

1255+
with recursive test as (
1256+
select 0 as x
1257+
union all
1258+
select (x + 1) % 10
1259+
from test
1260+
where not is_cycle -- redundant, but legal
1261+
) cycle x set is_cycle using path
1262+
select * from test;
1263+
x | is_cycle | path
1264+
---+----------+-----------------------------------------------
1265+
0 | f | {(0)}
1266+
1 | f | {(0),(1)}
1267+
2 | f | {(0),(1),(2)}
1268+
3 | f | {(0),(1),(2),(3)}
1269+
4 | f | {(0),(1),(2),(3),(4)}
1270+
5 | f | {(0),(1),(2),(3),(4),(5)}
1271+
6 | f | {(0),(1),(2),(3),(4),(5),(6)}
1272+
7 | f | {(0),(1),(2),(3),(4),(5),(6),(7)}
1273+
8 | f | {(0),(1),(2),(3),(4),(5),(6),(7),(8)}
1274+
9 | f | {(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)}
1275+
0 | t | {(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(0)}
1276+
(11 rows)
1277+
12551278
-- multiple CTEs
12561279
with recursive
12571280
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
@@ -613,6 +613,15 @@ with recursive test as (
613613
) cycle x set is_cycle using path
614614
select * from test;
615615

616+
with recursive test as (
617+
select 0 as x
618+
union all
619+
select (x + 1) % 10
620+
from test
621+
where not is_cycle -- redundant, but legal
622+
) cycle x set is_cycle using path
623+
select * from test;
624+
616625
-- multiple CTEs
617626
with recursive
618627
graph(f, t, label) as (

0 commit comments

Comments
 (0)