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 0f684d1fe3 Fix wrong result due to ignore PlaceHolderVar.
0f684d1fe3 is described below
commit 0f684d1fe32e5bfc50080e83e558ef2960ff57b9
Author: Tender Wang <[email protected]>
AuthorDate: Mon Feb 10 14:56:48 2025 +0800
Fix wrong result due to ignore PlaceHolderVar.
The targetlist in nullable side may be PlaceHolderVar when query has
outer join. The targetlist in sublink will also be PlaceHolderVar if
it refer to nullable side column.
In current CorrelatedVarWalker(), we don't take PlaceHolderVar into
account. Wrong result will return if we use Postgres Planner.
---
src/backend/optimizer/plan/subselect.c | 9 +++++
src/test/regress/expected/subselect_gp.out | 39 +++++++++++++++++++++
.../regress/expected/subselect_gp_optimizer.out | 40 ++++++++++++++++++++++
src/test/regress/sql/subselect_gp.sql | 16 +++++++++
4 files changed, 104 insertions(+)
diff --git a/src/backend/optimizer/plan/subselect.c
b/src/backend/optimizer/plan/subselect.c
index e2b424b7d6..251168e63c 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -215,6 +215,15 @@ CorrelatedVarWalker(Node *node, CorrelatedVarWalkerContext
*ctx)
}
return false;
}
+ else if (IsA(node, PlaceHolderVar))
+ {
+ PlaceHolderVar *phv = (PlaceHolderVar *) node;
+ if (phv->phlevelsup > ctx->maxLevelsUp)
+ {
+ ctx->maxLevelsUp = phv->phlevelsup;
+ }
+ return false;
+ }
else if (IsA(node, Query))
{
return query_tree_walker((Query *) node, CorrelatedVarWalker,
ctx, 0 /* flags */);
diff --git a/src/test/regress/expected/subselect_gp.out
b/src/test/regress/expected/subselect_gp.out
index f3b17982af..0d45865de7 100644
--- a/src/test/regress/expected/subselect_gp.out
+++ b/src/test/regress/expected/subselect_gp.out
@@ -62,6 +62,45 @@ select csq_t1.x, (select bar.x from csq_t1 bar where bar.x =
csq_t1.x) as sum fr
4 | 4
(3 rows)
+--
+-- Another case correlations in the targetlist: PlaceHolderVar
+--
+drop table if exists phv_t;
+NOTICE: table "phv_t" does not exist, skipping
+create table phv_t(a int, b int) distributed by (a);
+insert into phv_t values(1,1),(2,2);
+explain(costs off) select *, (select ss.y as z from phv_t as t3 limit 1) from
phv_t t1 left join
+(select a as x, 42 as y from phv_t t2) ss on t1.b = ss.x order by 1,2;
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: t1.a, t1.b
+ -> Sort
+ Sort Key: t1.a, t1.b
+ -> Hash Left Join
+ Hash Cond: (t1.b = t2.a)
+ -> Redistribute Motion 3:3 (slice3; segments: 3)
+ Hash Key: t1.b
+ -> Seq Scan on phv_t t1
+ -> Hash
+ -> Seq Scan on phv_t t2
+ SubPlan 1
+ -> Limit
+ -> Result
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice2;
segments: 3)
+ -> Seq Scan on phv_t t3
+ Optimizer: Postgres query optimizer
+(18 rows)
+
+select *, (select ss.y as z from phv_t as t3 limit 1) from phv_t t1 left join
+(select a as x, 42 as y from phv_t t2) ss on t1.b = ss.x order by 1,2;
+ a | b | x | y | z
+---+---+---+----+----
+ 1 | 1 | 1 | 42 | 42
+ 2 | 2 | 2 | 42 | 42
+(2 rows)
+
--
-- CSQs with partitioned tables
--
diff --git a/src/test/regress/expected/subselect_gp_optimizer.out
b/src/test/regress/expected/subselect_gp_optimizer.out
index 6484fc977c..bf55ce2dc6 100644
--- a/src/test/regress/expected/subselect_gp_optimizer.out
+++ b/src/test/regress/expected/subselect_gp_optimizer.out
@@ -62,6 +62,46 @@ select csq_t1.x, (select bar.x from csq_t1 bar where bar.x =
csq_t1.x) as sum fr
4 | 4
(3 rows)
+--
+-- Another case correlations in the targetlist: PlaceHolderVar
+--
+drop table if exists phv_t;
+NOTICE: table "phv_t" does not exist, skipping
+create table phv_t(a int, b int) distributed by (a);
+insert into phv_t values(1,1),(2,2);
+explain(costs off) select *, (select ss.y as z from phv_t as t3 limit 1) from
phv_t t1 left join
+(select a as x, 42 as y from phv_t t2) ss on t1.b = ss.x order by 1,2;
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3)
+ Merge Key: phv_t.a, phv_t.b
+ -> Result
+ -> Sort
+ Sort Key: phv_t.a, phv_t.b
+ -> Hash Left Join
+ Hash Cond: (phv_t.b = phv_t_1.a)
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: phv_t.b
+ -> Seq Scan on phv_t
+ -> Hash
+ -> Seq Scan on phv_t phv_t_1
+ SubPlan 1
+ -> Limit
+ -> Result
+ -> Materialize
+ -> Broadcast Motion 3:3 (slice3; segments: 3)
+ -> Seq Scan on phv_t phv_t_2
+ Optimizer: Pivotal Optimizer (GPORCA)
+(19 rows)
+
+select *, (select ss.y as z from phv_t as t3 limit 1) from phv_t t1 left join
+(select a as x, 42 as y from phv_t t2) ss on t1.b = ss.x order by 1,2;
+ a | b | x | y | z
+---+---+---+----+----
+ 1 | 1 | 1 | 42 | 42
+ 2 | 2 | 2 | 42 | 42
+(2 rows)
+
--
-- CSQs with partitioned tables
--
diff --git a/src/test/regress/sql/subselect_gp.sql
b/src/test/regress/sql/subselect_gp.sql
index bf5f1288e6..cf3819918f 100644
--- a/src/test/regress/sql/subselect_gp.sql
+++ b/src/test/regress/sql/subselect_gp.sql
@@ -52,6 +52,22 @@ select csq_t1.x, (select sum(bar.x) from csq_t1 bar where
bar.x = csq_t1.x) as s
select csq_t1.x, (select bar.x from csq_t1 bar where bar.x = csq_t1.x) as sum
from csq_t1 order by csq_t1.x;
+--
+-- Another case correlations in the targetlist: PlaceHolderVar
+--
+
+drop table if exists phv_t;
+
+create table phv_t(a int, b int) distributed by (a);
+
+insert into phv_t values(1,1),(2,2);
+
+explain(costs off) select *, (select ss.y as z from phv_t as t3 limit 1) from
phv_t t1 left join
+(select a as x, 42 as y from phv_t t2) ss on t1.b = ss.x order by 1,2;
+
+select *, (select ss.y as z from phv_t as t3 limit 1) from phv_t t1 left join
+(select a as x, 42 as y from phv_t t2) ss on t1.b = ss.x order by 1,2;
+
--
-- CSQs with partitioned tables
--
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
