sql: unable to alter column type for a column that has (but actually doesn't have) a constraint

[Marcuzz]

Describe the problem

When writing some migrations to swap out unique_rowid() with uuids we ran into a peculiar problem where the following error would be thrown, even though there were absolutely no constraints on the id column:

[2021-10-04 20:35:26] [0A000] ERROR: unimplemented: ALTER COLUMN TYPE for a column that has a constraint is currently not supported
[2021-10-04 20:35:26] Hint: You have attempted to use a feature that is not yet implemented.
[2021-10-04 20:35:26] See: https://go.crdb.dev/issue-v/48288/v21.1

This error points to #48288, but we don't actually have a constraint pointing to, or on the column in question.

To Reproduce

In a fresh database you have to run the following queries for the 'bug' to occur. This is a minimum working reproduction of the bug:

SET enable_experimental_alter_column_type_general = true;
CREATE TABLE a
(
    id uuid primary key
);
CREATE TABLE b
(
    id   int8                   primary key,
    a_id uuid references a (id) not null,
    legacy_id int8 not null
);
BEGIN;
ALTER TABLE b
    DROP CONSTRAINT "primary";
ALTER TABLE b
    ADD CONSTRAINT "primary" PRIMARY KEY (legacy_id);
COMMIT;
ALTER TABLE b
    ALTER COLUMN id TYPE float8;

It will throw the aforementioned error, citing that there are constraints on the primary key column in table b, thus it's not able to drop the primary key constraint. There's no extra constraint on the primary key in table b, and we can confirm that with the following query:

SHOW CONSTRAINTS FROM b;

Which outputs:

To confirm our theory we can run the following queries (after deleting the previously created b and a tables), which will work without a hitch:

SET enable_experimental_alter_column_type_general = true;
CREATE TABLE a
(
    id uuid primary key
);
CREATE TABLE b
(
    id   int8                   primary key,
    a_id uuid references a (id) not null,
    legacy_id int8 not null
);
BEGIN;
ALTER TABLE b
    DROP CONSTRAINT "primary";
ALTER TABLE b
    ADD CONSTRAINT "primary" PRIMARY KEY (legacy_id);
COMMIT;
-- This makes it work
ALTER TABLE b DROP CONSTRAINT "fk_a_id_ref_a";
ALTER TABLE b
    ALTER COLUMN id TYPE float8;

Notice that we drop the foreign key constraint in this set of queries, and now it succeeds.

Expected behavior

The expected behaviour would be for CockroachDB to not complain that there's a constraint on table b's id column, as there is in fact no constraint on b.id.

Environment:

• CockroachDB version: 21.1.9
• Server OS: Cockroach in Docker (cockroachdb/cockroach:v21.1.9)
• Client app: cockroach sql (and also JetBrains DataGrip)

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @RichardJCai (author of sql: ALTER COLUMN TYPE general for column that has a check constraint not supported. #48288)
• @cockroachdb/sql-schema (found keywords: ALTER TABLE)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[postamar]

Thanks for reporting this, this is rather intriguing. I'll look into this.