Evaluate a subquery once and reuse its result (materialized "let")

[philrz]

There's currently no user-facing way to evaluate a subquery a single time and reuse its result across a query. Today a named subquery defined with let is not guaranteed to be evaluated only once. Such a subquery with side effects (e.g., one that invokes HTTP via from) re-evaluates on each reference, so the side effect happens repeatedly, which can have negative consequences.

Details

At the time this issue is being opened, super is at commit 6731d1f.

A common pattern when talking to authenticated HTTP APIs is to obtain a bearer token from a token endpoint and then attach it to many subsequent requests:

let token = (
  from "https://auth.example/oauth2/token" (
    method POST
    headers {"Content-Type": "application/x-www-form-urlencoded"}
    body f"client_id={BC_CLIENT_ID}&client_secret={BC_CLIENT_SECRET}&..."
    format json
  )
  | values access_token
)

-- ... many requests, each doing: headers {Authorization: f"Bearer {token}"} ...

There are currently limitations with the HTTP client in super that prevent creating headers dynamically using values in the data pipeline, but this issue is written with the assumption that we'll address that at some point. In the interim, a prototype branch auth-demo-prototype enabled this ability for some hacking a user was attempting, and this effect was observed during their work. Because each reference to the token re-triggers the fetch, a query that reads from several authenticated endpoints performs the token handshake many times over (in one of the user's examples, two endpoint reads caused eight separate token fetches). It's easy to imagine how a more ambitious query could trip a rate limiter or anomaly/DDoS detection on an API server.

SQL happens to offer this concept via materialized common table expressions (CTEs), e.g., in Postgres WITH x AS MATERIALIZED (…) guarantees the CTE is evaluated once and its result reused across references. The concept has two natural homes in SuperSQL: MATERIALIZED on its SQL CTEs (WITH) (which would directly match the precedent expected by legacy SQL users) and/or a materialized form of the let named subquery for pipe syntax.

Non-goal

Note that this is currently only envisioned as caching within a single super invocation with no persistence or server involvement. While brainstorming with the user, they did also imagine the concept of "global" caching, e.g., if persistent storage was available via a continuously-running super db serve, such a value could potentially be cached across query runs. But that would open up lifetime/TTL and invalidation concerns, so for now that's considered out of scope. If a user required functionality approximating that, of course they could redirect the output of one query to a file and read it for re-use via -I on a subsequent run of super.