matrixorigin / matrixorigin/matrixone

[Bug]: MEDIAN overflows on valid DECIMAL(38,s) values including singleton input

Open
#28,230 1 comment 0 reactions 1 assignee Claimed by @jiangxinmeng1 View on GitHub
kind/bug needs-triage
Dominant language
Go
Stars
1.9k
Forks
311
Avg merge
1d 3h
Merged PRs (30d)
768

Description

## 问题

`MEDIAN` 声明支持 `DECIMAL128`,但对 `DECIMAL(38,0)` 和 `DECIMAL(38,38)` 的合法值无法可靠返回结果。即使过滤后聚合中只有一个值,也会报 `Decimal128 scale overflow`。

## 环境

- MatrixOne: official `main`
- commit: `e7bb0572235ec4bac81eb098eb0ad8900f0065ab`

## 复现

```sql
create database median_decimal38_repro;
use median_decimal38_repro;

create table d0(v decimal(38,0));
insert into d0 values
(99999999999999999999999999999999999997),
(99999999999999999999999999999999999999);

select median(v) from d0;
select median(v) from d0
where v = 99999999999999999999999999999999999999;

create table d38(v decimal(38,38));
insert into d38 values
('0.10000000000000000000000000000000000000'),
('0.20000000000000000000000000000000000000');

select median(v) from d38;
select median(v) from d38 where v = 0.2;
```

实际错误包括:

```text
Decimal128 Add overflow: 99999999999999999999999999999999999997+99999999999999999999999999999999999999
Decimal128 scale overflow: 9999999999999999999999999999999999999.9
Decimal128 scale overflow: 2000000000000000000000000000000000000.0
```

单值中位数应等于该值,不需要加法,也不应溢出。

## 代码定位

`pkg/sql/colexec/aggexec/median2.go` 中:

- `MedianReturnType` 对所有 DECIMAL 输入返回 `DECIMAL(38, input_scale + 1)`,没有处理输入已经达到 precision/scale 38 的情况;
- 奇数/单值路径仍调用 `Scale(1)`;
- 偶数路径的 `averageMedianDecimal` 先执行 `v1.Add128(v2)`,可在数学平均值仍可表示时提前溢出。

## 期望

`MEDIAN` 对其声明支持的 `DECIMAL(38,s)` 输入返回可表示的精确结果;单值输入直接返回该值,偶数输入以避免中间加法溢出的方式计算平均值,并为 precision/scale 38 选择可承载结果的返回尺度。

以上场景重复执行 3 次,结果稳定。

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.