Testing SEQUENCE object

Below are mostly minor issues found during testing of SEQUENCE object, so I may need feedback for each - if everyone agrees that my understanding is correct, if the issue is minor and we should ignore it for now, or create MDEV task for each, etc.

1. (FIXED) Name of column 'next_value' in sequence tables.

One may expect that actions which look similar - should do similar things. Thus there may be little confusion that these commands lead to completely opposite results:

select next_value from my_sequence;

and

select next value for my_sequence;

Additionally, it is not obvious that nextval() returns values much less than number in table.next_val (depending on 'cache' column).

I understand that it is minor issue and there is too much work to change it, so just bringing your attention here. If you think that this issue is irrelevant - I am OK with that. Otherwise - I think the name of the column something like 'cached_value' will not cause confusion for similar cases.

2. (FIXED) Currently users with just SELECT privilege can increment SEQUENCE by nextval() or other similar means; I should open MDEV task for that, unless any objections.

3. (MDEV-13006) Column update_time in I_S.tables remains NULL for innodb sequences
It may be useful to find out which sequences are rarely used, so I would open a new MDEV task

4. (FIXED) It looks nextval() does set innodb locks on sequence table until transaction is completed. Is it really required? Or if something which users must deal with, e.g. by using aria / myisam sequence objects? Can this be clarified or should I open MDEV for that
Edit: I was experimenting with this and came to conclusion that user manual should warn users against using InnoDB sequences when long transactions are involved. Big 'cache' value in Sequence may help to some extend , but still the sequence still may behave like table lock on unlucky conditions.

5. <removed>

6. (FIXED) FLUSH LOCAL TABLES; will update sequence tables, but will not write that to binary log. This probably just needs to be documented, unless somebody can see any bigger issue here.

7. (FIXED) FLUSH TABLES WITH READ LOCK on slave with read_only=1 will update sequence tables . I don't see any immediate issue here so far, but I still feel that it may cause problems in some scenarios where read_only should guarantee that tables are unchanged.

8. (MDEV-13008) Users may experience confusion between SEQUENCE Storage Engine and SEQUENCE Object. I probably will open documentation task to document it in both places brief difference and 'Not to be confused with ... '

9. (FIXED) It looks SEQUENCE object by design will not work with binlog_format=STATEMENT . If it is true - it should be documented as well

10. (Ignore for now) Currently SEQUENCE objects with 'increment 0' react to dynamic change of 'auto_increment_increment' only after Server restart, FLUSH TABLES or ALTER SEQUENCE . I see a problem here that old value may be used for long time, just this looks like a low priority for now.

11. (MDEV-13008) Currently user must lock SEQUENCE objects when locking regular tables with LOCK TABLES:

MariaDB [test]> lock tables x write;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into x select nextval(i);

ERROR 1100 (HY000): Table 'i' was not locked with LOCK TABLES

I believe this limitation will be an issue when migrating e.g. from Oracle, because in PL/SQL it is not possible to lock a sequence object.
Is implementation possible when `lock tables tablename` still allows to use sequence commands?

12. (MDEV-13007) I see problem with temporary sequences after 'ALTER engine' command, e.g.:

MariaDB [test]> create temporary sequence s;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> alter table s engine myisam;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test]> select nextval(s);

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

| nextval(s)      |

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

| 140241957546134 |

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

1 row in set (0.00 sec)

MariaDB [test]> \r

Connection id:    16

Current database: test

MariaDB [test]> create temporary sequence s;

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> alter table s engine innodb;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test]> select nextval(s);

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

| nextval(s) |

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

|          0 |

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

1 row in set (0.00 sec)

MariaDB [test]> select nextval(s);

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

| nextval(s)      |

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

| 140241957547216 |

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

1 row in set (0.00 sec)