Skip to content

Commit fd059ac

Browse files
tglsfdctglsfdc
committedOct 14, 2021
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 79c7fe1 commit fd059ac

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

10791079
/*
10801080
* Simplify constant expressions. For function RTEs, this was already
1081-
* done by preprocess_function_rtes ... but we have to do it again if the
1082-
* RTE is LATERAL and might have contained join alias variables.
1081+
* done by preprocess_function_rtes. (But note we must do it again for
1082+
* EXPRKIND_RTFUNC_LATERAL, because those might by now contain
1083+
* un-simplified subexpressions inserted by flattening of subqueries or
1084+
* join alias variables.)
10831085
*
10841086
* Note: an essential effect of this is to convert named-argument function
10851087
* calls to positional notation and insert the current actual values of
@@ -1093,8 +1095,7 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
10931095
* careful to maintain AND/OR flatness --- that is, do not generate a tree
10941096
* with AND directly under AND, nor OR directly under OR.
10951097
*/
1096-
if (!(kind == EXPRKIND_RTFUNC ||
1097-
(kind == EXPRKIND_RTFUNC_LATERAL && !root->hasJoinRTEs)))
1098+
if (kind != EXPRKIND_RTFUNC)
10981099
expr = eval_const_expressions(root, expr);
10991100

11001101
/*

‎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)