This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.1 by this push:
new 01bba92ee0e branch-4.1: [Fix](variance) Fix sample variance/stddev NaN
res for single value#63605 (#63912)
01bba92ee0e is described below
commit 01bba92ee0e14606d3b16ebc1a61d0d88f0b6617
Author: linrrarity <[email protected]>
AuthorDate: Mon Jun 1 17:29:34 2026 +0800
branch-4.1: [Fix](variance) Fix sample variance/stddev NaN res for single
value#63605 (#63912)
cherry-pick: https://github.com/apache/doris/pull/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 +++++++--------
.../data/nereids_p0/aggregate/agg_nullable_2.out | 19 +-
.../test_select_stddev_variance_window.out | 293 ++++---
.../normalize_window_nullable_agg_test.out | 5 +-
.../nereids_syntax_p0/mv/aggregate/agg_sync_mv.out | 157 ++--
.../data/query_p0/aggregate/aggregate.out | 30 +-
.../test_select_stddev_variance_window.out | 205 +++--
.../suites/query_p0/aggregate/aggregate.groovy | 1 +
12 files changed, 1211 insertions(+), 1138 deletions(-)
diff --git a/be/src/exprs/aggregate/aggregate_function_stddev.h
b/be/src/exprs/aggregate/aggregate_function_stddev.h
index 0169d0cdf82..90c86cc7f4f 100644
--- a/be/src/exprs/aggregate/aggregate_function_stddev.h
+++ b/be/src/exprs/aggregate/aggregate_function_stddev.h
@@ -21,13 +21,11 @@
#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"
@@ -38,8 +36,6 @@ class Arena;
class BufferReadable;
class BufferWritable;
template <PrimitiveType T>
-class ColumnDecimal;
-template <PrimitiveType T>
class ColumnVector;
template <PrimitiveType T, bool is_stddev>
@@ -72,7 +68,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::nan("");
+ return std::numeric_limits<double>::quiet_NaN();
}
return val;
};
@@ -126,14 +122,9 @@ 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<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());
- }
+ auto& col = assert_cast<ColumnFloat64&>(to);
+ col.get_data().push_back(this->get_pop_result());
}
static DataTypePtr get_return_type() { return
std::make_shared<DataTypeFloat64>(); }
@@ -145,17 +136,12 @@ 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<ColVecResult&>(to);
+ auto& col = assert_cast<ColumnFloat64&>(to);
if (this->count == 1 || this->count == 0) {
- col.insert_default();
+ col.get_data().push_back(std::numeric_limits<double>::quiet_NaN());
} else {
- 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());
- }
+ 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 0c6ee4f84bc..f1563106a8a 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,613 +1,612 @@
-- This file is automatically generated. You should know what you did if you
want to edit this
-- !select_default --
-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
+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
10 21153.67606592388
-11 12502.185191397542
-12 22705.953345616945
-13 24652.592905412606
-14 23184.696218626632
+11 12502.18519139754
+12 22705.95334561694
+13 24652.59290541261
+14 23184.69621862663
15 751.5664890746526
-- !select_default --
1 \N
2 \N
-3 0.0
+3 0
4 1.5
-5 0.0
-6 2.0
-7 2.0548046676563256
-8 14509.831241839674
-9 14935.247526126464
-10 16384.606949478188
-11 14933.950939609607
+5 0
+6 2
+7 2.054804667656326
+8 14509.83124183967
+9 14935.24752612646
+10 16384.60694947819
+11 14933.95093960961
12 16385.07833365468
-13 26770.601404442812
-14 26770.601404442812
+13 26770.60140444281
+14 26770.60140444281
15 817.8872511266792
-- !select_default --
-1 2.0
+1 2
2 1.5
-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
+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
15 751.5664890746526
-- !select_default --
-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
+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
3 21153.67606592388
-4 22705.953345616945
-5 11616.726911608488
-6 12502.185191397542
-7 24652.592905412606
+4 22705.95334561694
+5 11616.72691160849
+6 12502.18519139754
+7 24652.59290541261
8 751.5664890746526
9 1.247219128924647
-10 23184.696218626632
+10 23184.69621862663
11 1.5
12 26754.41351752392
-13 16511.0
-14 0.0
-15 0.0
+13 16511
+14 0
+15 0
-- !select_default --
-1 0.0
-2 0.0
+1 0
+2 0
3 1.5
-4 2.0548046676563256
-5 2.0
-6 14509.831241839674
-7 15049.645234606029
-8 13596.522128838684
-9 12502.835735944067
-10 13902.500120481925
-11 11616.935190583712
+4 2.054804667656326
+5 2
+6 14509.83124183967
+7 15049.64523460603
+8 13596.52212883868
+9 12502.83573594407
+10 13902.50012048193
+11 11616.93519058371
12 18941.26337230146
13 21209.57875727909
-14 19879.492787402174
-15 10887.581821431579
+14 19879.49278740217
+15 10887.58182143158
-- !select_default --
-1 11759.932436232868
+1 11759.93243623287
2 21252.07172380075
3 21252.07172380075
4 21252.07172380075
-5 11759.932436232868
-6 11759.932436232868
+5 11759.93243623287
+6 11759.93243623287
7 21252.07172380075
-8 11759.932436232868
-9 12725.486747730582
+8 11759.93243623287
+9 12725.48674773058
10 22848.58156689486
-11 13977.867977628062
-12 24873.125674108593
-13 27562.436779065818
-14 26771.381205807567
+11 13977.86797762806
+12 24873.12567410859
+13 27562.43677906582
+14 26771.38120580757
15 867.8342295623053
-- !select_default --
1 \N
2 \N
-3 0.0
+3 NaN
4 2.1213203435596424
-5 0.0
+5 NaN
6 2.8284271247461903
7 2.5166114784235836
8 17770.84139820059
-9 18291.867810587304
-10 20066.963331140396
+9 18291.8678105873
+10 20066.9633311404
11 18290.27982290047
-12 20067.540656493013
-13 32787.156774159805
-14 32787.156774159805
+12 20067.54065649301
+13 32787.1567741598
+14 32787.1567741598
15 1001.703216193965
-- !select_default --
-1 2.8284271247461903
-2 2.1213203435596424
-3 2.5166114784235836
+1 2.82842712474619
+2 2.121320343559642
+3 2.516611478423584
4 17377.83345481632
5 17770.84139820059
-6 15699.911422255434
-7 15543.467663298301
-8 13978.595308542272
-9 13978.595308542272
-10 23195.399828414254
+6 15699.91142225543
+7 15543.4676632983
+8 13978.59530854227
+9 13978.59530854227
+10 23195.39982841425
11 13977.86797762806
12 27673.28036211103
-13 27562.436779065818
-14 26771.381205807567
+13 27562.43677906582
+14 26771.38120580757
15 867.8342295623053
-- !select_default --
-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
+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
2 21252.07172380075
3 22848.58156689486
-4 24873.125674108593
-5 12725.486747730582
-6 13977.867977628062
-7 27562.436779065818
+4 24873.12567410859
+5 12725.48674773058
+6 13977.86797762806
+7 27562.43677906582
8 867.8342295623053
-9 1.5275252316519468
-10 26771.381205807567
-11 2.1213203435596424
-12 32767.330742677226
+9 1.527525231651947
+10 26771.38120580757
+11 2.121320343559642
+12 32767.33074267723
13 23350.08012834217
-14 0.0
-15 0.0
+14 NaN
+15 NaN
-- !select_default --
-1 0.0
-2 0.0
+1 NaN
+2 NaN
3 2.1213203435596424
4 2.5166114784235836
5 2.8284271247461903
6 17770.84139820059
7 17377.83345481632
-8 15699.911422255434
-9 13978.595308542272
-10 15543.467663298301
+8 15699.91142225543
+9 13978.59530854227
+10 15543.4676632983
11 12725.71490591655
-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
+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
15 564852.1875
-- !select_default --
1 \N
2 \N
-3 0.0
+3 0
4 2.25
-5 0.0
-6 4.0
+5 0
+6 4
7 4.222222222222222
-8 2.1053520266666666E8
-9 2.2306161866666666E8
-10 2.684553448888889E8
-11 2.2302289066666666E8
-12 2.68470792E8
-13 7.166650995555555E8
-14 7.166650995555555E8
+8 210535202.6666667
+9 223061618.6666667
+10 268455344.8888889
+11 223022890.6666667
+12 268470792
+13 716665099.5555555
+14 716665099.5555555
15 668939.5555555556
-- !select_default --
-1 4.0
+1 4
2 2.25
3 4.222222222222222
-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
+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
15 564852.1875
-- !select_default --
-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
+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
8 564852.1875
-9 1.5555555555555556
-10 5.3753013875E8
+9 1.555555555555556
+10 537530138.75
11 2.25
-12 7.157986426666666E8
-13 2.72613121E8
-14 0.0
-15 0.0
+12 715798642.6666666
+13 272613121
+14 0
+15 0
-- !select_default --
-1 0.0
-2 0.0
+1 0
+2 0
3 2.25
4 4.222222222222222
-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
+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
15 753136.25
-- !select_default --
1 \N
2 \N
-3 0.0
+3 NaN
4 4.5
-5 0.0
+5 NaN
6 8.0
7 6.333333333333334
-8 3.15802804E8
-9 3.34592428E8
-10 4.026830173333333E8
-11 3.34534336E8
-12 4.02706188E8
-13 1.0749976493333333E9
-14 1.0749976493333333E9
-15 1003409.3333333334
+8 315802804
+9 334592428
+10 402683017.3333333
+11 334534336
+12 402706188
+13 1074997649.333333
+14 1074997649.333333
+15 1003409.333333333
-- !select_default --
-1 8.0
+1 8
2 4.5
3 6.333333333333334
-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
+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
15 753136.25
-- !select_default --
-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
+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
8 753136.25
-9 2.3333333333333335
-10 7.167068516666666E8
+9 2.333333333333333
+10 716706851.6666666
11 4.5
12 1.073697964E9
13 5.45226242E8
-14 0.0
-15 0.0
+14 NaN
+15 NaN
-- !select_default --
-1 0.0
-2 0.0
+1 NaN
+2 NaN
3 4.5
4 6.333333333333334
-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
+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
14 14301.40000000001
15 1991.4
-- !select_default --
1 \N
2 \N
-3 1986.0
+3 1986
4 1988.4
-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
+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
2 1988.4
-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
+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
14 14301.40000000001
15 1991.4
-- !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 1991.8000000000002
-2 1991.0
-3 1991.0
-4 1991.0
-5 1992.0
-6 8147.0000000000055
-7 8146.200000000006
+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
8 1991.4
-9 1991.6000000000001
+9 1991.6
10 14301.40000000001
11 1991.4
-12 19762.200000000004
-13 -6349.399999999999
-14 255.0
-15 1992.0
+12 19762.2
+13 -6349.399999999998
+14 255
+15 1992
-- !select_default --
-1 1989.0
-2 1986.0
+1 1989
+2 1986
3 1988.4
-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
+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
2 1987.5
3 1987.5
4 1987.5
-5 1989.0
-6 1989.0
+5 1989
+6 1989
7 1987.5
-8 1989.0
-9 1990.0
-10 1989.0
-11 1991.0
-12 1123.0
-13 255.0
-14 1123.0
-15 1990.0
+8 1989
+9 1990
+10 1989
+11 1991
+12 1123
+13 255
+14 1123
+15 1990
-- !select_default --
1 \N
2 \N
-3 1986.0
+3 1986
4 1987.5
-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
+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
2 1987.5
-3 1989.0
+3 1989
4 1987.5
-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
+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
2 1987.5
-3 1989.0
-4 1123.0
-5 1990.0
-6 1991.0
-7 255.0
-8 1990.0
-9 1991.0
-10 1123.0
+3 1989
+4 1123
+5 1990
+6 1991
+7 255
+8 1990
+9 1991
+10 1123
11 1990.5
-12 255.0
-13 -16256.0
-14 255.0
-15 1992.0
+12 255
+13 -16256
+14 255
+15 1992
-- !select_default --
-1 1989.0
-2 1986.0
+1 1989
+2 1986
3 1987.5
-4 1989.0
-5 1987.0
-6 1989.0
+4 1989
+5 1987
+6 1989
7 1987.5
-8 1987.0
-9 1989.0
-10 1989.0
-11 1989.0
-12 1990.0
-13 1989.0
+8 1987
+9 1989
+10 1989
+11 1989
+12 1990
+13 1989
14 1987.5
-15 1989.0
-
+15 1989
diff --git a/regression-test/data/function_p0/test_agg_foreach.out
b/regression-test/data/function_p0/test_agg_foreach.out
index 9a6c77d1383..0ae73e90d3d 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, 0]
[1840.222222222222, 0, 0] [2760.333333333333, 0, 0]
+[121, 4, 3] [42.89781139198388, 0, 0] [52.53887449625594, 0, NaN]
[1840.222222222222, 0, 0] [2760.333333333333, 0, NaN]
-- !sql --
[1840.222222222222, 0, 0] [2760.333333333333, 0, 0] [1, 0, 0]
@@ -19,4 +19,3 @@
-- !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 5bd1bf2fef0..71683b04143 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, 0]
[1840.222222222222, 0, 0] [2760.333333333333, 0, 0]
+[121, 4, 3] [42.89781139198388, 0, 0] [52.53887449625594, 0, NaN]
[1840.222222222222, 0, 0] [2760.333333333333, 0, NaN]
-- !sql --
[1840.222222222222, 0, 0] [2760.333333333333, 0, 0] [1, 0, 0]
@@ -13,4 +13,3 @@
-- !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 d0f3dfc22a6..4a50028a3ad 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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !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 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
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
+1 NaN
-- !sql_variance_samp_DecimalV2_agg_phase_2_notnull --
12 0.12999999999999998
diff --git a/regression-test/data/nereids_p0/aggregate/agg_nullable_2.out
b/regression-test/data/nereids_p0/aggregate/agg_nullable_2.out
index 4d498a30bae..cadd56b75cb 100644
--- a/regression-test/data/nereids_p0/aggregate/agg_nullable_2.out
+++ b/regression-test/data/nereids_p0/aggregate/agg_nullable_2.out
@@ -378,13 +378,13 @@ false
0.0
-- !select_stddev_samp --
-0.0
+NaN
-- !select_stddev_samp2 --
-0.0
+NaN
-- !select_stddev_samp_n --
-0.0
+NaN
-- !select_sum0 --
50
@@ -441,22 +441,22 @@ false
0.0
-- !select_variance_samp --
-0.0
+NaN
-- !select_variance_samp2 --
-0.0
+NaN
-- !select_variance_samp_n --
-0.0
+NaN
-- !select_var_samp --
-0.0
+NaN
-- !select_var_samp2 --
-0.0
+NaN
-- !select_var_samp_n --
-0.0
+NaN
-- !select_window_funnel --
0
@@ -493,4 +493,3 @@ false
-- !select_retention_n --
[0, 0]
-
diff --git
a/regression-test/data/nereids_p0/sql_functions/window_functions/test_select_stddev_variance_window.out
b/regression-test/data/nereids_p0/sql_functions/window_functions/test_select_stddev_variance_window.out
index cc19ecd6be6..c3c2c9a9095 100644
---
a/regression-test/data/nereids_p0/sql_functions/window_functions/test_select_stddev_variance_window.out
+++
b/regression-test/data/nereids_p0/sql_functions/window_functions/test_select_stddev_variance_window.out
@@ -30,7 +30,7 @@
11 14933.950939609607
12 16385.07833365468
13 26770.601404442812
-14 26770.60140444281
+14 26770.601404442812
15 817.8872511266792
-- !select_default --
@@ -43,7 +43,7 @@
7 13902.500120481925
8 12502.835735944067
9 12502.835735944067
-10 20746.59631264849
+10 20746.596312648493
11 12502.185191397542
12 24751.734420036104
13 24652.592905412606
@@ -121,11 +121,11 @@
-- !select_default --
1 \N
2 \N
-3 0.0
-4 2.1213203435596424
-5 0.0
-6 2.8284271247461903
-7 2.5166114784235836
+3 NaN
+4 2.121320343559642
+5 NaN
+6 2.82842712474619
+7 2.516611478423584
8 17770.84139820059
9 18291.867810587304
10 20066.963331140396
@@ -145,29 +145,29 @@
7 15543.467663298301
8 13978.595308542272
9 13978.595308542272
-10 23195.39982841425
+10 23195.399828414254
11 13977.86797762806
12 27673.28036211103
-13 27562.436779065814
+13 27562.436779065818
14 26771.381205807567
15 867.8342295623053
-- !select_default --
-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
+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.932436232868
@@ -183,15 +183,15 @@
11 2.1213203435596424
12 32767.330742677226
13 23350.08012834217
-14 0.0
-15 0.0
+14 NaN
+15 NaN
-- !select_default --
-1 0.0
-2 0.0
-3 2.1213203435596424
-4 2.5166114784235836
-5 2.8284271247461903
+1 NaN
+2 NaN
+3 2.121320343559642
+4 2.516611478423584
+5 2.82842712474619
6 17770.84139820059
7 17377.83345481632
8 15699.911422255434
@@ -234,7 +234,7 @@
11 2.2302289066666666E8
12 2.68470792E8
13 7.166650995555555E8
-14 7.166650995555553E8
+14 7.166650995555555E8
15 668939.5555555556
-- !select_default --
@@ -247,10 +247,10 @@
7 1.9327950959999996E8
8 1.5632090144E8
9 1.5632090144E8
-10 4.3042125855999994E8
+10 4.3042125856000006E8
11 1.5630463456E8
12 6.126483568E8
-13 6.077503369599999E8
+13 6.0775033696E8
14 5.3753013875E8
15 564852.1875
@@ -325,10 +325,10 @@
-- !select_default --
1 \N
2 \N
-3 0.0
+3 NaN
4 4.5
-5 0.0
-6 8.0
+5 NaN
+6 8
7 6.333333333333334
8 3.15802804E8
9 3.34592428E8
@@ -336,7 +336,7 @@
11 3.34534336E8
12 4.02706188E8
13 1.0749976493333333E9
-14 1.074997649333333E9
+14 1.0749976493333333E9
15 1003409.3333333334
-- !select_default --
@@ -349,29 +349,29 @@
7 2.4159938699999997E8
8 1.954011268E8
9 1.954011268E8
-10 5.380265731999999E8
+10 5.380265732E8
11 1.953807932E8
-12 7.658104459999999E8
-13 7.596879211999999E8
+12 7.65810446E8
+13 7.596879212E8
14 7.167068516666666E8
15 753136.25
-- !select_default --
-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
+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 1.382960109047619E8
@@ -385,14 +385,14 @@
9 2.3333333333333335
10 7.167068516666666E8
11 4.5
-12 1.073697964E9
-13 5.45226242E8
-14 0.0
-15 0.0
+12 1073697964
+13 545226242
+14 NaN
+15 NaN
-- !select_default --
-1 0.0
-2 0.0
+1 NaN
+2 NaN
3 4.5
4 6.333333333333334
5 8.0
@@ -529,11 +529,11 @@
-- !select_default --
1 \N
2 \N
-3 0.0
-4 2.1213203435596424
-5 0.0
-6 2.8284271247461903
-7 2.5166114784235836
+3 NaN
+4 2.121320343559642
+5 NaN
+6 2.82842712474619
+7 2.516611478423584
8 17770.84139820059
9 18291.867810587304
10 20066.963331140396
@@ -561,21 +561,21 @@
15 867.8342295623053
-- !select_default --
-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
+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.932436232868
@@ -591,15 +591,15 @@
11 2.1213203435596424
12 32767.330742677226
13 23350.08012834217
-14 0.0
-15 0.0
+14 NaN
+15 NaN
-- !select_default --
-1 0.0
-2 0.0
-3 2.1213203435596424
-4 2.5166114784235836
-5 2.8284271247461903
+1 NaN
+2 NaN
+3 2.121320343559642
+4 2.516611478423584
+5 2.82842712474619
6 17770.84139820059
7 17377.83345481632
8 15699.911422255434
@@ -733,10 +733,10 @@
-- !select_default --
1 \N
2 \N
-3 0.0
+3 NaN
4 4.5
-5 0.0
-6 8.0
+5 NaN
+6 8
7 6.333333333333334
8 3.15802804E8
9 3.34592428E8
@@ -765,21 +765,21 @@
15 753136.25
-- !select_default --
-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
+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 1.382960109047619E8
@@ -793,14 +793,14 @@
9 2.3333333333333335
10 7.167068516666666E8
11 4.5
-12 1.073697964E9
-13 5.45226242E8
-14 0.0
-15 0.0
+12 1073697964
+13 545226242
+14 NaN
+15 NaN
-- !select_default --
-1 0.0
-2 0.0
+1 NaN
+2 NaN
3 4.5
4 6.333333333333334
5 8.0
@@ -1019,3 +1019,70 @@
14 1987.5
15 1989.0
+-- !sql_row_number_1 --
+1 -32767 false
+1 255 false
+1 1985 true
+1 1986 false
+1 1989 false
+1 1991 false
+1 1992 true
+1 32767 false
+
+-- !sql_rank_1 --
+1 -32767 false
+1 -32767 false
+1 255 false
+1 1985 true
+1 1986 false
+1 1989 false
+1 1991 false
+1 1991 false
+1 1992 true
+1 32767 false
+
+-- !sql_dense_rank_1 --
+1 -32767 false
+1 -32767 false
+1 255 false
+1 1985 true
+1 1986 false
+1 1989 false
+1 1991 false
+1 1991 false
+1 1992 true
+1 32767 false
+
+-- !sql_row_number --
+1 -32767 false
+1 255 false
+1 1985 true
+1 1986 false
+1 1989 false
+1 1991 false
+1 1992 true
+1 32767 false
+
+-- !sql_rank --
+1 -32767 false
+1 -32767 false
+1 255 false
+1 1985 true
+1 1986 false
+1 1989 false
+1 1991 false
+1 1991 false
+1 1992 true
+1 32767 false
+
+-- !sql_dense_rank --
+1 -32767 false
+1 -32767 false
+1 255 false
+1 1985 true
+1 1986 false
+1 1989 false
+1 1991 false
+1 1991 false
+1 1992 true
+1 32767 false
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 f910333a57e..8ecf41687e3 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
-0.0
+NaN
0.5
0.0
0.5
@@ -166,7 +166,7 @@
-- !stddev_samp --
\N
-0.0
+NaN
0.7071067811865476
0.0
0.7071067811865476
@@ -294,4 +294,3 @@ 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 18af62dbfa7..21b784491d3 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 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
+\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
-- !select_stddev_samp_mv --
-\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
+\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
-- !select_sum0 --
\N 1
@@ -1410,64 +1410,64 @@
11 0.0
-- !select_variance_samp --
-\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
+\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
-- !select_variance_samp_mv --
-\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
+\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
-- !select_var_samp --
-\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
+\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
-- !select_var_samp_mv --
-\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
+\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
-- !select_window_funnel --
\N 1
@@ -1603,4 +1603,3 @@
10 {9:"string1"}
11 {10:"string2"}
12 {11:"string3"}
-
diff --git a/regression-test/data/query_p0/aggregate/aggregate.out
b/regression-test/data/query_p0/aggregate/aggregate.out
index f17c690ec49..918a701fb03 100644
--- a/regression-test/data/query_p0/aggregate/aggregate.out
+++ b/regression-test/data/query_p0/aggregate/aggregate.out
@@ -78,7 +78,10 @@ TESTING AGAIN
9882691.114285713 1808.1659023775003
-- !aggregate --
-9223845.040000001 1239.7633269557336
+NaN NaN NaN NaN
+
+-- !aggregate --
+9223845.040000001 1239.763326955734
-- !aggregate --
9891387.555555556 1239.763326955733
@@ -699,4 +702,27 @@ TESTING AGAIN
16 \N
-- !aggregate35 --
-12.25 32.625 43.75 54.999999999999986 77.49999999999994 \N
+12.25 32.625 43.75 54.99999999999999 77.49999999999994 \N
+
+-- !select_quantile_percent --
+5000
+
+-- !aggregate --
+1 1
+1 1
+1 1
+1 1
+1 1
+1 1
+1 1
+1 1
+1 1
+2 1
+2 1
+
+-- !having_with_limit --
+7 -32767
+
+-- !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 0da41c3f527..c3c2c9a9095 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,11 +121,11 @@
-- !select_default --
1 \N
2 \N
-3 0.0
-4 2.1213203435596424
-5 0.0
-6 2.8284271247461903
-7 2.5166114784235836
+3 NaN
+4 2.121320343559642
+5 NaN
+6 2.82842712474619
+7 2.516611478423584
8 17770.84139820059
9 18291.867810587304
10 20066.963331140396
@@ -153,21 +153,21 @@
15 867.8342295623053
-- !select_default --
-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
+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.932436232868
@@ -183,15 +183,15 @@
11 2.1213203435596424
12 32767.330742677226
13 23350.08012834217
-14 0.0
-15 0.0
+14 NaN
+15 NaN
-- !select_default --
-1 0.0
-2 0.0
-3 2.1213203435596424
-4 2.5166114784235836
-5 2.8284271247461903
+1 NaN
+2 NaN
+3 2.121320343559642
+4 2.516611478423584
+5 2.82842712474619
6 17770.84139820059
7 17377.83345481632
8 15699.911422255434
@@ -325,10 +325,10 @@
-- !select_default --
1 \N
2 \N
-3 0.0
+3 NaN
4 4.5
-5 0.0
-6 8.0
+5 NaN
+6 8
7 6.333333333333334
8 3.15802804E8
9 3.34592428E8
@@ -357,21 +357,21 @@
15 753136.25
-- !select_default --
-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
+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 1.382960109047619E8
@@ -385,14 +385,14 @@
9 2.3333333333333335
10 7.167068516666666E8
11 4.5
-12 1.073697964E9
-13 5.45226242E8
-14 0.0
-15 0.0
+12 1073697964
+13 545226242
+14 NaN
+15 NaN
-- !select_default --
-1 0.0
-2 0.0
+1 NaN
+2 NaN
3 4.5
4 6.333333333333334
5 8.0
@@ -529,11 +529,11 @@
-- !select_default --
1 \N
2 \N
-3 0.0
-4 2.1213203435596424
-5 0.0
-6 2.8284271247461903
-7 2.5166114784235836
+3 NaN
+4 2.121320343559642
+5 NaN
+6 2.82842712474619
+7 2.516611478423584
8 17770.84139820059
9 18291.867810587304
10 20066.963331140396
@@ -561,21 +561,21 @@
15 867.8342295623053
-- !select_default --
-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
+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.932436232868
@@ -591,15 +591,15 @@
11 2.1213203435596424
12 32767.330742677226
13 23350.08012834217
-14 0.0
-15 0.0
+14 NaN
+15 NaN
-- !select_default --
-1 0.0
-2 0.0
-3 2.1213203435596424
-4 2.5166114784235836
-5 2.8284271247461903
+1 NaN
+2 NaN
+3 2.121320343559642
+4 2.516611478423584
+5 2.82842712474619
6 17770.84139820059
7 17377.83345481632
8 15699.911422255434
@@ -733,10 +733,10 @@
-- !select_default --
1 \N
2 \N
-3 0.0
+3 NaN
4 4.5
-5 0.0
-6 8.0
+5 NaN
+6 8
7 6.333333333333334
8 3.15802804E8
9 3.34592428E8
@@ -765,21 +765,21 @@
15 753136.25
-- !select_default --
-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
+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 1.382960109047619E8
@@ -793,14 +793,14 @@
9 2.3333333333333335
10 7.167068516666666E8
11 4.5
-12 1.073697964E9
-13 5.45226242E8
-14 0.0
-15 0.0
+12 1073697964
+13 545226242
+14 NaN
+15 NaN
-- !select_default --
-1 0.0
-2 0.0
+1 NaN
+2 NaN
3 4.5
4 6.333333333333334
5 8.0
@@ -1086,4 +1086,3 @@
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 c9afd839fcc..29db09e2ce2 100644
--- a/regression-test/suites/query_p0/aggregate/aggregate.groovy
+++ b/regression-test/suites/query_p0/aggregate/aggregate.groovy
@@ -133,6 +133,7 @@ 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]
