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

Commitfca8e59

Browse files
committed
Fix oversight in flattening of subqueries with empty FROM.
I missed a restriction that commitf4abd02should have enforced: we can't pull up an empty-FROM subquery if it's underan outer join, because then we'd need to wrap its output columns inPlaceHolderVars. As the code currently stands, the PHVs end up with emptyrelid sets, which doesn't work (and is correctly caught by an Assert).It's possible that this could be fixed by assigning the PHVs the relidsets of the parent FromExpr/JoinExpr, but getting that to work is morecomplication than I care to add right now; indeed it's likely thatwe'll never bother, since pulling up empty-FROM subqueries is a rathermarginal optimization anyway.Per report from Andreas Seltenreich. Back-patch to 9.5 where the faultycode was added.
1 parent358eaa0 commitfca8e59

File tree

3 files changed

+81
-18
lines changed

3 files changed

+81
-18
lines changed

‎src/backend/optimizer/prep/prepjointree.c

Lines changed: 34 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -1435,25 +1435,40 @@ is_simple_subquery(Query *subquery, RangeTblEntry *rte,
14351435

14361436
/*
14371437
* Don't pull up a subquery with an empty jointree, unless it has no quals
1438-
* and deletion_ok is TRUE. query_planner() will correctly generate a
1439-
* Result plan for a jointree that's totally empty, but we can't cope with
1440-
* an empty FromExpr appearing lower down in a jointree: we identify join
1441-
* rels via baserelid sets, so we couldn't distinguish a join containing
1442-
* such a FromExpr from one without it. This would for example break the
1443-
* PlaceHolderVar mechanism, since we'd have no way to identify where to
1444-
* evaluate a PHV coming out of the subquery. We can only handle such
1445-
* cases if the place where the subquery is linked is a FromExpr or inner
1446-
* JOIN that would still be nonempty after removal of the subquery, so
1447-
* that it's still identifiable via its contained baserelids. Safe
1448-
* contexts are signaled by deletion_ok. But even in a safe context, we
1449-
* must keep the subquery if it has any quals, because it's unclear where
1450-
* to put them in the upper query. (Note that deletion of a subquery is
1451-
* also dependent on the check below that its targetlist contains no
1452-
* set-returning functions. Deletion from a FROM list or inner JOIN is
1453-
* okay only if the subquery must return exactly one row.)
1438+
* and deletion_ok is TRUE and we're not underneath an outer join.
1439+
*
1440+
* query_planner() will correctly generate a Result plan for a jointree
1441+
* that's totally empty, but we can't cope with an empty FromExpr
1442+
* appearing lower down in a jointree: we identify join rels via baserelid
1443+
* sets, so we couldn't distinguish a join containing such a FromExpr from
1444+
* one without it. We can only handle such cases if the place where the
1445+
* subquery is linked is a FromExpr or inner JOIN that would still be
1446+
* nonempty after removal of the subquery, so that it's still identifiable
1447+
* via its contained baserelids. Safe contexts are signaled by
1448+
* deletion_ok.
1449+
*
1450+
* But even in a safe context, we must keep the subquery if it has any
1451+
* quals, because it's unclear where to put them in the upper query.
1452+
*
1453+
* Also, we must forbid pullup if such a subquery is underneath an outer
1454+
* join, because then we might need to wrap its output columns with
1455+
* PlaceHolderVars, and the PHVs would then have empty relid sets meaning
1456+
* we couldn't tell where to evaluate them. (This test is separate from
1457+
* the deletion_ok flag for possible future expansion: deletion_ok tells
1458+
* whether the immediate parent site in the jointree could cope, not
1459+
* whether we'd have PHV issues. It's possible this restriction could be
1460+
* fixed by letting the PHVs use the relids of the parent jointree item,
1461+
* but that complication is for another day.)
1462+
*
1463+
* Note that deletion of a subquery is also dependent on the check below
1464+
* that its targetlist contains no set-returning functions. Deletion from
1465+
* a FROM list or inner JOIN is okay only if the subquery must return
1466+
* exactly one row.
14541467
*/
14551468
if (subquery->jointree->fromlist==NIL&&
1456-
(subquery->jointree->quals|| !deletion_ok))
1469+
(subquery->jointree->quals!=NULL||
1470+
!deletion_ok||
1471+
lowest_outer_join!=NULL))
14571472
return false;
14581473

14591474
/*
@@ -1667,7 +1682,8 @@ is_simple_values(PlannerInfo *root, RangeTblEntry *rte, bool deletion_ok)
16671682

16681683
/*
16691684
* Because VALUES can't appear under an outer join (or at least, we won't
1670-
* try to pull it up if it does), we need not worry about LATERAL.
1685+
* try to pull it up if it does), we need not worry about LATERAL, nor
1686+
* about validity of PHVs for the VALUES' outputs.
16711687
*/
16721688

16731689
/*

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

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2184,6 +2184,40 @@ select aa, bb, unique1, unique1
21842184
----+----+---------+---------
21852185
(0 rows)
21862186

2187+
--
2188+
-- regression test: check handling of empty-FROM subquery underneath outer join
2189+
--
2190+
explain (costs off)
2191+
select * from int8_tbl i1 left join (int8_tbl i2 join
2192+
(select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2
2193+
order by 1, 2;
2194+
QUERY PLAN
2195+
-------------------------------------------------
2196+
Sort
2197+
Sort Key: i1.q1, i1.q2
2198+
-> Hash Left Join
2199+
Hash Cond: (i1.q2 = i2.q2)
2200+
-> Seq Scan on int8_tbl i1
2201+
-> Hash
2202+
-> Hash Join
2203+
Hash Cond: (i2.q1 = (123))
2204+
-> Seq Scan on int8_tbl i2
2205+
-> Hash
2206+
-> Result
2207+
(11 rows)
2208+
2209+
select * from int8_tbl i1 left join (int8_tbl i2 join
2210+
(select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2
2211+
order by 1, 2;
2212+
q1 | q2 | q1 | q2 | x
2213+
------------------+-------------------+-----+------------------+-----
2214+
123 | 456 | 123 | 456 | 123
2215+
123 | 4567890123456789 | 123 | 4567890123456789 | 123
2216+
4567890123456789 | -4567890123456789 | | |
2217+
4567890123456789 | 123 | | |
2218+
4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 123
2219+
(5 rows)
2220+
21872221
--
21882222
-- Clean up
21892223
--

‎src/test/regress/sql/join.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -365,6 +365,19 @@ select aa, bb, unique1, unique1
365365
from tenk1right join bon aa= unique1
366366
where bb< bband bb isnull;
367367

368+
--
369+
-- regression test: check handling of empty-FROM subquery underneath outer join
370+
--
371+
explain (costs off)
372+
select*from int8_tbl i1left join (int8_tbl i2join
373+
(select123as x) ssoni2.q1= x)oni1.q2=i2.q2
374+
order by1,2;
375+
376+
select*from int8_tbl i1left join (int8_tbl i2join
377+
(select123as x) ssoni2.q1= x)oni1.q2=i2.q2
378+
order by1,2;
379+
380+
368381
--
369382
-- Clean up
370383
--

0 commit comments

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