matrixorigin / matrixorigin/matrixone
[Bug]: distinct aggregates split SQL-equal JSON numeric encodings
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
## Description
Distinct aggregates count SQL-equal JSON numeric encodings as different values. `GROUP BY` uses canonical JSON semantics and merges them, but `COUNT(DISTINCT ...)`, `APPROX_COUNT_DISTINCT`, and `HLL_ADD_AGG` use non-canonical argument bytes.
The problem occurs for a numeric JSON scalar and recursively inside objects and arrays.
## Environment
- MatrixOne: latest official `main`, commit `07fdd4ae0f80f287b93fc4b525fd296d5617abc7`
- Deployment: local standalone launch (isolated Log/TN/CN ports and data directory)
## Reproduction
```sql
drop database if exists json_distinct_canonical_minimal;
create database json_distinct_canonical_minimal;
use json_distinct_canonical_minimal;
create table t(id int, j json);
insert into t values
(1, '1'),
(2, '1.0'),
(3, '1e0'),
(4, '{"n":1}'),
(5, '{"n":1.0}'),
(6, '[1]'),
(7, '[1.0]');
select a.id, b.id, a.j = b.j
from t a join t b on (a.id,b.id) in ((1,2),(1,3),(4,5),(6,7));
select count(distinct j), approx_count_distinct(j),
hll_cardinality(hll_add_agg(j))
from t where id between 1 and 3;
select count(*)
from (select j from t where id between 1 and 3 group by j) g;
```
## Actual behavior
All four selected JSON pairs compare equal. For the scalar values `1`, `1.0`, and `1e0`:
```text
COUNT(DISTINCT j) 2
APPROX_COUNT_DISTINCT(j) 2
HLL_CARDINALITY(HLL_ADD_AGG(j)) 2
GROUP BY group count 1
```
The same 2-versus-1 split occurs independently for `{"n":1}` versus `{"n":1.0}` and `[1]` versus `[1.0]`.
The result reproduced identically in 3/3 fresh-table runs. A non-numeric JSON control containing `true`, `false`, `null`, `"1"`, and `1` returns five in every aggregate and grouping path.
## Expected behavior
Each SQL-equivalent numeric set should contribute one distinct value in exact and approximate distinct aggregation, consistent with `=` and `GROUP BY`.
## Code-path analysis
`pkg/common/hashmap/keycodec.AppendCanonicalJSON` canonicalizes JSON numbers recursively and is used by grouping keys. In contrast:
- exact distinct-aggregate key preparation in `pkg/sql/colexec/aggexec` copies raw argument bytes except for scalar floating signed zero;
- `APPROX_COUNT_DISTINCT` / `HLL_ADD_AGG` hash the raw vector bytes.
Both aggregate paths therefore distinguish JSON storage encodings that the comparison and grouping paths deliberately define as equal.
## Suggested regression coverage
- scalar, object, and array JSON numeric spellings (`1`, `1.0`, `1e0`);
- exact DISTINCT, approximate DISTINCT, HLL add/merge, DOP 1/>1, partial/final, and spill;
- nested composites, SQL NULL versus JSON null, and non-numeric JSON controls.
## Duplicate search
Open and closed issues were searched for JSON numeric canonicalization, JSON distinct aggregation, `1.0`, HLL, and `COUNT(DISTINCT)`; no matching report was found.
Contributor guide
Assessment
This issue has not been assessed yet.