can we generally support adding auto-increment column online? It seems that because of its global nature (auto-increment column values depend on other rows in the table) it cannot be possibly be added online. Consider:

create table t1 as select seq as a from seq_1_to_1000;

alter table t1 add b int auto_increment, key(b);

and then in parallel

delete from t1 where a < 100;

You'll get different results depending on whether auto-increment values were generated before or after DELETE. Looks like a fundamental unsolvable auto-increment issue, doesn't it?

Seems you are right, serg, since we stick to taking the effect after the parallel DMLs.

Moreover, it means that we only can support the commutative pairs, i.e.

O(A(t)) = A(O(t), 

where

• O is a set of parallel DMLs (onine operations)
• A is ALTER TABLE Copy phase
• t is an initial table.

Though this also means that we can support adding autoincrement field for some subset, like inserts (and maybe updates?)
Then if we meet an incompatible operation, ALTER TABLE could fail, but at the later stage. OR maybe report earlier, that we encounter an incompatible op, just as DML is issued?

Or we could just allow Online ALTER to take after the DMLs

It seems that because of its global nature (auto-increment column values depend on other rows in the table) it cannot be possibly be added online

Another example of the same problem would be adding a column with a sequence value for a default

create sequence s;

create table t1 as select seq as a from seq_1_to_200;

alter table t1 add b int default (nextval(s));

and then in parallel

delete from t1 where a < 100;

Thank you, elenst! Yes, nextval also shouldn't be possible. I'm surprised that it is classified as deterministic.

elenst so far I have forbidden both, however with a few rules, which follow.

But first let me address the non-deterministic question:
i thought something is forbidden regarding the determinism of DEFAULT because I remember I wrote some detection. But that detection was to allow new fields with deterministic defaults be a part of a key when searching for a row to update or to delete.

Nothing was forbidden regarding the defaults. Only MDEV-31058 added som restrictions to the columns.

Now I am not forbidding non-deterministic DEFAULTs as well: only ADD...AUTO_INCREMENT and DEFAULT(nextval(..)) . The non-deterministic columns are not expected to break the O(A)=A(O) rule in general. If you find something - you can report.

For example if the function changes its results from call to call with the same arguments.
Like: times_called(), returninn 0,1,2,...

An interesting example here is RAND(), which formally breaks this rule, but the random function is not a concern.

So the rule is tricky and case-by-case I suppose.

With RAND() we can surely get the cases when the rule "as if DML was executed first" is violated, but RAND() behavior is so obscure, even when it's put into the deterministic context, that I wouldn't be able to even formulate a complaint.
For example, it works differently depending whether instant or copy algorithm is used (regardless whether it's "online" or not).
So, I'm going to ignore RAND() unless somebody can specify how exactly it is expected to behave.

Let me try: unless we don't care of an exact value, but rather a property, which is preserved throughout the statement

But we don't want this complication yes, let's just ignore random functions for now and see what else can we find

Especially since it is not even true for RAND(). When I say it behaves differently with COPY and NOCOPY, I don't mean that it returns different values. It exactly doesn't "preserve the property", if by that we mean that the value is expected to be (generally) different for each row.

MariaDB [test]> alter table t add b double(10,9) default(rand(3)), algorithm=copy, lock=shared;

Query OK, 3 rows affected (0.055 sec)              

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from t;

+------+-------------+

| a    | b           |

+------+-------------+

|    1 | 0.905769756 |

|    2 | 0.373079058 |

|    3 | 0.148086053 |

+------+-------------+

3 rows in set (0.014 sec)

That's what we would probably expect as a "property" (and if we were optimistic enough, we would even expect the sequence of values preserved, as that's what the argument to RAND is for). But

MariaDB [test]> alter table t add b double(10,9) default(rand(3)), algorithm=instant;

Query OK, 0 rows affected (0.051 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from t;

+------+-------------+

| a    | b           |

+------+-------------+

|    1 | 0.373079058 |

|    2 | 0.373079058 |

|    3 | 0.373079058 |

+------+-------------+

3 rows in set (0.002 sec)

that's what we probably wouldn't immediately expect.

wow! i think this is an INSTANT bug. Neither i would expect it, nor want as a user. Nice find.