fix: stdio malformed-frame resilience, JSON() param fan-out, window/CTE test coverage - #702

Description

Three issues addressed on this branch:

Stdio MCP server survives malformed JSON-RPC frames (closes #701). The SDK's mcp.StdioTransport treats any JSON decode failure on stdin as fatal, so a single garbled frame terminated the server with exit code 1 (still the behaviour at go-sdk v1.7.0, so a library upgrade does not resolve it). A new resilient stdio transport (pkg/mcp_server/stdio_transport.go) reads newline-delimited frames itself: invalid JSON is answered with -32700 (id: null), valid JSON that is not a JSON-RPC message with -32600, and the session continues. Frames are bounded at 8 MiB (oversized frames are drained and answered with -32700), offending frames are logged at warn level truncated to 256 bytes, EOF remains the clean shutdown path, and all stdout writes share one mutex so the protocol stream cannot interleave. The issue #668 CRLF tolerance is folded into the same boundary, replacing the previous stdin CR-stripping pipe. Deliberate divergence, for the record: inbound JSON-RPC batch arrays are answered with -32600 rather than fanned out; batching was removed in MCP 2025-06-18 and the stock SDK kills modern-protocol sessions on batch frames anyway.

Execution-level coverage for window functions and CTEs (closes #698). Nine robot scenarios over new mocked github fixtures (contributors with tied counts, releases with distinct publish dates) cover ranking (ROW_NUMBER/RANK/DENSE_RANK plus tie detection), aggregates with OVER (running and grand totals), LAG/LEAD, frame clauses (FIRST_VALUE/LAST_VALUE unbounded, moving average), distribution (NTILE/PERCENT_RANK/CUME_DIST), PARTITION BY over a union, multiple CTEs in one statement, and DENSE_RANK over a grouped aggregate. Assertions are header shape or invariants (violation counts of zero, existence counts of exactly one), not literal fixture rows, so the same scenarios hold on both backends. The recursive-CTE scenario adjudicates the docs discrepancy: recursive CTEs parse but fail to plan (cannot resolve ServiceHandle), so the MCP dialect instructions are correct and the stackql.io language-spec docs overstate support (docs repo correction to follow).

JSON() structured SELECT params fan out through query transposition (closes #700). TransformSQLRawParameters discarded every FuncExpr parameter value, so a required structured param such as InstanceRequirements = JSON('{...}') never reached the request preparator and AWS query/ec2 operations with required structure params could not be called via SELECT. JSON() FuncExprs now pass through intact; any-sdk's existing AWSCanonical transposition and get_query_to_post_form_utf_8 translation produce the botocore canonical form on the wire. Other FuncExprs in WHERE are still discarded here, so the #689 evaluation path is unchanged. The issue's caveat about the local re-filter dropping rows for the consumed JSON() predicate did not materialise on either backend.

Type of change

• Bug fix (non-breaking change to fix a bug).
• Feature (non-breaking change to add functionality).
• Breaking change.
• Other (eg: documentation change). Please explain.

Issues referenced.

• Closes Server exits on a malformed JSON-RPC frame (denial-of-service) #701
• Closes [FEATURE] test: no black-box regression coverage for window functions or CTEs (documented language-spec features) #698
• Closes [FEATURE] Fan out JSON() structured values in SELECT WHERE clauses through query param transposition (AWS query/ec2 protocols) #700
• Related: [FEATURE] MCP stdio server exits silently when JSON-RPC messages are CRLF-terminated #668 (CRLF tolerance folded into the new transport), feat: mcp credential (re)sourcing via server.env_file + reload_credentials tool (#688) #689 (FuncExpr evaluation path preserved), [FEATURE] Extended support for batch mutation operations in the planner #683, [FEATURE] SQL functions in WHERE clause not evaluated for required parameters (start_time), and projected parameter/response columns return null #686

Evidence

• Server exits on a malformed JSON-RPC frame (denial-of-service) #701: unit tests over the new connection (malformed variants, five invalid-request shapes, CRLF, blank lines, size cap with chunked reads, unterminated final frame, write framing) plus four robot scenarios driving the built binary end to end: initialize -> malformed frame -> assert -32700/-32600 with id: null -> ping answered {} -> clean EOF exit 0. Local Mcp stdio subset: 7/7 pass including the pre-existing [FEATURE] MCP stdio server exits silently when JSON-RPC messages are CRLF-terminated #668 and [FEATURE] Support credential (re)sourcing at any point in the MCP server lifecycle #688 scenarios.
• [FEATURE] test: no black-box regression coverage for window functions or CTEs (documented language-spec features) #698: all scenario queries verified by hand against both the sqlite3 and postgres backends (postgres 16 via container) before being wired into robot; local robot run of the new scenarios: 10/10 pass.
• [FEATURE] Fan out JSON() structured values in SELECT WHERE clauses through query param transposition (AWS query/ec2 protocols) #700: wire body captured from the mock confirms the botocore canonical form: Action=GetInstanceTypesFromInstanceRequirements&...&InstanceRequirements.MemoryMiB.Min=4096&InstanceRequirements.VCpuCount.Max=8&InstanceRequirements.VCpuCount.Min=2. The mock route only matches when the fanned-out keys arrive, so the robot scenario fails without the fix. Verified on both backends manually.
• Full local run of the mocked functional suite: 338 tests, 336 passed; the only 2 failures are the documented environmental mTLS key-permission failures on /mnt/c (WSL), unrelated to these changes.

Checklist:

• A full round of testing has been completed, and there are no test failures as a result of these changes.
• The changes are covered with functional and/or integration robot testing.
• The changes work on all supported platforms.
• Unit tests pass locally, as per the developer guide.
• Robot tests pass locally, as per the developer guide.
• Linter passes locally, as per the developer guide.

Variations

• Platform coverage relies on the CI matrix; local validation was linux (WSL). The stdio transport change is platform-sensitive (CRLF handling) and is covered by the existing [FEATURE] MCP stdio server exits silently when JSON-RPC messages are CRLF-terminated #668 LF/CRLF scenarios plus the new Server exits on a malformed JSON-RPC frame (denial-of-service) #701 scenarios.
• The postgres-backend robot path requires the docker execution platform, so postgres coverage for the new [FEATURE] test: no black-box regression coverage for window functions or CTEs (documented language-spec features) #698/[FEATURE] Fan out JSON() structured values in SELECT WHERE clauses through query param transposition (AWS query/ec2 protocols) #700 scenarios was validated locally by hand against a postgres 16 container (identical results to sqlite3) and lands in CI via the existing postgres_tcp job, which runs the same suite.

Tech Debt

Nothing accrued by this change set itself. Two pre-existing defect families were surfaced while writing the #698 scenarios and will be raised as separate issues rather than expanded here:

1. Planner: a CTE referencing an earlier CTE panics (nil pointer) in the plan builder; multi-condition CASE over derived-table/CTE aggregates, outer references to COUNT(*) / COUNT(*) OVER () aliases, arithmetic between aggregates over a derived table/CTE, and CTE references inside derived tables all fail query rewriting. The new scenarios are written around these limits and document them in the suite header.
2. stackql.io language-spec docs claim recursive CTE support; the recursive-CTE scenario proves otherwise (docs repo change, not this repo).