aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--src/backend/optimizer/prep/prepjointree.c52
-rw-r--r--src/test/regress/expected/join.out34
-rw-r--r--src/test/regress/sql/join.sql13
3 files changed, 81 insertions, 18 deletions
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 34144ccaf0f..9bf1c662b53 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -1435,25 +1435,40 @@ is_simple_subquery(Query *subquery, RangeTblEntry *rte,
/*
* Don't pull up a subquery with an empty jointree, unless it has no quals
- * and deletion_ok is TRUE. query_planner() will correctly generate a
- * Result plan for a jointree that's totally empty, but we can't cope with
- * an empty FromExpr appearing lower down in a jointree: we identify join
- * rels via baserelid sets, so we couldn't distinguish a join containing
- * such a FromExpr from one without it. This would for example break the
- * PlaceHolderVar mechanism, since we'd have no way to identify where to
- * evaluate a PHV coming out of the subquery. We can only handle such
- * cases if the place where the subquery is linked is a FromExpr or inner
- * JOIN that would still be nonempty after removal of the subquery, so
- * that it's still identifiable via its contained baserelids. Safe
- * contexts are signaled by deletion_ok. But even in a safe context, we
- * must keep the subquery if it has any quals, because it's unclear where
- * to put them in the upper query. (Note that deletion of a subquery is
- * also dependent on the check below that its targetlist contains no
- * set-returning functions. Deletion from a FROM list or inner JOIN is
- * okay only if the subquery must return exactly one row.)
+ * and deletion_ok is TRUE and we're not underneath an outer join.
+ *
+ * query_planner() will correctly generate a Result plan for a jointree
+ * that's totally empty, but we can't cope with an empty FromExpr
+ * appearing lower down in a jointree: we identify join rels via baserelid
+ * sets, so we couldn't distinguish a join containing such a FromExpr from
+ * one without it. We can only handle such cases if the place where the
+ * subquery is linked is a FromExpr or inner JOIN that would still be
+ * nonempty after removal of the subquery, so that it's still identifiable
+ * via its contained baserelids. Safe contexts are signaled by
+ * deletion_ok.
+ *
+ * But even in a safe context, we must keep the subquery if it has any
+ * quals, because it's unclear where to put them in the upper query.
+ *
+ * Also, we must forbid pullup if such a subquery is underneath an outer
+ * join, because then we might need to wrap its output columns with
+ * PlaceHolderVars, and the PHVs would then have empty relid sets meaning
+ * we couldn't tell where to evaluate them. (This test is separate from
+ * the deletion_ok flag for possible future expansion: deletion_ok tells
+ * whether the immediate parent site in the jointree could cope, not
+ * whether we'd have PHV issues. It's possible this restriction could be
+ * fixed by letting the PHVs use the relids of the parent jointree item,
+ * but that complication is for another day.)
+ *
+ * Note that deletion of a subquery is also dependent on the check below
+ * that its targetlist contains no set-returning functions. Deletion from
+ * a FROM list or inner JOIN is okay only if the subquery must return
+ * exactly one row.
*/
if (subquery->jointree->fromlist == NIL &&
- (subquery->jointree->quals || !deletion_ok))
+ (subquery->jointree->quals != NULL ||
+ !deletion_ok ||
+ lowest_outer_join != NULL))
return false;
/*
@@ -1667,7 +1682,8 @@ is_simple_values(PlannerInfo *root, RangeTblEntry *rte, bool deletion_ok)
/*
* Because VALUES can't appear under an outer join (or at least, we won't
- * try to pull it up if it does), we need not worry about LATERAL.
+ * try to pull it up if it does), we need not worry about LATERAL, nor
+ * about validity of PHVs for the VALUES' outputs.
*/
/*
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 96ec997ed16..4ce01cbcd5b 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2185,6 +2185,40 @@ select aa, bb, unique1, unique1
(0 rows)
--
+-- regression test: check handling of empty-FROM subquery underneath outer join
+--
+explain (costs off)
+select * from int8_tbl i1 left join (int8_tbl i2 join
+ (select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2
+order by 1, 2;
+ QUERY PLAN
+-------------------------------------------------
+ Sort
+ Sort Key: i1.q1, i1.q2
+ -> Hash Left Join
+ Hash Cond: (i1.q2 = i2.q2)
+ -> Seq Scan on int8_tbl i1
+ -> Hash
+ -> Hash Join
+ Hash Cond: (i2.q1 = (123))
+ -> Seq Scan on int8_tbl i2
+ -> Hash
+ -> Result
+(11 rows)
+
+select * from int8_tbl i1 left join (int8_tbl i2 join
+ (select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2
+order by 1, 2;
+ q1 | q2 | q1 | q2 | x
+------------------+-------------------+-----+------------------+-----
+ 123 | 456 | 123 | 456 | 123
+ 123 | 4567890123456789 | 123 | 4567890123456789 | 123
+ 4567890123456789 | -4567890123456789 | | |
+ 4567890123456789 | 123 | | |
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 123
+(5 rows)
+
+--
-- Clean up
--
DROP TABLE t1;
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index ada78db2644..3a71dbf4dff 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -366,6 +366,19 @@ select aa, bb, unique1, unique1
where bb < bb and bb is null;
--
+-- regression test: check handling of empty-FROM subquery underneath outer join
+--
+explain (costs off)
+select * from int8_tbl i1 left join (int8_tbl i2 join
+ (select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2
+order by 1, 2;
+
+select * from int8_tbl i1 left join (int8_tbl i2 join
+ (select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2
+order by 1, 2;
+
+
+--
-- Clean up
--