同表不同创建方式下UPDATE使用旧列值的差异原因排查

直接创建表 #

create table FOO
(
    ID number(19) primary key,
    DATE1 DATE default sysdate,
    DATE2 DATE
);

执行以下操作：

insert into FOO (ID) VALUES (1);
update FOO set DATE1 = null where id = 1;
update FOO set DATE2 = DATE1 where id = 1;
select DATE2 from FOO;

结果DATE2为null，符合预期。

分两步创建表 #

create table FOO
(
    ID number(19) primary key
);
alter table FOO
    add DATE1 DATE default sysdate
    add DATE2 DATE;

执行相同的插入及更新操作后，DATE2最终为DATE1最初的sysdate值，尽管DATE1最终被设置为null。

两种方式创建的表，执行describe foo结果一致：

SQL> describe foo
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                    NOT NULL NUMBER(19)
 DATE1                          DATE
 DATE2                          DATE

疑问：为何两种表创建方式会导致UPDATE操作的结果出现差异？

这个差异的核心原因是Oracle对CREATE TABLE和ALTER TABLE ADD COLUMN中定义的DEFAULT属性的底层处理逻辑不同：

1. 直接创建表的逻辑 #

当你在CREATE TABLE中直接定义DATE1 DATE DEFAULT SYSDATE时，这个默认值是仅作用于插入阶段的动态规则：

• 插入未指定DATE1的行时，Oracle会实时计算SYSDATE并填充到列中；
• 当你显式将DATE1更新为NULL后，该列的实际存储值就是NULL，后续所有对DATE1的引用（包括UPDATE DATE2 = DATE1）都会直接读取这个NULL值，因此最终DATE2为NULL。

2. 分两步创建表的逻辑 #

当通过ALTER TABLE ADD COLUMN添加带有DEFAULT的列时，Oracle的处理存在细微差异：

• 如果操作顺序是先创建空表→添加列→插入行，理论上和直接建表逻辑一致，但部分Oracle旧版本（如11gR1及更早）存在特性差异：通过ALTER添加的列，其默认值会被持久化为列的静态属性，当你将列更新为NULL后，Oracle在某些场景下会隐式调用默认值而非读取实际存储的NULL；
• 如果你的实际操作顺序是先创建表→插入行→添加列，Oracle会在执行ALTER语句时，立即为已有行的DATE1填充ALTER时刻的SYSDATE值，若后续更新DATE1为NULL时出现异常行为，大概率是版本相关的特性或bug导致。

验证方法 #

你可以查询数据字典确认列的属性差异：

SELECT COLUMN_NAME, DATA_DEFAULT, NULLABLE 
FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME = 'FOO';

直接建表的DATE1列，DATA_DEFAULT应为字符串'SYSDATE'；而通过ALTER添加的DATE1列，部分版本中会存储ALTER时刻的具体日期常量，而非动态表达式。

统一行为的办法 #

如果需要两种建表方式的行为完全一致：

• 对于Oracle 12c及以上版本，可以在ALTER添加列时显式定义默认值的行为（若需要保留默认值逻辑）：

  ALTER TABLE FOO ADD DATE1 DATE DEFAULT SYSDATE ON NULL;
  

• 若不需要特殊的默认值处理，确保两种建表方式的列定义逻辑完全对齐即可。

内容的提问来源于stack exchange，提问作者rlovtang