Support AS OF SYSTEM TIME syntax

[shenli]

Feature Request

Is your feature request related to a problem? Please describe:
Reading historical data requires setting a system variable. This has two problems:

1. It is not convenient to use a separate SET statement to set the snapshot version.
2. Session context is weird in go - since the SQL driver will pool connections unless you start an explicit transaction.

Describe the feature you'd like:
Support the AS OF SYSTEM TIME syntax like sql-server.
CockroachDB and BigQuery also supports syntax like that.

Describe alternatives you've considered:
No

Teachability, Documentation, Adoption, Migration Strategy:
No

[wwar]

CockroachDB also has a handy helper-function: experimental_follower_read_timestamp().

This automatically enables follower reads, up to the most recent point possible. I think it's a lot more convenient than explicitly enabling follower reads.

[kennytm]

If we support MySQL 8.0's SET_VAR optimizer hint (#7968), we could have the following alternative syntax:

SELECT /*+ SET_VAR(tidb_snapshot = 1234567890) */ * FROM t;

[zz-jason]

If we support MySQL 8.0's SET_VAR optimizer hint (#7968), we could have the following alternative syntax:

SELECT /*+ SET_VAR(tidb_snapshot = 1234567890) */ * FROM t;

We can regard this hint as a workaround temporally. The AS OF SYSTEM TIME syntax can be supported in non-DML or DQL statements. For example, in AS OF SYSTEM TIME - CockroachDB:

The AS OF SYSTEM TIME clause is supported in multiple SQL contexts, including but not limited to:

In SELECT clauses, at the very end of the FROM sub-clause.
In BACKUP, after the parameters of the TO sub-clause.
In RESTORE, after the parameters of the FROM sub-clause.
In BEGIN, after the BEGIN keyword.
In SET, after the SET TRANSACTION keyword.

In BigQuery, it is FOR SYSTEM_TIME AS OF. From the SQL Syntax, seems it's only supported in the SELECT statements, can only appear after the FROM sub-clause:

from_item: {
    table_name [ [ AS ] alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ]  |
    join |
    ( query_expr ) [ [ AS ] alias ] |
    field_path |
    { UNNEST( array_expression ) | UNNEST( array_path ) | array_path }
        [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] |
    with_query_name [ [ AS ] alias ]
}

[ghost]

Another potential behavior difference between dedicated syntax and hints, is that hints typically only generate warnings at most. If using AS OF SYSTEM TIME, I think it would be appropriate to generate an error if the snapshot time can not be provided.

[scsldb]

duplicate #18672