From a6492ff8970b06b9e27cc314c7d1aa574fcc7b04 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 31 Jul 2015 19:26:33 -0400 Subject: [PATCH] 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 fe9fd57317..b6c9494fed 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 1afd0c328b..10336d48a3 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 d34cefac5a..7553aefc6b 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; -- 2.40.0