DECIMAL
更新时间：2025-10-17
DECIMAL
DECIMAL
描述
Plain Text
1DECIMAL(P[,S])
2高精度定点数，P 代表一共有多少个有效数字(precision)，S 代表小数位有多少数字(scale)。
3有效数字 P 的范围是 [1, MAX_P]，enable_decimal256=false时，MAX_P=38, enable_decimal256=true时，MAX_P=76。
4小数位数字数量 S 的范围是 [0, P]。
5
6P默认值是38，S默认是9（DECIMAL(38, 9)）。
7
8enable_decimal256 的默认值是false，设置为true 可以获得更加精确的结果，但是会带来一些性能损失。精度推演
DECIMAL 有一套很复杂的类型推演规则，针对不同的表达式，会应用不同规则进行精度推断。
四则运算
假定e1(p1, s1)和e2(p2, s2)是两个DECIMAL类型的数字，运算结果精度推演规则如下：
| 运算 | 结果precision | 结果scale | 溢出时结果precision | 溢出时结果scale | 中间结果e1类型 | 中间e2类型 |
|---|---|---|---|---|---|---|
| e1 + e2 | max(p1 - s1,p2 - s2) + max(s1, s2) + 1 | max(s1, s2) | MAX_P | min(MAX_P, p) - max(p1 - s1,p2 - s2) | 按照结果cast | 按照结果cast |
| e1 - e2 | max(p1 - s1,p2 - s2) + max(s1, s2) + 1 | max(s1, s2) | MAX_P | min(MAX_P, p) - max(p1 - s1,p2 - s2) | 按照结果cast | 按照结果cast |
| e1 * e2 | p1 + p2 | s1 + s2 | MAX_P |
|
不变 | 不变 |
| e1 / e2 | p1 + s2 + div_precision_increment |
s1 + div_precision_increment |
MAX_P |
|
p按照结果cast，s按照结果+e2.scale cast | |
| e1 % e2 | max(p1 - s1,p2 - s2) + max(s1, s2) | max(s1, s2) | MAX_P | min(MAX_P, p) - max(p1 - s1,p2 - s2) | 按照结果cast | 按照结果cast |
表格中计算溢出时结果scale的规则中，precision表示结果precision列中precision，scale表示结果scale列中的scale。
div_precision_increment是FE的配置参数。
decimal_overflow_scale是FE的session variable，表示当decimal数值计算结果精度溢出时，计算结果最多可保留的小数位数，默认值是6。
值得注意的是，除法计算的过程是DECIMAL(p1, s1) / DECIMAL(p2, s2) 先转换成 DECIMAL(p1 + s2 + div_precision_increment, s1 + s2 ) / DECIMAL(p2, s2) 然后再进行计算，所以可能会出现DECIMAL(p1 + s2 + div_precision_increment, s1 + div_precision_increment) 是满足 DECIMAL 的范围，但是由于先转换成了 DECIMAL(p1 + s2 + div_precision_increment, s1 + s2 )导致超出范围，PALO默认情况下会报Arithmetic overflow错误。
示例
乘法不溢出
SQL
1create table test_decimal_mul_no_overflow(f1 decimal(19, 9), f2 decimal(19, 9)) properties('replication_num'='1');
2insert into test_decimal_mul_no_overflow values('9999999999.999999999', '9999999999.999999999');根据乘法结果精度的计算规则，结果类型是decimal(38, 18)，不会溢出：
SQL
1explain verbose select f1, f2, f1 * f2 from test_decimal_mul_no_overflow;
2+----------------------------------------------------------------------------------------------------------------------------------+
3| Explain String(Nereids Planner) |
4+----------------------------------------------------------------------------------------------------------------------------------+
5| PLAN FRAGMENT 0 |
6| OUTPUT EXPRS: |
7| f1[#2] |
8| f2[#3] |
9| f1 * f2[#4] |
10| PARTITION: UNPARTITIONED |
11| |
12| HAS_COLO_PLAN_NODE: false |
13| |
14| VRESULT SINK |
15| MYSQL_PROTOCAL |
16| |
17| 1:VEXCHANGE |
18| offset: 0 |
19| distribute expr lists: |
20| tuple ids: 1N |
21| |
22| PLAN FRAGMENT 1 |
23| |
24| PARTITION: RANDOM |
25| |
26| HAS_COLO_PLAN_NODE: false |
27| |
28| STREAM DATA SINK |
29| EXCHANGE ID: 01 |
30| UNPARTITIONED |
31| |
32| 0:VOlapScanNode(59) |
33| TABLE: test.test_decimal_mul_no_overflow(test_decimal_mul_no_overflow), PREAGGREGATION: ON |
34| partitions=1/1 (test_decimal_mul_no_overflow) |
35| tablets=10/10, tabletList=1750210355691,1750210355693,1750210355695 ... |
36| cardinality=1, avgRowSize=3065.0, numNodes=1 |
37| pushAggOp=NONE |
38| desc: 0 |
39| final projections: f1[#0], f2[#1], (f1[#0] * f2[#1]) |
40| final project output tuple id: 1 |
41| tuple ids: 0 |
42| |
43| Tuples: |
44| TupleDescriptor{id=0, tbl=test_decimal_mul_no_overflow} |
45| SlotDescriptor{id=0, col=f1, colUniqueId=0, type=decimalv3(19,9), nullable=true, isAutoIncrement=false, subColPath=null} |
46| SlotDescriptor{id=1, col=f2, colUniqueId=1, type=decimalv3(19,9), nullable=true, isAutoIncrement=false, subColPath=null} |
47| |
48| TupleDescriptor{id=1, tbl=test_decimal_mul_no_overflow} |
49| SlotDescriptor{id=2, col=f1, colUniqueId=0, type=decimalv3(19,9), nullable=true, isAutoIncrement=false, subColPath=null} |
50| SlotDescriptor{id=3, col=f2, colUniqueId=1, type=decimalv3(19,9), nullable=true, isAutoIncrement=false, subColPath=null} |
51| SlotDescriptor{id=4, col=null, colUniqueId=null, type=decimalv3(38,18), nullable=true, isAutoIncrement=false, subColPath=null} |计算结果：
SQL
1select f1, f2, f1 * f2 from test_decimal_mul_no_overflow;
2+----------------------+----------------------+-----------------------------------------+
3| f1 | f2 | f1 * f2 |
4+----------------------+----------------------+-----------------------------------------+
5| 9999999999.999999999 | 9999999999.999999999 | 99999999999999999980.000000000000000001 |
6+----------------------+----------------------+-----------------------------------------+乘法溢出规则
SQL
1create table test_decimal_mul_overflow1(f1 decimal(20, 5), f2 decimal(21, 6)) properties('replication_num'='1');
2insert into test_decimal_mul_overflow1 values('12345678901234.12345', '12345678901234.123456');根据乘法结果精度的计算规则，默认配置下(enable_decimal256=false, decimal_overflow_scale=6, div_precision_increment=4)，正常计算出来的结果类型是decimal(41, 11)，precision溢出了，需要按照溢出时的规则重新计算：MAX_P - decimal_overflow_scale = 38 - 6 = 32，precision - scale = 41 - 11 = 30 < 32， 适用规则1，最终结果scale = min(11, 38 - 30) = 8，最终结果类型是decimal(38, 8)：
SQL
1 explain verbose select f1, f2, f1 * f2 from test_decimal_mul_overflow1;
2+---------------------------------------------------------------------------------------------------------------------------------+
3| Explain String(Nereids Planner) |
4+---------------------------------------------------------------------------------------------------------------------------------+
5| PLAN FRAGMENT 0 |
6| OUTPUT EXPRS: |
7| f1[#2] |
8| f2[#3] |
9| f1 * f2[#4] |
10| PARTITION: UNPARTITIONED |
11| |
12| HAS_COLO_PLAN_NODE: false |
13| |
14| VRESULT SINK |
15| MYSQL_PROTOCAL |
16| |
17| 1:VEXCHANGE |
18| offset: 0 |
19| distribute expr lists: |
20| tuple ids: 1N |
21| |
22| PLAN FRAGMENT 1 |
23| |
24| PARTITION: RANDOM |
25| |
26| HAS_COLO_PLAN_NODE: false |
27| |
28| STREAM DATA SINK |
29| EXCHANGE ID: 01 |
30| UNPARTITIONED |
31| |
32| 0:VOlapScanNode(59) |
33| TABLE: test.test_decimal_mul_overflow1(test_decimal_mul_overflow1), PREAGGREGATION: ON |
34| partitions=1/1 (test_decimal_mul_overflow1) |
35| tablets=10/10, tabletList=1750210355791,1750210355793,1750210355795 ... |
36| cardinality=1, avgRowSize=3115.0, numNodes=1 |
37| pushAggOp=NONE |
38| desc: 0 |
39| final projections: f1[#0], f2[#1], (f1[#0] * f2[#1]) |
40| final project output tuple id: 1 |
41| tuple ids: 0 |
42| |
43| Tuples: |
44| TupleDescriptor{id=0, tbl=test_decimal_mul_overflow1} |
45| SlotDescriptor{id=0, col=f1, colUniqueId=0, type=decimalv3(20,5), nullable=true, isAutoIncrement=false, subColPath=null} |
46| SlotDescriptor{id=1, col=f2, colUniqueId=1, type=decimalv3(21,6), nullable=true, isAutoIncrement=false, subColPath=null} |
47| |
48| TupleDescriptor{id=1, tbl=test_decimal_mul_overflow1} |
49| SlotDescriptor{id=2, col=f1, colUniqueId=0, type=decimalv3(20,5), nullable=true, isAutoIncrement=false, subColPath=null} |
50| SlotDescriptor{id=3, col=f2, colUniqueId=1, type=decimalv3(21,6), nullable=true, isAutoIncrement=false, subColPath=null} |
51| SlotDescriptor{id=4, col=null, colUniqueId=null, type=decimalv3(38,8), nullable=true, isAutoIncrement=false, subColPath=null} |计算结果：
SQL
1select f1, f2, f1 * f2 from test_decimal_mul_overflow1;
2+----------------------+-----------------------+--------------------------------------+
3| f1 | f2 | f1 * f2 |
4+----------------------+-----------------------+--------------------------------------+
5| 12345678901234.12345 | 12345678901234.123456 | 152415787532377393748917544.09724464 |
6+----------------------+-----------------------+--------------------------------------+如果调大decimal_overflow_scale的值，比如set decimal_overflow_scale=9;，按照溢出时的规则进行计算：MAX_P - decimal_overflow_scale = 38 - 9 = 29，precision - scale = 41 - 11 = 30 > 29，且scale > decimal_overflow_scale，适用溢出规则3，最终计算出的结果类型为：decimal(38,9)：
SQL
1explain verbose select f1, f2, f1 * f2 from test_decimal_mul_overflow1;
2+---------------------------------------------------------------------------------------------------------------------------------+
3| Explain String(Nereids Planner) |
4+---------------------------------------------------------------------------------------------------------------------------------+
5| PLAN FRAGMENT 0 |
6| OUTPUT EXPRS: |
7| f1[#2] |
8| f2[#3] |
9| f1 * f2[#4] |
10| PARTITION: UNPARTITIONED |
11| |
12| HAS_COLO_PLAN_NODE: false |
13| |
14| VRESULT SINK |
15| MYSQL_PROTOCAL |
16| |
17| 1:VEXCHANGE |
18| offset: 0 |
19| distribute expr lists: |
20| tuple ids: 1N |
21| |
22| PLAN FRAGMENT 1 |
23| |
24| PARTITION: RANDOM |
25| |
26| HAS_COLO_PLAN_NODE: false |
27| |
28| STREAM DATA SINK |
29| EXCHANGE ID: 01 |
30| UNPARTITIONED |
31| |
32| 0:VOlapScanNode(59) |
33| TABLE: test.test_decimal_mul_overflow1(test_decimal_mul_overflow1), PREAGGREGATION: ON |
34| partitions=1/1 (test_decimal_mul_overflow1) |
35| tablets=10/10, tabletList=1750210355963,1750210355965,1750210355967 ... |
36| cardinality=1, avgRowSize=3145.0, numNodes=1 |
37| pushAggOp=NONE |
38| desc: 0 |
39| final projections: f1[#0], f2[#1], (f1[#0] * f2[#1]) |
40| final project output tuple id: 1 |
41| tuple ids: 0 |
42| |
43| Tuples: |
44| TupleDescriptor{id=0, tbl=test_decimal_mul_overflow1} |
45| SlotDescriptor{id=0, col=f1, colUniqueId=0, type=decimalv3(20,5), nullable=true, isAutoIncrement=false, subColPath=null} |
46| SlotDescriptor{id=1, col=f2, colUniqueId=1, type=decimalv3(21,6), nullable=true, isAutoIncrement=false, subColPath=null} |
47| |
48| TupleDescriptor{id=1, tbl=test_decimal_mul_overflow1} |
49| SlotDescriptor{id=2, col=f1, colUniqueId=0, type=decimalv3(20,5), nullable=true, isAutoIncrement=false, subColPath=null} |
50| SlotDescriptor{id=3, col=f2, colUniqueId=1, type=decimalv3(21,6), nullable=true, isAutoIncrement=false, subColPath=null} |
51| SlotDescriptor{id=4, col=null, colUniqueId=null, type=decimalv3(38,9), nullable=true, isAutoIncrement=false, subColPath=null} |计算结果：
SQL
1select f1, f2, f1 * f2 from test_decimal_mul_overflow1;
2+----------------------+-----------------------+---------------------------------------+
3| f1 | f2 | f1 * f2 |
4+----------------------+-----------------------+---------------------------------------+
5| 12345678901234.12345 | 12345678901234.123456 | 152415787532377393748917544.097244643 |
6+----------------------+-----------------------+---------------------------------------+如果继续调大decimal_overflow_scale的值，比如set decimal_overflow_scale=12;，按照溢出时的规则进行计算：MAX_P - decimal_overflow_scale = 38 - 12 = 26，precision - scale = 41 - 11 = 30 > 26，且scale < decimal_overflow_scale，此时适用溢出规则2，最终计算出的结果类型为：decimal(38,11)：
SQL
1explain verbose select f1, f2, f1 * f2 from test_decimal_mul_overflow1;
2+----------------------------------------------------------------------------------------------------------------------------------+
3| Explain String(Nereids Planner) |
4+----------------------------------------------------------------------------------------------------------------------------------+
5| PLAN FRAGMENT 0 |
6| OUTPUT EXPRS: |
7| f1[#2] |
8| f2[#3] |
9| f1 * f2[#4] |
10| PARTITION: UNPARTITIONED |
11| |
12| HAS_COLO_PLAN_NODE: false |
13| |
14| VRESULT SINK |
15| MYSQL_PROTOCAL |
16| |
17| 1:VEXCHANGE |
18| offset: 0 |
19| distribute expr lists: |
20| tuple ids: 1N |
21| |
22| PLAN FRAGMENT 1 |
23| |
24| PARTITION: RANDOM |
25| |
26| HAS_COLO_PLAN_NODE: false |
27| |
28| STREAM DATA SINK |
29| EXCHANGE ID: 01 |
30| UNPARTITIONED |
31| |
32| 0:VOlapScanNode(59) |
33| TABLE: test.test_decimal_mul_overflow1(test_decimal_mul_overflow1), PREAGGREGATION: ON |
34| partitions=1/1 (test_decimal_mul_overflow1) |
35| tablets=10/10, tabletList=1750210355963,1750210355965,1750210355967 ... |
36| cardinality=1, avgRowSize=3145.0, numNodes=1 |
37| pushAggOp=NONE |
38| desc: 0 |
39| final projections: f1[#0], f2[#1], (f1[#0] * f2[#1]) |
40| final project output tuple id: 1 |
41| tuple ids: 0 |
42| |
43| Tuples: |
44| TupleDescriptor{id=0, tbl=test_decimal_mul_overflow1} |
45| SlotDescriptor{id=0, col=f1, colUniqueId=0, type=decimalv3(20,5), nullable=true, isAutoIncrement=false, subColPath=null} |
46| SlotDescriptor{id=1, col=f2, colUniqueId=1, type=decimalv3(21,6), nullable=true, isAutoIncrement=false, subColPath=null} |
47| |
48| TupleDescriptor{id=1, tbl=test_decimal_mul_overflow1} |
49| SlotDescriptor{id=2, col=f1, colUniqueId=0, type=decimalv3(20,5), nullable=true, isAutoIncrement=false, subColPath=null} |
50| SlotDescriptor{id=3, col=f2, colUniqueId=1, type=decimalv3(21,6), nullable=true, isAutoIncrement=false, subColPath=null} |
51| SlotDescriptor{id=4, col=null, colUniqueId=null, type=decimalv3(38,11), nullable=true, isAutoIncrement=false, subColPath=null} |计算结果：
SQL
1select f1, f2, f1 * f2 from test_decimal_mul_overflow1;
2+----------------------+-----------------------+-----------------------------------------+
3| f1 | f2 | f1 * f2 |
4+----------------------+-----------------------+-----------------------------------------+
5| 12345678901234.12345 | 12345678901234.123456 | 152415787532377393748917544.09724464320 |
6+----------------------+-----------------------+-----------------------------------------+乘法溢出时开启decimal256
SQL
1create table test_decimal_mul_overflow_dec256(f1 decimal(38, 19), f2 decimal(38, 19)) properties('replication_num'='1');
2insert into test_decimal_mul_overflow_dec256 values('9999999999999999999.9999999999999999999', '9999999999999999999.9999999999999999999');默认情况下(enable_decimal256=false)，相乘的实际结果会溢出，此时可以开启decimal256：set enable_decimal256=true，可以计算出精确的结果，结果类型为decimal(76, 38)：
SQL
1set enable_decimal256=true;
2
3elect f1, f2, f1 * f2 from test_decimal_mul_overflow_dec256;
4+-----------------------------------------+-----------------------------------------+-------------------------------------------------------------------------------+
5| f1 | f2 | f1 * f2 |
6+-----------------------------------------+-----------------------------------------+-------------------------------------------------------------------------------+
7| 9999999999999999999.9999999999999999999 | 9999999999999999999.9999999999999999999 | 99999999999999999999999999999999999998.00000000000000000000000000000000000001 |
8+-----------------------------------------+-----------------------------------------+-------------------------------------------------------------------------------+除法不溢出
SQL
1create table test_decimal_div_no_overflow(f1 decimal(19, 9), f2 decimal(19, 9)) properties('replication_num'='1');
2
3insert into test_decimal_div_no_overflow values('1234567890.123456789', '234567890.123456789');根据除法结果精度的计算规则，默认配置下(enable_decimal256=false, decimal_overflow_scale=6, div_precision_increment=4)，正常计算出来的结果类型是decimal(19 + 9 + 4, 9 + 4)，即decimal(32, 13)，precision没有溢出，结果的最终类型就是decimal(32, 13)：
SQL
1 explain verbose select f1, f2, f1 / f2 from test_decimal_div_no_overflow;
2+----------------------------------------------------------------------------------------------------------------------------------+
3| Explain String(Nereids Planner) |
4+----------------------------------------------------------------------------------------------------------------------------------+
5| PLAN FRAGMENT 0 |
6| OUTPUT EXPRS: |
7| f1[#2] |
8| f2[#3] |
9| f1 / f2[#4] |
10| PARTITION: UNPARTITIONED |
11| |
12| HAS_COLO_PLAN_NODE: false |
13| |
14| VRESULT SINK |
15| MYSQL_PROTOCAL |
16| |
17| 1:VEXCHANGE |
18| offset: 0 |
19| distribute expr lists: |
20| tuple ids: 1N |
21| |
22| PLAN FRAGMENT 1 |
23| |
24| PARTITION: RANDOM |
25| |
26| HAS_COLO_PLAN_NODE: false |
27| |
28| STREAM DATA SINK |
29| EXCHANGE ID: 01 |
30| UNPARTITIONED |
31| |
32| 0:VOlapScanNode(59) |
33| TABLE: test_decimal.test_decimal_div_no_overflow(test_decimal_div_no_overflow), PREAGGREGATION: ON |
34| partitions=1/1 (test_decimal_div_no_overflow) |
35| tablets=10/10, tabletList=1750210335692,1750210335694,1750210335696 ... |
36| cardinality=1, avgRowSize=0.0, numNodes=1 |
37| pushAggOp=NONE |
38| desc: 0 |
39| final projections: f1[#0], f2[#1], (CAST(f1[#0] AS decimalv3(32,22)) / f2[#1]) |
40| final project output tuple id: 1 |
41| tuple ids: 0 |
42| |
43| Tuples: |
44| TupleDescriptor{id=0, tbl=test_decimal_div_no_overflow} |
45| SlotDescriptor{id=0, col=f1, colUniqueId=0, type=decimalv3(19,9), nullable=true, isAutoIncrement=false, subColPath=null} |
46| SlotDescriptor{id=1, col=f2, colUniqueId=1, type=decimalv3(19,9), nullable=true, isAutoIncrement=false, subColPath=null} |
47| |
48| TupleDescriptor{id=1, tbl=test_decimal_div_no_overflow} |
49| SlotDescriptor{id=2, col=f1, colUniqueId=0, type=decimalv3(19,9), nullable=true, isAutoIncrement=false, subColPath=null} |
50| SlotDescriptor{id=3, col=f2, colUniqueId=1, type=decimalv3(19,9), nullable=true, isAutoIncrement=false, subColPath=null} |
51| SlotDescriptor{id=4, col=null, colUniqueId=null, type=decimalv3(32,13), nullable=true, isAutoIncrement=false, subColPath=null} |
52
53select f1, f2, f1 / f2 from test_decimal_div_no_overflow;
54+----------------------+---------------------+-----------------+
55| f1 | f2 | f1 / f2 |
56+----------------------+---------------------+-----------------+
57| 1234567890.123456789 | 234567890.123456789 | 5.2631580966759 |
58+----------------------+---------------------+-----------------+如果期望结果保留更多小数位，可以调大div_precision_increment, 比如admin set frontend config('div_precision_increment'='8');，则根据上述计算规则，计算出的结果类型为decimal(36, 17)：
SQL
1admin set frontend config('div_precision_increment'='8');
2explain verbose select f1, f2, f1 / f2 from test_decimal_div_no_overflow;
3+----------------------------------------------------------------------------------------------------------------------------------+
4| Explain String(Nereids Planner) |
5+----------------------------------------------------------------------------------------------------------------------------------+
6| PLAN FRAGMENT 0 |
7| OUTPUT EXPRS: |
8| f1[#2] |
9| f2[#3] |
10| f1 / f2[#4] |
11| PARTITION: UNPARTITIONED |
12| |
13| HAS_COLO_PLAN_NODE: false |
14| |
15| VRESULT SINK |
16| MYSQL_PROTOCAL |
17| |
18| 1:VEXCHANGE |
19| offset: 0 |
20| distribute expr lists: |
21| tuple ids: 1N |
22| |
23| PLAN FRAGMENT 1 |
24| |
25| PARTITION: RANDOM |
26| |
27| HAS_COLO_PLAN_NODE: false |
28| |
29| STREAM DATA SINK |
30| EXCHANGE ID: 01 |
31| UNPARTITIONED |
32| |
33| 0:VOlapScanNode(59) |
34| TABLE: test.test_decimal_div_no_overflow(test_decimal_div_no_overflow), PREAGGREGATION: ON |
35| partitions=1/1 (test_decimal_div_no_overflow) |
36| tablets=10/10, tabletList=1750210354910,1750210354912,1750210354914 ... |
37| cardinality=1, avgRowSize=3120.0, numNodes=1 |
38| pushAggOp=NONE |
39| desc: 0 |
40| final projections: f1[#0], f2[#1], (CAST(f1[#0] AS decimalv3(36,26)) / f2[#1]) |
41| final project output tuple id: 1 |
42| tuple ids: 0 |
43| |
44| Tuples: |
45| TupleDescriptor{id=0, tbl=test_decimal_div_no_overflow} |
46| SlotDescriptor{id=0, col=f1, colUniqueId=0, type=decimalv3(19,9), nullable=true, isAutoIncrement=false, subColPath=null} |
47| SlotDescriptor{id=1, col=f2, colUniqueId=1, type=decimalv3(19,9), nullable=true, isAutoIncrement=false, subColPath=null} |
48| |
49| TupleDescriptor{id=1, tbl=test_decimal_div_no_overflow} |
50| SlotDescriptor{id=2, col=f1, colUniqueId=0, type=decimalv3(19,9), nullable=true, isAutoIncrement=false, subColPath=null} |
51| SlotDescriptor{id=3, col=f2, colUniqueId=1, type=decimalv3(19,9), nullable=true, isAutoIncrement=false, subColPath=null} |
52| SlotDescriptor{id=4, col=null, colUniqueId=null, type=decimalv3(36,17), nullable=true, isAutoIncrement=false, subColPath=null} |计算结果：
SQL
1select f1, f2, f1 / f2 from test_decimal_div_no_overflow;
2+----------------------+---------------------+---------------------+
3| f1 | f2 | f1 / f2 |
4+----------------------+---------------------+---------------------+
5| 1234567890.123456789 | 234567890.123456789 | 5.26315809667590986 |
6+----------------------+---------------------+---------------------+除法溢出规则1
SQL
1create table test_decimal_div_overflow1(f1 decimal(27, 8), f2 decimal(27, 8)) properties('replication_num'='1');
2
3insert into test_decimal_div_overflow1 values('123456789012345678.12345678', '23456789012345678.12345678');根据除法结果精度的计算规则，默认配置下(enable_decimal256=false, decimal_overflow_scale=6, div_precision_increment=4)，正常计算出来的结果类型是decimal(27 + 8 + 4, 8 + 4)，即decimal(39, 12)。precision溢出了，需要按照溢出时的规则重新计算：MAX_P - decimal_overflow_scale = 38 - 6 = 32，precision - s1 = 39 - 8 = 31 < 32，所以适用溢出时scale规则1，结果scale为(MAX_P - (precision - s1))+ div_precision_increment = (38 - (39 - 8)) + 4 = 11，结果类型为decimal(38, 11)：
SQL
1explain verbose select f1, f2, f1 / f2 from test_decimal_div_overflow1;
2+----------------------------------------------------------------------------------------------------------------------------------+
3| Explain String(Nereids Planner) |
4+----------------------------------------------------------------------------------------------------------------------------------+
5| PLAN FRAGMENT 0 |
6| OUTPUT EXPRS: |
7| f1[#2] |
8| f2[#3] |
9| f1 / f2[#4] |
10| PARTITION: UNPARTITIONED |
11| |
12| HAS_COLO_PLAN_NODE: false |
13| |
14| VRESULT SINK |
15| MYSQL_PROTOCAL |
16| |
17| 1:VEXCHANGE |
18| offset: 0 |
19| distribute expr lists: |
20| tuple ids: 1N |
21| |
22| PLAN FRAGMENT 1 |
23| |
24| PARTITION: RANDOM |
25| |
26| HAS_COLO_PLAN_NODE: false |
27| |
28| STREAM DATA SINK |
29| EXCHANGE ID: 01 |
30| UNPARTITIONED |
31| |
32| 0:VOlapScanNode(59) |
33| TABLE: test_decimal.test_decimal_div_overflow1(test_decimal_div_overflow1), PREAGGREGATION: ON |
34| partitions=1/1 (test_decimal_div_overflow1) |
35| tablets=10/10, tabletList=1750210336251,1750210336253,1750210336255 ... |
36| cardinality=1, avgRowSize=3455.0, numNodes=1 |
37| pushAggOp=NONE |
38| desc: 0 |
39| final projections: f1[#0], f2[#1], (CAST(f1[#0] AS decimalv3(38,19)) / f2[#1]) |
40| final project output tuple id: 1 |
41| tuple ids: 0 |
42| |
43| Tuples: |
44| TupleDescriptor{id=0, tbl=test_decimal_div_overflow1} |
45| SlotDescriptor{id=0, col=f1, colUniqueId=0, type=decimalv3(27,8), nullable=true, isAutoIncrement=false, subColPath=null} |
46| SlotDescriptor{id=1, col=f2, colUniqueId=1, type=decimalv3(27,8), nullable=true, isAutoIncrement=false, subColPath=null} |
47| |
48| TupleDescriptor{id=1, tbl=test_decimal_div_overflow1} |
49| SlotDescriptor{id=2, col=f1, colUniqueId=0, type=decimalv3(27,8), nullable=true, isAutoIncrement=false, subColPath=null} |
50| SlotDescriptor{id=3, col=f2, colUniqueId=1, type=decimalv3(27,8), nullable=true, isAutoIncrement=false, subColPath=null} |
51| SlotDescriptor{id=4, col=null, colUniqueId=null, type=decimalv3(38,11), nullable=true, isAutoIncrement=false, subColPath=null} |计算结果：
SQL
1select f1, f2, f1 / f2 from test_decimal_div_overflow1;
2+-----------------------------+----------------------------+---------------+
3| f1 | f2 | f1 / f2 |
4+-----------------------------+----------------------------+---------------+
5| 123456789012345678.12345678 | 23456789012345678.12345678 | 5.26315809667 |
6+-----------------------------+----------------------------+---------------+如果调大decimal_overflow_scale的值，比如set decimal_overflow_scale=8;，按照溢出时的规则进行计算：MAX_P - decimal_overflow_scale = 38 - 8 = 30，precision - s1 = 39 - 8 = 31 > 30，且s1 == decimal_overflow_scale，适用溢出规则3，最终计算出的结果类型为：decimalv3(38,12)：
SQL
1set decimal_overflow_scale=8;
2explain verbose select f1, f2, f1 / f2 from test_decimal_div_overflow1;
3+----------------------------------------------------------------------------------------------------------------------------------+
4| Explain String(Nereids Planner) |
5+----------------------------------------------------------------------------------------------------------------------------------+
6| PLAN FRAGMENT 0 |
7| OUTPUT EXPRS: |
8| f1[#2] |
9| f2[#3] |
10| f1 / f2[#4] |
11| PARTITION: UNPARTITIONED |
12| |
13| HAS_COLO_PLAN_NODE: false |
14| |
15| VRESULT SINK |
16| MYSQL_PROTOCAL |
17| |
18| 1:VEXCHANGE |
19| offset: 0 |
20| distribute expr lists: |
21| tuple ids: 1N |
22| |
23| PLAN FRAGMENT 1 |
24| |
25| PARTITION: RANDOM |
26| |
27| HAS_COLO_PLAN_NODE: false |
28| |
29| STREAM DATA SINK |
30| EXCHANGE ID: 01 |
31| UNPARTITIONED |
32| |
33| 0:VOlapScanNode(59) |
34| TABLE: test.test_decimal_div_overflow1(test_decimal_div_overflow1), PREAGGREGATION: ON |
35| partitions=1/1 (test_decimal_div_overflow1) |
36| tablets=10/10, tabletList=1750210355035,1750210355037,1750210355039 ... |
37| cardinality=1, avgRowSize=3355.0, numNodes=1 |
38| pushAggOp=NONE |
39| desc: 0 |
40| final projections: f1[#0], f2[#1], (CAST(f1[#0] AS decimalv3(38,20)) / f2[#1]) |
41| final project output tuple id: 1 |
42| tuple ids: 0 |
43| |
44| Tuples: |
45| TupleDescriptor{id=0, tbl=test_decimal_div_overflow1} |
46| SlotDescriptor{id=0, col=f1, colUniqueId=0, type=decimalv3(27,8), nullable=true, isAutoIncrement=false, subColPath=null} |
47| SlotDescriptor{id=1, col=f2, colUniqueId=1, type=decimalv3(27,8), nullable=true, isAutoIncrement=false, subColPath=null} |
48| |
49| TupleDescriptor{id=1, tbl=test_decimal_div_overflow1} |
50| SlotDescriptor{id=2, col=f1, colUniqueId=0, type=decimalv3(27,8), nullable=true, isAutoIncrement=false, subColPath=null} |
51| SlotDescriptor{id=3, col=f2, colUniqueId=1, type=decimalv3(27,8), nullable=true, isAutoIncrement=false, subColPath=null} |
52| SlotDescriptor{id=4, col=null, colUniqueId=null, type=decimalv3(38,12), nullable=true, isAutoIncrement=false, subColPath=null} |计算结果：
SQL
1select f1, f2, f1 / f2 from test_decimal_div_overflow1;
2+-----------------------------+----------------------------+----------------+
3| f1 | f2 | f1 / f2 |
4+-----------------------------+----------------------------+----------------+
5| 123456789012345678.12345678 | 23456789012345678.12345678 | 5.263158096675 |
6+-----------------------------+----------------------------+----------------+除法溢出规则2
SQL
1create table test_decimal(f1 decimal(38, 4), f2 decimal(38, 4)) properties('replication_num'='1');
2
3insert into test_decimal values('123456789012345678.1234', '23456789012345678.1234');根据除法结果精度的计算规则，默认配置下(enable_decimal256=false, decimal_overflow_scale=6, div_precision_increment=4)，正常计算出来的结果类型是decimal(38 + 4 + 4, 4 + 4)，即decimal(46, 8)。precision溢出了，需要按照溢出时的规则重新计算：MAX_P - decimal_overflow_scale = 38 - 6 = 32，precision - s1 = 46 - 4 = 42 > 32，s1 = 4 < decimal_overflow_scale，所以适用溢出时scale规则2，结果scale为s1 + div_precision_increment = 4 + 4 = 8，结果类型为decimal(38, 8)：
SQL
1explain verbose select f1, f2, f1 / f2 from test_decimal;
2+---------------------------------------------------------------------------------------------------------------------------------+
3| Explain String(Nereids Planner) |
4+---------------------------------------------------------------------------------------------------------------------------------+
5| PLAN FRAGMENT 0 |
6| OUTPUT EXPRS: |
7| f1[#2] |
8| f2[#3] |
9| f1 / f2[#4] |
10| PARTITION: UNPARTITIONED |
11| |
12| HAS_COLO_PLAN_NODE: false |
13| |
14| VRESULT SINK |
15| MYSQL_PROTOCAL |
16| |
17| 1:VEXCHANGE |
18| offset: 0 |
19| distribute expr lists: |
20| tuple ids: 1N |
21| |
22| PLAN FRAGMENT 1 |
23| |
24| PARTITION: RANDOM |
25| |
26| HAS_COLO_PLAN_NODE: false |
27| |
28| STREAM DATA SINK |
29| EXCHANGE ID: 01 |
30| UNPARTITIONED |
31| |
32| 0:VOlapScanNode(59) |
33| TABLE: test_decimal.test_decimal(test_decimal), PREAGGREGATION: ON |
34| partitions=1/1 (test_decimal) |
35| tablets=10/10, tabletList=1750210334096,1750210334098,1750210334100 ... |
36| cardinality=1, avgRowSize=3250.0, numNodes=1 |
37| pushAggOp=NONE |
38| desc: 0 |
39| final projections: f1[#0], f2[#1], (CAST(f1[#0] AS decimalv3(38,12)) / f2[#1]) |
40| final project output tuple id: 1 |
41| tuple ids: 0 |
42| |
43| Tuples: |
44| TupleDescriptor{id=0, tbl=test_decimal} |
45| SlotDescriptor{id=0, col=f1, colUniqueId=0, type=decimalv3(38,4), nullable=true, isAutoIncrement=false, subColPath=null} |
46| SlotDescriptor{id=1, col=f2, colUniqueId=1, type=decimalv3(38,4), nullable=true, isAutoIncrement=false, subColPath=null} |
47| |
48| TupleDescriptor{id=1, tbl=test_decimal} |
49| SlotDescriptor{id=2, col=f1, colUniqueId=0, type=decimalv3(38,4), nullable=true, isAutoIncrement=false, subColPath=null} |
50| SlotDescriptor{id=3, col=f2, colUniqueId=1, type=decimalv3(38,4), nullable=true, isAutoIncrement=false, subColPath=null} |
51| SlotDescriptor{id=4, col=null, colUniqueId=null, type=decimalv3(38,8), nullable=true, isAutoIncrement=false, subColPath=null} |计算结果：
SQL
1select f1, f2, f1 / f2 from test_decimal;
2+-------------------------+------------------------+-------------+
3| f1 | f2 | f1 / f2 |
4+-------------------------+------------------------+-------------+
5| 123456789012345678.1234 | 23456789012345678.1234 | 5.26315809 |
6+-------------------------+------------------------+-------------+如果期望结果保留更多小数位，可以调大div_precision_increment, 比如admin set frontend config('div_precision_increment'='8');，则根据上述计算规则，计算出的结果类型为decimal(38, 12)：
SQL
1admin set frontend config('div_precision_increment'='8');
2
3explain verbose select f1, f2, f1 / f2 from test_decimal;
4+----------------------------------------------------------------------------------------------------------------------------------+
5| Explain String(Nereids Planner) |
6+----------------------------------------------------------------------------------------------------------------------------------+
7| PLAN FRAGMENT 0 |
8| OUTPUT EXPRS: |
9| f1[#2] |
10| f2[#3] |
11| f1 / f2[#4] |
12| PARTITION: UNPARTITIONED |
13| |
14| HAS_COLO_PLAN_NODE: false |
15| |
16| VRESULT SINK |
17| MYSQL_PROTOCAL |
18| |
19| 1:VEXCHANGE |
20| offset: 0 |
21| distribute expr lists: |
22| tuple ids: 1N |
23| |
24| PLAN FRAGMENT 1 |
25| |
26| PARTITION: RANDOM |
27| |
28| HAS_COLO_PLAN_NODE: false |
29| |
30| STREAM DATA SINK |
31| EXCHANGE ID: 01 |
32| UNPARTITIONED |
33| |
34| 0:VOlapScanNode(59) |
35| TABLE: test_decimal.test_decimal(test_decimal), PREAGGREGATION: ON |
36| partitions=1/1 (test_decimal) |
37| tablets=10/10, tabletList=1750210334096,1750210334098,1750210334100 ... |
38| cardinality=2, avgRowSize=3240.0, numNodes=1 |
39| pushAggOp=NONE |
40| desc: 0 |
41| final projections: f1[#0], f2[#1], (CAST(f1[#0] AS decimalv3(38,16)) / f2[#1]) |
42| final project output tuple id: 1 |
43| tuple ids: 0 |
44| |
45| Tuples: |
46| TupleDescriptor{id=0, tbl=test_decimal} |
47| SlotDescriptor{id=0, col=f1, colUniqueId=0, type=decimalv3(38,4), nullable=true, isAutoIncrement=false, subColPath=null} |
48| SlotDescriptor{id=1, col=f2, colUniqueId=1, type=decimalv3(38,4), nullable=true, isAutoIncrement=false, subColPath=null} |
49| |
50| TupleDescriptor{id=1, tbl=test_decimal} |
51| SlotDescriptor{id=2, col=f1, colUniqueId=0, type=decimalv3(38,4), nullable=true, isAutoIncrement=false, subColPath=null} |
52| SlotDescriptor{id=3, col=f2, colUniqueId=1, type=decimalv3(38,4), nullable=true, isAutoIncrement=false, subColPath=null} |
53| SlotDescriptor{id=4, col=null, colUniqueId=null, type=decimalv3(38,12), nullable=true, isAutoIncrement=false, subColPath=null} |计算结果：
SQL
1select f1, f2, f1 / f2 from test_decimal;
2+-------------------------+------------------------+-----------------+
3| f1 | f2 | f1 / f2 |
4+-------------------------+------------------------+-----------------+
5| 123456789012345678.1234 | 23456789012345678.1234 | 5.263158096675 |
6+-------------------------+------------------------+-----------------+如果开启decimal256(set enable_decimal256 = true;)，则正常计算出的结果precision没有溢出，结果类型是decimal(46, 8)：
SQL
1set enable_decimal256=true;
2
3admin set frontend config('div_precision_increment'='4');
4
5explain verbose select f1, f2, f1 / f2 from test_decimal;
6+---------------------------------------------------------------------------------------------------------------------------------+
7| Explain String(Nereids Planner) |
8+---------------------------------------------------------------------------------------------------------------------------------+
9| PLAN FRAGMENT 0 |
10| OUTPUT EXPRS: |
11| f1[#2] |
12| f2[#3] |
13| f1 / f2[#4] |
14| PARTITION: UNPARTITIONED |
15| |
16| HAS_COLO_PLAN_NODE: false |
17| |
18| VRESULT SINK |
19| MYSQL_PROTOCAL |
20| |
21| 1:VEXCHANGE |
22| offset: 0 |
23| distribute expr lists: |
24| tuple ids: 1N |
25| |
26| PLAN FRAGMENT 1 |
27| |
28| PARTITION: RANDOM |
29| |
30| HAS_COLO_PLAN_NODE: false |
31| |
32| STREAM DATA SINK |
33| EXCHANGE ID: 01 |
34| UNPARTITIONED |
35| |
36| 0:VOlapScanNode(59) |
37| TABLE: test_decimal.test_decimal(test_decimal), PREAGGREGATION: ON |
38| partitions=1/1 (test_decimal) |
39| tablets=10/10, tabletList=1750210334096,1750210334098,1750210334100 ... |
40| cardinality=2, avgRowSize=3240.0, numNodes=1 |
41| pushAggOp=NONE |
42| desc: 0 |
43| final projections: f1[#0], f2[#1], (CAST(f1[#0] AS decimalv3(46,12)) / f2[#1]) |
44| final project output tuple id: 1 |
45| tuple ids: 0 |
46| |
47| Tuples: |
48| TupleDescriptor{id=0, tbl=test_decimal} |
49| SlotDescriptor{id=0, col=f1, colUniqueId=0, type=decimalv3(38,4), nullable=true, isAutoIncrement=false, subColPath=null} |
50| SlotDescriptor{id=1, col=f2, colUniqueId=1, type=decimalv3(38,4), nullable=true, isAutoIncrement=false, subColPath=null} |
51| |
52| TupleDescriptor{id=1, tbl=test_decimal} |
53| SlotDescriptor{id=2, col=f1, colUniqueId=0, type=decimalv3(38,4), nullable=true, isAutoIncrement=false, subColPath=null} |
54| SlotDescriptor{id=3, col=f2, colUniqueId=1, type=decimalv3(38,4), nullable=true, isAutoIncrement=false, subColPath=null} |
55| SlotDescriptor{id=4, col=null, colUniqueId=null, type=decimalv3(46,8), nullable=true, isAutoIncrement=false, subColPath=null} |
56
57select f1, f2, f1 / f2 from test_decimal;
58+-------------------------+------------------------+-------------+
59| f1 | f2 | f1 / f2 |
60+-------------------------+------------------------+-------------+
61| 123456789012345678.1234 | 23456789012345678.1234 | 5.26315809 |
62+-------------------------+------------------------+-------------+除法溢出规则3
SQL
1create table test_decimal_div_overflow3(f1 decimal(38, 7), f2 decimal(38, 7)) properties('replication_num'='1');
2
3insert into test_decimal_div_overflow3 values('123456789012345678.1234567', '23456789012345678.1234567');根据除法结果精度的计算规则，默认配置下(enable_decimal256=false, decimal_overflow_scale=6, div_precision_increment=4)，正常计算出来的结果类型是decimal(38 + 7 + 4, 7 + 4)，即decimal(49, 11)。precision溢出了，需要按照溢出时的规则重新计算：MAX_P - decimal_overflow_scale = 38 - 6 = 32，precision - s1 = 49 - 7 = 42 > 32，s1 = 7 > decimal_overflow_scale，所以适用溢出时scale规则3，结果scale为decimal_overflow_scale + div_precision_increment = 6 + 4 = 10，结果类型为decimal(38, 10)：
SQL
1explain verbose select f1, f2, f1 / f2 from test_decimal_div_overflow3;
2+----------------------------------------------------------------------------------------------------------------------------------+
3| Explain String(Nereids Planner) |
4+----------------------------------------------------------------------------------------------------------------------------------+
5| PLAN FRAGMENT 0 |
6| OUTPUT EXPRS: |
7| f1[#2] |
8| f2[#3] |
9| f1 / f2[#4] |
10| PARTITION: UNPARTITIONED |
11| |
12| HAS_COLO_PLAN_NODE: false |
13| |
14| VRESULT SINK |
15| MYSQL_PROTOCAL |
16| |
17| 1:VEXCHANGE |
18| offset: 0 |
19| distribute expr lists: |
20| tuple ids: 1N |
21| |
22| PLAN FRAGMENT 1 |
23| |
24| PARTITION: RANDOM |
25| |
26| HAS_COLO_PLAN_NODE: false |
27| |
28| STREAM DATA SINK |
29| EXCHANGE ID: 01 |
30| UNPARTITIONED |
31| |
32| 0:VOlapScanNode(59) |
33| TABLE: test_decimal.test_decimal_div_overflow3(test_decimal_div_overflow3), PREAGGREGATION: ON |
34| partitions=1/1 (test_decimal_div_overflow3) |
35| tablets=10/10, tabletList=1750210336825,1750210336827,1750210336829 ... |
36| cardinality=1, avgRowSize=0.0, numNodes=1 |
37| pushAggOp=NONE |
38| desc: 0 |
39| final projections: f1[#0], f2[#1], (CAST(f1[#0] AS decimalv3(38,17)) / f2[#1]) |
40| final project output tuple id: 1 |
41| tuple ids: 0 |
42| |
43| Tuples: |
44| TupleDescriptor{id=0, tbl=test_decimal_div_overflow3} |
45| SlotDescriptor{id=0, col=f1, colUniqueId=0, type=decimalv3(38,7), nullable=true, isAutoIncrement=false, subColPath=null} |
46| SlotDescriptor{id=1, col=f2, colUniqueId=1, type=decimalv3(38,7), nullable=true, isAutoIncrement=false, subColPath=null} |
47| |
48| TupleDescriptor{id=1, tbl=test_decimal_div_overflow3} |
49| SlotDescriptor{id=2, col=f1, colUniqueId=0, type=decimalv3(38,7), nullable=true, isAutoIncrement=false, subColPath=null} |
50| SlotDescriptor{id=3, col=f2, colUniqueId=1, type=decimalv3(38,7), nullable=true, isAutoIncrement=false, subColPath=null} |
51| SlotDescriptor{id=4, col=null, colUniqueId=null, type=decimalv3(38,10), nullable=true, isAutoIncrement=false, subColPath=null} |计算结果：
SQL
1select f1, f2, f1 / f2 from test_decimal_div_overflow3;
2+----------------------------+---------------------------+--------------+
3| f1 | f2 | f1 / f2 |
4+----------------------------+---------------------------+--------------+
5| 123456789012345678.1234567 | 23456789012345678.1234567 | 5.2631580966 |
6+----------------------------+---------------------------+--------------+聚合运算
- SUM / MULTI_DISTINCT_SUM: SUM(DECIMAL(a, b)) -> DECIMAL(MAX_P, b).
- AVG: AVG(DECIMAL(a, b)) -> DECIMAL(MAX_P, max(b, 4)).
默认规则
除上述提到的函数外，其余表达式都使用默认规则进行精度推演。即对于表达式 expr(DECIMAL(a, b))，结果类型同样也是 DECIMAL(a, b)。
调整结果精度
不同用户对 DECIMAL 的精度要求各不相同，上述规则为当前 PALO 的默认行为，如果用户有不同的精度需求，可以通过以下方式进行精度调整：
- 如果期望的结果精度大于默认精度，可以通过调整入参精度来调整结果精度。例如用户期望计算
AVG(col)得到 DECIMAL(x, y) 作为结果，其中col的类型为 DECIMAL(a, b)，则可以改写表达式为AVG(CAST(col as DECIMAL(x, y)))。 - 如果期望的结果精度小于默认精度，可以通过对输出结果求近似得到想要的精度。例如用户期望计算
AVG(col)得到 DECIMAL(x, y) 作为结果，其中col的类型为 DECIMAL(a, b)，则可以改写表达式为ROUND(AVG(col), y)。
为什么需要 DECIMAL
PALO 中的 DECIMAL 是真正意义上的高精度定点数，Decimal 有以下核心优势：
- 可表示范围更大。DECIMAL 中 precision 和 scale 的取值范围都进行了明显扩充。
- 性能更高。老版本的 DECIMAL 在内存中需要占用 16 bytes，在存储中占用 12 bytes，而 DECIMAL 进行了自适应调整（如下表格）。
| precision | 占用空间（内存/磁盘） |
|---|---|
| 0 < precision <= 9 | 4 bytes |
| 9 < precision <= 18 | 8 bytes |
| 18 < precision <= 38 | 16 bytes |
| 38 < precision <= 76 | 32 bytes |
- 更完备的精度推演。对于不同的表达式，应用不同的精度推演规则对结果的精度进行推演。
keywords
Plain Text
1DECIMAL