matrixorigin / matrixorigin/matrixone

[Bug]: distinct aggregates split SQL-equal JSON numeric encodings

Open
#28,860 0 comments 0 reactions 1 assignee Claimed by @XuPeng-SH View on GitHub
kind/bug needs-triage
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

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.