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

BLOB fields may be suddenly set to NULLs during UPDATE after a table format change [CORE6090] #6340

Closed
firebird-automations opened this issue Jun 29, 2019 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @dyemanov

Is related to CORE6089
Is related to CORE5600

Test case:

recreate table t (col1 int, col2 int, col3 int, col4 blob);

insert into t values (0, 0, null, '0');
insert into t values (1, 1, 1, '1');
insert into t values (2, 2, 2, '2');
commit;

select * from t;

    COL1         COL2         COL3              COL4 

============ ============ ============ =================
0 0 <null> 83:0
1 1 1 83:1
2 2 2 83:2

update t set col1 = 1 where col2 >= 0;
-- COL1 should be set to 1 in all three rows
select * from t;

    COL1         COL2         COL3              COL4 

============ ============ ============ =================
1 0 <null> 83:0
1 1 1 83:1
1 2 2 83:2

rollback;

update t set col1 = 1 where col2 >= 0 and col3 >= 0;
-- COL1 should be set to 1 in 2nd and 3rd rows
select * from t;

    COL1         COL2         COL3              COL4 

============ ============ ============ =================
0 0 <null> 83:0
1 1 1 83:1
1 2 2 83:2

rollback;

alter table t add col5 date;
commit;

update t set col1 = 1 where col2 >= 0;
-- COL1 should be set to 1 in all three rows
select * from t;

    COL1         COL2         COL3              COL4        COL5 

============ ============ ============ ================= ===========
1 0 <null> 83:3 <null>
1 1 1 83:4 <null>
1 2 2 83:5 <null>

-- BLOB IDs in COL4 were changed (see CORE6089) but contents is correct

rollback;

update t set col1 = 1 where col2 >= 0 and col3 >= 0;
-- COL1 should be set to 1 in 2nd and 3rd rows
select * from t;

    COL1         COL2         COL3              COL4        COL5 

============ ============ ============ ================= ===========
0 0 <null> 83:0 <null>
1 1 1 <null> <null>
1 2 2 83:6 <null>

-- BUG: COL4 in the second row was nullified!!!

rollback;

This issue manifests itself this way: if some WHERE condition (COL3 >= 0 in our case) is evaluated to NULL/UNKNOWN for the prior (skipped) row, then all blobs are set to NULLs in the next row (unless explicitly set to something else by the SET clause). This happens only for BLOB fields and only if OLD and NEW records have different formats (i.e. after ALTER TABLE).

The bug became visible after fixing CORE5600 (thus v3.0.3 and later versions are affected), but the issue itself seems to be old. We were just lucky that the "buggy" code path was never used before.

Commits: 27ae588

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

priority: Major [ 3 ] => Critical [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE6089 [ CORE6089 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE5600 [ CORE5600 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0.5 [ 10885 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

@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 ]

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