Adding a column with default value incorrectly fills a different column

Question

After executing the following script:

alter table test_table 
add (test1 varchar2(1 char),
     test2 varchar2(1 char) default 'T' not null);

I got interesting results... both columns are filled with 'T'. Default value is correctly added to "test2" column. "test1" column correctly has no default value.

Also, executing this script:

alter table test_table 
add (test1 varchar2(1 char),
     test2 varchar2(1 char),
     test3 varchar2(1 char) default 'T' not null);

resulting in "test1" and "test3" columns filled with 'T'.

Could anyone explain to me why this is happening?

I'm using SQLDeveloper to execute scripts.

Answer 1

This unpleasant behaviour has been documentated by Oracle as bug number 9170308.

This is for example mentioned in Neil Chandler’s blog here

The best way to solve this is to upgrade this very obsolete version. The bug doesn't appear in newer versions, it has been fixed in some patch of version 11. The current version is Oracle 23.

So you should upgrade as soon as possible.

As long as you have to keep your old version, you should be able to apply the intended setup in separate steps:

-- Step 1: Add column test1 without issues
ALTER TABLE test_table 
ADD test1 VARCHAR2(1 CHAR);

-- Step 2: Add test2 as NULLABLE with DEFAULT value
ALTER TABLE test_table 
ADD test2 VARCHAR2(1 CHAR) DEFAULT 'T';

-- Step 3: Update existing rows explicitly
UPDATE test_table 
SET test2 = 'T' 
WHERE test2 IS NULL;

-- Step 4: Add the NOT NULL constraint
ALTER TABLE test_table 
MODIFY test2 VARCHAR2(1 CHAR) NOT NULL;