diff options
| -rw-r--r-- | src/backend/optimizer/prep/prepjointree.c | 12 | ||||
| -rw-r--r-- | src/test/regress/expected/join.out | 16 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 7 |
3 files changed, 34 insertions, 1 deletions
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 688b3a1c396..cd6e11904ee 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -2044,6 +2044,18 @@ replace_vars_in_jointree(Node *jtnode, } replace_vars_in_jointree(j->larg, context, lowest_nulling_outer_join); replace_vars_in_jointree(j->rarg, context, lowest_nulling_outer_join); + + /* + * Use PHVs within the join quals of a full join, even when it's the + * lowest nulling outer join. Otherwise, we cannot identify which + * side of the join a pulled-up var-free expression came from, which + * can lead to failure to make a plan at all because none of the quals + * appear to be mergeable or hashable conditions. For this purpose we + * don't care about the state of wrap_non_vars, so leave it alone. + */ + if (j->jointype == JOIN_FULL) + context->need_phvs = true; + j->quals = pullup_replace_vars(j->quals, context); /* diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index dc6262be43a..1f5378080d3 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2024,6 +2024,20 @@ NATURAL FULL JOIN ee | | 42 | 2 | (4 rows) +-- Constants as join keys can also be problematic +SELECT * FROM + (SELECT name, n as s1_n FROM t1) as s1 +FULL JOIN + (SELECT name, 2 as s2_n FROM t2) as s2 +ON (s1_n = s2_n); + name | s1_n | name | s2_n +------+------+------+------ + | | bb | 2 + | | cc | 2 + | | ee | 2 + bb | 11 | | +(4 rows) + -- Test for propagation of nullability constraints into sub-joins create temp table x (x1 int, x2 int); insert into x values (1,11); @@ -2854,7 +2868,7 @@ SELECT qq, unique1 --------------------------------------------------------------------------------------------------------- Nested Loop -> Hash Full Join - Hash Cond: (COALESCE(a.q1, '0'::bigint) = COALESCE(b.q2, '-1'::bigint)) + Hash Cond: ((COALESCE(a.q1, '0'::bigint)) = (COALESCE(b.q2, '-1'::bigint))) -> Seq Scan on int8_tbl a -> Hash -> Seq Scan on int8_tbl b diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index d3ba2a1c339..334a4dce2d0 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -297,6 +297,13 @@ NATURAL FULL JOIN (SELECT name, n as s3_n FROM t3) as s3 ) ss2; +-- Constants as join keys can also be problematic +SELECT * FROM + (SELECT name, n as s1_n FROM t1) as s1 +FULL JOIN + (SELECT name, 2 as s2_n FROM t2) as s2 +ON (s1_n = s2_n); + -- Test for propagation of nullability constraints into sub-joins |