This is an automated email from the ASF dual-hosted git repository.
mrhhsg 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 355a300ec37 [fix](window) Recompute sliding frames for floating-point
sum/avg (#68113)
355a300ec37 is described below
commit 355a300ec37c6a0f36dbf0c1c9f0835dc74c68d8
Author: Jerry Hu <[email protected]>
AuthorDate: Fri Sep 18 16:33:35 2026 +0800
[fix](window) Recompute sliding frames for floating-point sum/avg (#68113)
### What problem does this PR solve?
Issue Number: None
Problem Summary:
Sliding `ROWS` window frames evaluate `sum`/`avg` incrementally: the
outgoing
row is subtracted from the accumulator and the incoming row is added.
For
floating-point accumulators this is not exact. Once `2^54 + 1` rounds to
`2^54`, subtracting `2^54` again leaves `0` instead of `1`, so the
rounding
lost by a value that already left the frame keeps distorting later
results.
```sql
WITH t AS (
SELECT 1 AS id, CAST(18014398509481984 AS DOUBLE) AS v
UNION ALL SELECT 2, CAST(1 AS DOUBLE)
UNION ALL SELECT 3, CAST(1 AS DOUBLE)
)
SELECT id, v,
avg(v) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
AS got
FROM t ORDER BY id;
```
Before: the third row returned `0.5` (frame `[1, 1]`, sum `1`, count
`2`).
After: the third row returns `1`.
Fix: `AggregateFunctionSum` and `AggregateFunctionAvg` now report
`supported_incremental_mode() == false` when the accumulator is a
floating-point type, so the analytic sink recomputes each sliding frame
from
its rows (the same approach PostgreSQL takes by not providing inverse
transition functions for `float4`/`float8`). Integer and decimal
accumulators
are exact and keep the incremental path. Frames that only grow
(`UNBOUNDED PRECEDING`) never removed rows and are unaffected.
Cost: sliding `ROWS` frames over `FLOAT`/`DOUBLE` columns now cost
O(frame size) per row instead of O(1). This is the trade-off for exact
results; wide sliding frames over floating-point columns will be slower.
### Release note
None
### Check List (For Author)
- Test:
- Unit Test:
`AnalyticSinkOperatorTest.SlidingRowsDouble{Avg,Sum}IgnoresRoundingOfOutgoingRow`,
`AggregateFunction{Avg,Sum}Test.test_incremental_mode_only_for_exact_sum`
- Regression test:
`query_p0/sql_functions/window_functions/test_window_float_sliding_frame`
- Behavior changed: Yes (sliding window sum/avg over FLOAT/DOUBLE now
return the exact per-frame result; wide frames are slower)
- Does this need documentation: No
https://claude.ai/code/session_01KSyhoeWWGWukpHP6MBTbEt
---
be/src/exprs/aggregate/aggregate_function_avg.h | 5 +-
be/src/exprs/aggregate/aggregate_function_sum.h | 8 +-
.../exec/operator/analytic_sink_operator_test.cpp | 79 +++++++++++
be/test/exprs/aggregate/agg_avg_test.cpp | 11 ++
.../{agg_avg_test.cpp => agg_sum_test.cpp} | 19 ++-
.../test_window_float_sliding_frame.out | 66 +++++++++
.../test_window_float_sliding_frame.groovy | 150 +++++++++++++++++++++
7 files changed, 332 insertions(+), 6 deletions(-)
diff --git a/be/src/exprs/aggregate/aggregate_function_avg.h
b/be/src/exprs/aggregate/aggregate_function_avg.h
index 795dce34ec3..2ea1ba8c020 100644
--- a/be/src/exprs/aggregate/aggregate_function_avg.h
+++ b/be/src/exprs/aggregate/aggregate_function_avg.h
@@ -305,7 +305,10 @@ public:
return std::make_shared<DataTypeFixedLengthObject>();
}
- bool supported_incremental_mode() const override { return true; }
+ // Floating-point accumulation is not exactly invertible: subtracting an
outgoing
+ // value cannot restore the rounding lost when it was added, so a value
that has
+ // left the frame would still distort later results. Recompute such frames
instead.
+ bool supported_incremental_mode() const override { return
!std::is_floating_point_v<DataType>; }
void execute_function_with_incremental(int64_t partition_start, int64_t
partition_end,
int64_t frame_start, int64_t
frame_end,
diff --git a/be/src/exprs/aggregate/aggregate_function_sum.h
b/be/src/exprs/aggregate/aggregate_function_sum.h
index 8eddb893ed9..8c00d1934ea 100644
--- a/be/src/exprs/aggregate/aggregate_function_sum.h
+++ b/be/src/exprs/aggregate/aggregate_function_sum.h
@@ -23,6 +23,7 @@
#include <stddef.h>
#include <memory>
+#include <type_traits>
#include <vector>
#include "common/compiler_util.h"
@@ -205,7 +206,12 @@ public:
return std::make_shared<DataTypeFixedLengthObject>();
}
- bool supported_incremental_mode() const override { return true; }
+ // Floating-point accumulation is not exactly invertible: subtracting an
outgoing
+ // value cannot restore the rounding lost when it was added, so a value
that has
+ // left the frame would still distort later results. Recompute such frames
instead.
+ bool supported_incremental_mode() const override {
+ return !std::is_floating_point_v<typename
PrimitiveTypeTraits<TResult>::CppType>;
+ }
NO_SANITIZE_UNDEFINED void execute_function_with_incremental(
int64_t partition_start, int64_t partition_end, int64_t
frame_start, int64_t frame_end,
diff --git a/be/test/exec/operator/analytic_sink_operator_test.cpp
b/be/test/exec/operator/analytic_sink_operator_test.cpp
index 4641a42cd56..a8537f12f6a 100644
--- a/be/test/exec/operator/analytic_sink_operator_test.cpp
+++ b/be/test/exec/operator/analytic_sink_operator_test.cpp
@@ -572,6 +572,85 @@ TEST_F(AnalyticSinkOperatorTest,
SlidingRowsSumRetainsOutgoingRowDuringEviction)
std::cout << "######### sliding rows sum eviction test end #########" <<
std::endl;
}
+// Floating-point sum/avg cannot be rolled back exactly: once 2^54 + 1 rounds
to 2^54,
+// removing 2^54 leaves 0 instead of 1. Sliding frames must be recomputed so
that a value
+// which already left the frame cannot distort the current result.
+TEST_F(AnalyticSinkOperatorTest,
SlidingRowsDoubleAvgIgnoresRoundingOfOutgoingRow) {
+ const std::vector<double> data_vals {18014398509481984.0, 1.0, 1.0};
+ const std::vector<double> expect_vals {18014398509481984.0,
9007199254740992.0, 1.0};
+ Initialize(data_vals.size());
+ create_operator(true, 1, "avg", {std::make_shared<DataTypeFloat64>()},
+ std::make_shared<DataTypeFloat64>());
+ sink->_agg_expr_ctxs.resize(1);
+ sink->_agg_expr_ctxs[0] =
+ MockSlotRef::create_mock_contexts(0,
std::make_shared<DataTypeFloat64>());
+ TAnalyticWindow temp_window;
+ temp_window.type = TAnalyticWindowType::ROWS;
+ TAnalyticWindowBoundary window_start;
+ window_start.type = TAnalyticWindowBoundaryType::PRECEDING;
+ window_start.__set_rows_offset_value(1);
+ temp_window.__set_window_start(window_start);
+ TAnalyticWindowBoundary window_end;
+ window_end.type = TAnalyticWindowBoundaryType::CURRENT_ROW;
+ temp_window.__set_window_end(window_end);
+ create_window_type(true, true, temp_window);
+ create_local_state();
+ EXPECT_FALSE(sink_local_state->_support_incremental_calculate);
+
+ {
+ Block block = ColumnHelper::create_block<DataTypeFloat64>(data_vals);
+ auto st = sink->sink(state.get(), &block, true);
+ EXPECT_TRUE(st.ok()) << st.msg();
+ }
+ {
+ Block block = ColumnHelper::create_block<DataTypeFloat64>({});
+ bool eos = false;
+ auto st = source->get_block(state.get(), &block, &eos);
+ EXPECT_TRUE(st.ok()) << st.msg();
+ EXPECT_TRUE(ColumnHelper::block_equal(
+ block, ColumnHelper::create_block<DataTypeFloat64>(data_vals,
expect_vals)))
+ << block.dump_data();
+ }
+}
+
+TEST_F(AnalyticSinkOperatorTest,
SlidingRowsDoubleSumIgnoresRoundingOfOutgoingRow) {
+ const std::vector<double> data_vals {18014398509481984.0, 1.0, 1.0};
+ const std::vector<double> expect_vals {18014398509481984.0,
18014398509481984.0, 2.0};
+ Initialize(data_vals.size());
+ create_operator(true, 1, "sum", {std::make_shared<DataTypeFloat64>()},
+ std::make_shared<DataTypeFloat64>());
+ sink->_agg_expr_ctxs.resize(1);
+ sink->_agg_expr_ctxs[0] =
+ MockSlotRef::create_mock_contexts(0,
std::make_shared<DataTypeFloat64>());
+ TAnalyticWindow temp_window;
+ temp_window.type = TAnalyticWindowType::ROWS;
+ TAnalyticWindowBoundary window_start;
+ window_start.type = TAnalyticWindowBoundaryType::PRECEDING;
+ window_start.__set_rows_offset_value(1);
+ temp_window.__set_window_start(window_start);
+ TAnalyticWindowBoundary window_end;
+ window_end.type = TAnalyticWindowBoundaryType::CURRENT_ROW;
+ temp_window.__set_window_end(window_end);
+ create_window_type(true, true, temp_window);
+ create_local_state();
+ EXPECT_FALSE(sink_local_state->_support_incremental_calculate);
+
+ {
+ Block block = ColumnHelper::create_block<DataTypeFloat64>(data_vals);
+ auto st = sink->sink(state.get(), &block, true);
+ EXPECT_TRUE(st.ok()) << st.msg();
+ }
+ {
+ Block block = ColumnHelper::create_block<DataTypeFloat64>({});
+ bool eos = false;
+ auto st = source->get_block(state.get(), &block, &eos);
+ EXPECT_TRUE(st.ok()) << st.msg();
+ EXPECT_TRUE(ColumnHelper::block_equal(
+ block, ColumnHelper::create_block<DataTypeFloat64>(data_vals,
expect_vals)))
+ << block.dump_data();
+ }
+}
+
TEST_F(AnalyticSinkOperatorTest, AggFunction5) {
int batch_size = 2;
Initialize(batch_size);
diff --git a/be/test/exprs/aggregate/agg_avg_test.cpp
b/be/test/exprs/aggregate/agg_avg_test.cpp
index 90847bda0aa..4c7858353ac 100644
--- a/be/test/exprs/aggregate/agg_avg_test.cpp
+++ b/be/test/exprs/aggregate/agg_avg_test.cpp
@@ -31,4 +31,15 @@ TEST_F(AggregateFunctionAvgTest, test_int64) {
execute(Block({ColumnHelper::create_column_with_name<DataTypeInt64>({1, 2,
3})}),
ColumnHelper::create_column_with_name<DataTypeFloat64>({2}));
}
+
+TEST_F(AggregateFunctionAvgTest, test_incremental_mode_only_for_exact_sum) {
+ create_agg("avg", false, {std::make_shared<DataTypeInt64>()},
+ std::make_shared<DataTypeFloat64>());
+ EXPECT_TRUE(agg_fn->supported_incremental_mode());
+
+ // Floating-point sums are not exactly invertible, so sliding frames must
be recomputed.
+ create_agg("avg", false, {std::make_shared<DataTypeFloat64>()},
+ std::make_shared<DataTypeFloat64>());
+ EXPECT_FALSE(agg_fn->supported_incremental_mode());
+}
} // namespace doris
diff --git a/be/test/exprs/aggregate/agg_avg_test.cpp
b/be/test/exprs/aggregate/agg_sum_test.cpp
similarity index 58%
copy from be/test/exprs/aggregate/agg_avg_test.cpp
copy to be/test/exprs/aggregate/agg_sum_test.cpp
index 90847bda0aa..b8761a1ab69 100644
--- a/be/test/exprs/aggregate/agg_avg_test.cpp
+++ b/be/test/exprs/aggregate/agg_sum_test.cpp
@@ -22,13 +22,24 @@
namespace doris {
-struct AggregateFunctionAvgTest : public AggregateFunctiontest {};
+struct AggregateFunctionSumTest : public AggregateFunctiontest {};
-TEST_F(AggregateFunctionAvgTest, test_int64) {
- create_agg("avg", false, {std::make_shared<DataTypeInt64>()},
+TEST_F(AggregateFunctionSumTest, test_int64) {
+ create_agg("sum", false, {std::make_shared<DataTypeInt64>()},
std::make_shared<DataTypeInt64>());
execute(Block({ColumnHelper::create_column_with_name<DataTypeInt64>({1, 2,
3})}),
- ColumnHelper::create_column_with_name<DataTypeFloat64>({2}));
+ ColumnHelper::create_column_with_name<DataTypeInt64>({6}));
+}
+
+TEST_F(AggregateFunctionSumTest, test_incremental_mode_only_for_exact_sum) {
+ create_agg("sum", false, {std::make_shared<DataTypeInt64>()},
+ std::make_shared<DataTypeInt64>());
+ EXPECT_TRUE(agg_fn->supported_incremental_mode());
+
+ // Floating-point sums are not exactly invertible, so sliding frames must
be recomputed.
+ create_agg("sum", false, {std::make_shared<DataTypeFloat64>()},
+ std::make_shared<DataTypeFloat64>());
+ EXPECT_FALSE(agg_fn->supported_incremental_mode());
}
} // namespace doris
diff --git
a/regression-test/data/query_p0/sql_functions/window_functions/test_window_float_sliding_frame.out
b/regression-test/data/query_p0/sql_functions/window_functions/test_window_float_sliding_frame.out
new file mode 100644
index 00000000000..a1d913228bf
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/window_functions/test_window_float_sliding_frame.out
@@ -0,0 +1,66 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !avg_double_preceding_1 --
+1 1.8014398509481984e+16 1.8014398509481984e+16
+2 0.5 9007199254740992
+3 0.5 0.5
+4 0.5 0.5
+
+-- !sum_double_preceding_1 --
+1 1.8014398509481984e+16 1.8014398509481984e+16
+2 0.5 1.8014398509481984e+16
+3 0.5 1
+4 0.5 1
+
+-- !avg_sum_float_preceding_1 --
+1 1.80143985E16 1.8014398509481984e+16 1.8014398509481984e+16
+2 0.5 9007199254740992 1.8014398509481984e+16
+3 0.5 0.5 1
+4 0.5 0.5 1
+
+-- !avg_sum_double_sign_flip --
+5 1.8014398509481984e+16 1.8014398509481984e+16 1.8014398509481984e+16
+6 0.5 9007199254740992 1.8014398509481984e+16
+7 -1.8014398509481984e+16 -9007199254740992 -1.8014398509481984e+16
+8 0.5 -9007199254740992 -1.8014398509481984e+16
+9 0.5 0.5 1
+
+-- !avg_sum_double_wider_frames --
+10 1.8014398509481984e+16 1.8014398509481984e+16 1.8014398509481984e+16
9007199254740992 1.8014398509481984e+16 6004799503160661
+11 0.5 9007199254740992 1.8014398509481984e+16
6004799503160661 1.8014398509481984e+16 0.5
+12 0.5 6004799503160661 1.8014398509481984e+16 0.5 1.5
0.5
+13 0.5 0.5 1.5 0.5 1.5 0.5
+14 0.5 0.5 1.5 0.5 1 0.5
+
+-- !avg_sum_nullable_partitioned --
+1 1 1.8014398509481984e+16 1.8014398509481984e+16
1.8014398509481984e+16
+10 3 \N \N \N
+11 3 0.5 0.5 0.5
+12 3 0.5 0.5 1
+13 3 0.5 0.5 1
+14 3 0.5 0.5 1
+2 1 0.5 9007199254740992 1.8014398509481984e+16
+3 1 0.5 0.5 1
+4 1 \N 0.5 0.5
+5 2 1.8014398509481984e+16 1.8014398509481984e+16
1.8014398509481984e+16
+6 2 0.5 9007199254740992 1.8014398509481984e+16
+7 2 -1.8014398509481984e+16 -9007199254740992
-1.8014398509481984e+16
+8 2 0.5 -9007199254740992 -1.8014398509481984e+16
+9 2 0.5 0.5 1
+
+-- !avg_sum_double_unbounded --
+1 1.8014398509481984e+16 1.8014398509481984e+16 1.8014398509481984e+16
+2 0.5 9007199254740992 1.8014398509481984e+16
+3 0.5 6004799503160661 1.8014398509481984e+16
+4 0.5 4503599627370496 1.8014398509481984e+16
+
+-- !avg_sum_exact_types --
+1 1.8014398509481984e+16 3.602879701896397e+16 36028797018963968
18014398509481984.0000 18014398509481984.000
+2 0.5 1.8014398509481984e+16 36028797018963969
9007199254740992.2500 18014398509481984.500
+3 0.5 1 2 0.5000 1.000
+4 0.5 1 2 0.5000 1.000
+
+-- !avg_double_cte --
+1 1.8014398509481984e+16 1.8014398509481984e+16
+2 1 9007199254740992
+3 1 1
+
diff --git
a/regression-test/suites/query_p0/sql_functions/window_functions/test_window_float_sliding_frame.groovy
b/regression-test/suites/query_p0/sql_functions/window_functions/test_window_float_sliding_frame.groovy
new file mode 100644
index 00000000000..15a630e0b1d
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/window_functions/test_window_float_sliding_frame.groovy
@@ -0,0 +1,150 @@
+// 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.
+
+// Sliding ROWS frames over floating-point columns must not carry the rounding
+// loss of a value that already left the frame into the current result.
+//
+// Expected values must not depend on how the compiler groups the additions
+// (the accumulators allow floating-point reassociation). The data therefore
+// uses 2^54 as the large value and 0.5 as the small value: half an ulp of 2^54
+// is 2, so as long as the small values inside a frame sum to less than 2 in
+// magnitude, any grouping rounds the frame sum back to 2^54, while removing
+// 2^54 incrementally would still leave 0 instead of the exact small sum.
+// Frames that mix the large positive and negative value hold two operands
+// only, because a two-operand IEEE addition has a single correctly rounded
+// result.
+suite("test_window_float_sliding_frame") {
+ sql "DROP TABLE IF EXISTS test_window_float_sliding_frame"
+ sql """
+ CREATE TABLE test_window_float_sliding_frame (
+ id INT,
+ grp INT,
+ v_double DOUBLE,
+ v_float FLOAT,
+ v_nullable DOUBLE NULL
+ ) ENGINE = OLAP
+ DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES ("replication_allocation" = "tag.location.default: 1")
+ """
+
+ // grp 1: a large value (2^54) leaves the frame, then only small values
remain.
+ // grp 2: sign flip of a large value around small values.
+ // grp 3: consecutive small values after a large one, with NULL in the
nullable column.
+ sql """
+ INSERT INTO test_window_float_sliding_frame VALUES
+ (1, 1, 18014398509481984, 18014398509481984, 18014398509481984),
+ (2, 1, 0.5, 0.5, 0.5),
+ (3, 1, 0.5, 0.5, 0.5),
+ (4, 1, 0.5, 0.5, NULL),
+ (5, 2, 18014398509481984, 18014398509481984, 18014398509481984),
+ (6, 2, 0.5, 0.5, 0.5),
+ (7, 2, -18014398509481984, -18014398509481984, -18014398509481984),
+ (8, 2, 0.5, 0.5, 0.5),
+ (9, 2, 0.5, 0.5, 0.5),
+ (10, 3, 18014398509481984, 18014398509481984, NULL),
+ (11, 3, 0.5, 0.5, 0.5),
+ (12, 3, 0.5, 0.5, 0.5),
+ (13, 3, 0.5, 0.5, 0.5),
+ (14, 3, 0.5, 0.5, 0.5)
+ """
+
+ // The reported case: after row 1 leaves the frame, avg over [0.5, 0.5]
must be 0.5.
+ order_qt_avg_double_preceding_1 """
+ SELECT id, v_double,
+ avg(v_double) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND
CURRENT ROW) AS got
+ FROM test_window_float_sliding_frame
+ WHERE grp = 1
+ """
+
+ order_qt_sum_double_preceding_1 """
+ SELECT id, v_double,
+ sum(v_double) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND
CURRENT ROW) AS got
+ FROM test_window_float_sliding_frame
+ WHERE grp = 1
+ """
+
+ order_qt_avg_sum_float_preceding_1 """
+ SELECT id, v_float,
+ avg(v_float) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND
CURRENT ROW) AS got_avg,
+ sum(v_float) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND
CURRENT ROW) AS got_sum
+ FROM test_window_float_sliding_frame
+ WHERE grp = 1
+ """
+
+ // Sign flip: a large positive value leaves, then a large negative value
+ // enters and leaves. Two-operand frames keep the expected sums exact.
+ order_qt_avg_sum_double_sign_flip """
+ SELECT id, v_double,
+ avg(v_double) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND
CURRENT ROW) AS got_avg,
+ sum(v_double) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND
CURRENT ROW) AS got_sum
+ FROM test_window_float_sliding_frame
+ WHERE grp = 2
+ """
+
+ // Consecutive small values after the large one, wider frames and
following bounds.
+ order_qt_avg_sum_double_wider_frames """
+ SELECT id, v_double,
+ avg(v_double) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND
CURRENT ROW) AS avg_p2,
+ sum(v_double) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND
CURRENT ROW) AS sum_p2,
+ avg(v_double) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING) AS avg_p1f1,
+ sum(v_double) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING) AS sum_p1f1,
+ avg(v_double) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND 2
FOLLOWING) AS avg_f2
+ FROM test_window_float_sliding_frame
+ WHERE grp = 3
+ """
+
+ // Nullable input: NULL rows are skipped while the frame keeps sliding.
+ order_qt_avg_sum_nullable_partitioned """
+ SELECT id, grp, v_nullable,
+ avg(v_nullable) OVER (PARTITION BY grp ORDER BY id ROWS BETWEEN
1 PRECEDING AND CURRENT ROW) AS got_avg,
+ sum(v_nullable) OVER (PARTITION BY grp ORDER BY id ROWS BETWEEN
1 PRECEDING AND CURRENT ROW) AS got_sum
+ FROM test_window_float_sliding_frame
+ """
+
+ // Frames that only grow keep the incremental path and stay unchanged.
+ order_qt_avg_sum_double_unbounded """
+ SELECT id, v_double,
+ avg(v_double) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) AS got_avg,
+ sum(v_double) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) AS got_sum
+ FROM test_window_float_sliding_frame
+ WHERE grp = 1
+ """
+
+ // Exact accumulators (integer / decimal) still use the incremental path
and stay exact.
+ order_qt_avg_sum_exact_types """
+ SELECT id, v_double,
+ avg(CAST(v_double * 2 AS BIGINT)) OVER (ORDER BY id ROWS
BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_bigint,
+ sum(CAST(v_double * 2 AS BIGINT)) OVER (ORDER BY id ROWS
BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_bigint,
+ avg(CAST(v_double AS DECIMAL(27, 3))) OVER (ORDER BY id ROWS
BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg_decimal,
+ sum(CAST(v_double AS DECIMAL(27, 3))) OVER (ORDER BY id ROWS
BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_decimal
+ FROM test_window_float_sliding_frame
+ WHERE grp = 1
+ """
+
+ // The reported shape without a table.
+ order_qt_avg_double_cte """
+ WITH t AS (
+ SELECT 1 AS id, CAST(18014398509481984 AS DOUBLE) AS v
+ UNION ALL SELECT 2, CAST(1 AS DOUBLE)
+ UNION ALL SELECT 3, CAST(1 AS DOUBLE)
+ )
+ SELECT id, v,
+ avg(v) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND CURRENT
ROW) AS got
+ FROM t
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
