The standard introduces a new table PERIODS. TABLE_TYPE will not be affected.

Full list of what periods affect in I_S:

• PERIODS view
  • columns TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, PERIOD_NAME, START_COLUMN_NAME (NULL if the user has no privileges to see it), END_COLUMN_NAME
  • we'll do it. should show application-time periods and by system time.
• COLUMNS view has columns: IS_SYSTEM_TIME_PERIOD_START, IS_SYSTEM_TIME_PERIOD_END, SYSTEM_TIME_PERIOD_TIMESTAMP_GENERATION
  • we'll do the first two, but not SYSTEM_TIME_PERIOD_TIMESTAMP_GENERATION
• CONSTRAINT_PERIOD_USAGE
  • we can have part of it (can show UNIQUE, but not FOREIGN KEY and CHECK constraints), but it doesn't make a lot of sense to have partially implemented table, and unique keys will be seen in the next table
  • so we won't have it
• KEY_PERIOD_USAGE
  • columns CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME,
    TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, PERIOD_NAME
  • we'll do it
• ROUTINE_PERIOD_USAGE, TRIGGER_PERIOD_USAGE, VIEW_PERIOD_USAGE
  • we cannot have it

Please review commits:

82a49812 MDEV-22597 Add views for periods in information_schema

23b15d24 cleanup: add Field::store_yesno

on branch bb-11.3-periods-schema

82a49812 and 23b15d24 are ok to push

would be good to add a test that

create table t1 (a int) with system versioning

does not show anything in the period tables.

Most testing was done so far on bb-11.3-periods-schema commits 24018c74 and 90656c9f. Some changes from commits 27839d43 and 44f64348 also taken into account.

Must analyze

• MDEV-32501 Information schema views for periods reveal information to unprivileged user
• MDEV-32503 Queries from I_S.KEY_PERIOD_USAGE do not obey case-sensitivity
• MDEV-32504 Search by I_S.KEY_PERIOD_USAGE.CONSTRAINT_NAME does not work

It is "must analyze" but not necessarily "must fix" because it's possible that some or all of these problems are generic for information schema, not specific to the views added by this patch. If so, the priority can be decreased, although my recommendation would be to try to fix them by the GA.

Suggestions
MTR coverage lacks this line:

===File sql/sql_class.cc:

   6871 : +        bool is_update= MY_TEST(sql_command_flags() & CF_UPDATES_DATA);

To my understanding, it is not a part of the feature as such, but of a preceding commit in the feature tree. Still, it would be good to fill the gap. Something like this does the trick, although of course it is not worth a separate test file, it should be added to an existing one instead:

--source include/have_binlog_format_row.inc

--source include/have_blackhole.inc

CREATE TABLE t1(a INT) ENGINE=BLACKHOLE;

UPDATE t1 SET a = a + 1;

DROP TABLE t1;

After the branch is ready, a final round of testing should be performed (even if no changes are made, because the previous testing was done on a mix of commits and its results may lack consistency).

Testing is done, bb-11.3-periods-schema 29f5901c8a95ece33d. Ok to push into 11.4

Please link to the Slack discussion or anywhere else relevant so I can see what these additions are supposed to do.

greenman The best link I have is this Serg's comment, according to which it has been implemented.

Also regarding the privilege rules see this comment in MDEV-32501.