[P2] Lens IN-list filter on PROJECT_ID unreliable — 73 IDs in, 3 rows out (SQL translation/truncation bug)

[seberslinux]

Summary

IN-list filtering on PROJECT_ID in lens returns a severe subset of expected results. Sending 73 Salesforce IDs returns only 3 rows. The same query rewritten with a slug-based IN clause returns 40 rows.

Reproduction

-- Returns 3 rows
WHERE PROJECT_ID IN ('a0941000002wBz9AAE', ... 72 more ...)

-- Returns 40 rows (same intent, slug-based)
WHERE PROJECT_SLUG IN ('tlf', 'cncf', ... equivalent list ...)

Hypothesis

SQL translation layer is truncating or mis-escaping the IN list when using Salesforce-format IDs. Likely a string quoting or list-length truncation bug in the MCP → SQL translation layer.

Impact

Any query that filters by PROJECT_ID with multiple values silently returns incomplete data. Callers have no way to know results are truncated.

Location

Lens · PROJECT_SEGMENTS — IN-list filter translation

Severity

P2 — data completeness issue, silent truncation

Suggested fix

Audit the SQL translation layer for IN-list handling with Salesforce-format IDs. Add a test covering IN lists of 10, 50, and 100 IDs.

---

Reported by Stephan Ebers — found while building NPS survey automation against the LFX APIs.