You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by ja...@apache.org on 2023/11/30 06:42:38 UTC
(doris) branch master updated: [test](Planner): add regression-test for eager-aggregate (#27732)
This is an automated email from the ASF dual-hosted git repository.
jakevin pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new f10b7bf7e73 [test](Planner): add regression-test for eager-aggregate (#27732)
f10b7bf7e73 is described below
commit f10b7bf7e73340d0c05a805385cd03e49502a46d
Author: jakevin <ja...@gmail.com>
AuthorDate: Thu Nov 30 14:42:26 2023 +0800
[test](Planner): add regression-test for eager-aggregate (#27732)
---
.../nereids_rules_p0/eager_aggregate/basic.out | 63 ++
.../push_down_count_through_join.out | 716 +++++++++++++++++++++
.../eager_aggregate/push_down_max_through_join.out | 366 +++++++++++
.../eager_aggregate/push_down_min_through_join.out | 366 +++++++++++
.../eager_aggregate/push_down_sum_through_join.out | 379 +++++++++++
.../nereids_rules_p0/eager_aggregate/basic.groovy | 130 ++++
.../push_down_count_through_join.groovy | 239 +++++++
.../push_down_max_through_join.groovy | 154 +++++
.../push_down_min_through_join.groovy | 154 +++++
.../push_down_sum_through_join.groovy | 154 +++++
10 files changed, 2721 insertions(+)
diff --git a/regression-test/data/nereids_rules_p0/eager_aggregate/basic.out b/regression-test/data/nereids_rules_p0/eager_aggregate/basic.out
new file mode 100644
index 00000000000..c85ab935d7d
--- /dev/null
+++ b/regression-test/data/nereids_rules_p0/eager_aggregate/basic.out
@@ -0,0 +1,63 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !1 --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((a.device_id = b.device_id)) otherCondition=() build RFs:RF0 device_id->[device_id]
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------filter((a.event_id = 'ad_click'))
+--------------------PhysicalOlapScan[com_dd_library] apply RFs: RF0
+--------------PhysicalDistribute
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------filter((cast(experiment_id as DOUBLE) = 37))
+----------------------PhysicalOlapScan[shunt_log_com_dd_library]
+
+-- !2 --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((a.device_id = b.device_id)) otherCondition=() build RFs:RF0 device_id->[device_id]
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[com_dd_library] apply RFs: RF0
+--------------PhysicalDistribute
+----------------hashAgg[LOCAL]
+------------------filter((cast(experiment_id as DOUBLE) = 73))
+--------------------PhysicalOlapScan[shunt_log_com_dd_library]
+
+-- !3 --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((a.device_id = b.device_id)) otherCondition=() build RFs:RF0 device_id->[device_id]
+--------------PhysicalOlapScan[com_dd_library] apply RFs: RF0
+--------------PhysicalDistribute
+----------------filter((cast(experiment_id as DOUBLE) = 73))
+------------------PhysicalOlapScan[shunt_log_com_dd_library]
+
+-- !4 --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((a.device_id = b.device_id)) otherCondition=()
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[com_dd_library]
+--------------PhysicalDistribute
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[shunt_log_com_dd_library]
+
diff --git a/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_count_through_join.out b/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_count_through_join.out
new file mode 100644
index 00000000000..bde67d5c165
--- /dev/null
+++ b/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_count_through_join.out
@@ -0,0 +1,716 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !groupby_pushdown_basic --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_left_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_right_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+----------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_full_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_left_semi_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_left_anti_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_ANTI_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_complex_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=((t1.name < t2.name))
+----------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_with_aggregate --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_subquery --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------filter((count_t.score > 10))
+--------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_outer_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_deep_subquery --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------filter((count_t.score > 10))
+--------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_having --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------filter((count(score) > 100))
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+------------------hashAgg[LOCAL]
+--------------------PhysicalOlapScan[count_t]
+------------------hashAgg[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_mixed_aggregates --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_multi_table_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.name = t3.name)) otherCondition=()
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------------hashAgg[LOCAL]
+------------------------PhysicalOlapScan[count_t]
+----------------------hashAgg[LOCAL]
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[count_t]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_with_order_by --
+PhysicalResultSink
+--PhysicalProject
+----PhysicalQuickSort[MERGE_SORT]
+------PhysicalDistribute
+--------PhysicalQuickSort[LOCAL_SORT]
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+--------------------hashAgg[LOCAL]
+----------------------PhysicalOlapScan[count_t]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_multiple_equal_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_equal_conditions_with_aggregate --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+----------------PhysicalOlapScan[count_t]
+----------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_equal_conditions_non_aggregate_selection --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+--------------hashAgg[LOCAL]
+----------------PhysicalOlapScan[count_t]
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_equal_conditions_non_aggregate_selection_with_aggregate --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+--------------hashAgg[LOCAL]
+----------------PhysicalOlapScan[count_t]
+--------------hashAgg[LOCAL]
+----------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_with_where_clause --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------filter((t1.score > 50))
+--------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_varied_aggregates --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_with_order_by_limit --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+--------------------hashAgg[LOCAL]
+----------------------PhysicalOlapScan[count_t]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_alias_multiple_equal_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1_alias.id = t2_alias.id) and (t1_alias.name = t2_alias.name)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_complex_join_condition --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.score = t2.score)) otherCondition=(( not (name = name)))
+----------------PhysicalOlapScan[count_t]
+----------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_function_processed_columns --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_nested_queries --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() build RFs:RF0 id->[id]
+----------------hashAgg[LOCAL]
+------------------filter((count_t.score > 20) and (t1.id < 100))
+--------------------PhysicalOlapScan[count_t] apply RFs: RF0
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------filter((count_t.id < 100))
+----------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_basic --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_left_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_right_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_full_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_left_semi_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_left_anti_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_ANTI_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_complex_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=((t1.name < t2.name))
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_with_aggregate --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_subquery --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------filter((count_t.score > 10))
+----------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_outer_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_deep_subquery --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------filter((count_t.score > 10))
+----------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_having --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------filter((count(*) > 100))
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+------------------hashAgg[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[count_t]
+------------------hashAgg[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_multi_table_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.name = t3.name)) otherCondition=()
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------------hashAgg[LOCAL]
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[count_t]
+----------------------hashAgg[LOCAL]
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[count_t]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_with_order_by --
+PhysicalResultSink
+--PhysicalProject
+----PhysicalQuickSort[MERGE_SORT]
+------PhysicalDistribute
+--------PhysicalQuickSort[LOCAL_SORT]
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[count_t]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_multiple_equal_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_equal_conditions_non_aggregate_selection --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_with_where_clause --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------filter((t1.score > 50))
+----------------------PhysicalOlapScan[count_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_varied_aggregates --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_with_order_by_limit --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[count_t]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_complex_join_condition --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.score = t2.score)) otherCondition=(( not (name = name)))
+----------------PhysicalOlapScan[count_t]
+----------------PhysicalOlapScan[count_t]
+
+-- !groupby_pushdown_nested_queries --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() build RFs:RF0 id->[id]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------filter((count_t.score > 20) and (t1.id < 100))
+----------------------PhysicalOlapScan[count_t] apply RFs: RF0
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------filter((count_t.id < 100))
+----------------------PhysicalOlapScan[count_t]
+
diff --git a/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_max_through_join.out b/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_max_through_join.out
new file mode 100644
index 00000000000..3bc9c52d47e
--- /dev/null
+++ b/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_max_through_join.out
@@ -0,0 +1,366 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !groupby_pushdown_basic --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[max_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_left_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[max_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_right_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+----------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_full_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[max_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_left_semi_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[max_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_left_anti_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_ANTI_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[max_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_complex_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=((t1.name < t2.name))
+----------------PhysicalOlapScan[max_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_with_aggregate --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[max_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_subquery --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() build RFs:RF0 id->[id]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t] apply RFs: RF0
+----------------hashAgg[LOCAL]
+------------------filter((max_t.score > 10))
+--------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_outer_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[max_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_deep_subquery --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() build RFs:RF0 id->[id]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t] apply RFs: RF0
+----------------hashAgg[LOCAL]
+------------------filter((max_t.score > 10))
+--------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_having --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------filter((max(score) > 100))
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+------------------hashAgg[LOCAL]
+--------------------PhysicalOlapScan[max_t]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_mixed_aggregates --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[max_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_multi_table_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.name = t3.name)) otherCondition=()
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------------hashAgg[LOCAL]
+------------------------PhysicalOlapScan[max_t]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[max_t]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_with_order_by --
+PhysicalResultSink
+--PhysicalProject
+----PhysicalQuickSort[MERGE_SORT]
+------PhysicalDistribute
+--------PhysicalQuickSort[LOCAL_SORT]
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+--------------------hashAgg[LOCAL]
+----------------------PhysicalOlapScan[max_t]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_multiple_equal_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[max_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_equal_conditions_with_aggregate --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+----------------PhysicalOlapScan[max_t]
+----------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_equal_conditions_non_aggregate_selection --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+--------------hashAgg[LOCAL]
+----------------PhysicalOlapScan[max_t]
+--------------PhysicalProject
+----------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_equal_conditions_non_aggregate_selection_with_aggregate --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+--------------hashAgg[LOCAL]
+----------------PhysicalOlapScan[max_t]
+--------------hashAgg[LOCAL]
+----------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_with_where_clause --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() build RFs:RF0 id->[id]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t] apply RFs: RF0
+----------------hashAgg[LOCAL]
+------------------filter((t1.score > 50))
+--------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_varied_aggregates --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[max_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_with_order_by_limit --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+--------------------hashAgg[LOCAL]
+----------------------PhysicalOlapScan[max_t]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_alias_multiple_equal_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1_alias.id = t2_alias.id) and (t1_alias.name = t2_alias.name)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[max_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_complex_join_condition --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.score = t2.score)) otherCondition=(( not (name = name)))
+----------------PhysicalOlapScan[max_t]
+----------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_function_processed_columns --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[max_t]
+
+-- !groupby_pushdown_nested_queries --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() build RFs:RF0 id->[id]
+----------------PhysicalProject
+------------------filter((max_t.id < 100))
+--------------------PhysicalOlapScan[max_t] apply RFs: RF0
+----------------hashAgg[LOCAL]
+------------------filter((max_t.score > 20) and (t1.id < 100))
+--------------------PhysicalOlapScan[max_t]
+
diff --git a/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_min_through_join.out b/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_min_through_join.out
new file mode 100644
index 00000000000..891466033e1
--- /dev/null
+++ b/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_min_through_join.out
@@ -0,0 +1,366 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !groupby_pushdown_basic --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[min_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_left_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[min_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_right_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+----------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_full_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[min_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_left_semi_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[min_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_left_anti_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_ANTI_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[min_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_complex_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=((t1.name < t2.name))
+----------------PhysicalOlapScan[min_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_with_aggregate --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[min_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_subquery --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() build RFs:RF0 id->[id]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t] apply RFs: RF0
+----------------hashAgg[LOCAL]
+------------------filter((min_t.score > 10))
+--------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_outer_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[min_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_deep_subquery --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() build RFs:RF0 id->[id]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t] apply RFs: RF0
+----------------hashAgg[LOCAL]
+------------------filter((min_t.score > 10))
+--------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_having --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------filter((min(score) > 100))
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+------------------hashAgg[LOCAL]
+--------------------PhysicalOlapScan[min_t]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_mixed_aggregates --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[min_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_multi_table_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.name = t3.name)) otherCondition=()
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------------hashAgg[LOCAL]
+------------------------PhysicalOlapScan[min_t]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[min_t]
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_with_order_by --
+PhysicalResultSink
+--PhysicalProject
+----PhysicalQuickSort[MERGE_SORT]
+------PhysicalDistribute
+--------PhysicalQuickSort[LOCAL_SORT]
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+--------------------hashAgg[LOCAL]
+----------------------PhysicalOlapScan[min_t]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_multiple_equal_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[min_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_equal_conditions_with_aggregate --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+----------------PhysicalOlapScan[min_t]
+----------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_equal_conditions_non_aggregate_selection --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+--------------hashAgg[LOCAL]
+----------------PhysicalOlapScan[min_t]
+--------------PhysicalProject
+----------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_equal_conditions_non_aggregate_selection_with_aggregate --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+--------------hashAgg[LOCAL]
+----------------PhysicalOlapScan[min_t]
+--------------hashAgg[LOCAL]
+----------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_with_where_clause --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() build RFs:RF0 id->[id]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t] apply RFs: RF0
+----------------hashAgg[LOCAL]
+------------------filter((t1.score > 50))
+--------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_varied_aggregates --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[min_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_with_order_by_limit --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+--------------------hashAgg[LOCAL]
+----------------------PhysicalOlapScan[min_t]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_alias_multiple_equal_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1_alias.id = t2_alias.id) and (t1_alias.name = t2_alias.name)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[min_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_complex_join_condition --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.score = t2.score)) otherCondition=(( not (name = name)))
+----------------PhysicalOlapScan[min_t]
+----------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_function_processed_columns --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[min_t]
+
+-- !groupby_pushdown_nested_queries --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() build RFs:RF0 id->[id]
+----------------PhysicalProject
+------------------filter((min_t.id < 100))
+--------------------PhysicalOlapScan[min_t] apply RFs: RF0
+----------------hashAgg[LOCAL]
+------------------filter((min_t.score > 20) and (t1.id < 100))
+--------------------PhysicalOlapScan[min_t]
+
diff --git a/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_sum_through_join.out b/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_sum_through_join.out
new file mode 100644
index 00000000000..6dca4f076c8
--- /dev/null
+++ b/regression-test/data/nereids_rules_p0/eager_aggregate/push_down_sum_through_join.out
@@ -0,0 +1,379 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !groupby_pushdown_basic --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[sum_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_left_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[sum_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_right_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[sum_t]
+----------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_full_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[FULL_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[sum_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_left_semi_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[sum_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_left_anti_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_ANTI_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[sum_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_complex_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=((t1.name < t2.name))
+----------------PhysicalOlapScan[sum_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_with_aggregate --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[sum_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_subquery --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------filter((sum_t.score > 10))
+--------------------PhysicalOlapScan[sum_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_outer_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[LEFT_OUTER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[sum_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_deep_subquery --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------filter((sum_t.score > 10))
+--------------------PhysicalOlapScan[sum_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_having --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------filter((sum(score) > 100))
+--------hashAgg[GLOBAL]
+----------PhysicalDistribute
+------------hashAgg[LOCAL]
+--------------PhysicalProject
+----------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+------------------hashAgg[LOCAL]
+--------------------PhysicalOlapScan[sum_t]
+------------------hashAgg[LOCAL]
+--------------------PhysicalProject
+----------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_mixed_aggregates --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[sum_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_multi_table_join --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[LOCAL]
+--------PhysicalProject
+----------hashJoin[INNER_JOIN] hashCondition=((t1.name = t3.name)) otherCondition=()
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------------hashAgg[LOCAL]
+------------------------PhysicalOlapScan[sum_t]
+----------------------hashAgg[LOCAL]
+------------------------PhysicalProject
+--------------------------PhysicalOlapScan[sum_t]
+------------hashAgg[GLOBAL]
+--------------PhysicalDistribute
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_with_order_by --
+PhysicalResultSink
+--PhysicalProject
+----PhysicalQuickSort[MERGE_SORT]
+------PhysicalDistribute
+--------PhysicalQuickSort[LOCAL_SORT]
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+--------------------hashAgg[LOCAL]
+----------------------PhysicalOlapScan[sum_t]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_multiple_equal_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[sum_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_equal_conditions_with_aggregate --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+----------------PhysicalOlapScan[sum_t]
+----------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_equal_conditions_non_aggregate_selection --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+--------------hashAgg[LOCAL]
+----------------PhysicalOlapScan[sum_t]
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_equal_conditions_non_aggregate_selection_with_aggregate --
+PhysicalResultSink
+--PhysicalDistribute
+----hashAgg[GLOBAL]
+------PhysicalDistribute
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.name = t2.name)) otherCondition=()
+--------------PhysicalOlapScan[sum_t]
+--------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_with_where_clause --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------filter((t1.score > 50))
+--------------------PhysicalOlapScan[sum_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_varied_aggregates --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalOlapScan[sum_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_with_order_by_limit --
+PhysicalResultSink
+--PhysicalTopN[MERGE_SORT]
+----PhysicalDistribute
+------PhysicalTopN[LOCAL_SORT]
+--------PhysicalProject
+----------hashAgg[GLOBAL]
+------------PhysicalDistribute
+--------------hashAgg[LOCAL]
+----------------PhysicalProject
+------------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+--------------------hashAgg[LOCAL]
+----------------------PhysicalOlapScan[sum_t]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalProject
+------------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_alias_multiple_equal_conditions --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1_alias.id = t2_alias.id) and (t1_alias.name = t2_alias.name)) otherCondition=()
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[sum_t]
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_complex_join_condition --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id) and (t1.score = t2.score)) otherCondition=(( not (name = name)))
+----------------PhysicalOlapScan[sum_t]
+----------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_function_processed_columns --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=()
+----------------PhysicalProject
+------------------PhysicalOlapScan[sum_t]
+----------------PhysicalProject
+------------------PhysicalOlapScan[sum_t]
+
+-- !groupby_pushdown_nested_queries --
+PhysicalResultSink
+--PhysicalDistribute
+----PhysicalProject
+------hashAgg[GLOBAL]
+--------PhysicalDistribute
+----------hashAgg[LOCAL]
+------------PhysicalProject
+--------------hashJoin[INNER_JOIN] hashCondition=((t1.id = t2.id)) otherCondition=() build RFs:RF0 id->[id]
+----------------hashAgg[LOCAL]
+------------------filter((sum_t.score > 20) and (t1.id < 100))
+--------------------PhysicalOlapScan[sum_t] apply RFs: RF0
+----------------hashAgg[LOCAL]
+------------------PhysicalProject
+--------------------filter((sum_t.id < 100))
+----------------------PhysicalOlapScan[sum_t]
+
diff --git a/regression-test/suites/nereids_rules_p0/eager_aggregate/basic.groovy b/regression-test/suites/nereids_rules_p0/eager_aggregate/basic.groovy
new file mode 100644
index 00000000000..c81509e95ad
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/eager_aggregate/basic.groovy
@@ -0,0 +1,130 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("eager_aggregate_basic") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+
+ sql "SET ENABLE_NEREIDS_RULES=push_down_min_max_through_join"
+ sql "SET ENABLE_NEREIDS_RULES=push_down_sum_through_join"
+ sql "SET ENABLE_NEREIDS_RULES=push_down_count_through_join"
+
+ sql """
+ DROP TABLE IF EXISTS shunt_log_com_dd_library;
+ """
+ sql """
+ DROP TABLE IF EXISTS com_dd_library;
+ """
+
+ sql"""
+ CREATE TABLE `shunt_log_com_dd_library` (
+ `device_id` varchar(255) NOT NULL,
+ `experiment_id` varchar(255) NOT NULL,
+ `group_id` varchar(255) NOT NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`device_id`)
+ DISTRIBUTED BY HASH(`device_id`) BUCKETS 4
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ sql"""
+ CREATE TABLE `com_dd_library` (
+ `event_id` varchar(255) NULL,
+ `device_id` varchar(255) NULL DEFAULT "",
+ `time_stamp` datetime NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`event_id`)
+ DISTRIBUTED BY HASH(`device_id`) BUCKETS 4
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ qt_1 """
+ explain shape plan
+ select
+ b.group_id,
+ COUNT(a.event_id)
+ from
+ com_dd_library a
+ join shunt_log_com_dd_library b on
+ a.device_id = b.device_id
+ where
+ a.event_id = "ad_click"
+ and b.experiment_id = 37
+ group by
+ b.group_id;
+ """
+
+ qt_2 """
+ explain shape plan
+ select
+ a.event_id,
+ b.experiment_id,
+ b.group_id,
+ COUNT(a.event_id)
+ from
+ com_dd_library a
+ join shunt_log_com_dd_library b on
+ a.device_id = b.device_id
+ where
+ b.experiment_id = 73
+ group by
+ b.group_id,
+ b.experiment_id,
+ a.event_id;
+ """
+
+ qt_3 """
+ explain shape plan
+ select
+ a.event_id,
+ b.experiment_id,
+ b.group_id,
+ COUNT(a.event_id),
+ date_format(a.time_stamp, '%Y-%m-%d') as dayF
+ from
+ com_dd_library a
+ join shunt_log_com_dd_library b on
+ a.device_id = b.device_id
+ where
+ b.experiment_id = 73
+ group by
+ b.group_id,
+ b.experiment_id,
+ a.event_id,
+ dayF;
+ """
+
+ qt_4 """
+ explain shape plan
+ select
+ a.event_id,
+ b.experiment_id,
+ b.group_id,
+ COUNT(a.event_id)
+ from
+ com_dd_library a
+ join shunt_log_com_dd_library b on
+ a.device_id = b.device_id
+ group by
+ b.group_id,
+ b.experiment_id,
+ a.event_id;
+ """
+}
diff --git a/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_count_through_join.groovy b/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_count_through_join.groovy
new file mode 100644
index 00000000000..8ffb44d3682
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_count_through_join.groovy
@@ -0,0 +1,239 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("push_down_count_through_join") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+
+ sql """
+ DROP TABLE IF EXISTS count_t;
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS count_t(
+ `id` int(32),
+ `score` int(64) NULL,
+ `name` varchar(64) NULL
+ ) ENGINE = OLAP
+ DISTRIBUTED BY HASH(id) BUCKETS 4
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql "insert into count_t values (1, 1, 'a')"
+ sql "insert into count_t values (2, null, 'a')"
+ sql "insert into count_t values (3, 1, null)"
+ sql "insert into count_t values (4, 2, 'b')"
+ sql "insert into count_t values (5, null, 'b')"
+ sql "insert into count_t values (6, 2, null)"
+ sql "insert into count_t values (7, 3, 'c')"
+ sql "insert into count_t values (8, null, 'c')"
+ sql "insert into count_t values (9, 3, null)"
+ sql "insert into count_t values (10, null, null)"
+
+ sql "SET ENABLE_NEREIDS_RULES=push_down_count_through_join"
+
+ qt_groupby_pushdown_basic """
+ explain shape plan select count(t1.score) from count_t t1, count_t t2 where t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_join """
+ explain shape plan select count(t1.score) from count_t t1 left join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_right_join """
+ explain shape plan select count(t1.score) from count_t t1 right join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_full_join """
+ explain shape plan select count(t1.score) from count_t t1 full join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_semi_join """
+ explain shape plan select count(t1.score) from count_t t1 inner join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_anti_join """
+ explain shape plan select count(t1.score) from count_t t1 left anti join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_complex_conditions """
+ explain shape plan select count(t1.score) from count_t t1 join count_t t2 on t1.id = t2.id and t1.name < t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_aggregate """
+ explain shape plan select count(t1.score), avg(t1.score) from count_t t1 join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_subquery """
+ explain shape plan select count(t1.score) from (select * from count_t where score > 10) t1 join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_outer_join """
+ explain shape plan select count(t1.score) from count_t t1 left join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_deep_subquery """
+ explain shape plan select count(t1.score) from (select * from (select * from count_t) count_t where score > 10) t1 join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_having """
+ explain shape plan select count(t1.score) from count_t t1, count_t t2 where t1.id = t2.id group by t1.name having count(t1.score) > 100;
+ """
+
+ qt_groupby_pushdown_mixed_aggregates """
+ explain shape plan select count(t1.score), count(*), max(t1.score) from count_t t1 join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_multi_table_join """
+ explain shape plan select count(t1.score) from count_t t1 join count_t t2 on t1.id = t2.id join count_t t3 on t1.name = t3.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_order_by """
+ explain shape plan select count(t1.score) from count_t t1, count_t t2 where t1.id = t2.id group by t1.name order by t1.name;
+ """
+
+ qt_groupby_pushdown_multiple_equal_conditions """
+ explain shape plan select count(t1.score) from count_t t1, count_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_equal_conditions_with_aggregate """
+ explain shape plan select max(t1.score), count(t2.score) from count_t t1 join count_t t2 on t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_equal_conditions_non_aggregate_selection """
+ explain shape plan select t1.name, count(t1.score) from count_t t1, count_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_equal_conditions_non_aggregate_selection_with_aggregate """
+ explain shape plan select t1.name, count(t1.score), count(t2.score) from count_t t1, count_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_where_clause """
+ explain shape plan select count(t1.score) from count_t t1, count_t t2 where t1.id = t2.id and t1.score > 50 group by t1.name;
+ """
+
+ qt_groupby_pushdown_varied_aggregates """
+ explain shape plan select count(t1.score), avg(t1.id), count(t2.name) from count_t t1 join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_order_by_limit """
+ explain shape plan select count(t1.score) from count_t t1, count_t t2 where t1.id = t2.id group by t1.name order by count(t1.score) limit 10;
+ """
+
+ qt_groupby_pushdown_alias_multiple_equal_conditions """
+ explain shape plan select count(t1_alias.score) from count_t t1_alias join count_t t2_alias on t1_alias.id = t2_alias.id and t1_alias.name = t2_alias.name group by t1_alias.name;
+ """
+
+ qt_groupby_pushdown_complex_join_condition """
+ explain shape plan select count(t1.score) from count_t t1 join count_t t2 on t1.id = t2.id and t1.score = t2.score and t1.name <> t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_function_processed_columns """
+ explain shape plan select count(LENGTH(t1.name)) from count_t t1, count_t t2 where t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_nested_queries """
+ explain shape plan select count(t1.score) from (select * from count_t where score > 20) t1 join (select * from count_t where id < 100) t2 on t1.id = t2.id group by t1.name;
+ """
+
+ /* COUNT(*) */
+ qt_groupby_pushdown_basic """
+ explain shape plan select count(*) from count_t t1, count_t t2 where t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_join """
+ explain shape plan select count(*) from count_t t1 left join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_right_join """
+ explain shape plan select count(*) from count_t t1 right join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_full_join """
+ explain shape plan select count(*) from count_t t1 full join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_semi_join """
+ explain shape plan select count(*) from count_t t1 inner join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_anti_join """
+ explain shape plan select count(*) from count_t t1 left anti join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_complex_conditions """
+ explain shape plan select count(*) from count_t t1 join count_t t2 on t1.id = t2.id and t1.name < t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_aggregate """
+ explain shape plan select count(*) from count_t t1 join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_subquery """
+ explain shape plan select count(*) from (select * from count_t where score > 10) t1 join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_outer_join """
+ explain shape plan select count(*) from count_t t1 left join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_deep_subquery """
+ explain shape plan select count(*) from (select * from (select * from count_t) count_t where score > 10) t1 join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_having """
+ explain shape plan select count(*) from count_t t1, count_t t2 where t1.id = t2.id group by t1.name having count(*) > 100;
+ """
+
+ qt_groupby_pushdown_multi_table_join """
+ explain shape plan select count(*) from count_t t1 join count_t t2 on t1.id = t2.id join count_t t3 on t1.name = t3.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_order_by """
+ explain shape plan select count(*) from count_t t1, count_t t2 where t1.id = t2.id group by t1.name order by t1.name;
+ """
+
+ qt_groupby_pushdown_multiple_equal_conditions """
+ explain shape plan select count(*) from count_t t1, count_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_equal_conditions_non_aggregate_selection """
+ explain shape plan select t1.name, count(*) from count_t t1, count_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_where_clause """
+ explain shape plan select count(*) from count_t t1, count_t t2 where t1.id = t2.id and t1.score > 50 group by t1.name;
+ """
+
+ qt_groupby_pushdown_varied_aggregates """
+ explain shape plan select count(*), avg(t1.id), count(t2.name) from count_t t1 join count_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_order_by_limit """
+ explain shape plan select count(*) from count_t t1, count_t t2 where t1.id = t2.id group by t1.name order by count(*) limit 10;
+ """
+
+ qt_groupby_pushdown_complex_join_condition """
+ explain shape plan select count(*) from count_t t1 join count_t t2 on t1.id = t2.id and t1.score = t2.score and t1.name <> t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_nested_queries """
+ explain shape plan select count(*) from (select * from count_t where score > 20) t1 join (select * from count_t where id < 100) t2 on t1.id = t2.id group by t1.name;
+ """
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_max_through_join.groovy b/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_max_through_join.groovy
new file mode 100644
index 00000000000..91079ba9de3
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_max_through_join.groovy
@@ -0,0 +1,154 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("push_down_max_through_join") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+
+ sql """
+ DROP TABLE IF EXISTS max_t;
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS max_t(
+ `id` int(32),
+ `score` int(64) NULL,
+ `name` varchar(64) NULL
+ ) ENGINE = OLAP
+ DISTRIBUTED BY HASH(id) BUCKETS 4
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql "insert into max_t values (1, 1, 'a')"
+ sql "insert into max_t values (2, null, 'a')"
+ sql "insert into max_t values (3, 1, null)"
+ sql "insert into max_t values (4, 2, 'b')"
+ sql "insert into max_t values (5, null, 'b')"
+ sql "insert into max_t values (6, 2, null)"
+ sql "insert into max_t values (7, 3, 'c')"
+ sql "insert into max_t values (8, null, 'c')"
+ sql "insert into max_t values (9, 3, null)"
+ sql "insert into max_t values (10, null, null)"
+
+ sql "SET ENABLE_NEREIDS_RULES=push_down_min_max_through_join"
+
+ qt_groupby_pushdown_basic """
+ explain shape plan select max(t1.score) from max_t t1, max_t t2 where t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_join """
+ explain shape plan select max(t1.score) from max_t t1 left join max_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_right_join """
+ explain shape plan select max(t1.score) from max_t t1 right join max_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_full_join """
+ explain shape plan select max(t1.score) from max_t t1 full join max_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_semi_join """
+ explain shape plan select max(t1.score) from max_t t1 inner join max_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_anti_join """
+ explain shape plan select max(t1.score) from max_t t1 left anti join max_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_complex_conditions """
+ explain shape plan select max(t1.score) from max_t t1 join max_t t2 on t1.id = t2.id and t1.name < t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_aggregate """
+ explain shape plan select max(t1.score), avg(t1.score) from max_t t1 join max_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_subquery """
+ explain shape plan select max(t1.score) from (select * from max_t where score > 10) t1 join max_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_outer_join """
+ explain shape plan select max(t1.score) from max_t t1 left join max_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_deep_subquery """
+ explain shape plan select max(t1.score) from (select * from (select * from max_t) max_t where score > 10) t1 join max_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_having """
+ explain shape plan select max(t1.score) from max_t t1, max_t t2 where t1.id = t2.id group by t1.name having max(t1.score) > 100;
+ """
+
+ qt_groupby_pushdown_mixed_aggregates """
+ explain shape plan select max(t1.score), count(*), sum(t1.score) from max_t t1 join max_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_multi_table_join """
+ explain shape plan select max(t1.score) from max_t t1 join max_t t2 on t1.id = t2.id join max_t t3 on t1.name = t3.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_order_by """
+ explain shape plan select max(t1.score) from max_t t1, max_t t2 where t1.id = t2.id group by t1.name order by t1.name;
+ """
+
+ qt_groupby_pushdown_multiple_equal_conditions """
+ explain shape plan select max(t1.score) from max_t t1, max_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_equal_conditions_with_aggregate """
+ explain shape plan select sum(t1.score), max(t2.score) from max_t t1 join max_t t2 on t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_equal_conditions_non_aggregate_selection """
+ explain shape plan select t1.name, max(t1.score) from max_t t1, max_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_equal_conditions_non_aggregate_selection_with_aggregate """
+ explain shape plan select t1.name, max(t1.score), max(t2.score) from max_t t1, max_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_where_clause """
+ explain shape plan select max(t1.score) from max_t t1, max_t t2 where t1.id = t2.id and t1.score > 50 group by t1.name;
+ """
+
+ qt_groupby_pushdown_varied_aggregates """
+ explain shape plan select max(t1.score), avg(t1.id), count(t2.name) from max_t t1 join max_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_order_by_limit """
+ explain shape plan select max(t1.score) from max_t t1, max_t t2 where t1.id = t2.id group by t1.name order by max(t1.score) limit 10;
+ """
+
+ qt_groupby_pushdown_alias_multiple_equal_conditions """
+ explain shape plan select max(t1_alias.score) from max_t t1_alias join max_t t2_alias on t1_alias.id = t2_alias.id and t1_alias.name = t2_alias.name group by t1_alias.name;
+ """
+
+ qt_groupby_pushdown_complex_join_condition """
+ explain shape plan select max(t1.score) from max_t t1 join max_t t2 on t1.id = t2.id and t1.score = t2.score and t1.name <> t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_function_processed_columns """
+ explain shape plan select max(LENGTH(t1.name)) from max_t t1, max_t t2 where t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_nested_queries """
+ explain shape plan select max(t1.score) from (select * from max_t where score > 20) t1 join (select * from max_t where id < 100) t2 on t1.id = t2.id group by t1.name;
+ """
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_min_through_join.groovy b/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_min_through_join.groovy
new file mode 100644
index 00000000000..a9c8480415c
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_min_through_join.groovy
@@ -0,0 +1,154 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("push_down_min_through_join") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+
+ sql """
+ DROP TABLE IF EXISTS min_t;
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS min_t(
+ `id` int(32),
+ `score` int(64) NULL,
+ `name` varchar(64) NULL
+ ) ENGINE = OLAP
+ DISTRIBUTED BY HASH(id) BUCKETS 4
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql "insert into min_t values (1, 1, 'a')"
+ sql "insert into min_t values (2, null, 'a')"
+ sql "insert into min_t values (3, 1, null)"
+ sql "insert into min_t values (4, 2, 'b')"
+ sql "insert into min_t values (5, null, 'b')"
+ sql "insert into min_t values (6, 2, null)"
+ sql "insert into min_t values (7, 3, 'c')"
+ sql "insert into min_t values (8, null, 'c')"
+ sql "insert into min_t values (9, 3, null)"
+ sql "insert into min_t values (10, null, null)"
+
+ sql "SET ENABLE_NEREIDS_RULES=push_down_min_max_through_join"
+
+ qt_groupby_pushdown_basic """
+ explain shape plan select min(t1.score) from min_t t1, min_t t2 where t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_join """
+ explain shape plan select min(t1.score) from min_t t1 left join min_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_right_join """
+ explain shape plan select min(t1.score) from min_t t1 right join min_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_full_join """
+ explain shape plan select min(t1.score) from min_t t1 full join min_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_semi_join """
+ explain shape plan select min(t1.score) from min_t t1 inner join min_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_anti_join """
+ explain shape plan select min(t1.score) from min_t t1 left anti join min_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_complex_conditions """
+ explain shape plan select min(t1.score) from min_t t1 join min_t t2 on t1.id = t2.id and t1.name < t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_aggregate """
+ explain shape plan select min(t1.score), avg(t1.score) from min_t t1 join min_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_subquery """
+ explain shape plan select min(t1.score) from (select * from min_t where score > 10) t1 join min_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_outer_join """
+ explain shape plan select min(t1.score) from min_t t1 left join min_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_deep_subquery """
+ explain shape plan select min(t1.score) from (select * from (select * from min_t) min_t where score > 10) t1 join min_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_having """
+ explain shape plan select min(t1.score) from min_t t1, min_t t2 where t1.id = t2.id group by t1.name having min(t1.score) > 100;
+ """
+
+ qt_groupby_pushdown_mixed_aggregates """
+ explain shape plan select min(t1.score), count(*), sum(t1.score) from min_t t1 join min_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_multi_table_join """
+ explain shape plan select min(t1.score) from min_t t1 join min_t t2 on t1.id = t2.id join min_t t3 on t1.name = t3.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_order_by """
+ explain shape plan select min(t1.score) from min_t t1, min_t t2 where t1.id = t2.id group by t1.name order by t1.name;
+ """
+
+ qt_groupby_pushdown_multiple_equal_conditions """
+ explain shape plan select min(t1.score) from min_t t1, min_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_equal_conditions_with_aggregate """
+ explain shape plan select sum(t1.score), min(t2.score) from min_t t1 join min_t t2 on t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_equal_conditions_non_aggregate_selection """
+ explain shape plan select t1.name, min(t1.score) from min_t t1, min_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_equal_conditions_non_aggregate_selection_with_aggregate """
+ explain shape plan select t1.name, min(t1.score), min(t2.score) from min_t t1, min_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_where_clause """
+ explain shape plan select min(t1.score) from min_t t1, min_t t2 where t1.id = t2.id and t1.score > 50 group by t1.name;
+ """
+
+ qt_groupby_pushdown_varied_aggregates """
+ explain shape plan select min(t1.score), avg(t1.id), count(t2.name) from min_t t1 join min_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_order_by_limit """
+ explain shape plan select min(t1.score) from min_t t1, min_t t2 where t1.id = t2.id group by t1.name order by min(t1.score) limit 10;
+ """
+
+ qt_groupby_pushdown_alias_multiple_equal_conditions """
+ explain shape plan select min(t1_alias.score) from min_t t1_alias join min_t t2_alias on t1_alias.id = t2_alias.id and t1_alias.name = t2_alias.name group by t1_alias.name;
+ """
+
+ qt_groupby_pushdown_complex_join_condition """
+ explain shape plan select min(t1.score) from min_t t1 join min_t t2 on t1.id = t2.id and t1.score = t2.score and t1.name <> t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_function_processed_columns """
+ explain shape plan select min(LENGTH(t1.name)) from min_t t1, min_t t2 where t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_nested_queries """
+ explain shape plan select min(t1.score) from (select * from min_t where score > 20) t1 join (select * from min_t where id < 100) t2 on t1.id = t2.id group by t1.name;
+ """
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_sum_through_join.groovy b/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_sum_through_join.groovy
new file mode 100644
index 00000000000..4dc9917c56a
--- /dev/null
+++ b/regression-test/suites/nereids_rules_p0/eager_aggregate/push_down_sum_through_join.groovy
@@ -0,0 +1,154 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("push_down_sum_through_join") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+
+ sql """
+ DROP TABLE IF EXISTS sum_t;
+ """
+
+ sql """
+ CREATE TABLE IF NOT EXISTS sum_t(
+ `id` int(32),
+ `score` int(64) NULL,
+ `name` varchar(64) NULL
+ ) ENGINE = OLAP
+ DISTRIBUTED BY HASH(id) BUCKETS 4
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+
+ sql "insert into sum_t values (1, 1, 'a')"
+ sql "insert into sum_t values (2, null, 'a')"
+ sql "insert into sum_t values (3, 1, null)"
+ sql "insert into sum_t values (4, 2, 'b')"
+ sql "insert into sum_t values (5, null, 'b')"
+ sql "insert into sum_t values (6, 2, null)"
+ sql "insert into sum_t values (7, 3, 'c')"
+ sql "insert into sum_t values (8, null, 'c')"
+ sql "insert into sum_t values (9, 3, null)"
+ sql "insert into sum_t values (10, null, null)"
+
+ sql "SET ENABLE_NEREIDS_RULES=push_down_sum_through_join"
+
+ qt_groupby_pushdown_basic """
+ explain shape plan select sum(t1.score) from sum_t t1, sum_t t2 where t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_join """
+ explain shape plan select sum(t1.score) from sum_t t1 left join sum_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_right_join """
+ explain shape plan select sum(t1.score) from sum_t t1 right join sum_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_full_join """
+ explain shape plan select sum(t1.score) from sum_t t1 full join sum_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_semi_join """
+ explain shape plan select sum(t1.score) from sum_t t1 inner join sum_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_left_anti_join """
+ explain shape plan select sum(t1.score) from sum_t t1 left anti join sum_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_complex_conditions """
+ explain shape plan select sum(t1.score) from sum_t t1 join sum_t t2 on t1.id = t2.id and t1.name < t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_aggregate """
+ explain shape plan select sum(t1.score), avg(t1.score) from sum_t t1 join sum_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_subquery """
+ explain shape plan select sum(t1.score) from (select * from sum_t where score > 10) t1 join sum_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_outer_join """
+ explain shape plan select sum(t1.score) from sum_t t1 left join sum_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_deep_subquery """
+ explain shape plan select sum(t1.score) from (select * from (select * from sum_t) sum_t where score > 10) t1 join sum_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_having """
+ explain shape plan select sum(t1.score) from sum_t t1, sum_t t2 where t1.id = t2.id group by t1.name having sum(t1.score) > 100;
+ """
+
+ qt_groupby_pushdown_mixed_aggregates """
+ explain shape plan select sum(t1.score), count(*), max(t1.score) from sum_t t1 join sum_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_multi_table_join """
+ explain shape plan select sum(t1.score) from sum_t t1 join sum_t t2 on t1.id = t2.id join sum_t t3 on t1.name = t3.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_order_by """
+ explain shape plan select sum(t1.score) from sum_t t1, sum_t t2 where t1.id = t2.id group by t1.name order by t1.name;
+ """
+
+ qt_groupby_pushdown_multiple_equal_conditions """
+ explain shape plan select sum(t1.score) from sum_t t1, sum_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_equal_conditions_with_aggregate """
+ explain shape plan select max(t1.score), sum(t2.score) from sum_t t1 join sum_t t2 on t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_equal_conditions_non_aggregate_selection """
+ explain shape plan select t1.name, sum(t1.score) from sum_t t1, sum_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_equal_conditions_non_aggregate_selection_with_aggregate """
+ explain shape plan select t1.name, sum(t1.score), sum(t2.score) from sum_t t1, sum_t t2 where t1.id = t2.id and t1.name = t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_where_clause """
+ explain shape plan select sum(t1.score) from sum_t t1, sum_t t2 where t1.id = t2.id and t1.score > 50 group by t1.name;
+ """
+
+ qt_groupby_pushdown_varied_aggregates """
+ explain shape plan select sum(t1.score), avg(t1.id), count(t2.name) from sum_t t1 join sum_t t2 on t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_with_order_by_limit """
+ explain shape plan select sum(t1.score) from sum_t t1, sum_t t2 where t1.id = t2.id group by t1.name order by sum(t1.score) limit 10;
+ """
+
+ qt_groupby_pushdown_alias_multiple_equal_conditions """
+ explain shape plan select sum(t1_alias.score) from sum_t t1_alias join sum_t t2_alias on t1_alias.id = t2_alias.id and t1_alias.name = t2_alias.name group by t1_alias.name;
+ """
+
+ qt_groupby_pushdown_complex_join_condition """
+ explain shape plan select sum(t1.score) from sum_t t1 join sum_t t2 on t1.id = t2.id and t1.score = t2.score and t1.name <> t2.name group by t1.name;
+ """
+
+ qt_groupby_pushdown_function_processed_columns """
+ explain shape plan select sum(LENGTH(t1.name)) from sum_t t1, sum_t t2 where t1.id = t2.id group by t1.name;
+ """
+
+ qt_groupby_pushdown_nested_queries """
+ explain shape plan select sum(t1.score) from (select * from sum_t where score > 20) t1 join (select * from sum_t where id < 100) t2 on t1.id = t2.id group by t1.name;
+ """
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org