agree, this feature makes perfect sense

Should be rather trivial, however a lot of defence is written around.
Note that initially CHANGE of versioning columns was forbidden by tempesta's patch in scope of a bug fix: https://github.com/tempesta-tech/mariadb/issues/213

Also we should make that a syntax is supported properly:

 ADD rs TIMESTAMP(6) AS ROW START,

 ADD rs TIMESTAMP(6) AS ROW END,

 ADD PERIOD FOR sYSTEM_TIME(rs, re)

should actually change existing system columns and a period.

We could favor instead the simpler version both for us and for the user:

ALTER TABLE t1

 CHANGE row_start rs TIMESTAMP(6) AS ROW START,

 CHANGE row_end re TIMESTAMP(6) AS ROW END;

This requires less code and testing, but we'll refer an invisible system column, which wasn't possible before.

Given all this, and some decision-making, plus review fixes, I estimate the critical path as 7 days.

ralf.gebhardt we have to decide which semantics are we going to implement. The two above are mutually exclusive imo:
the first one takes into account that there is no user-exposed column row_start/row_end. And also the user didn't specify PERIOR FOR SYSTEM_TIME explicitly. It was silently created.
So it makes sense to write {{ ADD COLUMN rs..., ADD COLUMN re ..., ADD PERIOD ... }} since we never actually added it. CREATE TABLE WAS like:

CREATE TABLE t1(x int, y text) WITH SYSTEM VERSIONING;

But technically it would mean a rename of the system column.

Or we may acknowledge that row_start and row_end are the names of invisible system columns and simply allow to rename them, even though we never stated their creation.

The related question can be, can we rename an invisible system column in any other case? I know one such case – it's long unique index:

CREATE TABLE textie(t text UNIQUE);

There, some invisible system virtual column is created AS HASH(t), which actually has a key over it.
I don't know whether we can rename it or not, but I suspect that the behavior is undefined anyway.

So if we can rename an invisible column row_start, why can't we rename this one as well?

These questions must be answered and the behavior should be clarified. And for example one must make sure that they didn't apparently allow modifying all other system fields, when it shouldn't be done.

nikitamalyavin,

SHOW CREATE TABLE does not show the the row_start/row_end columns. Would desc <table> show them as INVISIBLE?
If yes, couldn't an ALTER TABLE <table> MODIFY be used to make them visible to archive the same as if an explicit CREATE TABLE was used?

Ok, DESC does not show it either.
nikitamalyavin Do we have any way to find these invisible columns. information_schema. columns does not show them either

I think we don't. midenok maybe you know?

I don't understand question about "find", but it was designed to "drop" as making them invisible. Invisible means pretend they not exist and inaccessible for user. ADD syntax in description is correct.

serg, here seems to be missing a decision regarding implementation details, mainly to implement ADD for a field which exists, but is invisible. Or to MODIFY a field we do not know that it exists. From my point of view, given that they seems to exist no way for even a SUPER user get an information from the server, that the field exists, ADD is the better option. What do you think?

Let's get the terminology right: INVISIBLE columns are columns that are shown in SHOW CREATE TABLE and INFORMATION_SCHEMA and carry an INVISIBLE attribute. System versioning columns that weren't explicitly created are not "invisible", they aren't columns and aren't part of the table definition. Internally, yes, the server reserves some space in the row image and uses it for storing internal technical system versioning information. And the user can use pseudo-columns ROW_START and ROW_END to access that system versioning information.

So midenok is absolutely right, there is no column to modify. You want a column — you have to add it.

serg and midenok, sop what would be your suggested approach to be able to fulfill this feature request?

Please review bb-11.5-midenok

Thank you midenok, the patch is clean and straightforward.

I kindly ask you to revisit the code in Vers_parse_info::fix_alter_info acording to my suggestion (there are two), other that that it's ok to push.

Updated task on top of the latest main: bb-11.7-MDEV-27293-sysver

Testing done. Ok to push