This is an automated email from the ASF dual-hosted git repository. avamingli pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit a8f37a3a6e343c990fd0824c3b1d78e52d13796b Author: Chris Hajas <[email protected]> AuthorDate: Thu Apr 27 09:22:37 2023 -0700 Fix another flaky aggregates ICW test (#15433) Sometimes these prepared statements would be replanned and log ORCA fallbacks, which are expected and ok. However, to make this more deterministic, reset the plan cache in the test. This uses the same approach as dee8ee04909f03e0aaa1e3def7c6fea4ab11778a and 8fa789e0cde3d6a6c158d6420fdd9c95603b2fd4 --- src/test/regress/expected/aggregates.out | 7 ++ src/test/regress/expected/aggregates_optimizer.out | 121 ++++++++++++++++++++- src/test/regress/sql/aggregates.sql | 7 ++ 3 files changed, 129 insertions(+), 6 deletions(-) diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index f8572ddb2a..b14f0c25ae 100644 --- a/src/test/regress/expected/aggregates.out +++ b/src/test/regress/expected/aggregates.out @@ -2651,6 +2651,8 @@ create aggregate my_sum(int4) sfunc = avg_transfn, finalfunc = sum_finalfn ); +-- reset the plan cache, sometimes it would re-plan these prepared statements and log ORCA fallbacks +discard plans; -- aggregate state should be shared as aggs are the same. select my_avg(one),my_avg(one) from (values(1),(3)) t(one); NOTICE: avg_transfn called with 1 @@ -2660,6 +2662,7 @@ NOTICE: avg_transfn called with 3 2 | 2 (1 row) +discard plans; -- aggregate state should be shared as transfn is the same for both aggs. select my_avg(one),my_sum(one) from (values(1),(3)) t(one); NOTICE: avg_transfn called with 1 @@ -2669,6 +2672,7 @@ NOTICE: avg_transfn called with 3 2 | 4 (1 row) +discard plans; -- same as previous one, but with DISTINCT, which requires sorting the input. select my_avg(distinct one),my_sum(distinct one) from (values(1),(3),(1)) t(one); NOTICE: avg_transfn called with 1 @@ -2678,6 +2682,7 @@ NOTICE: avg_transfn called with 3 2 | 4 (1 row) +discard plans; -- shouldn't share states due to the distinctness not matching. select my_avg(distinct one),my_sum(one) from (values(1),(3)) t(one); NOTICE: avg_transfn called with 1 @@ -2689,6 +2694,7 @@ NOTICE: avg_transfn called with 3 2 | 4 (1 row) +discard plans; -- shouldn't share states due to the filter clause not matching. select my_avg(one) filter (where one > 1),my_sum(one) from (values(1),(3)) t(one); NOTICE: avg_transfn called with 1 @@ -2699,6 +2705,7 @@ NOTICE: avg_transfn called with 3 3 | 4 (1 row) +discard plans; -- this should not share the state due to different input columns. select my_avg(one),my_sum(two) from (values(1,2),(3,4)) t(one,two); NOTICE: avg_transfn called with 1 diff --git a/src/test/regress/expected/aggregates_optimizer.out b/src/test/regress/expected/aggregates_optimizer.out index 5aac06ae99..ee9d88c873 100644 --- a/src/test/regress/expected/aggregates_optimizer.out +++ b/src/test/regress/expected/aggregates_optimizer.out @@ -2784,6 +2784,8 @@ create aggregate my_sum(int4) sfunc = avg_transfn, finalfunc = sum_finalfn ); +-- reset the plan cache, sometimes it would re-plan these prepared statements and log ORCA fallbacks +discard plans; -- aggregate state should be shared as aggs are the same. select my_avg(one),my_avg(one) from (values(1),(3)) t(one); INFO: GPORCA failed to produce a plan, falling back to planner @@ -2813,61 +2815,168 @@ DETAIL: GPDB Expression type: Query Parameter not supported in DXL 2 | 2 (1 row) +discard plans; -- aggregate state should be shared as transfn is the same for both aggs. select my_avg(one),my_sum(one) from (values(1),(3)) t(one); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL NOTICE: avg_transfn called with 1 -NOTICE: avg_transfn called with 1 -NOTICE: avg_transfn called with 3 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL NOTICE: avg_transfn called with 3 INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: GPDB Expression type: Query Parameter not supported in DXL my_avg | my_sum --------+-------- 2 | 4 (1 row) +discard plans; -- same as previous one, but with DISTINCT, which requires sorting the input. select my_avg(distinct one),my_sum(distinct one) from (values(1),(3),(1)) t(one); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL NOTICE: avg_transfn called with 1 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL NOTICE: avg_transfn called with 3 -NOTICE: avg_transfn called with 1 -NOTICE: avg_transfn called with 3 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL my_avg | my_sum --------+-------- 2 | 4 (1 row) +discard plans; -- shouldn't share states due to the distinctness not matching. select my_avg(distinct one),my_sum(one) from (values(1),(3)) t(one); +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL NOTICE: avg_transfn called with 1 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL NOTICE: avg_transfn called with 3 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL NOTICE: avg_transfn called with 1 NOTICE: avg_transfn called with 3 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL my_avg | my_sum --------+-------- 2 | 4 (1 row) +discard plans; -- shouldn't share states due to the filter clause not matching. select my_avg(one) filter (where one > 1),my_sum(one) from (values(1),(3)) t(one); INFO: GPORCA failed to produce a plan, falling back to planner DETAIL: Feature not supported: Aggregate functions with FILTER +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL NOTICE: avg_transfn called with 1 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL NOTICE: avg_transfn called with 3 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL NOTICE: avg_transfn called with 3 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL my_avg | my_sum --------+-------- 3 | 4 (1 row) +discard plans; -- this should not share the state due to different input columns. select my_avg(one),my_sum(two) from (values(1,2),(3,4)) t(one,two); -NOTICE: avg_transfn called with 1 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL NOTICE: avg_transfn called with 2 -NOTICE: avg_transfn called with 3 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +NOTICE: avg_transfn called with 1 NOTICE: avg_transfn called with 4 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +NOTICE: avg_transfn called with 3 +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL +INFO: GPORCA failed to produce a plan, falling back to planner +DETAIL: GPDB Expression type: Query Parameter not supported in DXL my_avg | my_sum --------+-------- 2 | 6 diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql index 4ae3bb5bfd..3462ccd532 100644 --- a/src/test/regress/sql/aggregates.sql +++ b/src/test/regress/sql/aggregates.sql @@ -1043,21 +1043,28 @@ create aggregate my_sum(int4) finalfunc = sum_finalfn ); +-- reset the plan cache, sometimes it would re-plan these prepared statements and log ORCA fallbacks +discard plans; -- aggregate state should be shared as aggs are the same. select my_avg(one),my_avg(one) from (values(1),(3)) t(one); +discard plans; -- aggregate state should be shared as transfn is the same for both aggs. select my_avg(one),my_sum(one) from (values(1),(3)) t(one); +discard plans; -- same as previous one, but with DISTINCT, which requires sorting the input. select my_avg(distinct one),my_sum(distinct one) from (values(1),(3),(1)) t(one); +discard plans; -- shouldn't share states due to the distinctness not matching. select my_avg(distinct one),my_sum(one) from (values(1),(3)) t(one); +discard plans; -- shouldn't share states due to the filter clause not matching. select my_avg(one) filter (where one > 1),my_sum(one) from (values(1),(3)) t(one); +discard plans; -- this should not share the state due to different input columns. select my_avg(one),my_sum(two) from (values(1,2),(3,4)) t(one,two); --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
