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
Appearance settings

Commit 7d4a10e

Browse filesBrowse files
tglsfdctglsfdc
committed
Use PlaceHolderVars within the quals of a FULL JOIN.
This prevents failures in cases where we pull up a constant or var-free expression from a subquery and put it into a full join's qual. That can result in not recognizing the qual as containing a mergejoin-able or hashjoin-able condition. A PHV prevents the problem because it is still recognized as belonging to the side of the join the subquery is in. I'm not very sure about the net effect of this change on plan quality. In "typical" cases where the join keys are Vars, nothing changes. In an affected case, the PHV-wrapped expression is less likely to be seen as equal to PHV-less instances below the join, but more likely to be seen as equal to similar expressions above the join, so it may end up being a wash. In the one existing case where there's any visible change in a regression-test plan, it amounts to referencing a lower computation of a COALESCE result instead of recomputing it, which seems like a win. Given my uncertainty about that and the lack of field complaints, no back-patch, even though this is a very ancient problem. Discussion: https://postgr.es/m/32090.1539378124@sss.pgh.pa.us
1 parent e9f42d5 commit 7d4a10e
Copy full SHA for 7d4a10e

File tree

Expand file treeCollapse file tree

3 files changed

+34
-1
lines changed
Filter options
Expand file treeCollapse file tree

3 files changed

+34
-1
lines changed

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

Copy file name to clipboardExpand all lines: src/backend/optimizer/prep/prepjointree.c
+12Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2044,6 +2044,18 @@ replace_vars_in_jointree(Node *jtnode,
20442044
}
20452045
replace_vars_in_jointree(j->larg, context, lowest_nulling_outer_join);
20462046
replace_vars_in_jointree(j->rarg, context, lowest_nulling_outer_join);
2047+
2048+
/*
2049+
* Use PHVs within the join quals of a full join, even when it's the
2050+
* lowest nulling outer join. Otherwise, we cannot identify which
2051+
* side of the join a pulled-up var-free expression came from, which
2052+
* can lead to failure to make a plan at all because none of the quals
2053+
* appear to be mergeable or hashable conditions. For this purpose we
2054+
* don't care about the state of wrap_non_vars, so leave it alone.
2055+
*/
2056+
if (j->jointype == JOIN_FULL)
2057+
context->need_phvs = true;
2058+
20472059
j->quals = pullup_replace_vars(j->quals, context);
20482060

20492061
/*

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

Copy file name to clipboardExpand all lines: src/test/regress/expected/join.out
+15-1Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2024,6 +2024,20 @@ NATURAL FULL JOIN
20242024
ee | | 42 | 2 |
20252025
(4 rows)
20262026

2027+
-- Constants as join keys can also be problematic
2028+
SELECT * FROM
2029+
(SELECT name, n as s1_n FROM t1) as s1
2030+
FULL JOIN
2031+
(SELECT name, 2 as s2_n FROM t2) as s2
2032+
ON (s1_n = s2_n);
2033+
name | s1_n | name | s2_n
2034+
------+------+------+------
2035+
| | bb | 2
2036+
| | cc | 2
2037+
| | ee | 2
2038+
bb | 11 | |
2039+
(4 rows)
2040+
20272041
-- Test for propagation of nullability constraints into sub-joins
20282042
create temp table x (x1 int, x2 int);
20292043
insert into x values (1,11);
@@ -2854,7 +2868,7 @@ SELECT qq, unique1
28542868
---------------------------------------------------------------------------------------------------------
28552869
Nested Loop
28562870
-> Hash Full Join
2857-
Hash Cond: (COALESCE(a.q1, '0'::bigint) = COALESCE(b.q2, '-1'::bigint))
2871+
Hash Cond: ((COALESCE(a.q1, '0'::bigint)) = (COALESCE(b.q2, '-1'::bigint)))
28582872
-> Seq Scan on int8_tbl a
28592873
-> Hash
28602874
-> Seq Scan on int8_tbl b

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

Copy file name to clipboardExpand all lines: src/test/regress/sql/join.sql
+7Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -297,6 +297,13 @@ NATURAL FULL JOIN
297297
(SELECT name, n as s3_n FROM t3) as s3
298298
) ss2;
299299

300+
-- Constants as join keys can also be problematic
301+
SELECT * FROM
302+
(SELECT name, n as s1_n FROM t1) as s1
303+
FULL JOIN
304+
(SELECT name, 2 as s2_n FROM t2) as s2
305+
ON (s1_n = s2_n);
306+
300307

301308
-- Test for propagation of nullability constraints into sub-joins
302309

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.