This is an automated email from the ASF dual-hosted git repository.
jacktengg 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 b515db281e1 [fix](be) Preserve decimal precision in format_round
(#68397)
b515db281e1 is described below
commit b515db281e1dc95779c3de05c2db64331d37c77a
Author: TengJianPing <[email protected]>
AuthorDate: Thu Sep 24 11:54:06 2026 +0800
[fix](be) Preserve decimal precision in format_round (#68397)
Issue Number: None
Related PR: None
Problem Summary: format_round(CAST('1.44' AS DECIMAL(38, 2)), 20)
returns 1.00000000000000000044 instead of 1.44000000000000000000. The
decimal formatter uses a 32-bit power-of-ten helper to expand the
fraction and narrows the fraction to int when rounding and rendering it.
This also corrupts existing high-scale fractions. The DECIMALV2 caller
additionally discards all but three fractional digits.
Round with full-width integer arithmetic and append trailing zeroes in
the output buffer instead of scaling the fraction. Pass the complete
DECIMALV2 fraction. Cover decimal widths, constant and column arguments,
NULLs, negative values, rounding carries, and the 1024-place boundary.
Regenerate the two existing incorrect format_round expectations and
verify money_format retains exact two-place rounding.
Fix incorrect format_round results for high-precision decimals and
preserve all fractional digits of DECIMALV2 inputs before rounding.
- Test:
- ASAN BE build via ./build.sh --be -j48
- Unit Test: 7 tests passed via ./run-be-ut.sh --run
--filter='FormatRoundTest.*:function_money_format_test.*' -j48
- Regression test: test_format_round, test_format_round_decimal, and
test_money_format passed
- Manual test: original SQL reproduced before the fix and returned exact
decimal results after it; 59 generated SQL output rows checked against
Python Decimal ROUND_HALF_UP
- clang-format v16 and build hygiene passed
- clang-tidy attempted; blocked by the pre-existing unmatched NOLINTEND
in be/src/core/types.h:576
- Behavior changed: Yes, decimal formatting preserves the correct value
and requested fractional digits
- Does this need documentation: No
### What problem does this PR solve?
Issue Number: close #xxx
Related PR: #xxx
Problem Summary:
### Release note
None
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [ ] Regression test
- [ ] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
- Behavior changed:
- [ ] No.
- [ ] Yes. <!-- Explain the behavior change -->
- Does this need documentation?
- [ ] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR
should merge into -->
---
be/src/exprs/function/function_string_format.h | 72 ++++++----
.../exprs/function/function_format_round_test.cpp | 160 +++++++++++++++++++++
.../math_functions/test_format_round.out | 4 +-
.../math_functions/test_format_round_decimal.out | 136 ++++++++++++++++++
.../test_format_round_decimal256.out | 85 +++++++++++
.../test_format_round_decimal.groovy | 95 ++++++++++++
.../test_format_round_decimal256.groovy | 101 +++++++++++++
7 files changed, 626 insertions(+), 27 deletions(-)
diff --git a/be/src/exprs/function/function_string_format.h
b/be/src/exprs/function/function_string_format.h
index a65f0768826..e3e58d5acdc 100644
--- a/be/src/exprs/function/function_string_format.h
+++ b/be/src/exprs/function/function_string_format.h
@@ -22,6 +22,7 @@
#include <cmath>
#include <cstddef>
#include <cstring>
+#include <limits>
#include <string>
#include <type_traits>
@@ -38,6 +39,7 @@
#include "core/data_type/data_type_string.h"
#include "core/data_type/define_primitive_type.h"
#include "core/data_type/primitive_type.h"
+#include "core/extended_types.h"
#include "core/string_ref.h"
#include "core/types.h"
#include "core/value/decimalv2_value.h"
@@ -64,7 +66,7 @@ template <typename T>
char* SimpleItoaWithCommas(T i, char* buffer, int32_t buffer_size) {
char* p = buffer + buffer_size;
// Need to use unsigned T instead of T to correctly handle
- std::make_unsigned_t<T> n = i;
+ MakeUnsignedT<T> n = i;
if (i < 0) {
n = 0 - n;
}
@@ -250,6 +252,12 @@ constexpr size_t MAX_FORMAT_LEN_DEC128V3() {
return 2 * (1 + 39 + (39 / 3) + 3);
}
+constexpr size_t MAX_FORMAT_LEN_DEC256() {
+ // Decimal(76, 0)
+ // Double the size to match the other decimal buffers.
+ return 2 * (1 + 76 + (76 / 3) + 3);
+}
+
constexpr size_t MAX_FORMAT_LEN_INT64() {
// INT_MIN = -9223372036854775807
// Double the size to avoid some unexpected bug.
@@ -264,33 +272,27 @@ constexpr size_t MAX_FORMAT_LEN_INT128() {
template <typename T, size_t N>
StringRef do_format_round(FunctionContext* context, UInt32 scale, T int_value,
T frac_value,
Int32 decimal_places) {
- static_assert(std::is_integral<T>::value);
+ static_assert(IsIntegralV<T>);
const bool is_negative = int_value < 0 || frac_value < 0;
+ frac_value = frac_value < 0 ? -frac_value : frac_value;
// do round to frac_part based on decimal_places
if (static_cast<Int32>(scale) > decimal_places) {
- DCHECK(scale <= 38);
+ DCHECK(scale <= std::numeric_limits<T>::digits10);
// do rounding, so we need to reserve decimal_places + 1 digits
- auto multiplier =
- common::exp10_i128(std::abs(static_cast<int>(scale -
(decimal_places + 1))));
+ auto multiplier = decimal_scale_multiplier<T>(scale - (decimal_places
+ 1));
// do divide first to avoid overflow
- // after round frac_value will be positive by design
- frac_value = std::abs(static_cast<int>(frac_value / multiplier)) + 5;
- frac_value /= 10;
- } else if (scale < decimal_places && decimal_places > 0) {
- // since scale <= decimal_places, overflow is impossible
- frac_value = frac_value * common::exp10_i32(decimal_places - scale);
- }
+ frac_value = (frac_value / multiplier + 5) / 10;
+ scale = decimal_places;
- // Calculate power of 10 for decimal_places
- T decimal_power = common::exp10_i32(decimal_places);
- if (frac_value == decimal_power) {
- if (is_negative) {
- int_value -= 1;
- } else {
- int_value += 1;
+ if (frac_value == decimal_scale_multiplier<T>(scale)) {
+ if (is_negative) {
+ int_value -= 1;
+ } else {
+ int_value += 1;
+ }
+ frac_value = 0;
}
- frac_value = 0;
}
bool append_sign_manually = false;
@@ -318,11 +320,13 @@ StringRef do_format_round(FunctionContext* context,
UInt32 scale, T int_value, T
*(result_data + whole_decimal_str_len - (frac_str_len + 1)) = '.';
}
- // Convert fractional part to string with proper padding
- T remaining_frac = std::abs(static_cast<int>(frac_value));
- for (int i = 0; i <= decimal_places - 1; ++i) {
- *(result_data + whole_decimal_str_len - 1 - i) = '0' + (remaining_frac
% 10);
- remaining_frac /= 10;
+ // Pad on the right without scaling the value: decimal_places can be as
large as 1024.
+ const Int32 trailing_zeros = decimal_places - scale;
+ char* frac_end = result_data + whole_decimal_str_len - trailing_zeros;
+ memset(frac_end, '0', trailing_zeros);
+ for (UInt32 i = 0; i < scale; ++i) {
+ *--frac_end = '0' + (frac_value % 10);
+ frac_value /= 10;
}
return result;
}
@@ -762,6 +766,24 @@ struct FormatRoundDecimalImpl {
FormatRound::MAX_FORMAT_LEN_DEC128V3()>(
context, scale, whole_part, frac_part,
decimal_places);
+ result_column->insert_data(str.data, str.size);
+ }
+ } else if (const auto* decimal256_column =
+ check_and_get_column<ColumnDecimal256>(*col_ptr)) {
+ const UInt32 scale = decimal256_column->get_scale();
+ for (size_t i = 0; i < input_rows_count; i++) {
+ int32_t decimal_places =
arg_column_data_2[index_check_const<is_const>(i)];
+ if (decimal_places < 0 || decimal_places > 1024) {
+ return Status::InvalidArgument(
+ "The second argument is {}, it should be in range
[0, 1024].",
+ decimal_places);
+ }
+ const auto frac_part =
decimal256_column->get_fractional_part(i);
+ const auto whole_part =
decimal256_column->get_intergral_part(i);
+ StringRef str = FormatRound::do_format_round<wide::Int256,
+
FormatRound::MAX_FORMAT_LEN_DEC256()>(
+ context, scale, whole_part, frac_part, decimal_places);
+
result_column->insert_data(str.data, str.size);
}
} else {
diff --git a/be/test/exprs/function/function_format_round_test.cpp
b/be/test/exprs/function/function_format_round_test.cpp
new file mode 100644
index 00000000000..de6b3a2f395
--- /dev/null
+++ b/be/test/exprs/function/function_format_round_test.cpp
@@ -0,0 +1,160 @@
+// 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.
+
+#include <limits>
+
+#include "exec/common/int_exp.h"
+#include "exprs/function/function_test_util.h"
+
+namespace doris {
+using namespace ut_type;
+
+template <typename Decimal>
+void check_format_round_padding(PrimitiveType type, int precision) {
+ const InputTypeSet arguments = {{type, 2, precision}, TYPE_INT};
+ DataSet data;
+ for (int places : {2, 9, 10, 18, 19, 20, 38, 39, 1024}) {
+ const std::string zeros(places - 2, '0');
+ data.push_back({{Decimal(144), places}, std::string("1.44") + zeros});
+ data.push_back({{Decimal(-144), places}, std::string("-1.44") +
zeros});
+ data.push_back({{Decimal(-44), places}, std::string("-0.44") + zeros});
+ data.push_back({{Decimal(1), places}, std::string("0.01") + zeros});
+ data.push_back({{Decimal(0), places}, std::string("0.00") + zeros});
+ }
+ data.push_back({{Null(), 20}, Null()});
+ data.push_back({{Decimal(144), Null()}, Null()});
+ check_function_all_arg_comb<DataTypeString, true>("format_round",
arguments, data);
+}
+
+TEST(FormatRoundTest, decimal_padding) {
+ check_format_round_padding<Decimal32>(TYPE_DECIMAL32, 9);
+ check_format_round_padding<Decimal64>(TYPE_DECIMAL64, 18);
+ check_format_round_padding<Decimal128V3>(TYPE_DECIMAL128I, 20);
+ check_format_round_padding<Decimal128V3>(TYPE_DECIMAL128I, 38);
+}
+
+TEST(FormatRoundTest, decimal64_rounding) {
+ const InputTypeSet arguments = {{TYPE_DECIMAL64, 17, 18}, TYPE_INT};
+ const DataSet data = {
+ {{Decimal64(112499999999999999), 2}, std::string("1.12")},
+ {{Decimal64(-112499999999999999), 2}, std::string("-1.12")},
+ {{Decimal64(112499999999999999), 10}, std::string("1.1250000000")},
+ {{Decimal64(112345678901234567), 17},
std::string("1.12345678901234567")},
+ {{Decimal64(-112345678901234567), 16},
std::string("-1.1234567890123457")},
+ {{Decimal64(999999999999999999), 16},
std::string("10.0000000000000000")},
+ {{Decimal64(-999999999999999999), 10},
std::string("-10.0000000000")},
+ {{Decimal64(999999999999999999), 0}, std::string("10")},
+ {{Decimal64(-49999999999999999), 0}, std::string("-0")},
+ {{Decimal64(-50000000000000000), 0}, std::string("-1")}};
+ check_function_all_arg_comb<DataTypeString, true>("format_round",
arguments, data);
+}
+
+TEST(FormatRoundTest, decimal128_rounding) {
+ const InputTypeSet arguments = {{TYPE_DECIMAL128I, 38, 38}, TYPE_INT};
+ const Int128 fraction = common::exp10_i128(37) + 1234567890123456789LL;
+ const Int128 max_fraction = common::exp10_i128(38) - 1;
+ const DataSet data = {
+ {{Decimal128V3(fraction), 38},
std::string("0.10000000000000000001234567890123456789")},
+ {{Decimal128V3(-fraction), 38},
+ std::string("-0.10000000000000000001234567890123456789")},
+ {{Decimal128V3(fraction), 20},
std::string("0.10000000000000000001")},
+ {{Decimal128V3(-fraction), 37},
+ std::string("-0.1000000000000000000123456789012345679")},
+ {{Decimal128V3(max_fraction), 37}, std::string("1.") +
std::string(37, '0')},
+ {{Decimal128V3(-max_fraction), 37}, std::string("-1.") +
std::string(37, '0')},
+ {{Decimal128V3(max_fraction), 1024},
+ std::string("0.") + std::string(38, '9') + std::string(986,
'0')}};
+ check_function_all_arg_comb<DataTypeString, true>("format_round",
arguments, data);
+}
+
+TEST(FormatRoundTest, decimal256_padding) {
+ check_format_round_padding<Decimal256>(TYPE_DECIMAL256, 76);
+}
+
+TEST(FormatRoundTest, decimal256_rounding) {
+ const InputTypeSet arguments = {{TYPE_DECIMAL256, 76, 76}, TYPE_INT};
+ const auto max_fraction = common::exp10_i256(76) - 1;
+ DataSet data;
+ for (int places : {0, 1, 20, 38, 39, 40, 74, 75, 76, 77, 1024}) {
+ std::string expected;
+ if (places == 0) {
+ expected = "1";
+ } else if (places < 76) {
+ expected = std::string("1.") + std::string(places, '0');
+ } else {
+ expected = std::string("0.") + std::string(76, '9') +
std::string(places - 76, '0');
+ }
+ data.push_back({{Decimal256(max_fraction), places}, expected});
+ data.push_back({{Decimal256(-max_fraction), places}, std::string("-")
+ expected});
+ }
+ const auto fraction = common::exp10_i256(75) + 1234567890123456789LL;
+ data.push_back({{Decimal256(fraction), 76},
+ std::string("0.1") + std::string(56, '0') +
"1234567890123456789"});
+ data.push_back({{Decimal256(-fraction), 75},
+ std::string("-0.1") + std::string(56, '0') +
"123456789012345679"});
+ data.push_back({{Decimal256(fraction), 20},
std::string("0.10000000000000000000")});
+ data.push_back({{Decimal256(1), 76}, std::string("0.") + std::string(75,
'0') + "1"});
+ data.push_back({{Decimal256(-1), 75}, std::string("-0.") + std::string(75,
'0')});
+ check_function_all_arg_comb<DataTypeString, true>("format_round",
arguments, data);
+}
+
+TEST(FormatRoundTest, decimal256_large_integer) {
+ const auto max_value = common::exp10_i256(76) - 1;
+ std::string max_integer = "9";
+ std::string carry_integer = "100";
+ for (int i = 0; i < 25; ++i) {
+ max_integer += ",999";
+ }
+ for (int i = 0; i < 24; ++i) {
+ carry_integer += ",000";
+ }
+ check_function_all_arg_comb<DataTypeString, true>(
+ "format_round", {{TYPE_DECIMAL256, 0, 76}, TYPE_INT},
+ {{{Decimal256(max_value), 0}, max_integer},
+ {{Decimal256(-max_value), 20},
+ std::string("-") + max_integer + "." + std::string(20, '0')}});
+ check_function_all_arg_comb<DataTypeString, true>(
+ "format_round", {{TYPE_DECIMAL256, 2, 76}, TYPE_INT},
+ {{{Decimal256(max_value), 1}, carry_integer + ".0"},
+ {{Decimal256(-max_value), 0}, std::string("-") + carry_integer}});
+}
+
+TEST(FormatRoundTest, integer_padding) {
+ check_function_all_arg_comb<DataTypeString, true>(
+ "format_round", {TYPE_BIGINT, TYPE_INT},
+ {{{Int64(-1234), 20}, std::string("-1,234.") + std::string(20,
'0')},
+ {{std::numeric_limits<Int64>::min(), 0},
std::string("-9,223,372,036,854,775,808")}});
+ check_function_all_arg_comb<DataTypeString, true>(
+ "format_round", {TYPE_LARGEINT, TYPE_INT},
+ {{{Int128(1234), 1024}, std::string("1,234.") + std::string(1024,
'0')}});
+}
+
+TEST(FormatRoundTest, money_format_high_scale) {
+ check_function_all_arg_comb<DataTypeString, true>(
+ "money_format", {{TYPE_DECIMAL32, 8, 9}},
+ {{{Decimal32(112499999)}, std::string("1.12")}});
+ check_function_all_arg_comb<DataTypeString, true>(
+ "money_format", {{TYPE_DECIMAL64, 17, 18}},
+ {{{Decimal64(112499999999999999)}, std::string("1.12")}});
+ const Int128 value =
+ common::exp10_i128(37) + common::exp10_i128(36) +
common::exp10_i128(34) * 25 - 1;
+ check_function_all_arg_comb<DataTypeString, true>(
+ "money_format", {{TYPE_DECIMAL128I, 37, 38}},
+ {{{Decimal128V3(value)}, std::string("1.12")}});
+}
+
+} // namespace doris
diff --git
a/regression-test/data/query_p0/sql_functions/math_functions/test_format_round.out
b/regression-test/data/query_p0/sql_functions/math_functions/test_format_round.out
index 1d91fdc4869..e1124cb7a9b 100644
---
a/regression-test/data/query_p0/sql_functions/math_functions/test_format_round.out
+++
b/regression-test/data/query_p0/sql_functions/math_functions/test_format_round.out
@@ -67,7 +67,7 @@
-123.457
-- !format_round_18 --
-1.1949288396
+1.2345678900
-- !format_round_19 --
0.00
@@ -85,7 +85,7 @@
123.5
-- !format_round_24 --
-123.00000000002147026859
+123.45678900000000000000
-- !format_round_25 --
179,769,313,486,231,570,814,527,423,731,704,356,798,070,567,525,844,996,598,917,476,803,157,260,780,028,538,760,589,558,632,766,878,171,540,458,953,514,382,464,234,321,326,889,464,182,768,467,546,703,537,516,986,049,910,576,551,282,076,245,490,090,389,328,944,075,868,508,455,133,942,304,583,236,903,222,948,165,808,559,332,123,348,274,797,826,204,144,723,168,738,177,180,919,299,881,250,404,026,184,124,858,368.00
diff --git
a/regression-test/data/query_p0/sql_functions/math_functions/test_format_round_decimal.out
b/regression-test/data/query_p0/sql_functions/math_functions/test_format_round_decimal.out
new file mode 100644
index 00000000000..e2876c615bc
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/math_functions/test_format_round_decimal.out
@@ -0,0 +1,136 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !padding --
+1.44 -0.44 0.01
+
+-- !padding --
+1.440000000 -0.440000000 0.010000000
+
+-- !padding --
+1.4400000000 -0.4400000000 0.0100000000
+
+-- !padding --
+1.440000000000000000 -0.440000000000000000 0.010000000000000000
+
+-- !padding --
+1.4400000000000000000 -0.4400000000000000000 0.0100000000000000000
+
+-- !padding --
+1.44000000000000000000 -0.44000000000000000000 0.01000000000000000000
+
+-- !padding --
+1.44000000000000000000000000000000000000
-0.44000000000000000000000000000000000000
0.01000000000000000000000000000000000000
+
+-- !padding --
+1.440000000000000000000000000000000000000
-0.440000000000000000000000000000000000000
0.010000000000000000000000000000000000000
+
+-- !padding --
+1.44 -0.44 0.01
+
+-- !padding --
+1.440000000 -0.440000000 0.010000000
+
+-- !padding --
+1.4400000000 -0.4400000000 0.0100000000
+
+-- !padding --
+1.440000000000000000 -0.440000000000000000 0.010000000000000000
+
+-- !padding --
+1.4400000000000000000 -0.4400000000000000000 0.0100000000000000000
+
+-- !padding --
+1.44000000000000000000 -0.44000000000000000000 0.01000000000000000000
+
+-- !padding --
+1.44000000000000000000000000000000000000
-0.44000000000000000000000000000000000000
0.01000000000000000000000000000000000000
+
+-- !padding --
+1.440000000000000000000000000000000000000
-0.440000000000000000000000000000000000000
0.010000000000000000000000000000000000000
+
+-- !padding --
+1.44 -0.44 0.01
+
+-- !padding --
+1.440000000 -0.440000000 0.010000000
+
+-- !padding --
+1.4400000000 -0.4400000000 0.0100000000
+
+-- !padding --
+1.440000000000000000 -0.440000000000000000 0.010000000000000000
+
+-- !padding --
+1.4400000000000000000 -0.4400000000000000000 0.0100000000000000000
+
+-- !padding --
+1.44000000000000000000 -0.44000000000000000000 0.01000000000000000000
+
+-- !padding --
+1.44000000000000000000000000000000000000
-0.44000000000000000000000000000000000000
0.01000000000000000000000000000000000000
+
+-- !padding --
+1.440000000000000000000000000000000000000
-0.440000000000000000000000000000000000000
0.010000000000000000000000000000000000000
+
+-- !padding --
+1.44 -0.44 0.01
+
+-- !padding --
+1.440000000 -0.440000000 0.010000000
+
+-- !padding --
+1.4400000000 -0.4400000000 0.0100000000
+
+-- !padding --
+1.440000000000000000 -0.440000000000000000 0.010000000000000000
+
+-- !padding --
+1.4400000000000000000 -0.4400000000000000000 0.0100000000000000000
+
+-- !padding --
+1.44000000000000000000 -0.44000000000000000000 0.01000000000000000000
+
+-- !padding --
+1.44000000000000000000000000000000000000
-0.44000000000000000000000000000000000000
0.01000000000000000000000000000000000000
+
+-- !padding --
+1.440000000000000000000000000000000000000
-0.440000000000000000000000000000000000000
0.010000000000000000000000000000000000000
+
+-- !high_scale --
+1.12345678901234567 -1.1234567890123457 10.0000000000 -10.0000000000
0.10000000000000000001234567890123456789
-0.1000000000000000000123456789012345679
1.0000000000000000000000000000000000000 -1.0000000000000000000000000000000000000
+
+-- !money_format --
+1.12 1.12 1.12 1.12
+
+-- !column_places --
+1 1.44000000000000000000 1.44000000000000000000 1.44000000000000000000
+2 -0.440000000000000000000000000000000000000
-0.440000000000000000000000000000000000000
-0.440000000000000000000000000000000000000
+3
0.01000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
[...]
+4 10,000.0 10,000.0 10,000.0
+5 -10,000 -10,000 -10,000
+6 0.00000000000000000000 0.00000000000000000000 0.00000000000000000000
+7 \N \N \N
+8 \N \N \N
+
+-- !constant_places --
+1 1.44000000000000000000 1.44000000000000000000 1.44000000000000000000
+2 -0.44000000000000000000 -0.44000000000000000000 -0.44000000000000000000
+3 0.01000000000000000000 0.01000000000000000000 0.01000000000000000000
+4 9,999.99000000000000000000 9,999.99000000000000000000
9,999.99000000000000000000
+5 -9,999.99000000000000000000 -9,999.99000000000000000000
-9,999.99000000000000000000
+6 0.00000000000000000000 0.00000000000000000000 0.00000000000000000000
+7 \N \N \N
+8 1.44000000000000000000 1.44000000000000000000 1.44000000000000000000
+
+-- !constant_value --
+1 1.44000000000000000000
+2 1.440000000000000000000000000000000000000
+3
1.44000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
[...]
+4 1.4
+5 1
+6 1.44000000000000000000
+7 1.44000000000000000000
+8 \N
+
+-- !integer --
+-1,234.00000000000000000000
1,234.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
[...]
+
diff --git
a/regression-test/data/query_p0/sql_functions/math_functions/test_format_round_decimal256.out
b/regression-test/data/query_p0/sql_functions/math_functions/test_format_round_decimal256.out
new file mode 100644
index 00000000000..e598210a429
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/math_functions/test_format_round_decimal256.out
@@ -0,0 +1,85 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !repro --
+1.44000000000000000000
+
+-- !column_places --
+1 1.44000000000000000000
+2
-0.4400000000000000000000000000000000000000000000000000000000000000000000000000
+3
0.01000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
[...]
+4
100,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000.0
+5
-100,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
+6
0.00000000000000000000000000000000000000000000000000000000000000000000000000000
+7 \N
+8 \N
+
+-- !constant_places --
+1 1.44000000000000000000
+2 -0.44000000000000000000
+3 0.01000000000000000000
+4
99,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999.99000000000000000000
+5
-99,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999.99000000000000000000
+6 0.00000000000000000000
+7 \N
+8 1.44000000000000000000
+
+-- !constant_value --
+1 1.44000000000000000000
+2
1.4400000000000000000000000000000000000000000000000000000000000000000000000000
+3
1.44000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
[...]
+4 1.4
+5 1
+6
1.44000000000000000000000000000000000000000000000000000000000000000000000000000
+7 1.44000000000000000000
+8 \N
+
+-- !high_scale_columns --
+1 1
+10
-0.000000000000000000000000000000000000000000000000000000000000000000000000000
+2 1.00000000000000000000
+3
1.000000000000000000000000000000000000000000000000000000000000000000000000000
+4
-1.000000000000000000000000000000000000000000000000000000000000000000000000000
+5
0.9999999999999999999999999999999999999999999999999999999999999999999999999999
+6
0.99999999999999999999999999999999999999999999999999999999999999999999999999990000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
[...]
+7
0.1000000000000000000000000000000000000000000000000000000001234567890123456789
+8
-0.100000000000000000000000000000000000000000000000000000000123456789012345679
+9
0.0000000000000000000000000000000000000000000000000000000000000000000000000001
+
+-- !high_scale_constants --
+1 -1
+
+-- !high_scale_constants --
+1.0 -1.0
+
+-- !high_scale_constants --
+1.00000000000000000000 -1.00000000000000000000
+
+-- !high_scale_constants --
+1.00000000000000000000000000000000000000
-1.00000000000000000000000000000000000000
+
+-- !high_scale_constants --
+1.000000000000000000000000000000000000000
-1.000000000000000000000000000000000000000
+
+-- !high_scale_constants --
+1.0000000000000000000000000000000000000000
-1.0000000000000000000000000000000000000000
+
+-- !high_scale_constants --
+1.00000000000000000000000000000000000000000000000000000000000000000000000000
-1.00000000000000000000000000000000000000000000000000000000000000000000000000
+
+-- !high_scale_constants --
+1.000000000000000000000000000000000000000000000000000000000000000000000000000
-1.000000000000000000000000000000000000000000000000000000000000000000000000000
+
+-- !high_scale_constants --
+0.9999999999999999999999999999999999999999999999999999999999999999999999999999
-0.9999999999999999999999999999999999999999999999999999999999999999999999999999
+
+-- !high_scale_constants --
+0.99999999999999999999999999999999999999999999999999999999999999999999999999990
-0.99999999999999999999999999999999999999999999999999999999999999999999999999990
+
+-- !high_scale_constants --
+0.9999999999999999999999999999999999999999999999999999999999999999999999999999000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
[...]
+
+-- !large_integer --
+9,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999
-9,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999,999.00000000000000000000
+
+-- !smallest_decimal256 --
+1.44000000000000000000 1.00000000000000000000000000000000000000
+
diff --git
a/regression-test/suites/query_p0/sql_functions/math_functions/test_format_round_decimal.groovy
b/regression-test/suites/query_p0/sql_functions/math_functions/test_format_round_decimal.groovy
new file mode 100644
index 00000000000..4b913ccb20e
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/math_functions/test_format_round_decimal.groovy
@@ -0,0 +1,95 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_format_round_decimal", "p0") {
+ for (int precision : [9, 18, 20, 38]) {
+ for (int places : [2, 9, 10, 18, 19, 20, 38, 39]) {
+ order_qt_padding """
+ SELECT format_round(CAST('1.44' AS DECIMAL(${precision}, 2)),
${places}),
+ format_round(CAST('-0.44' AS DECIMAL(${precision}, 2)),
${places}),
+ format_round(CAST('0.01' AS DECIMAL(${precision}, 2)),
${places})
+ """
+ }
+ }
+
+ order_qt_high_scale """
+ SELECT format_round(CAST('1.12345678901234567' AS DECIMAL(18, 17)),
17),
+ format_round(CAST('-1.12345678901234567' AS DECIMAL(18, 17)),
16),
+ format_round(CAST('9.99999999999999999' AS DECIMAL(18, 17)),
10),
+ format_round(CAST('-9.99999999999999999' AS DECIMAL(18, 17)),
10),
+ format_round(CAST('0.10000000000000000001234567890123456789' AS
DECIMAL(38, 38)), 38),
+ format_round(CAST('-0.10000000000000000001234567890123456789'
AS DECIMAL(38, 38)), 37),
+ format_round(CAST('0.99999999999999999999999999999999999999' AS
DECIMAL(38, 38)), 37),
+ format_round(CAST('-0.99999999999999999999999999999999999999'
AS DECIMAL(38, 38)), 37)
+ """
+
+ order_qt_money_format """
+ SELECT money_format(CAST('1.1249' AS DOUBLE)),
+ money_format(CAST(CONCAT('1.124', REPEAT('9', 5)) AS DECIMAL(9,
8))),
+ money_format(CAST(CONCAT('1.124', REPEAT('9', 14)) AS
DECIMAL(18, 17))),
+ money_format(CAST(CONCAT('1.124', REPEAT('9', 34)) AS
DECIMAL(38, 37)))
+ """
+
+ sql "DROP TABLE IF EXISTS test_format_round_decimal"
+ sql """
+ CREATE TABLE test_format_round_decimal (
+ id INT,
+ d32 DECIMAL(9, 2),
+ d64 DECIMAL(18, 2),
+ d128 DECIMAL(38, 2),
+ places INT
+ ) DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES ("replication_num" = "1")
+ """
+ sql """
+ INSERT INTO test_format_round_decimal VALUES
+ (1, 1.44, 1.44, 1.44, 20),
+ (2, -0.44, -0.44, -0.44, 39),
+ (3, 0.01, 0.01, 0.01, 1024),
+ (4, 9999.99, 9999.99, 9999.99, 1),
+ (5, -9999.99, -9999.99, -9999.99, 0),
+ (6, 0, 0, 0, 20),
+ (7, NULL, NULL, NULL, 20),
+ (8, 1.44, 1.44, 1.44, NULL)
+ """
+ order_qt_column_places """
+ SELECT id, format_round(d32, places), format_round(d64, places),
+ format_round(d128, places)
+ FROM test_format_round_decimal
+ """
+ order_qt_constant_places """
+ SELECT id, format_round(d32, 20), format_round(d64, 20),
format_round(d128, 20)
+ FROM test_format_round_decimal
+ """
+ order_qt_constant_value """
+ SELECT id, format_round(CAST('1.44' AS DECIMAL(38, 2)), places)
+ FROM test_format_round_decimal
+ """
+ order_qt_integer """
+ SELECT format_round(CAST('-1234' AS BIGINT), 20),
+ format_round(CAST('1234' AS LARGEINT), 1024)
+ """
+
+ test {
+ sql "SELECT format_round(CAST('1.44' AS DECIMAL(38, 2)), -1)"
+ exception "it should be in range [0, 1024]"
+ }
+ test {
+ sql "SELECT format_round(CAST('1.44' AS DECIMAL(38, 2)), 1025)"
+ exception "it should be in range [0, 1024]"
+ }
+}
diff --git
a/regression-test/suites/query_p0/sql_functions/math_functions/test_format_round_decimal256.groovy
b/regression-test/suites/query_p0/sql_functions/math_functions/test_format_round_decimal256.groovy
new file mode 100644
index 00000000000..a2e6d9b83d2
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/math_functions/test_format_round_decimal256.groovy
@@ -0,0 +1,101 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_format_round_decimal256", "p0") {
+ sql "SET enable_decimal256 = true"
+
+ sql "DROP TABLE IF EXISTS test_format_round_decimal256"
+ sql """
+ CREATE TABLE test_format_round_decimal256 (
+ id INT,
+ f1 DECIMAL(76, 2),
+ places INT
+ ) DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES ("replication_num" = "1")
+ """
+ sql """
+ INSERT INTO test_format_round_decimal256 VALUES
+ (1, 1.44, 20),
+ (2, -0.44, 76),
+ (3, 0.01, 1024),
+ (4, CONCAT(REPEAT('9', 74), '.99'), 1),
+ (5, CONCAT('-', REPEAT('9', 74), '.99'), 0),
+ (6, 0, 77),
+ (7, NULL, 20),
+ (8, 1.44, NULL)
+ """
+ order_qt_repro "SELECT format_round(f1, 20) FROM
test_format_round_decimal256 WHERE id = 1"
+ order_qt_column_places """
+ SELECT id, format_round(f1, places) FROM test_format_round_decimal256
+ """
+ order_qt_constant_places """
+ SELECT id, format_round(f1, 20) FROM test_format_round_decimal256
+ """
+ order_qt_constant_value """
+ SELECT id, format_round(CAST('1.44' AS DECIMAL(76, 2)), places)
+ FROM test_format_round_decimal256
+ """
+
+ sql "DROP TABLE IF EXISTS test_format_round_decimal256_high_scale"
+ sql """
+ CREATE TABLE test_format_round_decimal256_high_scale (
+ id INT,
+ f1 DECIMAL(76, 76),
+ places INT
+ ) DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES ("replication_num" = "1")
+ """
+ sql """
+ INSERT INTO test_format_round_decimal256_high_scale VALUES
+ (1, CONCAT('0.', REPEAT('9', 76)), 0),
+ (2, CONCAT('0.', REPEAT('9', 76)), 20),
+ (3, CONCAT('0.', REPEAT('9', 76)), 75),
+ (4, CONCAT('-0.', REPEAT('9', 76)), 75),
+ (5, CONCAT('0.', REPEAT('9', 76)), 76),
+ (6, CONCAT('0.', REPEAT('9', 76)), 1024),
+ (7, CONCAT('0.1', REPEAT('0', 56), '1234567890123456789'), 76),
+ (8, CONCAT('-0.1', REPEAT('0', 56), '1234567890123456789'), 75),
+ (9, CONCAT('0.', REPEAT('0', 75), '1'), 76),
+ (10, CONCAT('-0.', REPEAT('0', 75), '1'), 75)
+ """
+ order_qt_high_scale_columns """
+ SELECT id, format_round(f1, places) FROM
test_format_round_decimal256_high_scale
+ """
+ for (int places : [0, 1, 20, 38, 39, 40, 74, 75, 76, 77, 1024]) {
+ order_qt_high_scale_constants """
+ SELECT format_round(CAST(CONCAT('0.', REPEAT('9', 76)) AS
DECIMAL(76, 76)), ${places}),
+ format_round(CAST(CONCAT('-0.', REPEAT('9', 76)) AS
DECIMAL(76, 76)), ${places})
+ """
+ }
+ order_qt_large_integer """
+ SELECT format_round(CAST(REPEAT('9', 76) AS DECIMAL(76, 0)), 0),
+ format_round(CAST(CONCAT('-', REPEAT('9', 76)) AS DECIMAL(76,
0)), 20)
+ """
+ order_qt_smallest_decimal256 """
+ SELECT format_round(CAST('1.44' AS DECIMAL(39, 2)), 20),
+ format_round(CAST(CONCAT('0.', REPEAT('9', 39)) AS DECIMAL(39,
39)), 38)
+ """
+
+ test {
+ sql "SELECT format_round(CAST('1.44' AS DECIMAL(76, 2)), -1)"
+ exception "it should be in range [0, 1024]"
+ }
+ test {
+ sql "SELECT format_round(CAST('1.44' AS DECIMAL(76, 2)), 1025)"
+ exception "it should be in range [0, 1024]"
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
