Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Adding NOT NULL column with DEFAULT value may cause default values to update when selecting or have the wrong charset [CORE6358] #6599

Closed
firebird-automations opened this issue Jul 7, 2020 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @asfernandes

Since v2 when adding NOT NULL columns with default value we store the default value in metadata and read that value when reading pre-existent data. Before that, default values were evaluated at query time producing different values.

For example we have now:

recreate table t (n integer);
insert into t values (1);
insert into t values (2);
commit;
alter table t add t1 timestamp default current_timestamp not null;

select * from t;

       N                        T1

============ =========================
1 2020-07-07 12:56:04.7740
2 2020-07-07 12:56:04.7740

-- wait and see the same values

select * from t;

       N                        T1

============ =========================
1 2020-07-07 12:56:04.7740
2 2020-07-07 12:56:04.7740

But this is not done correctly when the default expression is not from the same type of the field. For example:

recreate table t (n integer);
insert into t values (1);
insert into t values (2);
commit;
alter table t add t1 timestamp default 'now' not null;

select * from t;

       N                        T1

============ =========================
1 2020-07-07 12:56:04.7740
2 2020-07-07 12:56:04.7740

-- wait and the values are changed

select * from t;

       N                        T1

============ =========================
1 2020-07-07 12:58:08.1254
2 2020-07-07 12:58:08.1254

Another problem is with charset. With a connection using UTF8 charset:

recreate table t2 (n integer);
insert into t2 values (1);
insert into t2 values (2);
commit;
alter table t2 add c1 varchar(10) character set win1252 default '123áé456' not null;
insert into t2 (n) values (3);
select * from t2;

       N C1

============ ==========
1 123áé456
2 123áé456
3 123áé456

Commits: 55627cf 2f30a82 7b5b1c0 2e146d8

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 RC 1 [ 10930 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Fix Version: 3.0.7 [ 10940 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment