You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by ma...@apache.org on 2025/02/27 01:17:01 UTC
(cloudberry) branch main updated: Revert "Fix crashes of lateral join (#16958)"
This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new 0b2eb333a8 Revert "Fix crashes of lateral join (#16958)"
0b2eb333a8 is described below
commit 0b2eb333a8cb1b02824fee8e06ea93cdf24bdea0
Author: Jianghua Yang <yj...@gmail.com>
AuthorDate: Wed Feb 26 10:05:00 2025 -0800
Revert "Fix crashes of lateral join (#16958)"
This reverts commit 0f96542cc39025167522a0a250df282c5d467a0c.
---
src/backend/cdb/cdbmutate.c | 104 -------
src/backend/optimizer/plan/createplan.c | 5 -
src/backend/optimizer/plan/subselect.c | 13 -
src/include/cdb/cdbmutate.h | 3 +-
src/test/regress/expected/join.out | 393 ++++++++++++++++-----------
src/test/regress/expected/join_optimizer.out | 227 ++++------------
src/test/regress/expected/tablesample.out | 40 +--
src/test/regress/init_file | 3 -
src/test/regress/sql/join.sql | 60 ++--
src/test/regress/sql/tablesample.sql | 3 +
10 files changed, 356 insertions(+), 495 deletions(-)
diff --git a/src/backend/cdb/cdbmutate.c b/src/backend/cdb/cdbmutate.c
index d368432cbc..9672ce25b5 100644
--- a/src/backend/cdb/cdbmutate.c
+++ b/src/backend/cdb/cdbmutate.c
@@ -1960,107 +1960,3 @@ contains_outer_params(Node *node, void *context)
}
return expression_tree_walker(node, contains_outer_params, context);
}
-
-typedef struct CTEMotionSearchContext
-{
- plan_tree_base_prefix base; /* Required prefix for plan_tree_walker/mutator */
- bool bellowMotion; /* True if we are under a Motion node */
-} CTEMotionSearchContext;
-
-/*
- * We can not pass params by a motion.
- * So there should not be any motion between RecursiveUnion and WorkTableScan.
- * Check if there is a motion between RecursiveUnion and WorkTableScan.
- */
-static bool
-cte_motion_search_walker(Node *node, CTEMotionSearchContext *context)
-{
- if (node == NULL)
- return false;
-
- if (IsA(node, Motion))
- {
- bool savedBellowMotion = context->bellowMotion;
- context->bellowMotion = true;
- plan_tree_walker(node, cte_motion_search_walker, context, true);
- context->bellowMotion = savedBellowMotion;
- return false;
- }
- if (IsA(node, RecursiveUnion))
- {
- /*
- * We process RecursiveUnion recursively in create_recursiveunion_plan
- * here, we do not process repeatedly.
- */
- return false;
- }
- if (IsA(node, WorkTableScan))
- {
- if (context->bellowMotion)
- {
- elog(ERROR, "Passing parameters across motion is not supported.");
- }
- return false;
- }
- return plan_tree_walker(node, cte_motion_search_walker, context, true);
-}
-
-/*
- * GPDB does not support pass params by a motion.
- * Check the rightplan of RecursiveUnion, wether there is a motion above WorkTableScan,
- * If true, throw an error.
- */
-void
-checkMotionAboveWorkTableScan(Node* node, PlannerInfo *root)
-{
- CTEMotionSearchContext context;
- planner_init_plan_tree_base(&context.base, root);
- context.bellowMotion = false;
-
- (void) cte_motion_search_walker(node, (void *) &context);
-}
-
-typedef struct MotionWithParamContext
-{
- plan_tree_base_prefix base; /* Required prefix for plan_tree_walker/mutator */
- Bitmapset *nestLoopParams; /* nestloop params */
-} MotionWithParamContext;
-
-/*
- * check whether pass params by a motion
- */
-static bool
-checkMotionWithParamWalker(Node *node, MotionWithParamContext* motionWithParamcontext)
-{
- if (node == NULL)
- return false;
-
- if (IsA(node, Motion))
- {
- Plan * plan = (Plan *) node;
- Bitmapset *finalExtParam;
- if (!bms_is_empty(plan->extParam))
- {
- finalExtParam = bms_intersect(plan->extParam, motionWithParamcontext->nestLoopParams);
- if (!bms_is_empty(finalExtParam))
- {
- elog(ERROR, "Passing parameters across motion is not supported.");
- }
- }
- }
-
- return plan_tree_walker(node, checkMotionWithParamWalker, motionWithParamcontext, true);
-}
-
-/*
- * We can not deliver a param by a motion node.
- * If there is a param on motion node, we should throw an error.
- */
-void
-checkMotionWithParam(Node *node, Bitmapset *bmsNestParams, PlannerInfo *root)
-{
- MotionWithParamContext motionWithParamcontext;
- motionWithParamcontext.nestLoopParams = bmsNestParams;
- planner_init_plan_tree_base(&motionWithParamcontext.base, root);
- checkMotionWithParamWalker(node, &motionWithParamcontext);
-}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 2ecd296f00..5a60096d27 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -3088,11 +3088,6 @@ create_recursiveunion_plan(PlannerInfo *root, RecursiveUnionPath *best_path)
best_path->distinctList,
numGroups);
- /*
- * Check whether there is a motion above WorkTableScan
- */
- checkMotionAboveWorkTableScan((Node *)rightplan, root);
-
copy_generic_path_info(&plan->plan, (Path *) best_path);
return plan;
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 8a87c4d763..c4da4c1b2d 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -3334,19 +3334,6 @@ finalize_plan(PlannerInfo *root, Plan *plan,
gather_param,
bms_union(nestloop_params, valid_params),
scan_params);
- /*
- * Currently GPDB doesn't fully support lateral, following sql will
- * pass params by a motion. Then cause panic in QE.
- * So add a walker to check whether motion in righttree of nestloop
- * will pass params, if true throw an error to avoid panic in QE.
- * explain SELECT * FROM
- * (VALUES (0.0),(10.4),(100.7)) v(nrows),
- * LATERAL (SELECT count(*) FROM test_tablesample
- * TABLESAMPLE system_rows (nrows)) ss;
- */
- if (IsA(plan, NestLoop) && !bms_is_empty(nestloop_params))
- checkMotionWithParam((Node*) plan->righttree, nestloop_params, root);
-
/* ... and they don't count as parameters used at my level */
child_params = bms_difference(child_params, nestloop_params);
bms_free(nestloop_params);
diff --git a/src/include/cdb/cdbmutate.h b/src/include/cdb/cdbmutate.h
index bffc7b236b..f7e29e6e02 100644
--- a/src/include/cdb/cdbmutate.h
+++ b/src/include/cdb/cdbmutate.h
@@ -56,6 +56,5 @@ extern void remove_subquery_in_RTEs(Node *node);
extern Plan *cdbpathtoplan_create_sri_plan(RangeTblEntry *rte, PlannerInfo *subroot, Path *subpath, int createplan_flags);
extern bool contains_outer_params(Node *node, void *context);
-extern void checkMotionAboveWorkTableScan(Node* node, PlannerInfo *root);
-extern void checkMotionWithParam(Node *node, Bitmapset *bmsNestParams, PlannerInfo *root);
+
#endif /* CDBMUTATE_H */
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 0a16f0a473..8d9fb9e558 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -3171,12 +3171,6 @@ order by 1,2;
--
-- variant where a PlaceHolderVar is needed at a join, but not above the join
--
--- TODO_LATERAL: failed to generate plan
--- If the left path needs params of outer path.
--- We can not add any motion above left path.
--- however, in right join, we can not add broadcast motion above right path.
--- So We can not add proper motion to generate path for right join,
--- And hit the error: could not devise a query plan for the given query.
explain (costs off)
select * from
int4_tbl as i41,
@@ -3218,12 +3212,34 @@ select * from
right join int4_tbl as i43 on (i43.f1 > 1)
where ss1.loc = ss1.lat) as ss2
where i41.f1 > 0;
-ERROR: could not devise a query plan for the given query (pathnode.c:275)
-select * from int4_tbl a inner join int4_tbl b on false;
- f1 | f1
-----+----
-(0 rows)
-
+ERROR: could not devise a query plan for the given query (pathnode.c:277)
+--start_ignore
+--GPDB_14_MERGE_FIXME: We need to make plan work. One idea is generate a gather
+--motion for relations. Even it is not efficient, but better than error out here.
+ f1 | x
+------------+---
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+(20 rows)
+--end_ignore
--
-- test the corner cases FULL JOIN ON TRUE and FULL JOIN ON FALSE
--
@@ -4521,6 +4537,7 @@ select * from
--
-- test for appropriate join order in the presence of lateral references
--
+-- start_ignore
explain (verbose, costs off)
select * from
text_tbl t1
@@ -4528,38 +4545,29 @@ select * from
on i8.q2 = 123,
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
where t1.f1 = ss.f1;
- QUERY PLAN
-------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------
Nested Loop
Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
Join Filter: (t1.f1 = t2.f1)
- -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
Output: t1.f1, i8.q1, i8.q2
- -> Nested Loop Left Join
- Output: t1.f1, i8.q1, i8.q2
- -> Seq Scan on public.text_tbl t1
- Output: t1.f1
- -> Materialize
+ -> Seq Scan on public.text_tbl t1
+ Output: t1.f1
+ -> Materialize
+ Output: i8.q1, i8.q2
+ -> Seq Scan on public.int8_tbl i8
Output: i8.q1, i8.q2
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- Output: i8.q1, i8.q2
- -> Seq Scan on public.int8_tbl i8
- Output: i8.q1, i8.q2
- Filter: (i8.q2 = 123)
- -> Materialize
+ Filter: (i8.q2 = 123)
+ -> Memoize
Output: (i8.q1), t2.f1
+ Cache Key: i8.q1
+ Cache Mode: binary
-> Limit
Output: (i8.q1), t2.f1
- -> Result
+ -> Seq Scan on public.text_tbl t2
Output: i8.q1, t2.f1
- -> Materialize
- Output: t2.f1
- -> Gather Motion 3:1 (slice3; segments: 3)
- Output: t2.f1
- -> Seq Scan on public.text_tbl t2
- Output: t2.f1
- Optimizer: Postgres-based planner
-(29 rows)
+(20 rows)
select * from
text_tbl t1
@@ -4580,49 +4588,39 @@ select * from
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1,
lateral (select ss1.* from text_tbl t3 limit 1) as ss2
where t1.f1 = ss2.f1;
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Nested Loop
Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1, ((i8.q1)), (t2.f1)
Join Filter: (t1.f1 = (t2.f1))
-> Nested Loop
Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
- -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
Output: t1.f1, i8.q1, i8.q2
- -> Nested Loop Left Join
- Output: t1.f1, i8.q1, i8.q2
- -> Seq Scan on public.text_tbl t1
- Output: t1.f1
- -> Materialize
+ -> Seq Scan on public.text_tbl t1
+ Output: t1.f1
+ -> Materialize
+ Output: i8.q1, i8.q2
+ -> Seq Scan on public.int8_tbl i8
Output: i8.q1, i8.q2
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- Output: i8.q1, i8.q2
- -> Seq Scan on public.int8_tbl i8
- Output: i8.q1, i8.q2
- Filter: (i8.q2 = 123)
- -> Materialize
+ Filter: (i8.q2 = 123)
+ -> Memoize
Output: (i8.q1), t2.f1
+ Cache Key: i8.q1
+ Cache Mode: binary
-> Limit
Output: (i8.q1), t2.f1
- -> Result
+ -> Seq Scan on public.text_tbl t2
Output: i8.q1, t2.f1
- -> Materialize
- Output: t2.f1
- -> Gather Motion 3:1 (slice3; segments: 3)
- Output: t2.f1
- -> Seq Scan on public.text_tbl t2
- Output: t2.f1
- -> Materialize
+ -> Memoize
Output: ((i8.q1)), (t2.f1)
+ Cache Key: (i8.q1), t2.f1
+ Cache Mode: binary
-> Limit
Output: ((i8.q1)), (t2.f1)
- -> Result
+ -> Seq Scan on public.text_tbl t3
Output: (i8.q1), t2.f1
- -> Materialize
- -> Gather Motion 3:1 (slice4; segments: 3)
- -> Seq Scan on public.text_tbl t3
- Optimizer: Postgres-based planner
-(40 rows)
+(30 rows)
select * from
text_tbl t1
@@ -4644,56 +4642,42 @@ select 1 from
left join text_tbl as tt4 on (tt3.f1 = tt4.f1),
lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1
where tt1.f1 = ss1.c0;
- QUERY PLAN
-------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------
Nested Loop
Output: 1
- -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Nested Loop Left Join
Output: tt1.f1, tt4.f1
- -> Hash Left Join
- Output: tt1.f1, tt4.f1
- Hash Cond: (tt3.f1 = tt4.f1)
+ -> Nested Loop
+ Output: tt1.f1
+ -> Seq Scan on public.text_tbl tt1
+ Output: tt1.f1
+ Filter: (tt1.f1 = 'foo'::text)
+ -> Seq Scan on public.text_tbl tt2
+ Output: tt2.f1
+ -> Materialize
+ Output: tt4.f1
-> Nested Loop Left Join
- Output: tt1.f1, tt3.f1
- -> Nested Loop
- Output: tt1.f1
- -> Seq Scan on public.text_tbl tt1
- Output: tt1.f1
- Filter: (tt1.f1 = 'foo'::text)
- -> Materialize
- -> Redistribute Motion 3:3 (slice2; segments: 3)
- Hash Key: 'foo'::text
- -> Seq Scan on public.text_tbl tt2
- -> Materialize
- Output: tt3.f1
- -> Redistribute Motion 1:3 (slice3; segments: 1)
- Output: tt3.f1
- Hash Key: 'foo'::text
- -> Seq Scan on public.text_tbl tt3
- Output: tt3.f1
- Filter: (tt3.f1 = 'foo'::text)
- -> Hash
Output: tt4.f1
- -> Redistribute Motion 1:3 (slice4; segments: 1)
+ Join Filter: (tt3.f1 = tt4.f1)
+ -> Seq Scan on public.text_tbl tt3
+ Output: tt3.f1
+ Filter: (tt3.f1 = 'foo'::text)
+ -> Seq Scan on public.text_tbl tt4
Output: tt4.f1
- Hash Key: 'foo'::text
- -> Seq Scan on public.text_tbl tt4
- Output: tt4.f1
- Filter: (tt4.f1 = 'foo'::text)
- -> Materialize
+ Filter: (tt4.f1 = 'foo'::text)
+ -> Memoize
Output: ss1.c0
+ Cache Key: tt4.f1
+ Cache Mode: binary
-> Subquery Scan on ss1
Output: ss1.c0
Filter: (ss1.c0 = 'foo'::text)
-> Limit
Output: (tt4.f1)
- -> Result
+ -> Seq Scan on public.text_tbl tt5
Output: tt4.f1
- -> Materialize
- -> Gather Motion 3:1 (slice5; segments: 3)
- -> Seq Scan on public.text_tbl tt5
- Optimizer: Postgres-based planner
-(47 rows)
+(33 rows)
select 1 from
text_tbl as tt1
@@ -4706,9 +4690,14 @@ where tt1.f1 = ss1.c0;
----------
(0 rows)
+--end_ignore
--
-- check a case in which a PlaceHolderVar forces join order
--
+--start_ignore
+--GPDB_94_STABLE_MERGE_FIXME: This query is lateral related and its plan is
+--different from PostgreSQL's. Do not know why yet. Ignore its plan
+--temporarily.
explain (verbose, costs off)
select ss2.* from
int4_tbl i41
@@ -4719,47 +4708,36 @@ select ss2.* from
on i41.f1 = ss1.c1,
lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2
where ss1.c2 = 0;
- QUERY PLAN
-------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------
Nested Loop
Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42))
- -> Gather Motion 3:1 (slice1; segments: 3)
- Output: i41.f1, i42.f1, i8.q1, i8.q2, i43.f1, (42)
+ -> Hash Join
+ Output: i41.f1, i42.f1, i8.q1, i8.q2, i43.f1, 42
+ Hash Cond: (i41.f1 = i42.f1)
-> Nested Loop
- Output: i41.f1, i42.f1, i8.q1, i8.q2, i43.f1, 42
+ Output: i8.q1, i8.q2, i43.f1, i41.f1
-> Nested Loop
- Output: i41.f1, i42.f1, i8.q1, i8.q2
- -> Redistribute Motion 3:3 (slice2; segments: 3)
- Output: i41.f1, i42.f1
- Hash Key: 0
- -> Hash Join
- Output: i41.f1, i42.f1
- Hash Cond: (i41.f1 = i42.f1)
- -> Seq Scan on public.int4_tbl i41
- Output: i41.f1
- -> Hash
- Output: i42.f1
- -> Seq Scan on public.int4_tbl i42
- Output: i42.f1
+ Output: i8.q1, i8.q2, i43.f1
-> Seq Scan on public.int8_tbl i8
Output: i8.q1, i8.q2
Filter: (i8.q1 = 0)
- -> Seq Scan on public.int4_tbl i43
- Output: i43.f1
- Filter: (i43.f1 = 0)
- -> Materialize
+ -> Seq Scan on public.int4_tbl i43
+ Output: i43.f1
+ Filter: (i43.f1 = 0)
+ -> Seq Scan on public.int4_tbl i41
+ Output: i41.f1
+ -> Hash
+ Output: i42.f1
+ -> Seq Scan on public.int4_tbl i42
+ Output: i42.f1
+ -> Limit
Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42))
- -> Limit
- Output: (i41.f1), (i8.q1), (i8.q2), (i42.f1), (i43.f1), ((42))
- -> Result
- Output: i41.f1, i8.q1, i8.q2, i42.f1, i43.f1, (42)
- -> Materialize
- -> Gather Motion 3:1 (slice3; segments: 3)
- -> Seq Scan on public.text_tbl
- Optimizer: Postgres-based planner
- Settings: optimizer = 'off'
-(37 rows)
+ -> Seq Scan on public.text_tbl
+ Output: i41.f1, i8.q1, i8.q2, i42.f1, i43.f1, (42)
+(25 rows)
+--end_ignore
select ss2.* from
int4_tbl i41
left join int8_tbl i8
@@ -4796,7 +4774,7 @@ select * from
-> Redistribute Motion 1:3 (slice2; segments: 1)
Hash Key: (1)
-> Result
- Optimizer: Postgres-based planner
+ Optimizer: Postgres query optimizer
(13 rows)
select * from
@@ -5556,6 +5534,11 @@ explain (costs off)
(11 rows)
-- lateral with UNION ALL subselect
+-- start_ignore
+-- GPDB_96_MERGE_FIXME: we used to get a better plan here, with
+-- the Nested Loop join being done on segments, and just a single
+-- Gather Motion at the top. Can we improve?
+-- end_ignore
explain (costs off)
select * from generate_series(100,200) g,
lateral (select * from int8_tbl a where g = q1 union all
@@ -5646,23 +5629,96 @@ select count(*) from tenk1 a,
(1 row)
-- lateral injecting a strange outer join condition
--- TODO_LATERAL: failed to generate plan
--- If the right path needs params of outer path.
--- We can not add any motion above right path.
--- however, in left join, we can not add broadcast motion above left path.
--- So We can not add proper motion to generate path for left join,
--- And hit the error: could not devise a query plan for the given query.
+-- start_ignore
+-- GPDB_93_MERGE_FIXME: These queries are failing at the moment. Need to investigate.
+-- There were a lot of LATERAL fixes in upstream minor versions, so I'm hoping that
+-- these will get fixed once we catch up to those. Or if not, at least it will be
+-- nicer to work on the code, knowing that there aren't going to be a dozen commits
+-- coming up, touching the same area.
explain (costs off)
select * from int8_tbl a,
int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
on x.q2 = ss.z
order by a.q1, a.q2, x.q1, x.q2, ss.z;
-ERROR: could not devise a query plan for the given query (pathnode.c:275)
+ QUERY PLAN
+------------------------------------------------
+ Sort
+ Sort Key: a.q1, a.q2, x.q1, x.q2, (a.q1)
+ -> Nested Loop
+ -> Seq Scan on int8_tbl a
+ -> Hash Right Join
+ Hash Cond: ((a.q1) = x.q2)
+ -> Seq Scan on int4_tbl y
+ -> Hash
+ -> Seq Scan on int8_tbl x
+(9 rows)
+
select * from int8_tbl a,
int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
on x.q2 = ss.z
order by a.q1, a.q2, x.q1, x.q2, ss.z;
-ERROR: could not devise a query plan for the given query (pathnode.c:275)
+ q1 | q2 | q1 | q2 | z
+------------------+-------------------+------------------+-------------------+------------------
+ 123 | 456 | 123 | 456 |
+ 123 | 456 | 123 | 4567890123456789 |
+ 123 | 456 | 4567890123456789 | -4567890123456789 |
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 123 | 123
+ 123 | 456 | 4567890123456789 | 4567890123456789 |
+ 123 | 4567890123456789 | 123 | 456 |
+ 123 | 4567890123456789 | 123 | 4567890123456789 |
+ 123 | 4567890123456789 | 4567890123456789 | -4567890123456789 |
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789 |
+ 4567890123456789 | -4567890123456789 | 123 | 456 |
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | -4567890123456789 |
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 123 |
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 456 |
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | -4567890123456789 |
+ 4567890123456789 | 123 | 4567890123456789 | 123 |
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 456 |
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789 |
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 123 |
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
+(57 rows)
+
+--end_ignore
-- lateral reference to a join alias variable
select * from (select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1,
lateral (select x) ss2(y);
@@ -6096,12 +6152,9 @@ select * from int4_tbl a,
(25 rows)
-- lateral reference in a PlaceHolderVar evaluated at join level
--- TODO_LATERAL: failed to generate plan
--- If the right path needs params of outer path.
--- We can not add any motion above right path.
--- however, in left join, we can not add broadcast motion above left path.
--- So We can not add proper motion to generate path for left join,
--- And hit the error: could not devise a query plan for the given query.
+-- GPDB_94_STABLE_MERGE_FIXME: The query fails. The change is related to
+-- upstream commit acfcd4. Need to come back to fix it when understanding more
+-- about that commit.
explain (verbose, costs off)
select * from
int8_tbl a left join lateral
@@ -6475,23 +6528,53 @@ create table join_pt1p1p1 partition of join_pt1p1 for values from (0) to (100);
insert into join_pt1 values (1, 1, 'x'), (101, 101, 'y');
create table join_ut1 (a int, b int, c varchar);
insert into join_ut1 values (101, 101, 'y'), (2, 2, 'z');
--- TODO_LATERAL: failed to generate plan
--- If the right path needs params of outer path.
--- We can not add any motion above right path.
--- however, in left join, we can not add broadcast motion above left path.
--- So We can not add proper motion to generate path for left join,
--- And hit the error: could not devise a query plan for the given query.
+-- GPDB_12_MERGE_FIXME: The query fails. This test query is new with v12,
+-- but a corresponding query fails on GPDB master, too. I think this is
+-- similar to the case marked with GPDB_94_STABLE_MERGE_FIXME above.
+-- upstream commit acfcd4. Need to come back to fix it when understanding more
+-- about that commit.
+-- start_ignore
explain (verbose, costs off)
select t1.b, ss.phv from join_ut1 t1 left join lateral
(select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss
on t1.a = ss.t2a order by t1.a;
-ERROR: could not devise a query plan for the given query (pathnode.c:275)
+ QUERY PLAN
+--------------------------------------------------------------------
+ Sort
+ Output: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a
+ Sort Key: t1.a
+ -> Nested Loop Left Join
+ Output: t1.b, (LEAST(t1.a, t2.a, t3.a)), t1.a
+ -> Seq Scan on public.join_ut1 t1
+ Output: t1.a, t1.b, t1.c
+ -> Hash Join
+ Output: t2.a, LEAST(t1.a, t2.a, t3.a)
+ Hash Cond: (t3.b = t2.a)
+ -> Seq Scan on public.join_ut1 t3
+ Output: t3.a, t3.b, t3.c
+ -> Hash
+ Output: t2.a
+ -> Append
+ -> Seq Scan on public.join_pt1p1p1 t2_1
+ Output: t2_1.a
+ Filter: (t1.a = t2_1.a)
+ -> Seq Scan on public.join_pt1p2 t2_2
+ Output: t2_2.a
+ Filter: (t1.a = t2_2.a)
+(21 rows)
+
select t1.b, ss.phv from join_ut1 t1 left join lateral
(select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss
on t1.a = ss.t2a order by t1.a;
-ERROR: could not devise a query plan for the given query (pathnode.c:275)
+ b | phv
+-----+-----
+ 2 |
+ 101 | 101
+(2 rows)
+
+-- end_ignore
drop table join_pt1;
drop table join_ut1;
--
diff --git a/src/test/regress/expected/join_optimizer.out b/src/test/regress/expected/join_optimizer.out
index cf1440d156..c5e1f03725 100644
--- a/src/test/regress/expected/join_optimizer.out
+++ b/src/test/regress/expected/join_optimizer.out
@@ -3233,12 +3233,6 @@ order by 1,2;
--
-- variant where a PlaceHolderVar is needed at a join, but not above the join
--
--- TODO_LATERAL: failed to generate plan
--- If the left path needs params of outer path.
--- We can not add any motion above left path.
--- however, in right join, we can not add broadcast motion above right path.
--- So We can not add proper motion to generate path for right join,
--- And hit the error: could not devise a query plan for the given query.
explain (costs off)
select * from
int4_tbl as i41,
@@ -3280,12 +3274,34 @@ select * from
right join int4_tbl as i43 on (i43.f1 > 1)
where ss1.loc = ss1.lat) as ss2
where i41.f1 > 0;
-ERROR: could not devise a query plan for the given query (pathnode.c:275)
-select * from int4_tbl a inner join int4_tbl b on false;
- f1 | f1
-----+----
-(0 rows)
-
+ERROR: could not devise a query plan for the given query (pathnode.c:277)
+--start_ignore
+--GPDB_14_MERGE_FIXME: We need to make plan work. One idea is generate a gather
+--motion for relations. Even it is not efficient, but better than error out here.
+ f1 | x
+------------+---
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 123456 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+ 2147483647 | 1
+(20 rows)
+--end_ignore
--
-- test the corner cases FULL JOIN ON TRUE and FULL JOIN ON FALSE
--
@@ -4688,6 +4704,11 @@ select * from
--
-- test for appropriate join order in the presence of lateral references
--
+-- start_ignore
+-- GPDB_94_STABLE_MERGE_FIXME: Currently LATERAL is not fully supported in GPDB
+-- and the queries below are failing at the moment (The first one fails with
+-- error and the other two fail with panic). Comment them off temporarily.
+/*
explain (verbose, costs off)
select * from
text_tbl t1
@@ -4695,38 +4716,6 @@ select * from
on i8.q2 = 123,
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
where t1.f1 = ss.f1;
- QUERY PLAN
-------------------------------------------------------------------------
- Nested Loop
- Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
- Join Filter: (t1.f1 = t2.f1)
- -> Gather Motion 3:1 (slice1; segments: 3)
- Output: t1.f1, i8.q1, i8.q2
- -> Nested Loop Left Join
- Output: t1.f1, i8.q1, i8.q2
- -> Seq Scan on public.text_tbl t1
- Output: t1.f1
- -> Materialize
- Output: i8.q1, i8.q2
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- Output: i8.q1, i8.q2
- -> Seq Scan on public.int8_tbl i8
- Output: i8.q1, i8.q2
- Filter: (i8.q2 = 123)
- -> Materialize
- Output: (i8.q1), t2.f1
- -> Limit
- Output: (i8.q1), t2.f1
- -> Result
- Output: i8.q1, t2.f1
- -> Materialize
- Output: t2.f1
- -> Gather Motion 3:1 (slice3; segments: 3)
- Output: t2.f1
- -> Seq Scan on public.text_tbl t2
- Output: t2.f1
- Optimizer: Postgres-based planner
-(29 rows)
select * from
text_tbl t1
@@ -4734,10 +4723,6 @@ select * from
on i8.q2 = 123,
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss
where t1.f1 = ss.f1;
- f1 | q1 | q2 | q1 | f1
-------+------------------+-----+------------------+------
- doh! | 4567890123456789 | 123 | 4567890123456789 | doh!
-(1 row)
explain (verbose, costs off)
select * from
@@ -4747,49 +4732,6 @@ select * from
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1,
lateral (select ss1.* from text_tbl t3 limit 1) as ss2
where t1.f1 = ss2.f1;
- QUERY PLAN
-------------------------------------------------------------------------------
- Nested Loop
- Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1, ((i8.q1)), (t2.f1)
- Join Filter: (t1.f1 = (t2.f1))
- -> Nested Loop
- Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
- -> Gather Motion 3:1 (slice1; segments: 3)
- Output: t1.f1, i8.q1, i8.q2
- -> Nested Loop Left Join
- Output: t1.f1, i8.q1, i8.q2
- -> Seq Scan on public.text_tbl t1
- Output: t1.f1
- -> Materialize
- Output: i8.q1, i8.q2
- -> Broadcast Motion 3:3 (slice2; segments: 3)
- Output: i8.q1, i8.q2
- -> Seq Scan on public.int8_tbl i8
- Output: i8.q1, i8.q2
- Filter: (i8.q2 = 123)
- -> Materialize
- Output: (i8.q1), t2.f1
- -> Limit
- Output: (i8.q1), t2.f1
- -> Result
- Output: i8.q1, t2.f1
- -> Materialize
- Output: t2.f1
- -> Gather Motion 3:1 (slice3; segments: 3)
- Output: t2.f1
- -> Seq Scan on public.text_tbl t2
- Output: t2.f1
- -> Materialize
- Output: ((i8.q1)), (t2.f1)
- -> Limit
- Output: ((i8.q1)), (t2.f1)
- -> Result
- Output: (i8.q1), t2.f1
- -> Materialize
- -> Gather Motion 3:1 (slice4; segments: 3)
- -> Seq Scan on public.text_tbl t3
- Optimizer: Postgres-based planner
-(40 rows)
select * from
text_tbl t1
@@ -4798,10 +4740,6 @@ select * from
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1,
lateral (select ss1.* from text_tbl t3 limit 1) as ss2
where t1.f1 = ss2.f1;
- f1 | q1 | q2 | q1 | f1 | q1 | f1
-------+------------------+-----+------------------+------+------------------+------
- doh! | 4567890123456789 | 123 | 4567890123456789 | doh! | 4567890123456789 | doh!
-(1 row)
explain (verbose, costs off)
select 1 from
@@ -4811,56 +4749,6 @@ select 1 from
left join text_tbl as tt4 on (tt3.f1 = tt4.f1),
lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1
where tt1.f1 = ss1.c0;
- QUERY PLAN
-------------------------------------------------------------------------------------
- Nested Loop
- Output: 1
- -> Gather Motion 3:1 (slice1; segments: 3)
- Output: tt1.f1, tt4.f1
- -> Hash Left Join
- Output: tt1.f1, tt4.f1
- Hash Cond: (tt3.f1 = tt4.f1)
- -> Nested Loop Left Join
- Output: tt1.f1, tt3.f1
- -> Nested Loop
- Output: tt1.f1
- -> Seq Scan on public.text_tbl tt1
- Output: tt1.f1
- Filter: (tt1.f1 = 'foo'::text)
- -> Materialize
- -> Redistribute Motion 3:3 (slice2; segments: 3)
- Hash Key: 'foo'::text
- -> Seq Scan on public.text_tbl tt2
- -> Materialize
- Output: tt3.f1
- -> Redistribute Motion 1:3 (slice3; segments: 1)
- Output: tt3.f1
- Hash Key: 'foo'::text
- -> Seq Scan on public.text_tbl tt3
- Output: tt3.f1
- Filter: (tt3.f1 = 'foo'::text)
- -> Hash
- Output: tt4.f1
- -> Redistribute Motion 1:3 (slice4; segments: 1)
- Output: tt4.f1
- Hash Key: 'foo'::text
- -> Seq Scan on public.text_tbl tt4
- Output: tt4.f1
- Filter: (tt4.f1 = 'foo'::text)
- -> Materialize
- Output: ss1.c0
- -> Subquery Scan on ss1
- Output: ss1.c0
- Filter: (ss1.c0 = 'foo'::text)
- -> Limit
- Output: (tt4.f1)
- -> Result
- Output: tt4.f1
- -> Materialize
- -> Gather Motion 3:1 (slice5; segments: 3)
- -> Seq Scan on public.text_tbl tt5
- Optimizer: Postgres-based planner
-(47 rows)
select 1 from
text_tbl as tt1
@@ -4869,13 +4757,15 @@ select 1 from
left join text_tbl as tt4 on (tt3.f1 = tt4.f1),
lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1
where tt1.f1 = ss1.c0;
- ?column?
-----------
-(0 rows)
-
+*/
+--end_ignore
--
-- check a case in which a PlaceHolderVar forces join order
--
+--start_ignore
+--GPDB_94_STABLE_MERGE_FIXME: This query is lateral related and its plan is
+--different from PostgreSQL's. Do not know why yet. Ignore its plan
+--temporarily.
explain (verbose, costs off)
select ss2.* from
int4_tbl i41
@@ -4927,6 +4817,7 @@ where ss1.c2 = 0;
Optimizer: Postgres query optimizer
(37 rows)
+--end_ignore
select ss2.* from
int4_tbl i41
left join int8_tbl i8
@@ -5816,12 +5707,13 @@ select count(*) from tenk1 a,
(1 row)
-- lateral injecting a strange outer join condition
--- TODO_LATERAL: failed to generate plan
--- If the right path needs params of outer path.
--- We can not add any motion above right path.
--- however, in left join, we can not add broadcast motion above left path.
--- So We can not add proper motion to generate path for left join,
--- And hit the error: could not devise a query plan for the given query.
+-- start_ignore
+-- GPDB_93_MERGE_FIXME: These queries are failing at the moment. Need to investigate.
+-- There were a lot of LATERAL fixes in upstream minor versions, so I'm hoping that
+-- these will get fixed once we catch up to those. Or if not, at least it will be
+-- nicer to work on the code, knowing that there aren't going to be a dozen commits
+-- coming up, touching the same area.
+-- FAIL with ERROR: could not devise a query plan for the given query (pathnode.c:416)
explain (costs off)
select * from int8_tbl a,
int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
@@ -5832,7 +5724,8 @@ select * from int8_tbl a,
int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
on x.q2 = ss.z
order by a.q1, a.q2, x.q1, x.q2, ss.z;
-ERROR: could not devise a query plan for the given query (pathnode.c:275)
+ERROR: could not devise a query plan for the given query (pathnode.c:277)
+--end_ignore
-- lateral reference to a join alias variable
select * from (select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1,
lateral (select x) ss2(y);
@@ -6265,12 +6158,9 @@ select * from int4_tbl a,
(25 rows)
-- lateral reference in a PlaceHolderVar evaluated at join level
--- TODO_LATERAL: failed to generate plan
--- If the right path needs params of outer path.
--- We can not add any motion above right path.
--- however, in left join, we can not add broadcast motion above left path.
--- So We can not add proper motion to generate path for left join,
--- And hit the error: could not devise a query plan for the given query.
+-- GPDB_94_STABLE_MERGE_FIXME: The query fails. The change is related to
+-- upstream commit acfcd4. Need to come back to fix it when understanding more
+-- about that commit.
explain (verbose, costs off)
select * from
int8_tbl a left join lateral
@@ -6646,12 +6536,12 @@ create table join_ut1 (a int, b int, c varchar);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into join_ut1 values (101, 101, 'y'), (2, 2, 'z');
--- TODO_LATERAL: failed to generate plan
--- If the right path needs params of outer path.
--- We can not add any motion above right path.
--- however, in left join, we can not add broadcast motion above left path.
--- So We can not add proper motion to generate path for left join,
--- And hit the error: could not devise a query plan for the given query.
+-- GPDB_12_MERGE_FIXME: The query fails. This test query is new with v12,
+-- but a corresponding query fails on GPDB master, too. I think this is
+-- similar to the case marked with GPDB_94_STABLE_MERGE_FIXME above.
+-- upstream commit acfcd4. Need to come back to fix it when understanding more
+-- about that commit.
+-- start_ignore
explain (verbose, costs off)
select t1.b, ss.phv from join_ut1 t1 left join lateral
(select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
@@ -6663,6 +6553,7 @@ select t1.b, ss.phv from join_ut1 t1 left join lateral
from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss
on t1.a = ss.t2a order by t1.a;
ERROR: could not devise a query plan for the given query (pathnode.c:275)
+-- end_ignore
drop table join_pt1;
drop table join_ut1;
--
diff --git a/src/test/regress/expected/tablesample.out b/src/test/regress/expected/tablesample.out
index ab3be0a615..8d31595481 100644
--- a/src/test/regress/expected/tablesample.out
+++ b/src/test/regress/expected/tablesample.out
@@ -347,35 +347,40 @@ RESET enable_hashjoin;
RESET enable_mergejoin;
RESET enable_nestloop;
-- check behavior during rescans, as well as correct handling of min/max pct
+-- Cloudberry: does not support laterals completely, rescan specific tests above
+-- start_ignore
select * from
(values (0),(100)) v(pct),
lateral (select count(*) from tenk1 tablesample bernoulli (pct)) ss;
-ERROR: Passing parameters across motion is not supported. (cdbmutate.c:2047)
+ pct | count
+-----+-------
+ 0 | 0
+ 100 | 10000
+(2 rows)
+
select * from
(values (0),(100)) v(pct),
lateral (select count(*) from tenk1 tablesample system (pct)) ss;
-ERROR: Passing parameters across motion is not supported. (cdbmutate.c:2047)
+ pct | count
+-----+-------
+ 0 | 0
+ 100 | 10000
+(2 rows)
+
explain (costs off)
select pct, count(unique1) from
(values (0),(100)) v(pct),
lateral (select * from tenk1 tablesample bernoulli (pct)) ss
group by pct;
- QUERY PLAN
---------------------------------------------------------------------------
- Finalize GroupAggregate
+ QUERY PLAN
+--------------------------------------------------------
+ HashAggregate
Group Key: "*VALUES*".column1
- -> Gather Motion 3:1 (slice1; segments: 3)
- Merge Key: "*VALUES*".column1
- -> Partial GroupAggregate
- Group Key: "*VALUES*".column1
- -> Sort
- Sort Key: "*VALUES*".column1
- -> Nested Loop
- -> Values Scan on "*VALUES*"
- -> Sample Scan on tenk1
- Sampling: bernoulli ("*VALUES*".column1)
- Optimizer: Postgres query optimizer
-(13 rows)
+ -> Nested Loop
+ -> Values Scan on "*VALUES*"
+ -> Sample Scan on tenk1
+ Sampling: bernoulli ("*VALUES*".column1)
+(6 rows)
select pct, count(unique1) from
(values (0),(100)) v(pct),
@@ -395,6 +400,7 @@ select pct, count(unique1) from
100 | 10000
(1 row)
+-- end_ignore
-- Cloudberry: we do have to test min/max pct tests though
select 0 as pct, count(*) from tenk1 tablesample bernoulli (0)
union all
diff --git a/src/test/regress/init_file b/src/test/regress/init_file
index 19e82800e4..d305579500 100644
--- a/src/test/regress/init_file
+++ b/src/test/regress/init_file
@@ -180,7 +180,4 @@ s/\(dbsize\.c:\d+\)/\(dbsize\.c:XXX\)/
# ignore resource queue ids
m/resource queue id: \d+/
s/resource queue id: \d+/resource queue id: XXXX/
-
-m/\(cdbmutate\.c\:\d+\)/
-s/\(cdbmutate\.c:\d+\)/\(cdbmutate\.c:XXX\)/
-- end_matchsubs
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 128638e1fe..b6c1197286 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -948,12 +948,7 @@ order by 1,2;
--
-- variant where a PlaceHolderVar is needed at a join, but not above the join
--
--- TODO_LATERAL: failed to generate plan
--- If the left path needs params of outer path.
--- We can not add any motion above left path.
--- however, in right join, we can not add broadcast motion above right path.
--- So We can not add proper motion to generate path for right join,
--- And hit the error: could not devise a query plan for the given query.
+
explain (costs off)
select * from
int4_tbl as i41,
@@ -977,8 +972,6 @@ select * from
where ss1.loc = ss1.lat) as ss2
where i41.f1 > 0;
-select * from int4_tbl a inner join int4_tbl b on false;
-
--
-- test the corner cases FULL JOIN ON TRUE and FULL JOIN ON FALSE
--
@@ -1460,6 +1453,11 @@ select * from
--
-- test for appropriate join order in the presence of lateral references
--
+-- start_ignore
+-- GPDB_94_STABLE_MERGE_FIXME: Currently LATERAL is not fully supported in GPDB
+-- and the queries below are failing at the moment (The first one fails with
+-- error and the other two fail with panic). Comment them off temporarily.
+/*
explain (verbose, costs off)
select * from
text_tbl t1
@@ -1508,11 +1506,17 @@ select 1 from
left join text_tbl as tt4 on (tt3.f1 = tt4.f1),
lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1
where tt1.f1 = ss1.c0;
-
+*/
+--end_ignore
--
-- check a case in which a PlaceHolderVar forces join order
--
+
+--start_ignore
+--GPDB_94_STABLE_MERGE_FIXME: This query is lateral related and its plan is
+--different from PostgreSQL's. Do not know why yet. Ignore its plan
+--temporarily.
explain (verbose, costs off)
select ss2.* from
int4_tbl i41
@@ -1523,6 +1527,7 @@ select ss2.* from
on i41.f1 = ss1.c1,
lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2
where ss1.c2 = 0;
+--end_ignore
select ss2.* from
int4_tbl i41
@@ -1887,12 +1892,13 @@ select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
-- lateral injecting a strange outer join condition
--- TODO_LATERAL: failed to generate plan
--- If the right path needs params of outer path.
--- We can not add any motion above right path.
--- however, in left join, we can not add broadcast motion above left path.
--- So We can not add proper motion to generate path for left join,
--- And hit the error: could not devise a query plan for the given query.
+-- start_ignore
+-- GPDB_93_MERGE_FIXME: These queries are failing at the moment. Need to investigate.
+-- There were a lot of LATERAL fixes in upstream minor versions, so I'm hoping that
+-- these will get fixed once we catch up to those. Or if not, at least it will be
+-- nicer to work on the code, knowing that there aren't going to be a dozen commits
+-- coming up, touching the same area.
+-- FAIL with ERROR: could not devise a query plan for the given query (pathnode.c:416)
explain (costs off)
select * from int8_tbl a,
int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
@@ -1902,6 +1908,7 @@ select * from int8_tbl a,
int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
on x.q2 = ss.z
order by a.q1, a.q2, x.q1, x.q2, ss.z;
+--end_ignore
-- lateral reference to a join alias variable
select * from (select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1,
@@ -1980,12 +1987,9 @@ select * from int4_tbl a,
) ss;
-- lateral reference in a PlaceHolderVar evaluated at join level
--- TODO_LATERAL: failed to generate plan
--- If the right path needs params of outer path.
--- We can not add any motion above right path.
--- however, in left join, we can not add broadcast motion above left path.
--- So We can not add proper motion to generate path for left join,
--- And hit the error: could not devise a query plan for the given query.
+-- GPDB_94_STABLE_MERGE_FIXME: The query fails. The change is related to
+-- upstream commit acfcd4. Need to come back to fix it when understanding more
+-- about that commit.
explain (verbose, costs off)
select * from
int8_tbl a left join lateral
@@ -2127,12 +2131,12 @@ create table join_pt1p1p1 partition of join_pt1p1 for values from (0) to (100);
insert into join_pt1 values (1, 1, 'x'), (101, 101, 'y');
create table join_ut1 (a int, b int, c varchar);
insert into join_ut1 values (101, 101, 'y'), (2, 2, 'z');
--- TODO_LATERAL: failed to generate plan
--- If the right path needs params of outer path.
--- We can not add any motion above right path.
--- however, in left join, we can not add broadcast motion above left path.
--- So We can not add proper motion to generate path for left join,
--- And hit the error: could not devise a query plan for the given query.
+-- GPDB_12_MERGE_FIXME: The query fails. This test query is new with v12,
+-- but a corresponding query fails on GPDB master, too. I think this is
+-- similar to the case marked with GPDB_94_STABLE_MERGE_FIXME above.
+-- upstream commit acfcd4. Need to come back to fix it when understanding more
+-- about that commit.
+-- start_ignore
explain (verbose, costs off)
select t1.b, ss.phv from join_ut1 t1 left join lateral
(select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
@@ -2142,7 +2146,7 @@ select t1.b, ss.phv from join_ut1 t1 left join lateral
(select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss
on t1.a = ss.t2a order by t1.a;
-
+-- end_ignore
drop table join_pt1;
drop table join_ut1;
diff --git a/src/test/regress/sql/tablesample.sql b/src/test/regress/sql/tablesample.sql
index cdbaebeeb5..b1f9d57594 100644
--- a/src/test/regress/sql/tablesample.sql
+++ b/src/test/regress/sql/tablesample.sql
@@ -92,6 +92,8 @@ RESET enable_mergejoin;
RESET enable_nestloop;
-- check behavior during rescans, as well as correct handling of min/max pct
+-- Cloudberry: does not support laterals completely, rescan specific tests above
+-- start_ignore
select * from
(values (0),(100)) v(pct),
lateral (select count(*) from tenk1 tablesample bernoulli (pct)) ss;
@@ -111,6 +113,7 @@ select pct, count(unique1) from
(values (0),(100)) v(pct),
lateral (select * from tenk1 tablesample system (pct)) ss
group by pct;
+-- end_ignore
-- Cloudberry: we do have to test min/max pct tests though
select 0 as pct, count(*) from tenk1 tablesample bernoulli (0)
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org