Skip to content

Commit 4d5f651

Browse files
tglsfdctglsfdc
committed
Fix planner error with pulling up subquery expressions into function RTEs.
If a function-in-FROM laterally references the output of some sub-SELECT earlier in the FROM clause, and we are able to flatten that sub-SELECT into the outer query, the expression(s) copied into the function RTE missed being processed by eval_const_expressions. This'd lead to trouble and probable crashes at execution if such expressions contained named-argument function call syntax or functions with defaulted arguments. The bug is masked if the query contains any explicit JOIN syntax, which may help explain why we'd not noticed. Per bug #17227 from Bernd Dorn. This is an oversight in commit 7266d09, so back-patch to v13 where that came in. Discussion: https://postgr.es/m/17227-5a28ed1512189fa4@postgresql.org
1 parent 811051c commit 4d5f651

File tree

3 files changed

+49
-4
lines changed

3 files changed

+49
-4
lines changed

‎src/backend/optimizer/plan/planner.c

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1084,8 +1084,10 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
10841084

10851085
/*
10861086
* Simplify constant expressions. For function RTEs, this was already
1087-
* done by preprocess_function_rtes ... but we have to do it again if the
1088-
* RTE is LATERAL and might have contained join alias variables.
1087+
* done by preprocess_function_rtes. (But note we must do it again for
1088+
* EXPRKIND_RTFUNC_LATERAL, because those might by now contain
1089+
* un-simplified subexpressions inserted by flattening of subqueries or
1090+
* join alias variables.)
10891091
*
10901092
* Note: an essential effect of this is to convert named-argument function
10911093
* calls to positional notation and insert the current actual values of
@@ -1099,8 +1101,7 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
10991101
* careful to maintain AND/OR flatness --- that is, do not generate a tree
11001102
* with AND directly under AND, nor OR directly under OR.
11011103
*/
1102-
if (!(kind == EXPRKIND_RTFUNC ||
1103-
(kind == EXPRKIND_RTFUNC_LATERAL && !root->hasJoinRTEs)))
1104+
if (kind != EXPRKIND_RTFUNC)
11041105
expr = eval_const_expressions(root, expr);
11051106

11061107
/*

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

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2416,3 +2416,32 @@ select *, row_to_json(u) from unnest(array[]::rngfunc2[]) u;
24162416
(0 rows)
24172417

24182418
drop type rngfunc2;
2419+
-- check handling of functions pulled up into function RTEs (bug #17227)
2420+
explain (verbose, costs off)
2421+
select * from
2422+
(select jsonb_path_query_array(module->'lectures', '$[*]') as lecture
2423+
from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb])
2424+
as unnested_modules(module)) as ss,
2425+
jsonb_to_recordset(ss.lecture) as j (id text);
2426+
QUERY PLAN
2427+
--------------------------------------------------------------------------------------------------------------------------------------------------------
2428+
Nested Loop
2429+
Output: jsonb_path_query_array((unnested_modules.module -> 'lectures'::text), '$[*]'::jsonpath, '{}'::jsonb, false), j.id
2430+
-> Function Scan on pg_catalog.unnest unnested_modules
2431+
Output: unnested_modules.module
2432+
Function Call: unnest('{"{\"lectures\": [{\"id\": \"1\"}]}"}'::jsonb[])
2433+
-> Function Scan on pg_catalog.jsonb_to_recordset j
2434+
Output: j.id
2435+
Function Call: jsonb_to_recordset(jsonb_path_query_array((unnested_modules.module -> 'lectures'::text), '$[*]'::jsonpath, '{}'::jsonb, false))
2436+
(8 rows)
2437+
2438+
select * from
2439+
(select jsonb_path_query_array(module->'lectures', '$[*]') as lecture
2440+
from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb])
2441+
as unnested_modules(module)) as ss,
2442+
jsonb_to_recordset(ss.lecture) as j (id text);
2443+
lecture | id
2444+
---------------+----
2445+
[{"id": "1"}] | 1
2446+
(1 row)
2447+

‎src/test/regress/sql/rangefuncs.sql

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -767,3 +767,18 @@ select *, row_to_json(u) from unnest(array[null::rngfunc2, (1,'foo')::rngfunc2,
767767
select *, row_to_json(u) from unnest(array[]::rngfunc2[]) u;
768768

769769
drop type rngfunc2;
770+
771+
-- check handling of functions pulled up into function RTEs (bug #17227)
772+
773+
explain (verbose, costs off)
774+
select * from
775+
(select jsonb_path_query_array(module->'lectures', '$[*]') as lecture
776+
from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb])
777+
as unnested_modules(module)) as ss,
778+
jsonb_to_recordset(ss.lecture) as j (id text);
779+
780+
select * from
781+
(select jsonb_path_query_array(module->'lectures', '$[*]') as lecture
782+
from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb])
783+
as unnested_modules(module)) as ss,
784+
jsonb_to_recordset(ss.lecture) as j (id text);

0 commit comments

Comments
 (0)