postgres.git / summary / log / commit / refs
commit a6492ff8970b06b9e27cc314c7d1aa574fcc7b04
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Jul 31 23:26:33 2015 +0000
Fix an oversight in checking whether a join with LATERAL refs is legal.
In many cases, we can implement a semijoin as a plain innerjoin by first
passing the righthand-side relation through a unique-ification step.
However, one of the cases where this does NOT work is where the RHS has
a LATERAL reference to the LHS; that makes the RHS dependent on the LHS
so that unique-ification is meaningless. joinpath.c understood this,
and so would not generate any join paths of this kind ... but join_is_legal
neglected to check for the case, so it would think that we could do it.
The upshot would be a "could not devise a query plan for the given query"
failure once we had failed to generate any join paths at all for the bogus
join pair.
Back-patch to 9.3 where LATERAL was added.
src/backend/optimizer/path/joinrels.c | 8 ++++++--
src/test/regress/expected/join.out | 35 +++++++++++++++++++++++++++++++++++
src/test/regress/sql/join.sql | 13 +++++++++++++
3 files changed, 54 insertions(+), 2 deletions(-)
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c
index fe9fd573174..b6c9494fed6 100644
--- a/src/backend/optimizer/path/joinrels.c
+++ b/src/backend/optimizer/path/joinrels.c
@@ -536,7 +536,9 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
if (!bms_is_subset(ljinfo->lateral_lhs, rel1->relids))
return false; /* rel1 can't compute the required parameter */
if (match_sjinfo &&
- (reversed || match_sjinfo->jointype == JOIN_FULL))
+ (reversed ||
+ unique_ified ||
+ match_sjinfo->jointype == JOIN_FULL))
return false; /* not implementable as nestloop */
}
if (bms_is_subset(ljinfo->lateral_rhs, rel1->relids) &&
@@ -549,7 +551,9 @@ join_is_legal(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
if (!bms_is_subset(ljinfo->lateral_lhs, rel2->relids))
return false; /* rel2 can't compute the required parameter */
if (match_sjinfo &&
- (!reversed || match_sjinfo->jointype == JOIN_FULL))
+ (!reversed ||
+ unique_ified ||
+ match_sjinfo->jointype == JOIN_FULL))
return false; /* not implementable as nestloop */
}
}
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 1afd0c328b5..10336d48a3a 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4527,6 +4527,41 @@ select * from
Output: 3
(11 rows)
+-- check we don't try to do a unique-ified semijoin with LATERAL
+explain (verbose, costs off)
+select * from
+ (values (0,9998), (1,1000)) v(id,x),
+ lateral (select f1 from int4_tbl
+ where f1 = any (select unique1 from tenk1
+ where unique2 = v.x offset 0)) ss;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Nested Loop
+ Output: "*VALUES*".column1, "*VALUES*".column2, int4_tbl.f1
+ -> Values Scan on "*VALUES*"
+ Output: "*VALUES*".column1, "*VALUES*".column2
+ -> Nested Loop Semi Join
+ Output: int4_tbl.f1
+ Join Filter: (int4_tbl.f1 = tenk1.unique1)
+ -> Seq Scan on public.int4_tbl
+ Output: int4_tbl.f1
+ -> Materialize
+ Output: tenk1.unique1
+ -> Index Scan using tenk1_unique2 on public.tenk1
+ Output: tenk1.unique1
+ Index Cond: (tenk1.unique2 = "*VALUES*".column2)
+(14 rows)
+
+select * from
+ (values (0,9998), (1,1000)) v(id,x),
+ lateral (select f1 from int4_tbl
+ where f1 = any (select unique1 from tenk1
+ where unique2 = v.x offset 0)) ss;
+ id | x | f1
+----+------+----
+ 0 | 9998 | 0
+(1 row)
+
-- test some error cases where LATERAL should have been used but wasn't
select f1,g from int4_tbl a, (select f1 as g) ss;
ERROR: column "f1" does not exist
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index d34cefac5a1..7553aefc6bd 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1365,6 +1365,19 @@ select * from
select * from (select 3 as z offset 0) z where z.z = x.x
) zz on zz.z = y.y;
+-- check we don't try to do a unique-ified semijoin with LATERAL
+explain (verbose, costs off)
+select * from
+ (values (0,9998), (1,1000)) v(id,x),
+ lateral (select f1 from int4_tbl
+ where f1 = any (select unique1 from tenk1
+ where unique2 = v.x offset 0)) ss;
+select * from
+ (values (0,9998), (1,1000)) v(id,x),
+ lateral (select f1 from int4_tbl
+ where f1 = any (select unique1 from tenk1
+ where unique2 = v.x offset 0)) ss;
+
-- test some error cases where LATERAL should have been used but wasn't
select f1,g from int4_tbl a, (select f1 as g) ss;
select f1,g from int4_tbl a, (select a.f1 as g) ss;
[parent: 16c4e6d8dc8f]