Support safe live-tail results for SELECT … STREAM

Context

ClickHouse 26.6 adds experimental continuous queries with SELECT … STREAM.
The browser already has useful building blocks for this:

• HTTP response streaming via resp.body.getReader()
• line-by-line JSONStringsEachRowWithProgress processing
• no wait_end_of_query in the streaming result path
• Stop already aborts the fetch and kills the query by query_id
• mid-stream exceptions are already surfaced from in-band {"exception"} rows

This issue is about making SELECT … STREAM safe and understandable in the browser.

It is not about enabling ClickHouse streaming settings. The browser should not inject
enable_streaming_queries or table settings. Those remain user/session/server choices.

Problem

A streaming query is open-ended. The current UI/result model mostly assumes a bounded query that eventually finishes.

Main gaps:

1. result.rows.push(...) grows memory without bound.
2. Every chunk can trigger a full table rebuild, which becomes janky over long streams.
3. The lifecycle reads like a normal query: “Running…”, then success/history. A stream should read as live, and Stop is the normal terminus.
4. The user needs a tailing experience: newest rows visible by default, with a way to pause auto-follow when scrolling up.

Proposed work

• Add detectStreaming(sql) as a pure core helper.
  • Detect STREAM as a table-expression modifier.
  • Avoid naive matching inside strings/comments where practical.
  • Prefer conservative detection; false negatives are safer than false positives.
• Add a bounded row buffer.
  • Keep last N rows, for example 10k.
  • Track total received rows.
  • Show “showing last N of M rows” when older rows are dropped.
• Throttle rendering.
  • Batch incoming chunks.
  • Render at a fixed cadence, for example around 10 fps.
  • Keep the throttle decision testable in core; keep rAF wiring in UI.
• Add table tail behavior.
  • Auto-scroll to newest rows while the user is already at the bottom.
  • Pause auto-follow when the user scrolls up.
  • Show “Jump to latest” when paused.
• Add live lifecycle UI.
  • Label state as “Streaming…” or “Live”.
  • Make Stop the primary action.
  • Show received row count and rows/sec.
  • Treat user Stop as a normal stream termination, not an error.
• Improve capability errors.
  • If the server rejects streaming as disabled/unsupported, show a friendly hint:
    ClickHouse streaming queries require a compatible server and user-enabled
    enable_streaming_queries.

Acceptance criteria

• A SELECT … STREAM query can run without unbounded memory growth.
• Long-running streams do not rebuild the full table on every chunk.
• The UI clearly distinguishes live streams from finite queries.
• Stop cleanly terminates the stream and does not look like a failure.
• The table follows the newest rows by default and supports pause/jump-to-latest.
• A finite STREAM LIMIT query can complete normally.
• Open streams are not automatically recorded as successful finite history entries.
• Core behavior has unit coverage: streaming detection, ring buffer, received/dropped counters, and render-throttle decisions.

Files likely touched

Open questions

1. Should live mode be auto-detected only, manually toggled, or both?
2. What should the default tail window be: 5k, 10k, or configurable?
3. Should STREAM LIMIT be recorded to history once it completes?
4. Should Stop on a stream be recorded anywhere, or treated as ephemeral?
5. How strict should detectStreaming(sql) be before we need a real parser?

Non-goals

• Enabling ClickHouse settings on behalf of the user.
• Table DDL changes for cursor/minmax optimization.
• Live chart animation or aggregation. That is tracked separately.

Start with src/core/format.js and src/core/stream.js, then trace the result lifecycle through src/ui/app.js and src/ui/results.js. Run the relevant tests under tests/unit/* before extending coverage for detection, buffering, counters, and throttling. Done means bounded live results, clear streaming lifecycle and tail controls, clean Stop behavior, finite-stream completion, and updated README guidance.