addAutoIncrement isn't working properly for HyperSQL, Mysql, MariaDB and H2

[KushnirykOleh]

Environment

Liquibase Version: 4.10

Liquibase Integration & Version: -

Liquibase Extension(s) & Version: -

Database Vendor & Version: Mysql, MariaDB, HyperSQL, H2

Operating System Type & Version: windows 10

Description

Mysql and MariaDB don't support incrementBy in a way how other platforms do. They have auto_increment_offset var set globally or per session, but not for particular table.

For H2 and HyperSQL databases generated queries are not correct

Steps To Reproduce

create xml changeLog file with next changesets

    <changeSet id="1" author="as">
        <createTable tableName="autoincrement_test">
            <column name="intColumn" type="int">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="dateColumn" type="date"/>
        </createTable>
        <rollback>
            <dropTable tableName="autoincrement_test"/>
        </rollback>
    </changeSet>
    <changeSet id="2" author="as">
        <addAutoIncrement columnDataType="int"
                          columnName="intColumn"
                          generationType="ALWAYS"
                          incrementBy="2"
                          startWith="100"
                          tableName="autoincrement_test"/>
        <rollback/>
    </changeSet>

run liquibase updateSql against mentioned DBs
observe generated SQL

Actual Behavior

Generated SQL for :

1. For Mysql and MariaDB, nothing about incrementBy may be confusing if user rely on incrementBy but didn't check generated SQL and expect it to be working

CREATE TABLE lbcat.autoincrement_test (intColumn INT NOT NULL, dateColumn date NULL, CONSTRAINT PK_AUTOINCREMENT_TEST PRIMARY KEY (intColumn))
ALTER TABLE lbcat.autoincrement_test MODIFY intColumn INT AUTO_INCREMENT
ALTER TABLE lbcat.autoincrement_test AUTO_INCREMENT=100

2. For H2

CREATE TABLE PUBLIC.autoincrement_test (intColumn INT NOT NULL, dateColumn date, CONSTRAINT PK_AUTOINCREMENT_TEST PRIMARY KEY (intColumn))
ALTER TABLE PUBLIC.autoincrement_test ALTER COLUMN intColumn int GENERATED BY DEFAULT AS IDENTITY (100, 2)

failing with
Syntax error in SQL statement "ALTER TABLE PUBLIC.autoincrement_test ALTER COLUMN intColumn int GENERATED BY DEFAULT AS IDENTITY ([*]100, 2)"; expected "START, RESTART, INCREMENT, MINVALUE, MAXVALUE, CYCLE, NO, EXHAUSTED, CACHE, NOMINVALUE, NOMAXVALUE, NOCYCLE, NOCACHE, )";

3. For HyperSQL

ALTER TABLE PUBLIC.autoincrement_test ALTER COLUMN intColumn int GENERATED BY DEFAULT AS IDENTITY (START WITH 100, INCREMENT BY 2)

failing with
Reason: liquibase.exception.DatabaseException: unexpected token: , required: ) [Failed SQL: (-5581) ALTER TABLE PUBLIC.autoincrement_test ALTER COLUMN intColumn int GENERATED BY DEFAULT AS IDENTITY (START WITH 100, INCREMENT BY 2)]

Expected/Desired Behavior

1. If liquibase can't do anything about incrementBy for Mysql and MariaDB indicate that in https://docs.liquibase.com/change-types/add-auto-increment.html as not supported and throw validation error for this attribute in changeset. Fix if there is a way.
2. Fix query for H2
3. Fix query for HyperSQL to remove comma, query like ALTER TABLE PUBLIC.autoincrement_test ALTER COLUMN intColumn int GENERATED BY DEFAULT AS IDENTITY (START WITH 100 INCREMENT BY 2) works fine

Additional Context

discovered in test-harness