Filter by single value does not work

[domoritz]

If there is a unique value, the profile shows it but when I click on it, the matching row is not shown.

[manzt]

Oh, interesting! This is an edge case I hadn't considered. I'm wondering how we could make the "unique" bar more generally selectable (not just for one value).

Right now the query for the "value counts" is a bit convoluted. I tried to push down the aggregation of uniqueness to duckdb so I'd return fewer entries to the client (e.g., in the case where the data is large and all values are unique).

quak/lib/clients/ValueCounts.ts

Lines 59 to 84 in 3dbcc8b

	override query(filter: Array<SQLExpression> = []): Query {
	let counts = Query
	.from({ source: this.#table })
	.select({
	value: sql`CASE
	WHEN ${column(this.#column)} IS NULL THEN '__quak_null__'
	ELSE ${column(this.#column)}
	END`,
	count: count(),
	})
	.groupby("value")
	.where(filter);
	return Query
	.with({ counts })
	.select(
	{
	key: sql`CASE
	WHEN "count" = 1 AND "value" != '__quak_null__' THEN '__quak_unique__'
	ELSE "value"
	END`,
	total: sum("count"),
	},
	)
	.from("counts")
	.groupby("key");
	}

So right now, the table I get back from duckdb looks like:

[{ "key": "A", "total": 500 }, { "key": "C", "total" 20" }, { "key": "__quak_unique__", "total": 1 }]

This structure means the client doesn't have direct access to the actual unique values.

I’m wondering whether we could rethink the approach to selecting unique values, making them more broadly selectable rather than limited to just a single unique value. For instance, we could use a predicate to exclude values like "A" and "C," allowing the "unique" bar to represent a broader, more flexible selection. Curious what you think.

[domoritz]

You could filer to the unique values with something like this maybe:

SELECT column_name
FROM table_name
GROUP BY column_name
HAVING COUNT(*) = 1;