Skip to content

Commit fdd6a4d

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 2cdf97f commit fdd6a4d

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
@@ -1074,8 +1074,10 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
10741074

10751075
/*
10761076
* Simplify constant expressions. For function RTEs, this was already
1077-
* done by preprocess_function_rtes ... but we have to do it again if the
1078-
* RTE is LATERAL and might have contained join alias variables.
1077+
* done by preprocess_function_rtes. (But note we must do it again for
1078+
* EXPRKIND_RTFUNC_LATERAL, because those might by now contain
1079+
* un-simplified subexpressions inserted by flattening of subqueries or
1080+
* join alias variables.)
10791081
*
10801082
* Note: an essential effect of this is to convert named-argument function
10811083
* calls to positional notation and insert the current actual values of
@@ -1089,8 +1091,7 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
10891091
* careful to maintain AND/OR flatness --- that is, do not generate a tree
10901092
* with AND directly under AND, nor OR directly under OR.
10911093
*/
1092-
if (!(kind == EXPRKIND_RTFUNC ||
1093-
(kind == EXPRKIND_RTFUNC_LATERAL && !root->hasJoinRTEs)))
1094+
if (kind != EXPRKIND_RTFUNC)
10941095
expr = eval_const_expressions(root, expr);
10951096

10961097
/*

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

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

24052405
drop type rngfunc2;
2406+
-- check handling of functions pulled up into function RTEs (bug #17227)
2407+
explain (verbose, costs off)
2408+
select * from
2409+
(select jsonb_path_query_array(module->'lectures', '$[*]') as lecture
2410+
from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb])
2411+
as unnested_modules(module)) as ss,
2412+
jsonb_to_recordset(ss.lecture) as j (id text);
2413+
QUERY PLAN
2414+
--------------------------------------------------------------------------------------------------------------------------------------------------------
2415+
Nested Loop
2416+
Output: jsonb_path_query_array((unnested_modules.module -> 'lectures'::text), '$[*]'::jsonpath, '{}'::jsonb, false), j.id
2417+
-> Function Scan on pg_catalog.unnest unnested_modules
2418+
Output: unnested_modules.module
2419+
Function Call: unnest('{"{\"lectures\": [{\"id\": \"1\"}]}"}'::jsonb[])
2420+
-> Function Scan on pg_catalog.jsonb_to_recordset j
2421+
Output: j.id
2422+
Function Call: jsonb_to_recordset(jsonb_path_query_array((unnested_modules.module -> 'lectures'::text), '$[*]'::jsonpath, '{}'::jsonb, false))
2423+
(8 rows)
2424+
2425+
select * from
2426+
(select jsonb_path_query_array(module->'lectures', '$[*]') as lecture
2427+
from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb])
2428+
as unnested_modules(module)) as ss,
2429+
jsonb_to_recordset(ss.lecture) as j (id text);
2430+
lecture | id
2431+
---------------+----
2432+
[{"id": "1"}] | 1
2433+
(1 row)
2434+

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

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

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

0 commit comments

Comments
 (0)