cockroachdb / cockroachdb/cockroach

sql/schemachanger: dropping a column after add in the same transaction fails

Open
#136,403 1 comment 0 reactions 0 assignees View on GitHub
A-schema-changes branch-master C-bug O-schema-testing T-sql-foundations
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

The declarative schema changer fails to build a proper plan when adding and dropping columns in separate statements within the same transaction.

**To Reproduce**

```
CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 TEXT NOT NULL, drop DATE, FAMILY F1 (c1, c2, drop));
set use_declarative_schema_changer = 'unsafe_always';
begin;
ALTER TABLE t1 ADD COLUMN c3 TEXT DEFAULT '';
ALTER TABLE t1 DROP COLUMN drop;
commit;
```

The drop column statement fails with this error:
```
ERROR: error executing StatementPhase stage 1 of 1 with 3 MutationType ops: relation "t1" (112): index "crdb_internal_index_2_name_placeholder" contains stored column "" with unknown ID 0
DETAIL: • Schema change plan for ALTER TABLE ‹movr›.public.‹t1› DROP COLUMN ‹drop›; following ALTER TABLE ‹movr›.public.‹t1› ADD COLUMN ‹c3› STRING DEFAULT ‹''›;
```

**Observations:**
- Reversing the order (DROP first, then ADD) or combining both actions in a single statement avoids the issue.

**Environment:**
- CockroachDB version: master branch 5d084bd4e20dcedd1dde7549a2b63010f1ab8e15

Jira issue: CRDB-45021

Epic CRDB-31472

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.