Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf330a6d

Browse files
committed
Fix mishandling of FieldSelect-on-whole-row-Var in nested lateral queries.
If an inline-able SQL function taking a composite argument is used in aLATERAL subselect, and the composite argument is a lateral reference,the planner could fail with "variable not found in subplan target list",as seen in bug #11703 from Karl Bartel. (The outer function call used inthe bug report and in the committed regression test is not really necessaryto provoke the bug --- you can get it if you manually expand the outerfunction into "LATERAL (SELECT inner_function(outer_relation))", too.)The cause of this is that we generate the reltargetlist for the referencedrelation before doing eval_const_expressions() on the lateral sub-select'sexpressions (cf find_lateral_references()), so what's scheduled to beemitted by the referenced relation is a whole-row Var, not the simplifiedsingle-column Var produced by optimizing the function's FieldSelect on thewhole-row Var. Then setrefs.c fails to match up that lateral reference towhat's available from the outer scan.Preserving the FieldSelect optimization in such cases would require eithermajor planner restructuring (to recursively do expression simplificationon sub-selects much earlier) or some amazingly ugly kluge to change thereltargetlist of a possibly-already-planned relation. It seems betterjust to skip the optimization when the Var is from an upper query level;the case is not so common that it's likely anyone will notice a fewwasted cycles.AFAICT this problem only occurs for uplevel LATERAL references, soback-patch to 9.3 where LATERAL was added.
1 parent488a7c9 commitf330a6d

File tree

3 files changed

+87
-5
lines changed

3 files changed

+87
-5
lines changed

‎src/backend/optimizer/util/clauses.c

Lines changed: 15 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -3196,10 +3196,19 @@ eval_const_expressions_mutator(Node *node,
31963196
* But it can arise while simplifying functions.) Also, we
31973197
* can optimize field selection from a RowExpr construct.
31983198
*
3199-
* We must however check that the declared type of the field
3200-
* is still the same as when the FieldSelect was created ---
3201-
* this can change if someone did ALTER COLUMN TYPE on the
3202-
* rowtype.
3199+
* However, replacing a whole-row Var in this way has a
3200+
* pitfall: if we've already built the reltargetlist for the
3201+
* source relation, then the whole-row Var is scheduled to be
3202+
* produced by the relation scan, but the simple Var probably
3203+
* isn't, which will lead to a failure in setrefs.c. This is
3204+
* not a problem when handling simple single-level queries, in
3205+
* which expression simplification always happens first. It
3206+
* is a risk for lateral references from subqueries, though.
3207+
* To avoid such failures, don't optimize uplevel references.
3208+
*
3209+
* We must also check that the declared type of the field is
3210+
* still the same as when the FieldSelect was created --- this
3211+
* can change if someone did ALTER COLUMN TYPE on the rowtype.
32033212
*/
32043213
FieldSelect*fselect= (FieldSelect*)node;
32053214
FieldSelect*newfselect;
@@ -3208,7 +3217,8 @@ eval_const_expressions_mutator(Node *node,
32083217
arg=eval_const_expressions_mutator((Node*)fselect->arg,
32093218
context);
32103219
if (arg&&IsA(arg,Var)&&
3211-
((Var*)arg)->varattno==InvalidAttrNumber)
3220+
((Var*)arg)->varattno==InvalidAttrNumber&&
3221+
((Var*)arg)->varlevelsup==0)
32123222
{
32133223
if (rowtype_field_matches(((Var*)arg)->vartype,
32143224
fselect->fieldnum,

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

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2006,3 +2006,55 @@ FROM
20062006
3 | FROM 10000000876 | from 10000000876
20072007
(3 rows)
20082008

2009+
-- check whole-row-Var handling in nested lateral functions (bug #11703)
2010+
create function extractq2(t int8_tbl) returns int8 as $$
2011+
select t.q2
2012+
$$ language sql immutable;
2013+
explain (verbose, costs off)
2014+
select x from int8_tbl, extractq2(int8_tbl) f(x);
2015+
QUERY PLAN
2016+
------------------------------------------
2017+
Nested Loop
2018+
Output: f.x
2019+
-> Seq Scan on public.int8_tbl
2020+
Output: int8_tbl.q1, int8_tbl.q2
2021+
-> Function Scan on f
2022+
Output: f.x
2023+
Function Call: int8_tbl.q2
2024+
(7 rows)
2025+
2026+
select x from int8_tbl, extractq2(int8_tbl) f(x);
2027+
x
2028+
-------------------
2029+
456
2030+
4567890123456789
2031+
123
2032+
4567890123456789
2033+
-4567890123456789
2034+
(5 rows)
2035+
2036+
create function extractq2_2(t int8_tbl) returns table(ret1 int8) as $$
2037+
select extractq2(t)
2038+
$$ language sql immutable;
2039+
explain (verbose, costs off)
2040+
select x from int8_tbl, extractq2_2(int8_tbl) f(x);
2041+
QUERY PLAN
2042+
-----------------------------------
2043+
Nested Loop
2044+
Output: ((int8_tbl.*).q2)
2045+
-> Seq Scan on public.int8_tbl
2046+
Output: int8_tbl.*
2047+
-> Result
2048+
Output: (int8_tbl.*).q2
2049+
(6 rows)
2050+
2051+
select x from int8_tbl, extractq2_2(int8_tbl) f(x);
2052+
x
2053+
-------------------
2054+
456
2055+
4567890123456789
2056+
123
2057+
4567890123456789
2058+
-4567890123456789
2059+
(5 rows)
2060+

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

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -608,3 +608,23 @@ SELECT *,
608608
END)
609609
FROM
610610
(VALUES (1,''), (2,'0000000049404'), (3,'FROM 10000000876')) v(id, str);
611+
612+
-- check whole-row-Var handling in nested lateral functions (bug #11703)
613+
614+
createfunctionextractq2(t int8_tbl) returns int8as $$
615+
selectt.q2
616+
$$ language sql immutable;
617+
618+
explain (verbose, costs off)
619+
select xfrom int8_tbl, extractq2(int8_tbl) f(x);
620+
621+
select xfrom int8_tbl, extractq2(int8_tbl) f(x);
622+
623+
createfunctionextractq2_2(t int8_tbl) returns table(ret1 int8)as $$
624+
select extractq2(t)
625+
$$ language sql immutable;
626+
627+
explain (verbose, costs off)
628+
select xfrom int8_tbl, extractq2_2(int8_tbl) f(x);
629+
630+
select xfrom int8_tbl, extractq2_2(int8_tbl) f(x);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp