Given this table
create table FOO
(
ID number(19) primary key,
DATE1 DATE default sysdate,
DATE2 DATE
);
DATE1 is initialized with sysdate when I insert a row, then set to null and then copied to DATE2:
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 ends up as null as expected.
But if I create the table in two steps:
create table FOO
(
ID number(19) primary key
);
alter table FOO
add DATE1 DATE default sysdate
add DATE2 DATE;
and runs the same inserts and updates, DATE2 ends up as the original sysdate value from DATE1. Even though DATE1 ends up as null as expected.
Why this difference? The two tables look the same to me.
Update: describe looks like this in both cases:
SQL> describe foo
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(19)
DATE1 DATE
DATE2 DATE
As noted in the comments, this seems to be a bug in some versions of Oracle.
Exhibits expected behavior: Oracle 11.
Exhibits this bug: Oracle 18, 21, 23.