Support "INSERT INTO … SELECT … AS OF timestamp" Query

[kennytm]

Feature Request

(On behalf of Marvin Jin)

Is your feature request related to a problem? Please describe:

TiDB using TiKV storage supports reading historical data using the @@tidb_snapshot system variable. However, you are not allowed to write anything while @@tidb_snapshot is set, so it is not possible to aggregate these historical data unless using external programs (backup/restore).

ERROR 1105 (HY000): can not execute write statement when 'tidb_snapshot' is set

Describe the feature you'd like:

In Oracle DB, there is an AS OF clause to read from a historical table without affecting the global timestamp.

INSERT INTO recovered_table 

    SELECT * FROM old_table AS OF TIMESTAMP TO_TIMESTAMP('2005-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
--                          ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        WHERE id BETWEEN 1000 AND 1999;

A similar syntax exists on CockroachDB (but not sure if INSERT/SELECT works):

SELECT * FROM old_table AS OF SYSTEM TIME '2005-04-04 09:30:00'
--                      ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
    WHERE id BETWEEN 1000 AND 1999;

ANSI SQL has the "Temporal" feature, which is supported on MariaDB, MS SQL server, DB2 and more. When a table is set to be "temporal", the following syntax is available:

INSERT INTO recovered_table

    SELECT * FROM old_table FOR system_time AS OF TIMESTAMP '2005-04-04 09:30:00'
--                          ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        WHERE id BETWEEN 1000 AND 1999;

Note, however, the system_time refers to a PERIOD bound to two columns of the table old_table, which is a totally different concept from @@tidb_snapshot.

Describe alternatives you've considered:

• Support creating "temporary" tables not governed by @@tidb_snapshot.
• Do nothing, rely on SELECT INTO OUTFILE in historical query and LOAD DATA INFILE to restore data to current time.

Teachability, Documentation, Adoption, Migration Strategy:

[ghost]

Thank you for the feature request! This is a duplicate of #11703

[kennytm]

@nullnotnil No, #11703 is about using AS OF SYSTEM TIME as an alternative way of saying SET @@tidb_snapshot = .... But this issue is about restricting a table of SELECT to one particular snapshot, allowing e.g.

SELECT `before`.id`, `before`.`value`, `after`.`value`
FROM `values` AS OF (NOW() - INTERVAL 1 HOUR) AS `before`
INNER JOIN `values` AS `after`
ON `before`.`id` = `after`.`id`

and in particular the presented example, using INSERT/SELECT to recover lost rows (like in https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#g1025584):

INSERT INTO employees
  (SELECT * FROM employees
     AS OF TIMESTAMP
       TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
         WHERE last_name = 'Chung');

Note that CrDB has these specific examples:

-- invalid
SELECT * FROM (SELECT * FROM t AS OF SYSTEM TIME '-4h'), u

SELECT * FROM (SELECT * FROM t AS OF SYSTEM TIME '-4h') tp
           JOIN u ON tp.x = u.y
           AS OF SYSTEM TIME '-4h'  -- same timestamp as above - OK.
     WHERE x < 123;

implying AS OF SYSTEM TIME is just a different way of setting the Snapshot TS.

---

SQL Server's FOR system_time AS OF <ts> is about Temporal Tables and is mostly irrelevant to @@tidb_snapshot.

[zz-jason]

#11703 has a good title "Support AS OF SYSTEM TIME syntax", but the issue description only discussed the inconvenience of set @@ tidb_snapshot operation.

I listed some statements CockraochDB supported in #11703 (comment), maybe we can consider them as well.

[zz-jason]

Your description of the syntax in ANSI SQL and other database systems inspired me about what syntax we should use in TiDB. Can we close one issue and discuss them in one place?

[SunRunAway]

Duplicate of #11703

[kennytm]

@SunRunAway I have explained previously that it is not a duplicate of #11703 in #18672 (comment). (Plus #11703 was closed-as-dupe to this issue in #11703 (comment) making this a silly cyclic dependency (#11703 should be a dupe of #22765, not this issue))

I have also checked v6.5.0 and nightly that the matching Oracle DB feature mentioned at the top is still not supported.

mysql> select * from x as of timestamp '2023-01-03 18:49:38' union all select * from x;
ERROR 8135 (HY000): can not set different time in the as of

I'm going to reopen given that there's no justification why my comment above is no longer valid, and edited the title to clarify the desired feature.

[SunRunAway]

@SunRunAway I have explained previously that it is not a duplicate of #11703 in #18672 (comment). (Plus #11703 was closed-as-dupe to this issue in #11703 (comment) making this a silly cyclic dependency (#11703 should be a dupe of #22765, not this issue))

I have also checked v6.5.0 and nightly that the matching Oracle DB feature mentioned at the top is still not supported.

mysql> select * from x as of timestamp '2023-01-03 18:49:38' union all select * from x;
ERROR 8135 (HY000): can not set different time in the as of

I'm going to reopen given that there's no justification why my comment above is no longer valid, and edited the title to clarify the desired feature.

Thanks for your patient explanation. I’ve changed title to an explicit syntax for better understanding for other people.