Description
We have a table like this
create table t (i int);
insert into t values (0);
set sql_safe_updates = false;
If we then perform the following
begin;
alter table t add column j int as (i) stored;
alter table t alter column j drop stored;
commit;
which supposedly should add a new column j with its value copied from column i. But selecting the table gives us
select * from t;
i | j
--------
0 | NULL
(1 row)
Time: 2ms total (execution 2ms / network 0ms)
The reason that we see a NULL value on column j is because of how we process DDLs in transactions -- there is work we do in the user transactions and work we do in the job. In this case, in this particular case, ADD COLUMN j and DROP STORED will produce a mutation of ADD COLUMN with no computed value, and therefore in the job phase when we backfill column j, it's filled with NULL.
It might not be easy for us to correct the behavior for this case (this showcases the difficulty of implementing transactional DDLs). Fortunately, as an immediate mitigation, if we were to execute all these ddls separately in their own transactions (i.e. single statement, implicit transaction), it would work as expected.
Jira issue: CRDB-31233
Epic CRDB-24534