Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.5.8
-
None
Description
Evolving applications sometimes add new columns to tables. Sensible values may be added for existing rows in those tables. Versioned tables don't sufficiently support this practice.
An error 1265 shows when adding a new, non null column to a versioned table.
Example 1:
SET SESSION system_versioning_alter_history=KEEP;
|
CREATE TABLE abc (a INT NOT NULL) WITH SYSTEM VERSIONING;
|
INSERT INTO abc(a) VALUES (1), (2), (3); |
ALTER TABLE abc ADD COLUMN b TEXT NULL;
|
UPDATE abc SET b = 'value'; |
ALTER TABLE abc CHANGE COLUMN b b TEXT NOT NULL;
|
Example 2:
SET SESSION system_versioning_alter_history=KEEP;
|
CREATE TABLE abc (a INT NOT NULL) WITH SYSTEM VERSIONING;
|
INSERT INTO abc(a) VALUES (1), (2), (3); |
DELETE FROM abc;
|
ALTER TABLE abc ADD COLUMN b TEXT NULL;
|
ALTER TABLE abc CHANGE COLUMN b b TEXT NOT NULL;
|
Interestingly, this does not fail. Example 3:
SET SESSION system_versioning_alter_history=KEEP;
|
CREATE TABLE abc (a INT NOT NULL) WITH SYSTEM VERSIONING;
|
INSERT INTO abc(a) VALUES (1), (2), (3); |
DELETE FROM abc;
|
ALTER TABLE abc ADD COLUMN b TEXT NOT NULL;
|
Example 2 and 3 do the same thing, so this is clearly a bug.
In any way, an additional ideal solution would be to allow queries that can alter historical rows, so that the historical nulls can get a sensible value too.
Attachments
Issue Links
- relates to
-
-
- Closed
-