sql: ALTER COULMN TYPE of stored computed column drops default value

[spilchen]

Describe the problem

If you alter the type of a stored computed column to a type that requires a cast (int -> string), then the default value is dropped and we no longer compute values when ingesting new data.

To Reproduce

demo@127.0.0.1:26257/demoapp/movr> create table control (real1 int, real2 int, comp1 int as ((real1 + real2)) stored);
CREATE TABLE
demo@127.0.0.1:26257/demoapp/movr> insert into control values (1,1);
INSERT 0 1

Time: 4ms total (execution 4ms / network 0ms)

demo@127.0.0.1:26257/demoapp/movr> select * from control;
  real1 | real2 | comp1
--------+-------+--------
      1 |     1 |   2
(1 row)
# alter to another int is fine. Computed column is correctly done.
demo@127.0.0.1:26257/demoapp/movr> alter table control alter column comp1 type bigint;
ALTER TABLE

Time: 61ms total (execution 61ms / network 0ms)

demo@127.0.0.1:26257/demoapp/movr> select * from control;
  real1 | real2 | comp1
--------+-------+--------
    1 |     1 |     2
(1 row)

Time: 3ms total (execution 2ms / network 0ms)

demo@127.0.0.1:26257/demoapp/movr> insert into control values (2,2);
INSERT 0 1

Time: 3ms total (execution 3ms / network 0ms)

demo@127.0.0.1:26257/demoapp/movr> select * from control;
  real1 | real2 | comp1
--------+-------+--------
    1 |     1 |     2
    2 |     2 |     4
(2 rows)

# try to alter the computed column from bigint to string. We succeed but results for new rows are wrong.
demo@127.0.0.1:26257/demoapp/movr> SET enable_experimental_alter_column_type_general = true;
SET

Time: 1ms total (execution 1ms / network 0ms)

demo@127.0.0.1:26257/demoapp/movr> alter table control alter column comp1 type varchar(100);
NOTICE: ALTER COLUMN TYPE changes are finalized asynchronously; further schema changes on this table may be restricted until the job completes; some writes to the altered column may be rejected until the schema change is finalized
ALTER TABLE

Time: 150ms total (execution 149ms / network 0ms)

demo@127.0.0.1:26257/demoapp/movr> select * from control;
  real1 | real2 | comp1
--------+-------+--------
    1 |     1 |     2
    2 |     2 |     4
(2 rows)

Time: 2ms total (execution 2ms / network 0ms)

demo@127.0.0.1:26257/demoapp/movr> insert into control values (3,3);
INSERT 0 1

Time: 3ms total (execution 2ms / network 0ms)

demo@127.0.0.1:26257/demoapp/movr> select * from control;
  real1 | real2 | comp1
--------+-------+--------
    1 |     1 |     2
    2 |     2 |     4
    3 |     3 | NULL
(3 rows)

# We can see that it drops the column default as well
demo@127.0.0.1:26257/demoapp/movr> \d control;
Table "public.control"
  Column |          Type        | Collation | Nullable |    Default
---------+------------------------+-----------+----------+-----------------
  real1  | bigint               |           |       |
  real2  | bigint               |           |       |
  comp1  | character varying(100) |         |       |
  rowid  | bigint               |           | not null | unique_rowid()
(4 rows)
                    Indexes
-----------------------------------------------
  control_pkey PRIMARY KEY, btree (rowid ASC)
(1 row)

Expected behavior The postgres behaviour accepts this:

postgres=# create table control (real1 int, real2 int, comp1 int generated always as (real1 + real2) stored);
CREATE TABLE
postgres=# insert into control values (1,1);
INSERT 0 1
postgres=# select * from control;
 real1 | real2 | comp1
-------+-------+-------
     1 |     1 |     2
(1 row)

postgres=# alter table control alter column comp1 type varchar(100);
ALTER TABLE
postgres=# insert into control values (2,2);
INSERT 0 1
postgres=# select * from control;
 real1 | real2 | comp1
-------+-------+-------
     1 |     1 | 2
     2 |     2 | 4
(2 rows)

postgres=# \d control;
                                         Table "public.control"
 Column |          Type          | Collation | Nullable |                    Default
--------+------------------------+-----------+----------+------------------------------------------------
 real1  | integer                |           |          |
 real2  | integer                |           |          |
 comp1  | character varying(100) |           |          | generated always as (((real1 + real2))) stored

So, we either want to match this behaviour or block the alter entirely.

Epic CRDB-25314

[blathers-crl[bot]]

Hi @spilchen, please add branch-* labels to identify which branch(es) this C-bug affects.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[rafiss]

This might be related to https://github.com/cockroachdb/cockroach/issues/119555 or https://github.com/cockroachdb/cockroach/issues/71571 (in terms of how the legacy schema changer goes through a few invalid intermediate states).