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/07 09:28:21 UTC
(cloudberry) 07/34: Addressing Review Comments
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
commit b49f333c49979f6ac835468342b72deddf429dd4
Author: DevChattopadhyay <cd...@vmware.com>
AuthorDate: Thu May 18 12:07:40 2023 +0530
Addressing Review Comments
---
src/test/regress/expected/join_gp.out | 26 ++++++++++++++++---------
src/test/regress/expected/join_gp_optimizer.out | 26 ++++++++++++++++---------
src/test/regress/sql/join_gp.sql | 26 ++++++++++++++++---------
3 files changed, 51 insertions(+), 27 deletions(-)
diff --git a/src/test/regress/expected/join_gp.out b/src/test/regress/expected/join_gp.out
index d651b89b46..f7e2b50ecf 100644
--- a/src/test/regress/expected/join_gp.out
+++ b/src/test/regress/expected/join_gp.out
@@ -2602,8 +2602,6 @@ SELECT 1 FROM inverse WHERE NOT (cidr <<= ANY(SELECT * FROM inverse));
----------
(0 rows)
--- check motion is added when performing a NL Left Outer Join between relations
--- when the join condition columns belong to different opfamily
create table foo_varchar (a varchar(5)) distributed by (a);
create table bar_char (p char(5)) distributed by (p);
create table random_dis_varchar (x varchar(5)) distributed randomly;
@@ -2615,6 +2613,9 @@ insert into random_dis_char values ('1 '),('2 '),('3 ');
set optimizer_enable_hashjoin to off;
set enable_hashjoin to off;
set enable_nestloop to on;
+-- check motion is added when performing a NL Left Outer Join between relations
+-- when the join condition columns belong to different opfamily and both are
+-- distribution keys
explain select * from foo_varchar left join bar_char on foo_varchar.a=bar_char.p;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
@@ -2629,7 +2630,7 @@ explain select * from foo_varchar left join bar_char on foo_varchar.a=bar_char.p
Optimizer: Postgres query optimizer
(9 rows)
-select * from foo_varchar left join bar_char on foo_varchar.a=bar_char.p order by foo_varchar.a;
+select * from foo_varchar left join bar_char on foo_varchar.a=bar_char.p;
a | p
------+-------
1 | 1
@@ -2637,6 +2638,9 @@ select * from foo_varchar left join bar_char on foo_varchar.a=bar_char.p order b
3 | 3
(3 rows)
+-- There is a plan change (from redistribution to broadcast) because a NULL
+-- matching distribution is returned when there is opfamily mismatch between join
+-- columns.
explain select * from foo_varchar left join random_dis_char on foo_varchar.a=random_dis_char.y;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
@@ -2653,7 +2657,7 @@ explain select * from foo_varchar left join random_dis_char on foo_varchar.a=ran
Optimizer: Postgres query optimizer
(11 rows)
-select * from foo_varchar left join random_dis_char on foo_varchar.a=random_dis_char.y order by foo_varchar.a;
+select * from foo_varchar left join random_dis_char on foo_varchar.a=random_dis_char.y;
a | y
------+-------
1 | 1
@@ -2675,7 +2679,7 @@ explain select * from bar_char left join random_dis_varchar on bar_char.p=random
Optimizer: Postgres query optimizer
(9 rows)
-select * from bar_char left join random_dis_varchar on bar_char.p=random_dis_varchar.x order by bar_char.p;
+select * from bar_char left join random_dis_varchar on bar_char.p=random_dis_varchar.x;
p | x
-------+------
1 | 1
@@ -2684,7 +2688,8 @@ select * from bar_char left join random_dis_varchar on bar_char.p=random_dis_var
(3 rows)
-- check motion is added when performing a NL Inner Join between relations when
--- the join condition columns belong to different opfamily
+-- the join condition columns belong to different opfamily and both are
+-- distribution keys
explain select * from foo_varchar inner join bar_char on foo_varchar.a=bar_char.p;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
@@ -2699,7 +2704,7 @@ explain select * from foo_varchar inner join bar_char on foo_varchar.a=bar_char.
Optimizer: Postgres query optimizer
(9 rows)
-select * from foo_varchar inner join bar_char on foo_varchar.a=bar_char.p order by foo_varchar.a;
+select * from foo_varchar inner join bar_char on foo_varchar.a=bar_char.p;
a | p
------+-------
1 | 1
@@ -2707,6 +2712,9 @@ select * from foo_varchar inner join bar_char on foo_varchar.a=bar_char.p order
3 | 3
(3 rows)
+-- There is a plan change (from redistribution to broadcast) because a NULL
+-- matching distribution is returned when there is opfamily mismatch between join
+-- columns.
explain select * from foo_varchar inner join random_dis_char on foo_varchar.a=random_dis_char.y;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
@@ -2723,7 +2731,7 @@ explain select * from foo_varchar inner join random_dis_char on foo_varchar.a=ra
Optimizer: Postgres query optimizer
(11 rows)
-select * from foo_varchar inner join random_dis_char on foo_varchar.a=random_dis_char.y order by foo_varchar.a;
+select * from foo_varchar inner join random_dis_char on foo_varchar.a=random_dis_char.y;
a | y
------+-------
1 | 1
@@ -2745,7 +2753,7 @@ explain select * from bar_char inner join random_dis_varchar on bar_char.p=rando
Optimizer: Postgres query optimizer
(9 rows)
-select * from bar_char inner join random_dis_varchar on bar_char.p=random_dis_varchar.x order by bar_char.p;
+select * from bar_char inner join random_dis_varchar on bar_char.p=random_dis_varchar.x;
p | x
-------+------
1 | 1
diff --git a/src/test/regress/expected/join_gp_optimizer.out b/src/test/regress/expected/join_gp_optimizer.out
index 868edd1053..3df0aa702a 100644
--- a/src/test/regress/expected/join_gp_optimizer.out
+++ b/src/test/regress/expected/join_gp_optimizer.out
@@ -2499,8 +2499,6 @@ explain SELECT 1 FROM inverse WHERE NOT (cidr <<= ANY(SELECT * FROM inverse));
Optimizer: Pivotal Optimizer (GPORCA)
(16 rows)
--- check motion is added when performing a NL Left Outer Join between relations
--- when the join condition columns belong to different opfamily
create table foo_varchar (a varchar(5)) distributed by (a);
create table bar_char (p char(5)) distributed by (p);
create table random_dis_varchar (x varchar(5)) distributed randomly;
@@ -2512,6 +2510,9 @@ insert into random_dis_char values ('1 '),('2 '),('3 ');
set optimizer_enable_hashjoin to off;
set enable_hashjoin to off;
set enable_nestloop to on;
+-- check motion is added when performing a NL Left Outer Join between relations
+-- when the join condition columns belong to different opfamily and both are
+-- distribution keys
explain select * from foo_varchar left join bar_char on foo_varchar.a=bar_char.p;
QUERY PLAN
---------------------------------------------------------------------------------------------------
@@ -2525,7 +2526,7 @@ explain select * from foo_varchar left join bar_char on foo_varchar.a=bar_char.p
Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)
-select * from foo_varchar left join bar_char on foo_varchar.a=bar_char.p order by foo_varchar.a;
+select * from foo_varchar left join bar_char on foo_varchar.a=bar_char.p;
a | p
------+-------
1 | 1
@@ -2533,6 +2534,9 @@ select * from foo_varchar left join bar_char on foo_varchar.a=bar_char.p order b
3 | 3
(3 rows)
+-- There is a plan change (from redistribution to broadcast) because a NULL
+-- matching distribution is returned when there is opfamily mismatch between join
+-- columns.
explain select * from foo_varchar left join random_dis_char on foo_varchar.a=random_dis_char.y;
QUERY PLAN
---------------------------------------------------------------------------------------------------
@@ -2546,7 +2550,7 @@ explain select * from foo_varchar left join random_dis_char on foo_varchar.a=ran
Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)
-select * from foo_varchar left join random_dis_char on foo_varchar.a=random_dis_char.y order by foo_varchar.a;
+select * from foo_varchar left join random_dis_char on foo_varchar.a=random_dis_char.y;
a | y
------+-------
1 | 1
@@ -2567,7 +2571,7 @@ explain select * from bar_char left join random_dis_varchar on bar_char.p=random
Optimizer: Pivotal Optimizer (GPORCA)
(8 rows)
-select * from bar_char left join random_dis_varchar on bar_char.p=random_dis_varchar.x order by bar_char.p;
+select * from bar_char left join random_dis_varchar on bar_char.p=random_dis_varchar.x;
p | x
-------+------
1 | 1
@@ -2576,7 +2580,8 @@ select * from bar_char left join random_dis_varchar on bar_char.p=random_dis_var
(3 rows)
-- check motion is added when performing a NL Inner Join between relations when
--- the join condition columns belong to different opfamily
+-- the join condition columns belong to different opfamily and both are
+-- distribution keys
explain select * from foo_varchar inner join bar_char on foo_varchar.a=bar_char.p;
QUERY PLAN
---------------------------------------------------------------------------------------------
@@ -2589,7 +2594,7 @@ explain select * from foo_varchar inner join bar_char on foo_varchar.a=bar_char.
Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)
-select * from foo_varchar inner join bar_char on foo_varchar.a=bar_char.p order by foo_varchar.a;
+select * from foo_varchar inner join bar_char on foo_varchar.a=bar_char.p;
a | p
------+-------
1 | 1
@@ -2597,6 +2602,9 @@ select * from foo_varchar inner join bar_char on foo_varchar.a=bar_char.p order
3 | 3
(3 rows)
+-- There is a plan change (from redistribution to broadcast) because a NULL
+-- matching distribution is returned when there is opfamily mismatch between join
+-- columns.
explain select * from foo_varchar inner join random_dis_char on foo_varchar.a=random_dis_char.y;
QUERY PLAN
---------------------------------------------------------------------------------------------
@@ -2609,7 +2617,7 @@ explain select * from foo_varchar inner join random_dis_char on foo_varchar.a=ra
Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)
-select * from foo_varchar inner join random_dis_char on foo_varchar.a=random_dis_char.y order by foo_varchar.a;
+select * from foo_varchar inner join random_dis_char on foo_varchar.a=random_dis_char.y;
a | y
------+-------
1 | 1
@@ -2629,7 +2637,7 @@ explain select * from bar_char inner join random_dis_varchar on bar_char.p=rando
Optimizer: Pivotal Optimizer (GPORCA)
(7 rows)
-select * from bar_char inner join random_dis_varchar on bar_char.p=random_dis_varchar.x order by bar_char.p;
+select * from bar_char inner join random_dis_varchar on bar_char.p=random_dis_varchar.x;
p | x
-------+------
1 | 1
diff --git a/src/test/regress/sql/join_gp.sql b/src/test/regress/sql/join_gp.sql
index acc3730c11..bf278e8f3f 100644
--- a/src/test/regress/sql/join_gp.sql
+++ b/src/test/regress/sql/join_gp.sql
@@ -973,8 +973,6 @@ INSERT INTO inverse values ('192.168.100.199');
explain SELECT 1 FROM inverse WHERE NOT (cidr <<= ANY(SELECT * FROM inverse));
SELECT 1 FROM inverse WHERE NOT (cidr <<= ANY(SELECT * FROM inverse));
--- check motion is added when performing a NL Left Outer Join between relations
--- when the join condition columns belong to different opfamily
create table foo_varchar (a varchar(5)) distributed by (a);
create table bar_char (p char(5)) distributed by (p);
create table random_dis_varchar (x varchar(5)) distributed randomly;
@@ -989,25 +987,35 @@ set optimizer_enable_hashjoin to off;
set enable_hashjoin to off;
set enable_nestloop to on;
+-- check motion is added when performing a NL Left Outer Join between relations
+-- when the join condition columns belong to different opfamily and both are
+-- distribution keys
explain select * from foo_varchar left join bar_char on foo_varchar.a=bar_char.p;
-select * from foo_varchar left join bar_char on foo_varchar.a=bar_char.p order by foo_varchar.a;
+select * from foo_varchar left join bar_char on foo_varchar.a=bar_char.p;
+-- There is a plan change (from redistribution to broadcast) because a NULL
+-- matching distribution is returned when there is opfamily mismatch between join
+-- columns.
explain select * from foo_varchar left join random_dis_char on foo_varchar.a=random_dis_char.y;
-select * from foo_varchar left join random_dis_char on foo_varchar.a=random_dis_char.y order by foo_varchar.a;
+select * from foo_varchar left join random_dis_char on foo_varchar.a=random_dis_char.y;
explain select * from bar_char left join random_dis_varchar on bar_char.p=random_dis_varchar.x;
-select * from bar_char left join random_dis_varchar on bar_char.p=random_dis_varchar.x order by bar_char.p;
+select * from bar_char left join random_dis_varchar on bar_char.p=random_dis_varchar.x;
-- check motion is added when performing a NL Inner Join between relations when
--- the join condition columns belong to different opfamily
+-- the join condition columns belong to different opfamily and both are
+-- distribution keys
explain select * from foo_varchar inner join bar_char on foo_varchar.a=bar_char.p;
-select * from foo_varchar inner join bar_char on foo_varchar.a=bar_char.p order by foo_varchar.a;
+select * from foo_varchar inner join bar_char on foo_varchar.a=bar_char.p;
+-- There is a plan change (from redistribution to broadcast) because a NULL
+-- matching distribution is returned when there is opfamily mismatch between join
+-- columns.
explain select * from foo_varchar inner join random_dis_char on foo_varchar.a=random_dis_char.y;
-select * from foo_varchar inner join random_dis_char on foo_varchar.a=random_dis_char.y order by foo_varchar.a;
+select * from foo_varchar inner join random_dis_char on foo_varchar.a=random_dis_char.y;
explain select * from bar_char inner join random_dis_varchar on bar_char.p=random_dis_varchar.x;
-select * from bar_char inner join random_dis_varchar on bar_char.p=random_dis_varchar.x order by bar_char.p;
+select * from bar_char inner join random_dis_varchar on bar_char.p=random_dis_varchar.x;
drop table foo_varchar;
drop table bar_char;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org