添加默认不可为空列时的 Oracle 错误

Question

我过去在 Oracle 11g 的各种实例中都遇到过这个错误。无法亲自联系 Oracle 支持来了解这是否是已识别/已解决的问题。

该错误会导致查询始终返回列的默认值，即使记录实际上具有不同的值也是如此。

它仅在特定查询中出现，并且是通过使用单个添加语句在先前存在的表中添加具有默认值的不可为空列引起的。

请参阅下面的完整复制品。

DROP TABLE testBugMain;
DROP TABLE testBugAdditional;

-- Create two tables related to one another through real or apparent FK.
CREATE TABLE testBugMain (
    mainid NUMBER(1) NOT NULL,
    str VARCHAR(200) NULL,
    CONSTRAINT PK_testBugMain PRIMARY KEY (mainid)
);
CREATE TABLE testBugAdditional (
    additionalid NUMBER(1) NOT NULL,
    mainid NUMBER(1) NOT NULL,
    CONSTRAINT PK_testBugAdditional PRIMARY KEY (additionalid)
);

-- Insert a couple of values on both.
INSERT INTO testBugMain (mainid, str) VALUES (1, 'test-with-1');
INSERT INTO testBugMain (mainid, str) VALUES (2, 'test-with-2');
INSERT INTO testBugMain (mainid, str) VALUES (3, 'test-with-3');

INSERT INTO testBugAdditional (additionalid, mainid) VALUES (1, 1);
INSERT INTO testBugAdditional (additionalid, mainid) VALUES (2, 2);
INSERT INTO testBugAdditional (additionalid, mainid) VALUES (3, 3);

-- Required for bug to manifest: add a new column, NOT NULL, with DEFAULT value (5 in this case).
-- It needs to be added through an ALTER and both DEFAULT and NOT NULL in the same sentence for the bug to manifest.
ALTER TABLE testBugAdditional ADD bug NUMBER(1) DEFAULT 5 NOT NULL;

-- Update the value on the column, so the query below should return these values instead of 5's.
UPDATE testBugAdditional SET bug = 1 WHERE mainid = 1;
UPDATE testBugAdditional SET bug = 2 WHERE mainid = 2;
UPDATE testBugAdditional SET bug = 3 WHERE mainid = 3;

-- Sanity check (returned values are correct).
SELECT ma.mainid, ma.str, ad.bug FROM testBugMain ma INNER JOIN testBugAdditional ad ON ma.mainid = ad.mainid;

-- Insanity check (returned values are default values).
SELECT mainid, str, bug FROM
(
   SELECT ma.mainid, ma.str, ad.bug FROM testBugMain ma
   LEFT JOIN -- Required for bug to manifest (left join).
   testBugAdditional ad ON ma.mainid = ad.mainid
   ORDER BY ma.mainid ASC -- Required for bug to manifest (any order by, perhaps?).
)
WHERE rownum < 6 -- Required for bug to manifest (pagination);​

虽然暴露错误的查询看起来有些复杂，但我认为使用左连接进行排序、分页查询实际上并不奇怪(而且，考虑到所有因素，很少出现错误 list 可能会更糟)。

问题

这是 Oracle 已识别/解决的问题吗？

这个问题有解决办法吗？

Answer 1

解决方案/解决方法

要解决此问题，请始终以类似于以下方式的方式向现有表添加不可为空的列:

-- Add the column as nullable with a default.
ALTER TABLE existingTable ADD newColumn NUMBER(1) DEFAULT 5;
-- Add the not-null constraint.
ALTER TABLE existingTable MODIFY newColumn NOT NULL;