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

Commit3af8773

Browse files
committed
Fix another cause of "wrong varnullingrels" planner failures.
I removed the delay_upper_joins mechanism in commitb448f1c,reasoning that it was only needed when we have a single-table(SELECT ... WHERE) as the immediate RHS child of a left join,and we could get rid of that by hoisting the WHERE condition intothe parent join's quals. However that new code missed a case:we could have "foo LEFT JOIN ((SELECT ... WHERE) LEFT JOIN bar)",and if the two left joins can be commuted then we now have theproblematic query shape. We can fix this too easily enough,by allowing the syntactically-lower left join to pass throughits parent qual location pointer recursively. That letsprepjointree.c discard the SELECT by temporarily hoisting theWHERE condition into the ancestor join's qual.Per bug #17978 from Zuming Jiang.Discussion:https://postgr.es/m/17978-12f3d93a55297266@postgresql.org
1 parentefeb12e commit3af8773

File tree

3 files changed

+51
-7
lines changed

3 files changed

+51
-7
lines changed

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

Lines changed: 15 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3213,6 +3213,8 @@ remove_useless_result_rtes(PlannerInfo *root)
32133213
* jtnode is the current jointree node. If it could be valid to merge
32143214
* its quals into those of the parent node, parent_quals should point to
32153215
* the parent's quals list; otherwise, pass NULL for parent_quals.
3216+
* (Note that in some cases, parent_quals points to the quals of a parent
3217+
* more than one level up in the tree.)
32163218
*/
32173219
staticNode*
32183220
remove_useless_results_recurse(PlannerInfo*root,Node*jtnode,
@@ -3316,13 +3318,22 @@ remove_useless_results_recurse(PlannerInfo *root, Node *jtnode,
33163318
intvarno;
33173319

33183320
/*
3319-
* First, recurse. We can accept pushed-up FromExpr quals from either
3320-
* child if the jointype is INNER, and we can accept them from the RHS
3321-
* child if the jointype is LEFT.
3321+
* First, recurse. We can absorb pushed-up FromExpr quals from either
3322+
* child into this node if the jointype is INNER, since then this is
3323+
* equivalent to a FromExpr. When the jointype is LEFT, we can absorb
3324+
* quals from the RHS child into the current node, as they're
3325+
* essentially degenerate quals of the outer join. Moreover, if we've
3326+
* been passed down a parent_quals pointer then we can allow quals of
3327+
* the LHS child to be absorbed into the parent. (This is important
3328+
* to ensure we remove single-child FromExprs immediately below
3329+
* commutable left joins.) For other jointypes, we can't move child
3330+
* quals up, or at least there's no particular reason to.
33223331
*/
33233332
j->larg=remove_useless_results_recurse(root,j->larg,
33243333
(j->jointype==JOIN_INNER) ?
3325-
&j->quals :NULL,
3334+
&j->quals :
3335+
(j->jointype==JOIN_LEFT) ?
3336+
parent_quals :NULL,
33263337
dropped_outer_joins);
33273338
j->rarg=remove_useless_results_recurse(root,j->rarg,
33283339
(j->jointype==JOIN_INNER||

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

Lines changed: 28 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2531,20 +2531,45 @@ select * from int4_tbl t1
25312531
on s.f1 = t1.f1;
25322532
QUERY PLAN
25332533
-------------------------------------------------
2534+
Hash Right Join
2535+
Hash Cond: (t2.f1 = t1.f1)
2536+
-> Nested Loop Left Join
2537+
Join Filter: (t2.f1 > 1)
2538+
-> Nested Loop Left Join
2539+
Join Filter: (t2.f1 > 0)
2540+
Filter: (t3.f1 IS NULL)
2541+
-> Seq Scan on int4_tbl t2
2542+
-> Materialize
2543+
-> Seq Scan on int4_tbl t3
2544+
-> Seq Scan on tenk1 t4
2545+
-> Hash
2546+
-> Seq Scan on int4_tbl t1
2547+
(13 rows)
2548+
2549+
explain (costs off)
2550+
select * from int4_tbl t1
2551+
left join ((select t2.f1 from int4_tbl t2
2552+
left join int4_tbl t3 on t2.f1 > 0
2553+
where t2.f1 <> coalesce(t3.f1, -1)) s
2554+
left join tenk1 t4 on s.f1 > 1)
2555+
on s.f1 = t1.f1;
2556+
QUERY PLAN
2557+
-----------------------------------------------------------------
25342558
Nested Loop Left Join
25352559
Join Filter: (t2.f1 > 1)
25362560
-> Hash Right Join
25372561
Hash Cond: (t2.f1 = t1.f1)
25382562
-> Nested Loop Left Join
25392563
Join Filter: (t2.f1 > 0)
2540-
Filter: (t3.f1IS NULL)
2564+
Filter: (t2.f1<> COALESCE(t3.f1, '-1'::integer))
25412565
-> Seq Scan on int4_tbl t2
25422566
-> Materialize
25432567
-> Seq Scan on int4_tbl t3
25442568
-> Hash
25452569
-> Seq Scan on int4_tbl t1
2546-
-> Seq Scan on tenk1 t4
2547-
(13 rows)
2570+
-> Materialize
2571+
-> Seq Scan on tenk1 t4
2572+
(14 rows)
25482573

25492574
explain (costs off)
25502575
select * from onek t1

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

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -502,6 +502,14 @@ select * from int4_tbl t1
502502
left join tenk1 t4ons.f1>1)
503503
ons.f1=t1.f1;
504504

505+
explain (costs off)
506+
select*from int4_tbl t1
507+
left join ((selectt2.f1from int4_tbl t2
508+
left join int4_tbl t3ont2.f1>0
509+
wheret2.f1<> coalesce(t3.f1,-1)) s
510+
left join tenk1 t4ons.f1>1)
511+
ons.f1=t1.f1;
512+
505513
explain (costs off)
506514
select*from onek t1
507515
left join onek t2ont1.unique1=t2.unique1

0 commit comments

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