sql: support different AS OF SYSTEM TIME timestamps inside a single statement

[knz]

Desired feature:

SELECT * FROM t AS OF SYSTEM TIME '-1h', u AS OF SYSTEM TIME '-2h'

Or, more usefully:

CREATE TABLE t AS SELECT * FROM t AS OF SYSTEM TIME '-1h'

The idea here is that currently, the same client.Txn object is used for an entire statement, but there is no reason why this should remain true.

Single read-only relational nodes, especially table readers, could use a different Txn object with a historical timestamp.

Epic: CRDB-9838

Jira issue: CRDB-4561

[jseldess]

@knz, should this be called out as a known limitation?

[knz]

That sounds right!

Not a new one though, so maybe worth calling out as such in previous versions too.

[awoods187]

This may also be relevant to providing access via bounded staleness in the event of quorum loss.

[ajwerner]

I can imagine CREATE TABLE t AS SELECT * FROM t AS OF SYSTEM TIME '-1h' or perhaps materializing a view as of a system time would be a neat way to work around some issues we've had in the past here. It's a little bit less subtle than generally mixing timestamps within a transaction.