HarperFast / HarperFast/harper

Support DISTINCT (non-duplicating) values in search conditions and SQL

Open
#587 0 comments 0 reactions 0 assignees View on GitHub
area:sql enhancement from-jira
Dominant language
JavaScript
Stars
89
Forks
10
Avg merge
2d 6h
Merged PRs (30d)
200

Description

Add the ability to return distinct (non-duplicating) values from search conditions and from the SQL/REST query layer — the equivalent of SQL `SELECT DISTINCT col FROM ...`.

## Motivation

Common when users want to enumerate the set of possible values for a single attribute (or a small projection). Today this requires fetching all records and de-duplicating client-side, which is wasteful when the cardinality is small.

## Ask

- Support `DISTINCT` in SQL queries (via the SQL translator / AlaSQL path).
- Surface a `distinct: true` (or equivalent) option in the `search_by_conditions` / `search_by_value` operations API so it's available without writing SQL.
- Optionally support it in REST/GraphQL query parameters where it makes sense.

## Acceptance criteria

- `SELECT DISTINCT attr FROM table` returns deduplicated rows in the projected columns.
- Operations API: a `distinct` flag on the search operations produces the same result without SQL.
- Documented in the search-conditions docs.

## Implementation notes

- For a single attribute already covered by an index, iterating the index uniquely is cheap. For arbitrary projections / cross-attribute dedup, the natural implementation is a streaming hash de-duplicator with a configurable bound (so an extremely high-cardinality projection doesn't blow memory silently).
- Behavior on `null` / `undefined` values should match SQL semantics (typically counted as a distinct value).

---

Tracked in Jira: [CORE-3034](https://harperdb.atlassian.net/browse/CORE-3034)

🤖 Filed by Claude on behalf of Kris.

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.