Two engine-side items found while building the BM25 search tool. Both are in RuntimeDB, tracked here so the AI-native-layer context stays in one place.
Verified behaviour: docs/engine-contract.md.
1. Ungrouped COUNT(*) is rejected — probable bug
SELECT COUNT(*) AS n FROM default.public.listings -- FAIL
SELECT COUNT(1) AS n FROM default.public.listings -- FAIL
SELECT 1 AS k, COUNT(*) AS n FROM default.public.listings -- FAIL
SELECT COUNT(id) AS n FROM default.public.listings -- OK -> 7535
SELECT MIN(id), MAX(id) FROM default.public.listings -- OK
SELECT AVG(review_scores_rating) FROM default.public.listings -- OK
SELECT room_type, COUNT(*) AS n FROM … GROUP BY room_type -- OK
SELECT n FROM (SELECT COUNT(id) AS n FROM …) t -- OK
Error: Invalid argument error: must either specify a row count or at least one column.
The pattern is that it fails only when the projection references no column at all — COUNT(*) and COUNT(1) do not, COUNT(id) does, and a GROUP BY column also satisfies it. Looks like projection pushdown producing an empty column set rather than intended behaviour.
Why it matters disproportionately: SELECT COUNT(*) is the single most common query an agent writes, and "how many rows are in X" is a first-contact question. We currently work around it by stating the constraint in the hotdata_execute_sql tool description, which is a documentation patch over what looks like a real defect.
Reproduced against api.hotdata.dev, a managed database with a DuckLake-backed table (7,535 rows).
2. No hybrid / rank-fusion primitive
A grep across the engine for hybrid/RRF/fusion finds nothing. Not needed yet — the plan is deliberately to build BM25+vector fusion client-side first and settle the parameters (RRF constant, dedup key, tie-breaking) empirically before pushing it down.
Recorded now because the eventual hybrid_search(...) UDTF is a genuine engine ask with measured motivation:
- A real 3-call agent run took 7,057 ms: 65% in model calls, 35% in tool calls.
- Each tool call was ~1,200 ms wall, against 49–79 ms of reported engine execution — so ~1,100 ms per call is round trip, not query time.
- A naive client-side hybrid therefore adds a full extra round trip. Issuing both searches concurrently recovers most of it; engine-side fusion removes it and avoids shipping two result sets to the client to merge.
It would serve hotdata-ibis and hotdata-dlt-destination as well, not just LangChain.
Note when this is picked up: fusion must combine ranks, not scores. Observed BM25 scores run ~8–11 and are unbounded; cosine distance is 0–2. The scales are not comparable, which is precisely why RRF is the right algorithm.
Not duplicated here
Two engine-side items found while building the BM25 search tool. Both are in RuntimeDB, tracked here so the AI-native-layer context stays in one place.
Verified behaviour:
docs/engine-contract.md.1. Ungrouped
COUNT(*)is rejected — probable bugError:
Invalid argument error: must either specify a row count or at least one column.The pattern is that it fails only when the projection references no column at all —
COUNT(*)andCOUNT(1)do not,COUNT(id)does, and aGROUP BYcolumn also satisfies it. Looks like projection pushdown producing an empty column set rather than intended behaviour.Why it matters disproportionately:
SELECT COUNT(*)is the single most common query an agent writes, and "how many rows are in X" is a first-contact question. We currently work around it by stating the constraint in thehotdata_execute_sqltool description, which is a documentation patch over what looks like a real defect.Reproduced against
api.hotdata.dev, a managed database with a DuckLake-backed table (7,535 rows).2. No hybrid / rank-fusion primitive
A grep across the engine for hybrid/RRF/fusion finds nothing. Not needed yet — the plan is deliberately to build BM25+vector fusion client-side first and settle the parameters (RRF constant, dedup key, tie-breaking) empirically before pushing it down.
Recorded now because the eventual
hybrid_search(...)UDTF is a genuine engine ask with measured motivation:It would serve
hotdata-ibisandhotdata-dlt-destinationas well, not just LangChain.Note when this is picked up: fusion must combine ranks, not scores. Observed BM25 scores run ~8–11 and are unbounded; cosine distance is 0–2. The scales are not comparable, which is precisely why RRF is the right algorithm.
Not duplicated here
select_best_indexhaving no cost model — runtimedb#481, acknowledged in the code's own comment.