matrixorigin / matrixorigin/matrixone

[Bug]: HLL distinct aggregates count positive and negative zero separately

Open
#28,856 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

`APPROX_COUNT_DISTINCT(DOUBLE)` and `HLL_ADD_AGG(DOUBLE)` count `+0.0` and `-0.0` as different values even though they are peers under MatrixOne SQL equality and `COUNT(DISTINCT ...)`.

## Environment

- Branch: `main`
- Commit: `07fdd4ae0f80f287b93fc4b525fd296d5617abc7`
- Deployment: local standalone, 1 CN / 1 TN / 1 LogService

## Steps to reproduce

```sql
drop database if exists hll_signed_zero_repro;
create database hll_signed_zero_repro;
use hll_signed_zero_repro;
create table t(x double);
insert into t values
(cast('0' as double)),
(cast('-0' as double)),
(cast('0.0' as double)),
(cast('-0.0' as double));

select cast(x as varchar) as x_text,hex(serial(x)) as encoded
from t order by encoded;

select min(x=0) as all_equal_to_zero,
count(distinct x) as exact_distinct,
approx_count_distinct(x) as approximate_distinct,
hll_cardinality(hll_add_agg(x)) as hll_distinct
from t;
```

## Actual behavior

```text
x_text encoded
-0 217FFFFFFFFFFFFFFF
-0 217FFFFFFFFFFFFFFF
0 218000000000000000
0 218000000000000000

all_equal_to_zero exact_distinct approximate_distinct hll_distinct
1 1 2 2
```

## Expected behavior

Approximate distinct aggregation may have bounded estimation error at scale, but it must hash SQL-equivalent values into the same equivalence class. Since every row satisfies `x=0` and exact distinct count is 1, both HLL results should be 1.

## Stability and controls

- Reproducer: `3/3`, recreating the fixture for each run.
- Positive-zero duplicates alone produce cardinality 1.
- Fixed-scale Decimal spellings `1`, `1.0`, `1.00`, and `01.000` produce exact/HLL cardinality 1.
- A 500,000-row / 100,000-cardinality control gives normal HLL estimates within about 1.2% across integer, string, Decimal, and JSON inputs, and DOP 1/8 plus shard merge are deterministic.
- No crash or data mutation occurs.

## Evidence

- Tested commit: https://github.com/matrixorigin/matrixone/commit/07fdd4ae0f80f287b93fc4b525fd296d5617abc7
- Three independent executions produced the displayed result.

## Code analysis

`pkg/sql/colexec/aggexec/approx_count2.go` hashes `vectors[0].GetRawBytesAt(row)` directly in `hllSketch.Insert`. `+0.0` and `-0.0` have different stored bytes, while `pkg/container/types/compare.go` deliberately treats signed zeroes as SQL ordering/equality peers. The HLL input therefore needs type-aware SQL-equivalence canonicalization before hashing; normalizing both signed zero encodings is the immediate missing case.

## Regression coverage

Add aggregate UT and SQL coverage comparing `COUNT(DISTINCT x)`, `APPROX_COUNT_DISTINCT(x)`, and `HLL_CARDINALITY(HLL_ADD_AGG(x))` for signed zero, NaN payload peers where constructible, Decimal encodings, CHAR/VARCHAR equality classes, NULL, partial/final merge, and spill.

## Related

- #28853 tracks a separate allocation-account failure when HLL/Bitmap/approximate states enter multi-level spill.
- Duplicate searches covered signed/negative zero, HLL, `APPROX_COUNT_DISTINCT`, float equality, raw-byte hashing, and open/closed issues.

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.