hyperdb-mcp: QUALIFY unsupported (42601); APPROX_COUNT_DISTINCT no speedup on string keys — surfaced by 299M-row benchmark

Start by reproducing the QUALIFY query and the exact versus approximate distinct queries against the benchmark table described in the issue. Determine whether the appropriate scope is QUALIFY support, dialect documentation, or optimization of constructed-string approximate aggregation. Done means the selected behavior is implemented or documented, with the reported queries and timings used to verify the result.

Summary

Follow-up from the same NYC TLC Yellow Taxi exercise as #1, this time a head-to-head benchmark of hyperdb-mcp vs DuckDB over the full normalized dataset (299,214,146 rows, 34 Parquet files, Jan + Jul × 2009–2025). hyperdb performed very well — it won 8 of 10 compute queries against DuckDB's own pre-loaded native table, by 1.25–3.9× — but the suite surfaced one dialect blocker and one perf papercut worth filing.

Environment: hyperdb-mcp (this repo), driven from Claude Code. Dialect is Salesforce Data Cloud SQL / Tableau Hyper. Data was warm in the daemon (one bench table). Timings are engine-side stats.elapsed_ms.

1. (Dialect gap) QUALIFY is not supported — 42601 syntax error

A standard "top-N per group" query using QUALIFY on a window function fails:

WITH z AS (
  SELECT data_year, "PULocationID", COUNT(*) trips
  FROM bench WHERE "PULocationID" IS NOT NULL
  GROUP BY data_year, "PULocationID"
)
SELECT data_year, "PULocationID", trips,
       ROW_NUMBER() OVER (PARTITION BY data_year ORDER BY trips DESC) rnk
FROM z
QUALIFY rnk <= 5;
-- server error (42601): ERROR: syntax error: got identifier, expected end-of-file

The documented subquery fallback works fine:

SELECT * FROM (
  WITH z AS (...) SELECT ..., ROW_NUMBER() OVER (...) rnk FROM z
) s
WHERE rnk <= 5;

Impact: Minor functionally (the fallback is mechanical), but QUALIFY is a very common idiom and is supported by DuckDB, Snowflake, BigQuery, and Databricks. The MCP server instructions / dialect card advertise extensive window-function support (row_number, rank, modified_rank, IGNORE NULLS, frame modes, etc.) but do not flag QUALIFY as absent, so a user reasonably expects it to work.

Suggestion: Support QUALIFY, or explicitly note its absence (and the subquery-wrap workaround) in the dialect card alongside the window-function list.

2. (Perf) APPROX_COUNT_DISTINCT gives almost no speedup when the argument is a constructed string

Exact vs approximate distinct over a 299M-row concatenated key were essentially the same:

SELECT COUNT(DISTINCT "PULocationID" || '-' || "DOLocationID") FROM bench;
-- ~2.31 s (median of 3)
SELECT APPROX_COUNT_DISTINCT("PULocationID" || '-' || "DOLocationID") FROM bench;
-- ~2.10 s (median of 3) — only ~9% faster

For comparison, on DuckDB the approximate path is dramatically cheaper than exact. Here the cost is dominated by materializing the BIGINT || '-' || BIGINT string for every row before the distinct/sketch step, so swapping exact for approximate barely moves the needle. Hashing the integer pair directly (e.g. "PULocationID" * 1000 + "DOLocationID") sidesteps it.

Impact: Low — this is a "shape your key as a number, not a string" lesson more than a bug. Filing because APPROX_COUNT_DISTINCT being ~equal to exact is a surprising result that suggests the string concat, not the cardinality estimation, dominates; worth a docs note or a look at whether string construction can be lazier under approximate aggregation.

Notes / context (not issues — positive findings)

• hyperdb beat DuckDB's pre-loaded native table on Q2/Q3/Q4/Q5/Q6/Q7/Q9/Q10 (global agg, low- and high-card GROUP BY, FILTER aggregates, PERCENTILE_CONT, window rank, EXTRACT grouping, GROUPING SETS) by 1.25–3.9×. GROUPING SETS (3.9×) and FILTER aggregates (2.6×) were the standouts.
• The only losses were Q1 (COUNT(*), ~28 ms vs ~8 ms — trivial fixed overhead, not throughput) and Q8 exact distinct (the string-key issue above).
• PERCENTILE_CONT ... WITHIN GROUP, FILTER (WHERE ...), GROUPING SETS, APPROX_COUNT_DISTINCT, and all window functions worked as documented.
• Build path: 34 load_files at concurrency 8 into distinct per-file tables, then one CREATE TABLE bench AS ... UNION ALL ... (= 61 s), was stable. (The unsafe pattern is concurrent appends to a single table — avoided here.)
• Mixed-case TLC column names (VendorID, PULocationID, DOLocationID, RatecodeID) require double-quoting (42703 otherwise) — expected for a PostgreSQL-family dialect, noted for completeness.