NOT NULL constraint might fail for strings starting with a null character
Details
| Detail name | Value |
|---|---|
| Changelog Number | 8474 |
| Type | Bug |
| Status | Resolved |
| Affected Versions | Exasol 6.1.0, Exasol 6.2.0 |
| Fix Versions | Exasol 7.0.0, Exasol 6.1.7, Exasol 6.2.2, Exasol 7.0.0-alpha1 |
| Resolution Date | 2019-08-07 |
Background Knowledge
The Exasol database considers strings having a length of one character and containing only the null character, i.e. CHR(0), as NULL.
Bug
DML statements like INSERT and IMPORT consider all strings starting with a null character as NULL. Depending on the processed data this causes the statements to fail with the error message "constraint violation - not null" for NOT NULL columns, even though IS NULL returns false for those values. This may also happen for DELETE statements if they cause data reorganization on multi-node databases.
How To Reproduce
Actual results
- Create a table with a NOT NULL constraint. IMPORT from a CSV file containing a string starting with a null character and having a length greater than one will fail.
- The same IMPORT will work for a table without NOT NULL constraint. Adding a NOT NULL constraint to the table will also be successful, but then a subsequent DELETE might fail.
- Transferring the data into a second table having a NOT NULL constraint will fail.
Expected results
All operations should be successful as long as the strings have a length greater than one character or contain a character which is not the null character.
Workaround
Temporarily remove affected NOT NULL constraints.
Changed behavior
IMPORT of a string of length one containing a null character into a NOT NULL CHAR column having a length greater than one is now possible. Previously it triggered the NOT NULL constraint even though the CHAR padding made the value non-NULL.
