This is an automated email from the ASF dual-hosted git repository.
zclllyybb 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 adbff78c0a7 Revert "[Fix](variance) Fix sample variance/stddev NaN res
for single value" (#64099)
adbff78c0a7 is described below
commit adbff78c0a74a70b81ebf55709278b50565702df
Author: linrrarity <[email protected]>
AuthorDate: Fri Jun 5 11:07:38 2026 +0800
Revert "[Fix](variance) Fix sample variance/stddev NaN res for single
value" (#64099)
Reverts apache/doris#63605
---
be/src/exprs/aggregate/aggregate_function_stddev.h | 28 +-
.../test_select_stddev_variance_window.out | 933 +++++++++++----------
.../data/function_p0/test_agg_foreach.out | 3 +-
.../data/function_p0/test_agg_foreach_notnull.out | 3 +-
.../data/nereids_function_p0/agg_function/agg.out | 672 +++++++--------
.../normalize_window_nullable_agg_test.out | 5 +-
.../nereids_syntax_p0/mv/aggregate/agg_sync_mv.out | 157 ++--
.../data/query_p0/aggregate/agg_nullable_2.out | 19 +-
.../data/query_p0/aggregate/aggregate.out | 4 +-
.../test_select_stddev_variance_window.out | 169 ++--
.../suites/query_p0/aggregate/aggregate.groovy | 1 -
11 files changed, 1006 insertions(+), 988 deletions(-)
diff --git a/be/src/exprs/aggregate/aggregate_function_stddev.h
b/be/src/exprs/aggregate/aggregate_function_stddev.h
index 2d76e7f354a..a870b4695ff 100644
--- a/be/src/exprs/aggregate/aggregate_function_stddev.h
+++ b/be/src/exprs/aggregate/aggregate_function_stddev.h
@@ -21,11 +21,13 @@
#include <cmath>
#include <cstddef>
#include <cstdint>
-#include <limits>
#include <memory>
+#include <type_traits>
#include "core/assert_cast.h"
#include "core/column/column.h"
+#include "core/column/column_nullable.h"
+#include "core/data_type/data_type_decimal.h"
#include "core/data_type/data_type_number.h"
#include "core/types.h"
#include "exprs/aggregate/aggregate_function.h"
@@ -35,6 +37,8 @@ class Arena;
class BufferReadable;
class BufferWritable;
template <PrimitiveType T>
+class ColumnDecimal;
+template <PrimitiveType T>
class ColumnVector;
template <PrimitiveType T, bool is_stddev>
@@ -67,7 +71,7 @@ struct BaseData {
// In MySQL, this will directly result in an error due to
exceeding the double range.
// For performance reasons, we are uniformly changing it to nan
if (std::isinf(val)) {
- return std::numeric_limits<double>::quiet_NaN();
+ return std::nan("");
}
return val;
};
@@ -121,9 +125,14 @@ struct BaseData {
template <PrimitiveType T, typename Name, bool is_stddev>
struct PopData : BaseData<T, is_stddev>, Name {
+ using ColVecResult = std::conditional_t<is_decimal(T), ColumnDecimal128V2,
ColumnFloat64>;
void insert_result_into(IColumn& to) const {
- auto& col = assert_cast<ColumnFloat64&>(to);
- col.get_data().push_back(this->get_pop_result());
+ auto& col = assert_cast<ColVecResult&>(to);
+ if constexpr (is_decimal(T)) {
+ col.get_data().push_back(this->get_pop_result().value());
+ } else {
+ col.get_data().push_back(this->get_pop_result());
+ }
}
static DataTypePtr get_return_type() { return
std::make_shared<DataTypeFloat64>(); }
@@ -135,12 +144,17 @@ struct PopData : BaseData<T, is_stddev>, Name {
template <PrimitiveType T, typename Name, bool is_stddev>
struct SampData : BaseData<T, is_stddev>, Name {
+ using ColVecResult = std::conditional_t<is_decimal(T), ColumnDecimal128V2,
ColumnFloat64>;
void insert_result_into(IColumn& to) const {
- auto& col = assert_cast<ColumnFloat64&>(to);
+ auto& col = assert_cast<ColVecResult&>(to);
if (this->count == 1 || this->count == 0) {
- col.get_data().push_back(std::numeric_limits<double>::quiet_NaN());
+ col.insert_default();
} else {
- col.get_data().push_back(this->get_samp_result());
+ if constexpr (is_decimal(T)) {
+ col.get_data().push_back(this->get_samp_result().value());
+ } else {
+ col.get_data().push_back(this->get_samp_result());
+ }
}
}
diff --git
a/regression-test/data/correctness_p0/test_select_stddev_variance_window.out
b/regression-test/data/correctness_p0/test_select_stddev_variance_window.out
index f1563106a8a..0c6ee4f84bc 100644
--- a/regression-test/data/correctness_p0/test_select_stddev_variance_window.out
+++ b/regression-test/data/correctness_p0/test_select_stddev_variance_window.out
@@ -1,612 +1,613 @@
-- This file is automatically generated. You should know what you did if you
want to edit this
-- !select_default --
-1 10887.58182143158
-2 19879.49278740217
-3 19879.49278740217
-4 19879.49278740217
-5 10887.58182143158
-6 10887.58182143158
-7 19879.49278740217
-8 10887.58182143158
-9 11616.72691160849
+1 10887.581821431579
+2 19879.492787402174
+3 19879.492787402174
+4 19879.492787402174
+5 10887.581821431579
+6 10887.581821431579
+7 19879.492787402174
+8 10887.581821431579
+9 11616.726911608488
10 21153.67606592388
-11 12502.18519139754
-12 22705.95334561694
-13 24652.59290541261
-14 23184.69621862663
+11 12502.185191397542
+12 22705.953345616945
+13 24652.592905412606
+14 23184.696218626632
15 751.5664890746526
-- !select_default --
1 \N
2 \N
-3 0
+3 0.0
4 1.5
-5 0
-6 2
-7 2.054804667656326
-8 14509.83124183967
-9 14935.24752612646
-10 16384.60694947819
-11 14933.95093960961
+5 0.0
+6 2.0
+7 2.0548046676563256
+8 14509.831241839674
+9 14935.247526126464
+10 16384.606949478188
+11 14933.950939609607
12 16385.07833365468
-13 26770.60140444281
-14 26770.60140444281
+13 26770.601404442812
+14 26770.601404442812
15 817.8872511266792
-- !select_default --
-1 2
+1 2.0
2 1.5
-3 2.054804667656326
-4 15049.64523460603
-5 14509.83124183967
-6 13596.52212883868
-7 13902.50012048193
-8 12502.83573594407
-9 12502.83573594407
-10 20746.59631264849
-11 12502.18519139754
-12 24751.7344200361
-13 24652.59290541261
-14 23184.69621862663
+3 2.0548046676563256
+4 15049.645234606029
+5 14509.831241839674
+6 13596.522128838684
+7 13902.500120481925
+8 12502.835735944067
+9 12502.835735944067
+10 20746.596312648493
+11 12502.185191397542
+12 24751.734420036104
+13 24652.592905412606
+14 23184.696218626632
15 751.5664890746526
-- !select_default --
-1 0
-2 0
-3 0
-4 0
-5 0
-6 0
-7 0
-8 0
-9 0
-10 0
-11 0
-12 0
-13 0
-14 0
-15 0
-
--- !select_default --
-1 10887.58182143158
-2 19879.49278740217
+1 0.0
+2 0.0
+3 0.0
+4 0.0
+5 0.0
+6 0.0
+7 0.0
+8 0.0
+9 0.0
+10 0.0
+11 0.0
+12 0.0
+13 0.0
+14 0.0
+15 0.0
+
+-- !select_default --
+1 10887.581821431579
+2 19879.492787402174
3 21153.67606592388
-4 22705.95334561694
-5 11616.72691160849
-6 12502.18519139754
-7 24652.59290541261
+4 22705.953345616945
+5 11616.726911608488
+6 12502.185191397542
+7 24652.592905412606
8 751.5664890746526
9 1.247219128924647
-10 23184.69621862663
+10 23184.696218626632
11 1.5
12 26754.41351752392
-13 16511
-14 0
-15 0
+13 16511.0
+14 0.0
+15 0.0
-- !select_default --
-1 0
-2 0
+1 0.0
+2 0.0
3 1.5
-4 2.054804667656326
-5 2
-6 14509.83124183967
-7 15049.64523460603
-8 13596.52212883868
-9 12502.83573594407
-10 13902.50012048193
-11 11616.93519058371
+4 2.0548046676563256
+5 2.0
+6 14509.831241839674
+7 15049.645234606029
+8 13596.522128838684
+9 12502.835735944067
+10 13902.500120481925
+11 11616.935190583712
12 18941.26337230146
13 21209.57875727909
-14 19879.49278740217
-15 10887.58182143158
+14 19879.492787402174
+15 10887.581821431579
-- !select_default --
-1 11759.93243623287
+1 11759.932436232868
2 21252.07172380075
3 21252.07172380075
4 21252.07172380075
-5 11759.93243623287
-6 11759.93243623287
+5 11759.932436232868
+6 11759.932436232868
7 21252.07172380075
-8 11759.93243623287
-9 12725.48674773058
+8 11759.932436232868
+9 12725.486747730582
10 22848.58156689486
-11 13977.86797762806
-12 24873.12567410859
-13 27562.43677906582
-14 26771.38120580757
+11 13977.867977628062
+12 24873.125674108593
+13 27562.436779065818
+14 26771.381205807567
15 867.8342295623053
-- !select_default --
1 \N
2 \N
-3 NaN
+3 0.0
4 2.1213203435596424
-5 NaN
+5 0.0
6 2.8284271247461903
7 2.5166114784235836
8 17770.84139820059
-9 18291.8678105873
-10 20066.9633311404
+9 18291.867810587304
+10 20066.963331140396
11 18290.27982290047
-12 20067.54065649301
-13 32787.1567741598
-14 32787.1567741598
+12 20067.540656493013
+13 32787.156774159805
+14 32787.156774159805
15 1001.703216193965
-- !select_default --
-1 2.82842712474619
-2 2.121320343559642
-3 2.516611478423584
+1 2.8284271247461903
+2 2.1213203435596424
+3 2.5166114784235836
4 17377.83345481632
5 17770.84139820059
-6 15699.91142225543
-7 15543.4676632983
-8 13978.59530854227
-9 13978.59530854227
-10 23195.39982841425
+6 15699.911422255434
+7 15543.467663298301
+8 13978.595308542272
+9 13978.595308542272
+10 23195.399828414254
11 13977.86797762806
12 27673.28036211103
-13 27562.43677906582
-14 26771.38120580757
+13 27562.436779065818
+14 26771.381205807567
15 867.8342295623053
-- !select_default --
-1 NaN
-2 NaN
-3 NaN
-4 NaN
-5 NaN
-6 NaN
-7 NaN
-8 NaN
-9 NaN
-10 NaN
-11 NaN
-12 NaN
-13 NaN
-14 NaN
-15 NaN
-
--- !select_default --
-1 11759.93243623287
+1 0.0
+2 0.0
+3 0.0
+4 0.0
+5 0.0
+6 0.0
+7 0.0
+8 0.0
+9 0.0
+10 0.0
+11 0.0
+12 0.0
+13 0.0
+14 0.0
+15 0.0
+
+-- !select_default --
+1 11759.932436232868
2 21252.07172380075
3 22848.58156689486
-4 24873.12567410859
-5 12725.48674773058
-6 13977.86797762806
-7 27562.43677906582
+4 24873.125674108593
+5 12725.486747730582
+6 13977.867977628062
+7 27562.436779065818
8 867.8342295623053
-9 1.527525231651947
-10 26771.38120580757
-11 2.121320343559642
-12 32767.33074267723
+9 1.5275252316519468
+10 26771.381205807567
+11 2.1213203435596424
+12 32767.330742677226
13 23350.08012834217
-14 NaN
-15 NaN
+14 0.0
+15 0.0
-- !select_default --
-1 NaN
-2 NaN
+1 0.0
+2 0.0
3 2.1213203435596424
4 2.5166114784235836
5 2.8284271247461903
6 17770.84139820059
7 17377.83345481632
-8 15699.91142225543
-9 13978.59530854227
-10 15543.4676632983
+8 15699.911422255434
+9 13978.595308542272
+10 15543.467663298301
11 12725.71490591655
-12 20749.11443331177
-13 22908.96337473091
-14 21252.07172380075
-15 11759.93243623287
-
--- !select_default --
-1 118539437.9183674
-2 395194233.484375
-3 395194233.484375
-4 395194233.484375
-5 118539437.9183674
-6 118539437.9183674
-7 395194233.484375
-8 118539437.9183674
-9 134948344.1388889
-10 447478011.1020408
-11 156304634.56
-12 515560317.3333333
-13 607750336.96
-14 537530138.75
+12 20749.114433311766
+13 22908.963374730905
+14 21252.071723800753
+15 11759.932436232868
+
+-- !select_default --
+1 1.1853943791836736E8
+2 3.95194233484375E8
+3 3.95194233484375E8
+4 3.95194233484375E8
+5 1.1853943791836736E8
+6 1.1853943791836736E8
+7 3.95194233484375E8
+8 1.1853943791836736E8
+9 1.349483441388889E8
+10 4.474780111020408E8
+11 1.5630463456E8
+12 5.155603173333333E8
+13 6.0775033696E8
+14 5.3753013875E8
15 564852.1875
-- !select_default --
1 \N
2 \N
-3 0
+3 0.0
4 2.25
-5 0
-6 4
+5 0.0
+6 4.0
7 4.222222222222222
-8 210535202.6666667
-9 223061618.6666667
-10 268455344.8888889
-11 223022890.6666667
-12 268470792
-13 716665099.5555555
-14 716665099.5555555
+8 2.1053520266666666E8
+9 2.2306161866666666E8
+10 2.684553448888889E8
+11 2.2302289066666666E8
+12 2.68470792E8
+13 7.166650995555555E8
+14 7.166650995555555E8
15 668939.5555555556
-- !select_default --
-1 4
+1 4.0
2 2.25
3 4.222222222222222
-4 226491821.6875
-5 210535202.6666667
-6 184865414
-7 193279509.6
-8 156320901.44
-9 156320901.44
-10 430421258.5600001
-11 156304634.56
-12 612648356.8
-13 607750336.96
-14 537530138.75
+4 2.2649182168749997E8
+5 2.1053520266666666E8
+6 1.84865414E8
+7 1.9327950959999996E8
+8 1.5632090144E8
+9 1.5632090144E8
+10 4.3042125856000006E8
+11 1.5630463456E8
+12 6.126483568E8
+13 6.0775033696E8
+14 5.3753013875E8
15 564852.1875
-- !select_default --
-1 0
-2 0
-3 0
-4 0
-5 0
-6 0
-7 0
-8 0
-9 0
-10 0
-11 0
-12 0
-13 0
-14 0
-15 0
-
--- !select_default --
-1 118539437.9183674
-2 395194233.484375
-3 447478011.1020408
-4 515560317.3333333
-5 134948344.1388889
-6 156304634.56
-7 607750336.96
+1 0.0
+2 0.0
+3 0.0
+4 0.0
+5 0.0
+6 0.0
+7 0.0
+8 0.0
+9 0.0
+10 0.0
+11 0.0
+12 0.0
+13 0.0
+14 0.0
+15 0.0
+
+-- !select_default --
+1 1.1853943791836736E8
+2 3.95194233484375E8
+3 4.474780111020408E8
+4 5.155603173333333E8
+5 1.349483441388889E8
+6 1.5630463456E8
+7 6.0775033696E8
8 564852.1875
-9 1.555555555555556
-10 537530138.75
+9 1.5555555555555556
+10 5.3753013875E8
11 2.25
-12 715798642.6666666
-13 272613121
-14 0
-15 0
+12 7.157986426666666E8
+13 2.72613121E8
+14 0.0
+15 0.0
-- !select_default --
-1 0
-2 0
+1 0.0
+2 0.0
3 2.25
4 4.222222222222222
-5 4
-6 210535202.6666667
-7 226491821.6875
-8 184865414
-9 156320901.44
-10 193279509.6
-11 134953183.2222222
-12 358771458.1388889
-13 449846231.0612245
-14 395194233.4843751
-15 118539437.9183674
-
--- !select_default --
-1 138296010.9047619
-2 451650552.5535714
-3 451650552.5535714
-4 451650552.5535714
-5 138296010.9047619
-6 138296010.9047619
-7 451650552.5535714
-8 138296010.9047619
-9 161938012.9666667
-10 522057679.6190476
-11 195380793.2
-12 618672380.8
-13 759687921.2
-14 716706851.6666666
+5 4.0
+6 2.1053520266666666E8
+7 2.2649182168749997E8
+8 1.84865414E8
+9 1.5632090144E8
+10 1.9327950959999996E8
+11 1.3495318322222224E8
+12 3.587714581388889E8
+13 4.498462310612245E8
+14 3.9519423348437506E8
+15 1.1853943791836736E8
+
+-- !select_default --
+1 1.382960109047619E8
+2 4.516505525535714E8
+3 4.516505525535714E8
+4 4.516505525535714E8
+5 1.382960109047619E8
+6 1.382960109047619E8
+7 4.516505525535714E8
+8 1.382960109047619E8
+9 1.6193801296666667E8
+10 5.2205767961904764E8
+11 1.9538079320000002E8
+12 6.186723808E8
+13 7.596879212E8
+14 7.167068516666666E8
15 753136.25
-- !select_default --
1 \N
2 \N
-3 NaN
+3 0.0
4 4.5
-5 NaN
+5 0.0
6 8.0
7 6.333333333333334
-8 315802804
-9 334592428
-10 402683017.3333333
-11 334534336
-12 402706188
-13 1074997649.333333
-14 1074997649.333333
-15 1003409.333333333
+8 3.15802804E8
+9 3.34592428E8
+10 4.026830173333333E8
+11 3.34534336E8
+12 4.02706188E8
+13 1.0749976493333333E9
+14 1.0749976493333333E9
+15 1003409.3333333334
-- !select_default --
-1 8
+1 8.0
2 4.5
3 6.333333333333334
-4 301989095.5833333
-5 315802804
-6 246487218.6666667
-7 241599387
-8 195401126.8
-9 195401126.8
-10 538026573.2
-11 195380793.2
-12 765810446
-13 759687921.2
-14 716706851.6666666
+4 3.019890955833333E8
+5 3.15802804E8
+6 2.4648721866666666E8
+7 2.4159938699999997E8
+8 1.954011268E8
+9 1.954011268E8
+10 5.380265732E8
+11 1.953807932E8
+12 7.65810446E8
+13 7.596879212E8
+14 7.167068516666666E8
15 753136.25
-- !select_default --
-1 NaN
-2 NaN
-3 NaN
-4 NaN
-5 NaN
-6 NaN
-7 NaN
-8 NaN
-9 NaN
-10 NaN
-11 NaN
-12 NaN
-13 NaN
-14 NaN
-15 NaN
-
--- !select_default --
-1 138296010.9047619
-2 451650552.5535714
-3 522057679.6190476
-4 618672380.8
-5 161938012.9666667
-6 195380793.2
-7 759687921.2
+1 0.0
+2 0.0
+3 0.0
+4 0.0
+5 0.0
+6 0.0
+7 0.0
+8 0.0
+9 0.0
+10 0.0
+11 0.0
+12 0.0
+13 0.0
+14 0.0
+15 0.0
+
+-- !select_default --
+1 1.382960109047619E8
+2 4.516505525535714E8
+3 5.2205767961904764E8
+4 6.186723808E8
+5 1.6193801296666667E8
+6 1.9538079320000002E8
+7 7.596879212E8
8 753136.25
-9 2.333333333333333
-10 716706851.6666666
+9 2.3333333333333335
+10 7.167068516666666E8
11 4.5
12 1.073697964E9
13 5.45226242E8
-14 NaN
-15 NaN
+14 0.0
+15 0.0
-- !select_default --
-1 NaN
-2 NaN
+1 0.0
+2 0.0
3 4.5
4 6.333333333333334
-5 8
-6 315802804
-7 301989095.5833333
-8 246487218.6666667
-9 195401126.8
-10 241599387
-11 161943819.8666667
-12 430525749.7666667
-13 524820602.904762
-14 451650552.5535715
-15 138296010.9047619
-
--- !select_default --
-1 1991.8
-2 1991
-3 1991
-4 1991
-5 1991.8
-6 1991.8
-7 1991
-8 1991.8
-9 1992
-10 1991
-11 8147.000000000005
-12 1991
-13 8146.200000000005
+5 8.0
+6 3.15802804E8
+7 3.019890955833333E8
+8 2.4648721866666666E8
+9 1.954011268E8
+10 2.4159938699999997E8
+11 1.6194381986666667E8
+12 4.305257497666667E8
+13 5.2482060290476197E8
+14 4.516505525535715E8
+15 1.382960109047619E8
+
+-- !select_default --
+1 1991.8000000000002
+2 1991.0
+3 1991.0
+4 1991.0
+5 1991.8000000000002
+6 1991.8000000000002
+7 1991.0
+8 1991.8000000000002
+9 1992.0
+10 1991.0
+11 8147.0000000000055
+12 1991.0
+13 8146.200000000006
14 14301.40000000001
15 1991.4
-- !select_default --
1 \N
2 \N
-3 1986
+3 1986.0
4 1988.4
-5 1989
-6 1988.2
-7 1990.2
-8 20455.8
-9 20454.2
-10 1990.2
-11 20456.6
-12 1991
-13 20456.6
-14 20456.6
-15 1990.2
-
--- !select_default --
-1 1988.2
+5 1989.0
+6 1988.1999999999998
+7 1990.1999999999998
+8 20455.800000000003
+9 20454.200000000004
+10 1990.1999999999998
+11 20456.600000000006
+12 1991.0
+13 20456.600000000006
+14 20456.600000000006
+15 1990.1999999999998
+
+-- !select_default --
+1 1988.1999999999998
2 1988.4
-3 1990.2
-4 1989.8
-5 20455.8
-6 14300.20000000001
-7 1991
-8 8146.200000000005
-9 8146.200000000005
-10 8146.200000000005
-11 8147.000000000005
-12 8146.200000000005
-13 8146.200000000005
+3 1990.1999999999998
+4 1989.7999999999997
+5 20455.800000000003
+6 14300.200000000012
+7 1991.0
+8 8146.200000000006
+9 8146.200000000006
+10 8146.200000000006
+11 8147.0000000000055
+12 8146.200000000006
+13 8146.200000000006
14 14301.40000000001
15 1991.4
-- !select_default --
-1 1989
-2 1986
-3 1989
-4 1991
-5 1985
-6 32767
-7 -32767
-8 255
-9 1991
-10 1991
-11 1989
-12 32767
-13 -32767
-14 255
-15 1992
-
--- !select_default --
-1 1991.8
-2 1991
-3 1991
-4 1991
-5 1992
-6 8147.000000000005
-7 8146.200000000005
+1 1989.0
+2 1986.0
+3 1989.0
+4 1991.0
+5 1985.0
+6 32767.0
+7 -32767.0
+8 255.0
+9 1991.0
+10 1991.0
+11 1989.0
+12 32767.0
+13 -32767.0
+14 255.0
+15 1992.0
+
+-- !select_default --
+1 1991.8000000000002
+2 1991.0
+3 1991.0
+4 1991.0
+5 1992.0
+6 8147.0000000000055
+7 8146.200000000006
8 1991.4
-9 1991.6
+9 1991.6000000000001
10 14301.40000000001
11 1991.4
-12 19762.2
-13 -6349.399999999998
-14 255
-15 1992
+12 19762.200000000004
+13 -6349.399999999999
+14 255.0
+15 1992.0
-- !select_default --
-1 1989
-2 1986
+1 1989.0
+2 1986.0
3 1988.4
-4 1990.2
-5 1988.2
-6 20455.8
-7 1989.8
-8 14300.20000000001
-9 8146.200000000005
-10 1991
-11 1991
-12 1991
-13 1991
-14 1991
-15 1991.8
-
--- !select_default --
-1 1989
+4 1990.1999999999998
+5 1988.1999999999998
+6 20455.800000000003
+7 1989.7999999999997
+8 14300.200000000012
+9 8146.200000000006
+10 1991.0
+11 1991.0
+12 1991.0
+13 1991.0
+14 1991.0
+15 1991.8000000000002
+
+-- !select_default --
+1 1989.0
2 1987.5
3 1987.5
4 1987.5
-5 1989
-6 1989
+5 1989.0
+6 1989.0
7 1987.5
-8 1989
-9 1990
-10 1989
-11 1991
-12 1123
-13 255
-14 1123
-15 1990
+8 1989.0
+9 1990.0
+10 1989.0
+11 1991.0
+12 1123.0
+13 255.0
+14 1123.0
+15 1990.0
-- !select_default --
1 \N
2 \N
-3 1986
+3 1986.0
4 1987.5
-5 1989
-6 1987
-7 1989
-8 1989
-9 1985
-10 1989
-11 1991
-12 1991
-13 1991
-14 1991
-15 1989
-
--- !select_default --
-1 1987
+5 1989.0
+6 1987.0
+7 1989.0
+8 1989.0
+9 1985.0
+10 1989.0
+11 1991.0
+12 1991.0
+13 1991.0
+14 1991.0
+15 1989.0
+
+-- !select_default --
+1 1987.0
2 1987.5
-3 1989
+3 1989.0
4 1987.5
-5 1989
-6 1987
-7 1989
-8 1989
-9 1989
-10 1991
-11 1991
-12 1991
-13 255
-14 1123
-15 1990
-
--- !select_default --
-1 1989
-2 1986
-3 1989
-4 1991
-5 1985
-6 32767
-7 -32767
-8 255
-9 1991
-10 1991
-11 1989
-12 32767
-13 -32767
-14 255
-15 1992
-
--- !select_default --
-1 1989
+5 1989.0
+6 1987.0
+7 1989.0
+8 1989.0
+9 1989.0
+10 1991.0
+11 1991.0
+12 1991.0
+13 255.0
+14 1123.0
+15 1990.0
+
+-- !select_default --
+1 1989.0
+2 1986.0
+3 1989.0
+4 1991.0
+5 1985.0
+6 32767.0
+7 -32767.0
+8 255.0
+9 1991.0
+10 1991.0
+11 1989.0
+12 32767.0
+13 -32767.0
+14 255.0
+15 1992.0
+
+-- !select_default --
+1 1989.0
2 1987.5
-3 1989
-4 1123
-5 1990
-6 1991
-7 255
-8 1990
-9 1991
-10 1123
+3 1989.0
+4 1123.0
+5 1990.0
+6 1991.0
+7 255.0
+8 1990.0
+9 1991.0
+10 1123.0
11 1990.5
-12 255
-13 -16256
-14 255
-15 1992
+12 255.0
+13 -16256.0
+14 255.0
+15 1992.0
-- !select_default --
-1 1989
-2 1986
+1 1989.0
+2 1986.0
3 1987.5
-4 1989
-5 1987
-6 1989
+4 1989.0
+5 1987.0
+6 1989.0
7 1987.5
-8 1987
-9 1989
-10 1989
-11 1989
-12 1990
-13 1989
+8 1987.0
+9 1989.0
+10 1989.0
+11 1989.0
+12 1990.0
+13 1989.0
14 1987.5
-15 1989
+15 1989.0
+
diff --git a/regression-test/data/function_p0/test_agg_foreach.out
b/regression-test/data/function_p0/test_agg_foreach.out
index 53b1759e9e6..785ff55e910 100644
--- a/regression-test/data/function_p0/test_agg_foreach.out
+++ b/regression-test/data/function_p0/test_agg_foreach.out
@@ -3,7 +3,7 @@
[1, 2, 3] [1, 2, 3] [100, 2, 3] [100, 2, 3]
[40.33333333333334, 2, 3] [85.95867768595042, 2, 3]
-- !sql --
-[121, 4, 3] [42.89781139198388, 0, 0] [52.53887449625594, 0, NaN]
[1840.222222222222, 0, 0] [2760.333333333333, 0, NaN]
+[121, 4, 3] [42.89781139198388, 0, 0] [52.53887449625594, 0, 0]
[1840.222222222222, 0, 0] [2760.333333333333, 0, 0]
-- !sql --
[1840.222222222222, 0, 0] [2760.333333333333, 0, 0] [1, 0, 0]
@@ -25,3 +25,4 @@
-- !sql --
["{"num_buckets":3,"buckets":[{"lower":"1","upper":"1","ndv":1,"count":1,"pre_sum":0},{"lower":"20","upper":"20","ndv":1,"count":1,"pre_sum":1},{"lower":"100","upper":"100","ndv":1,"count":1,"pre_sum":2}]}",
"{"num_buckets":1,"buckets":[{"lower":"2","upper":"2","ndv":1,"count":2,"pre_sum":0}]}",
"{"num_buckets":1,"buckets":[{"lower":"3","upper":"3","ndv":1,"count":1,"pre_sum":0}]}"]
+
diff --git a/regression-test/data/function_p0/test_agg_foreach_notnull.out
b/regression-test/data/function_p0/test_agg_foreach_notnull.out
index 971dcd35ed9..c7e12dd0a34 100644
--- a/regression-test/data/function_p0/test_agg_foreach_notnull.out
+++ b/regression-test/data/function_p0/test_agg_foreach_notnull.out
@@ -3,7 +3,7 @@
[1, 2, 3] [1, 2, 3] [100, 2, 3] [100, 2, 3]
[40.33333333333334, 2, 3] [85.95867768595042, 2, 3]
-- !sql --
-[121, 4, 3] [42.89781139198388, 0, 0] [52.53887449625594, 0, NaN]
[1840.222222222222, 0, 0] [2760.333333333333, 0, NaN]
+[121, 4, 3] [42.89781139198388, 0, 0] [52.53887449625594, 0, 0]
[1840.222222222222, 0, 0] [2760.333333333333, 0, 0]
-- !sql --
[1840.222222222222, 0, 0] [2760.333333333333, 0, 0] [1, 0, 0]
@@ -16,3 +16,4 @@
-- !sql --
["{"num_buckets":3,"buckets":[{"lower":"1","upper":"1","ndv":1,"count":1,"pre_sum":0},{"lower":"20","upper":"20","ndv":1,"count":1,"pre_sum":1},{"lower":"100","upper":"100","ndv":1,"count":1,"pre_sum":2}]}",
"{"num_buckets":1,"buckets":[{"lower":"2","upper":"2","ndv":1,"count":2,"pre_sum":0}]}",
"{"num_buckets":1,"buckets":[{"lower":"3","upper":"3","ndv":1,"count":1,"pre_sum":0}]}"]
+
diff --git a/regression-test/data/nereids_function_p0/agg_function/agg.out
b/regression-test/data/nereids_function_p0/agg_function/agg.out
index 4a50028a3ad..d0f3dfc22a6 100644
--- a/regression-test/data/nereids_function_p0/agg_function/agg.out
+++ b/regression-test/data/nereids_function_p0/agg_function/agg.out
@@ -4233,18 +4233,18 @@ true
-- !sql_stddev_samp_TinyInt_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_TinyInt_agg_phase_2 --
12 3.605551275463989
@@ -4265,18 +4265,18 @@ true
3.6055512754639896
-- !sql_stddev_samp_TinyInt_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_TinyInt_agg_phase_2_notnull --
12 3.6055512754639896
@@ -4299,18 +4299,18 @@ true
-- !sql_stddev_samp_SmallInt_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_SmallInt_agg_phase_2 --
12 3.6055512754639896
@@ -4331,18 +4331,18 @@ true
3.605551275463989
-- !sql_stddev_samp_SmallInt_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_SmallInt_agg_phase_2_notnull --
12 3.605551275463989
@@ -4365,18 +4365,18 @@ true
-- !sql_stddev_samp_Integer_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_Integer_agg_phase_2 --
12 3.605551275463989
@@ -4397,18 +4397,18 @@ true
3.605551275463989
-- !sql_stddev_samp_Integer_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_Integer_agg_phase_2_notnull --
12 3.605551275463989
@@ -4431,18 +4431,18 @@ true
-- !sql_stddev_samp_BigInt_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_BigInt_agg_phase_2 --
12 3.605551275463989
@@ -4463,18 +4463,18 @@ true
3.6055512754639896
-- !sql_stddev_samp_BigInt_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_BigInt_agg_phase_2_notnull --
12 3.605551275463989
@@ -4497,18 +4497,18 @@ true
-- !sql_stddev_samp_Float_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_Float_agg_phase_2 --
12 0.3605551333887302
@@ -4529,18 +4529,18 @@ true
0.3605551333887302
-- !sql_stddev_samp_Float_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_Float_agg_phase_2_notnull --
12 0.3605551333887302
@@ -4563,18 +4563,18 @@ true
-- !sql_stddev_samp_Double_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_Double_agg_phase_2 --
12 0.36055512754639896
@@ -4595,18 +4595,18 @@ true
0.36055512754639896
-- !sql_stddev_samp_Double_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_Double_agg_phase_2_notnull --
12 0.36055512754639896
@@ -4621,18 +4621,18 @@ true
-- !sql_stddev_samp_DecimalV2_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_DecimalV2_agg_phase_2 --
12 0.36055512754639896
@@ -4646,18 +4646,18 @@ true
12 0.36055512754639896
-- !sql_stddev_samp_DecimalV2_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_stddev_samp_DecimalV2_agg_phase_2_notnull --
12 0.36055512754639896
@@ -6366,18 +6366,18 @@ true
-- !sql_variance_samp_TinyInt_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_TinyInt_agg_phase_2 --
12 13.0
@@ -6398,18 +6398,18 @@ true
13.000000000000002
-- !sql_variance_samp_TinyInt_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_TinyInt_agg_phase_2_notnull --
12 13.0
@@ -6432,18 +6432,18 @@ true
-- !sql_variance_samp_SmallInt_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_SmallInt_agg_phase_2 --
12 13.000000000000002
@@ -6464,18 +6464,18 @@ true
13.0
-- !sql_variance_samp_SmallInt_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_SmallInt_agg_phase_2_notnull --
12 12.999999999999998
@@ -6498,18 +6498,18 @@ true
-- !sql_variance_samp_Integer_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_Integer_agg_phase_2 --
12 13.0
@@ -6530,18 +6530,18 @@ true
13.000000000000002
-- !sql_variance_samp_Integer_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_Integer_agg_phase_2_notnull --
12 13.000000000000002
@@ -6564,18 +6564,18 @@ true
-- !sql_variance_samp_BigInt_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_BigInt_agg_phase_2 --
12 13.0
@@ -6596,18 +6596,18 @@ true
13.000000000000002
-- !sql_variance_samp_BigInt_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_BigInt_agg_phase_2_notnull --
12 13.0
@@ -6630,18 +6630,18 @@ true
-- !sql_variance_samp_Float_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_Float_agg_phase_2 --
12 0.130000004212965
@@ -6662,18 +6662,18 @@ true
0.130000004212965
-- !sql_variance_samp_Float_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_Float_agg_phase_2_notnull --
12 0.13000000421296498
@@ -6696,18 +6696,18 @@ true
-- !sql_variance_samp_Double_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_Double_agg_phase_2 --
12 0.12999999999999998
@@ -6728,18 +6728,18 @@ true
0.12999999999999998
-- !sql_variance_samp_Double_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_Double_agg_phase_2_notnull --
12 0.13
@@ -6754,18 +6754,18 @@ true
-- !sql_variance_samp_DecimalV2_agg_phase_1 --
0 \N
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_DecimalV2_agg_phase_2 --
12 0.13
@@ -6779,18 +6779,18 @@ true
12 0.13
-- !sql_variance_samp_DecimalV2_agg_phase_1_notnull --
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
-1 NaN
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
+1 0.0
-- !sql_variance_samp_DecimalV2_agg_phase_2_notnull --
12 0.12999999999999998
diff --git
a/regression-test/data/nereids_rules_p0/normalize_window/normalize_window_nullable_agg_test.out
b/regression-test/data/nereids_rules_p0/normalize_window/normalize_window_nullable_agg_test.out
index 8ecf41687e3..f910333a57e 100644
---
a/regression-test/data/nereids_rules_p0/normalize_window/normalize_window_nullable_agg_test.out
+++
b/regression-test/data/nereids_rules_p0/normalize_window/normalize_window_nullable_agg_test.out
@@ -122,7 +122,7 @@
-- !variance_samp --
\N
-NaN
+0.0
0.5
0.0
0.5
@@ -166,7 +166,7 @@ NaN
-- !stddev_samp --
\N
-NaN
+0.0
0.7071067811865476
0.0
0.7071067811865476
@@ -294,3 +294,4 @@ false
-- !fold_window --
0 false 0 be
9999-12-31 23:59:59 false 9999-12-31 23:59:59 b
+
diff --git
a/regression-test/data/nereids_syntax_p0/mv/aggregate/agg_sync_mv.out
b/regression-test/data/nereids_syntax_p0/mv/aggregate/agg_sync_mv.out
index 21b784491d3..18af62dbfa7 100644
--- a/regression-test/data/nereids_syntax_p0/mv/aggregate/agg_sync_mv.out
+++ b/regression-test/data/nereids_syntax_p0/mv/aggregate/agg_sync_mv.out
@@ -1200,34 +1200,34 @@
11 0.0
-- !select_stddev_samp --
-\N NaN
-0 0
-1 0
-2 0
-3 0
-4 0
-5 0
-6 0
-7 0
-8 0
-9 0
-10 0
-11 0
+\N 0.0
+0 0.0
+1 0.0
+2 0.0
+3 0.0
+4 0.0
+5 0.0
+6 0.0
+7 0.0
+8 0.0
+9 0.0
+10 0.0
+11 0.0
-- !select_stddev_samp_mv --
-\N NaN
-0 0
-1 0
-2 0
-3 0
-4 0
-5 0
-6 0
-7 0
-8 0
-9 0
-10 0
-11 0
+\N 0.0
+0 0.0
+1 0.0
+2 0.0
+3 0.0
+4 0.0
+5 0.0
+6 0.0
+7 0.0
+8 0.0
+9 0.0
+10 0.0
+11 0.0
-- !select_sum0 --
\N 1
@@ -1410,64 +1410,64 @@
11 0.0
-- !select_variance_samp --
-\N NaN
-0 0
-1 0
-2 0
-3 0
-4 0
-5 0
-6 0
-7 0
-8 0
-9 0
-10 0
-11 0
+\N 0.0
+0 0.0
+1 0.0
+2 0.0
+3 0.0
+4 0.0
+5 0.0
+6 0.0
+7 0.0
+8 0.0
+9 0.0
+10 0.0
+11 0.0
-- !select_variance_samp_mv --
-\N NaN
-0 0
-1 0
-2 0
-3 0
-4 0
-5 0
-6 0
-7 0
-8 0
-9 0
-10 0
-11 0
+\N 0.0
+0 0.0
+1 0.0
+2 0.0
+3 0.0
+4 0.0
+5 0.0
+6 0.0
+7 0.0
+8 0.0
+9 0.0
+10 0.0
+11 0.0
-- !select_var_samp --
-\N NaN
-0 0
-1 0
-2 0
-3 0
-4 0
-5 0
-6 0
-7 0
-8 0
-9 0
-10 0
-11 0
+\N 0.0
+0 0.0
+1 0.0
+2 0.0
+3 0.0
+4 0.0
+5 0.0
+6 0.0
+7 0.0
+8 0.0
+9 0.0
+10 0.0
+11 0.0
-- !select_var_samp_mv --
-\N NaN
-0 0
-1 0
-2 0
-3 0
-4 0
-5 0
-6 0
-7 0
-8 0
-9 0
-10 0
-11 0
+\N 0.0
+0 0.0
+1 0.0
+2 0.0
+3 0.0
+4 0.0
+5 0.0
+6 0.0
+7 0.0
+8 0.0
+9 0.0
+10 0.0
+11 0.0
-- !select_window_funnel --
\N 1
@@ -1603,3 +1603,4 @@
10 {9:"string1"}
11 {10:"string2"}
12 {11:"string3"}
+
diff --git a/regression-test/data/query_p0/aggregate/agg_nullable_2.out
b/regression-test/data/query_p0/aggregate/agg_nullable_2.out
index cadd56b75cb..4d498a30bae 100644
--- a/regression-test/data/query_p0/aggregate/agg_nullable_2.out
+++ b/regression-test/data/query_p0/aggregate/agg_nullable_2.out
@@ -378,13 +378,13 @@ false
0.0
-- !select_stddev_samp --
-NaN
+0.0
-- !select_stddev_samp2 --
-NaN
+0.0
-- !select_stddev_samp_n --
-NaN
+0.0
-- !select_sum0 --
50
@@ -441,22 +441,22 @@ NaN
0.0
-- !select_variance_samp --
-NaN
+0.0
-- !select_variance_samp2 --
-NaN
+0.0
-- !select_variance_samp_n --
-NaN
+0.0
-- !select_var_samp --
-NaN
+0.0
-- !select_var_samp2 --
-NaN
+0.0
-- !select_var_samp_n --
-NaN
+0.0
-- !select_window_funnel --
0
@@ -493,3 +493,4 @@ NaN
-- !select_retention_n --
[0, 0]
+
diff --git a/regression-test/data/query_p0/aggregate/aggregate.out
b/regression-test/data/query_p0/aggregate/aggregate.out
index bfebe2e844d..4f523af8a6d 100644
--- a/regression-test/data/query_p0/aggregate/aggregate.out
+++ b/regression-test/data/query_p0/aggregate/aggregate.out
@@ -77,9 +77,6 @@ TESTING AGAIN
-- !aggregate --
9882691.114285713 1808.1659023775
--- !aggregate --
-NaN NaN NaN NaN
-
-- !aggregate --
9223845.040000001 1239.763326955734
@@ -726,3 +723,4 @@ NaN NaN NaN NaN
-- !four_phase_full_distribute --
hello 1 1
world 1 1
+
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/test_select_stddev_variance_window.out
b/regression-test/data/query_p0/sql_functions/window_functions/test_select_stddev_variance_window.out
index cf0fddae49c..04960d3b960 100644
---
a/regression-test/data/query_p0/sql_functions/window_functions/test_select_stddev_variance_window.out
+++
b/regression-test/data/query_p0/sql_functions/window_functions/test_select_stddev_variance_window.out
@@ -121,9 +121,9 @@
-- !select_default --
1 \N
2 \N
-3 NaN
+3 0
4 2.121320343559642
-5 NaN
+5 0
6 2.82842712474619
7 2.516611478423584
8 17770.84139820059
@@ -153,21 +153,21 @@
15 867.8342295623053
-- !select_default --
-1 NaN
-2 NaN
-3 NaN
-4 NaN
-5 NaN
-6 NaN
-7 NaN
-8 NaN
-9 NaN
-10 NaN
-11 NaN
-12 NaN
-13 NaN
-14 NaN
-15 NaN
+1 0
+2 0
+3 0
+4 0
+5 0
+6 0
+7 0
+8 0
+9 0
+10 0
+11 0
+12 0
+13 0
+14 0
+15 0
-- !select_default --
1 11759.93243623287
@@ -183,12 +183,12 @@
11 2.121320343559642
12 32767.33074267723
13 23350.08012834217
-14 NaN
-15 NaN
+14 0
+15 0
-- !select_default --
-1 NaN
-2 NaN
+1 0
+2 0
3 2.121320343559642
4 2.516611478423584
5 2.82842712474619
@@ -325,9 +325,9 @@
-- !select_default --
1 \N
2 \N
-3 NaN
+3 0
4 4.5
-5 NaN
+5 0
6 8
7 6.333333333333334
8 315802804
@@ -357,21 +357,21 @@
15 753136.25
-- !select_default --
-1 NaN
-2 NaN
-3 NaN
-4 NaN
-5 NaN
-6 NaN
-7 NaN
-8 NaN
-9 NaN
-10 NaN
-11 NaN
-12 NaN
-13 NaN
-14 NaN
-15 NaN
+1 0
+2 0
+3 0
+4 0
+5 0
+6 0
+7 0
+8 0
+9 0
+10 0
+11 0
+12 0
+13 0
+14 0
+15 0
-- !select_default --
1 138296010.9047619
@@ -387,12 +387,12 @@
11 4.5
12 1073697964
13 545226242
-14 NaN
-15 NaN
+14 0
+15 0
-- !select_default --
-1 NaN
-2 NaN
+1 0
+2 0
3 4.5
4 6.333333333333334
5 8
@@ -529,9 +529,9 @@
-- !select_default --
1 \N
2 \N
-3 NaN
+3 0
4 2.121320343559642
-5 NaN
+5 0
6 2.82842712474619
7 2.516611478423584
8 17770.84139820059
@@ -561,21 +561,21 @@
15 867.8342295623053
-- !select_default --
-1 NaN
-2 NaN
-3 NaN
-4 NaN
-5 NaN
-6 NaN
-7 NaN
-8 NaN
-9 NaN
-10 NaN
-11 NaN
-12 NaN
-13 NaN
-14 NaN
-15 NaN
+1 0
+2 0
+3 0
+4 0
+5 0
+6 0
+7 0
+8 0
+9 0
+10 0
+11 0
+12 0
+13 0
+14 0
+15 0
-- !select_default --
1 11759.93243623287
@@ -591,12 +591,12 @@
11 2.121320343559642
12 32767.33074267723
13 23350.08012834217
-14 NaN
-15 NaN
+14 0
+15 0
-- !select_default --
-1 NaN
-2 NaN
+1 0
+2 0
3 2.121320343559642
4 2.516611478423584
5 2.82842712474619
@@ -733,9 +733,9 @@
-- !select_default --
1 \N
2 \N
-3 NaN
+3 0
4 4.5
-5 NaN
+5 0
6 8
7 6.333333333333334
8 315802804
@@ -765,21 +765,21 @@
15 753136.25
-- !select_default --
-1 NaN
-2 NaN
-3 NaN
-4 NaN
-5 NaN
-6 NaN
-7 NaN
-8 NaN
-9 NaN
-10 NaN
-11 NaN
-12 NaN
-13 NaN
-14 NaN
-15 NaN
+1 0
+2 0
+3 0
+4 0
+5 0
+6 0
+7 0
+8 0
+9 0
+10 0
+11 0
+12 0
+13 0
+14 0
+15 0
-- !select_default --
1 138296010.9047619
@@ -795,12 +795,12 @@
11 4.5
12 1073697964
13 545226242
-14 NaN
-15 NaN
+14 0
+15 0
-- !select_default --
-1 NaN
-2 NaN
+1 0
+2 0
3 4.5
4 6.333333333333334
5 8
@@ -1052,3 +1052,4 @@
1 1991 false
1 1992 true
1 32767 false
+
diff --git a/regression-test/suites/query_p0/aggregate/aggregate.groovy
b/regression-test/suites/query_p0/aggregate/aggregate.groovy
index ad73a02d5b8..8485843aff1 100644
--- a/regression-test/suites/query_p0/aggregate/aggregate.groovy
+++ b/regression-test/suites/query_p0/aggregate/aggregate.groovy
@@ -133,7 +133,6 @@ suite("aggregate") {
qt_aggregate """ select var_samp(c_bigint), var_samp(c_double) from
${tableName} """
qt_aggregate """ select var_samp(distinct c_bigint), var_samp(c_double)
from ${tableName} """
qt_aggregate """ select var_samp(c_bigint), var_samp(distinct c_double)
from ${tableName} """
- qt_aggregate """ select var_samp(c_bigint), var_samp(c_double),
stddev_samp(c_bigint), stddev_samp(c_double) from ${tableName2} """
qt_aggregate """ select variance(c_bigint), variance(c_double) from
${tableName} """
qt_aggregate """ select variance(distinct c_bigint), variance(c_double)
from ${tableName} """
qt_aggregate """ select variance(c_bigint), variance(distinct c_double)
from ${tableName} """
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
