 |
| Special July 2017 - Null value handling |
|---|
| Category : User |
| Level : Easy |
| Back to Overview |
For some database users, null values might be a tricky thing. Nevertheless, for a clean database design it is important to understand the concept and deal with this special value. This web special describes how to handle null values with the cego database system which might be a little be different in some aspects.
We start by creating a table with a single nullable attribute a
ws > create table t1 ( a int null ); Table t1 created ok ( 0.000 s ) ws > desc table t1; +-----------+-----------+-----------+-----------+-----------+ | TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC | TABLEDESC | | ATTR | TYPE | LENGTH | DEFAULT | NULLABLE | +-----------+-----------+-----------+-----------+-----------+ | a | int | 4 | null | yes | +-----------+-----------+-----------+-----------+-----------+ 1 tuples ok ( 0.000 s ) |
Now we insert some tuples into the table
ws > insert into t1 values ( 1 ); 1 tuples inserted ok ( 0.000 s ) ws > insert into t1 values ( 2 ); 1 tuples inserted ok ( 0.000 s ) ws > insert into t1 values ( 3 ); 1 tuples inserted ok ( 0.000 s ) ws > insert into t1 values ( 4 ); 1 tuples inserted ok ( 0.000 s ) ws > insert into t1 values ( 5 ); 1 tuples inserted ok ( 0.000 s ) ws > insert into t1 values ( null ); 1 tuples inserted ok ( 0.000 s ) ws > insert into t1 values ( -3 ); 1 tuples inserted |
Now we can retrieve the inserted tuples by asking for "is null" or "is not null"
ws > select * from t1 where a is null; +-----------+ | t1 | | a | +-----------+ | null | +-----------+ 1 tuples ok ( 0.000 s ) ws > select * from t1 where a is not null; +-----------+ | t1 | | a | +-----------+ | 1 | | 2 | | 3 | | 4 | | 5 | | -3 | +-----------+ 5 tuples ok ( 0.000 s ) |
For Standard SQL, these are the only conditions for null values which returns a senseful result. The standard states that NULL is never equal to anything, so comparing null values with any other values always results in an empty set. cego takes a different approach with the assumption, that null is less than all other values.
The ordering of the null values can be proofed by adding the ordering clause to the query.
ws > select * from t1 order by a; +-----------+ | t1 | | a | +-----------+ | null | | -3 | | 1 | | 2 | | 3 | | 4 | | 5 | +-----------+ 7 tuples ok ( 0.000 s ) |
The complete ordering approach results in corresponding results for any conditions
ws > select * from t1 where a = null; +-----------+ | t1 | | a | +-----------+ | null | +-----------+ 1 tuples ok ( 0.000 s ) ws > select * from t1 where a > null; +-----------+ | t1 | | a | +-----------+ | 1 | | 2 | | 3 | | 4 | | 5 | | -3 | +-----------+ 6 tuples ok ( 0.000 s ) ws > select * from t1 where a < null; No rows ok ( 0.000 s ) |
Please note : In Standard SQL, all three queries return empty result sets
Now we will look at how index objects behave with null values For this, we create a unique btree b1 on t1
ws > create unique btree b1 on t1 ( a ); Btree b1 created ok ( 0.000 s ) |
We already have inserted one null value in t1. So now we try to add a second one
ws > insert into t1 values ( null ); Error : Duplicate key null on unique btree b1 Query failed |
The query fails, since the null value is compared to the existing one and this violates the index contraint. Since in standard SQL, the comparison in any time returns false, duplicate null values also for index objects with unique contraint are allowed. To simulate this behaviour in cego, the DUPLICATENULL flag in the xml database control file can be set to "ON".
<DATABASE NAME="cgdb" PAGESIZE="8192" ... DUPLICATENULL="ON" ... |
This has to be done by the database administrator and is set up for the whole database with all configured tablesets. If set up, null values duplicates are allowed also for unique index objects
ws > insert into t1 values ( null ); 1 tuples inserted ok ( 0.000 s ) ma > select * from t1; +-----------+ | t1 | | a | +-----------+ | 1 | | 2 | | 3 | | 4 | | 5 | | null | | -3 | | null | +-----------+ 8 tuples ok ( 0.000 s ) |