Auto-increment column starting with a custom value no longer works with H2 v1

[tibor-universe]

Environment

Liquibase Version: 4.14.0 - 4.19.0

Liquibase Integration & Version: embedded

Liquibase Extension(s) & Version: N/A

Database Vendor & Version: H2 v1.4.200

Operating System Type & Version: macOS

Infrastructure Type/Provider: N/A

Description

Commit 9f80bc0 introduced a regression to what was originally fixed by PR #305.

Steps to Reproduce

Specify the primary key of some table as follows:

<column name="id" type="BIGINT" autoIncrement="true" startWith="100">

Actual Behavior

Starting with version 4.14.0, Liquibase uses SQL syntax that H2 does not yet support in v1:

CREATE TABLE … (id BIGINT AUTO_INCREMENT (START WITH 100) NOT NULL, …);

Expected/Desired Behavior

SQL syntax supported by H2 v1:

CREATE TABLE … (id BIGINT AUTO_INCREMENT (100) NOT NULL, …);

Additional Context

See the AUTO_INCREMENT syntax in H2 v1's documentation: https://web.archive.org/web/20180106125259/http://www.h2database.com/html/grammar.html#column_definition.

[FBurguer]

Hello! Can you send a sample chanegset with this issue? i tried to follow your steps but didnt have the same behavior.
My changeset:

    <changeSet author="FBurguer (generated)" id="1657197710241-1" runInTransaction="false">
        <createTable tableName="COMPANY">
            <column name="id" type="BIGINT" autoIncrement="true" startWith="100">
                <constraints nullable="false" primaryKey="true" primaryKeyName="PK_COMPANY"/>
            </column>
            <column name="NAME" type="VARCHAR(50)">
                <constraints nullable="false"/>
            </column>
            <column name="ADDRESS1" type="VARCHAR(50)"/>
            <column name="ADDRESS2" type="VARCHAR(50)"/>
            <column name="CITY" type="VARCHAR(30)"/>
        </createTable>
    </changeSet>

My updatesql output:

-- Changeset changelogs8/changelog.xml::1657197710241-1::FBurguer (generated)
CREATE TABLE PUBLIC.COMPANY (id BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 100) NOT NULL, NAME VARCHAR(50) NOT NULL, ADDRESS1 VARCHAR(50), ADDRESS2 VARCHAR(50), CITY VARCHAR(30), CONSTRAINT PK_COMPANY PRIMARY KEY (id));

My db created the elements without any issue:

This was executed with liquibase v4.18.0
Looking forward for your response!

[tibor-universe]

Hi, thanks for following this up so quickly.

If you look at line 295 of the H2Database class in Liquibase v4.18.0, you'll see that for version 1 of H2, the emitted SQL will contain AUTO_INCREMENT whereas for version 2, it will be GENERATED BY DEFAULT AS IDENTITY instead:

@Override
protected String getAutoIncrementClause() {
    try {
        if (getDatabaseMajorVersion() == 1) {
            return "AUTO_INCREMENT";
        } else {
            return "GENERATED BY DEFAULT AS IDENTITY";
        }
    } catch (DatabaseException e) {
        return "AUTO_INCREMENT";
    }
}

The SQL output in your response indicates that you are using version 2 of H2, not version 1. Could I ask you to check that?

For H2 v1, the output would instead be:

CREATE TABLE PUBLIC.COMPANY (id BIGINT AUTO_INCREMENT (START WITH 100) NOT NULL, …);

H2 doesn't understand that syntax in version 1:

Syntax error in SQL statement "CREATE TABLE PUBLIC.auto_increment (id BIGINT AUTO_INCREMENT (START[*] WITH 100) NOT NULL, ""value"" VARCHAR(32), CONSTRAINT PK_AUTO_INCREMENT PRIMARY KEY (id))" … [42001-200]