Generated changelog is incorrect from CockroachDB if script has USING HASH

Tylorjg commented 1 year ago

Search first

[X] I searched and no similar issues were found

Description

If there is a object such as a table that was created that has one of the columns set to USING HASH, the generated changelog, either by generate-changelog or diff-changelog is incorrect.

In the case of a SQL changelog getting generated, it is not deployable as it creates two changesets, one creating the table with a primary key, and then another changeset creating the same primary key. After removing the second changeset, the object deployed doesn't match the same as a manual deployment.

For an XML generated changelog, the changelog is deployable and doesn't run into the primary key issue, but the object doesn't match a manual deployment.

Steps To Reproduce

Deploy the following SQL code to CockroachDB

CREATE TABLE product (
    product_id int,
    department_id int,
    CONSTRAINT pk_my_table PRIMARY KEY (product_id, department_id) USING HASH
)

Have Liquibase v4.23.0 installed
Create a Liquibase project that can connect to a CockroachDB
Run either generate-changelog or diff-changelog for an SQL or XML output
Attempt to deploy the SQL changelog to a database that doesn't have that table.
In both XML and SQL changelog output, the object doesn't match was is manually created in the database

Expected/Desired Behavior

The generated changelog either by generate-changelog or diff-changelog should be deployable SQL and match the object structure when manually doing a deployment.

Liquibase Version

Liquibase v4.23.0

Database Vendor & Version

CockroachDB v23.1

Liquibase Integration

No response

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

No response

Additional Context

No response

Are you willing to submit a PR?

[ ] I'm willing to submit a PR (Thank you!)

tati-qalified commented 1 year ago

Hello @Tylorjg, I haven't been able to replicate this exact bug.

Here's what I did:

Executed CREATE TABLE product ( product_id int, department_id int, CONSTRAINT pk_my_table PRIMARY KEY (product_id, department_id) USING HASH ) in database system
Ran liquibase generate-changelog (or diff-changelog)
Switched to new database secondSystem
Ran liquibase update using the generated changelog

With Liquibase version 4.23.0 and CockroachDB version 23.1, this is working just fine. One thing to note is that the generated SQL doesn't use "USING HASH"; it creates a hash-type column with a Unique constraint. If this isn't what's expected, then it's a problem, but the changelog file itself isn't generating any errors.

Here is the generated changelog for reference:

-- liquibase formatted sql

-- changeset tfernandez:1692719564225-1
CREATE TABLE "product" ("product_id" BIGINT NOT NULL, "department_id" BIGINT NOT NULL, "crdb_internal_department_id_product_id_shard_16" BIGINT NOT NULL, CONSTRAINT "pk_my_table" PRIMARY KEY ("product_id", "department_id"));

Would you be able to provide the generated code you're getting? Or re-test this and let me know if it's still an issue?

Thank you Tatiana

tati-qalified commented 9 months ago

@Tylorjg just following up on this, were you able to re-test it?

liquibase / liquibase