Skip to content

sql: bug when setting a column NOT NULL and then dropping it #47719

@thoszhang

Description

@thoszhang

This was uncovered from looking at logs for TestRandomSyntaxSchemaChangeColumn. If a column is dropped while a schema change for SET NOT NULL is in progress on the same column, the validation for the non-null column (which is a SQL query) can fail with the error column %q does not exist. The column drop can be queued after SET NOT NULL with a different mutation ID, or it can even happen if the two schema changes happen in the same transaction:

root@127.0.0.1:62055/movr> create table t (a int);
CREATE TABLE

Time: 3.051ms

root@127.0.0.1:62055/movr> begin; alter table t alter column a set not null; alter table t drop column a; commit;
ERROR: transaction committed but schema change aborted with error: (42703): validation of NOT NULL constraint failed: validate check constraint: column "a" does not exist
SQLSTATE: XXA00
HINT: Some of the non-DDL statements may have committed successfully, but some of the DDL statement(s) failed.
Manual inspection may be required to determine the actual state of the database.
--
See: https://github.com/cockroachdb/cockroach/issues/42061

This particular bug is not very severe; failing with an error is what we should be doing in this case, even though it's not a very good error. The larger question is what we can do to avoid bugs related to interactions between in-progress schema changes and schema changes that are later in line. We're currently dealing with these interactions on an ad-hoc basis, which is going to inevitably lead to similar bugs.

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions