SPS 5 quick note – on NULL values

One thing I found a bit annoying with HANA had been the fact that it was not possible to go and change table definitions later on as easily as I was used to from other DBMS.

The change I wanted to implement was to add a NOT NULL constraint to a table already containing data.

Up to Revision 41 (the last SPS 4 revision), this was what happened:

Revision 41 – you know this old, outdated stuff… 😉

  drop table aaa;
    create column table aaa (cola nvarchar(30) not null ,
                             colb integer ,
                             flag varchar(1) default ' ');
    insert into aaa values ('A', NULL, NULL);
    insert into aaa values ('A', 10, NULL);
    select * from aaa;
    COLA    COLB    FLAG
    A       NULL    NULL
    A       10      NULL

We’ve got a table, we’ve got data in it and there are NULL values in our table.

Let’s do something about this!.

    alter table "AAA" alter ("COLB" INTEGER NOT null);
    Could not execute 'alter table "AAA" alter ("COLB" INTEGER NOT null)' in 25 ms 571 µs Started: 2012-12-13 12:23:43.
    SAP DBTech JDBC: [7] (at 25):
    feature not supported: NULL value exists: COLB: line 1 col 26 (at pos 25) 

Fair enough, there are NULL values in there, so I have to get rid of them before enabling the new constraint.

Let’s do that:

    update aaa set colb=0 where colb is null;
    select * from aaa;
    COLA    COLB    FLAG
    A       0       NULL
    A       10      NULL

Now, let’s retry to enable the constraint:

    alter table "AAA" alter ("COLB" INTEGER NOT null);
    Could not execute 'alter table "AAA" alter ("COLB" INTEGER NOT null)' in 29 ms 68 µs Started: 2012-12-13 12:24:39.
    SAP DBTech JDBC: [7]: feature not supported: table must be empty to add NOT NULL column

“Feature not supported” Bummer!

Of course, at this point I could still create a new table with the constraint in place, copy over the data and the drop the original table.

But who on earth would want that?

Not me (so I opened a support message, asking for this feature to become supported…)!

Now we’re on SPS 5 (revision 45), everything is shiny and new, right?

Let’s see what happened to my request…

    drop table aaa;
    create column table aaa (cola nvarchar(30) not null ,
                             colb integer ,
                             flag varchar(1) default ' ');
    insert into aaa values ('A', NULL, NULL);
    insert into aaa values ('A', 10, NULL);
    select * from aaa;
     COLA    COLB    FLAG
    A       NULL    NULL
    A       10      NULL
   alter table "AAA" alter ("COLB" INTEGER NOT null);
   Could not execute 'alter table "LARS"."AAA" alter ("COLB" INTEGER not null)' in 44 ms 528 µs Started: 2012-12-13 12:27:17.
   SAP DBTech JDBC: [7]: feature not supported: NULL value exists: COLB: line 1 col 33 (at pos 32) 

Up to here no changes (good thing!) …

   update aaa set colb=0 where colb is null;
    select * from aaa;
    COLA    COLB    FLAG
    A       10      NULL
    A       0       NULL
   alter table "LARS"."AAA" alter ("COLB" INTEGER not null);
   Statement 'alter table "AAA" alter ("COLB" INTEGER NOT null)' successfully executed in 2.268 seconds
   Started: 2012-12-13 12:28:12 (server processing time: 2.245 seconds) - Rows Affected: 0 

This is A-amazing, isn’t it?

Really nice to now and have, especially if you finally decide to clean up your data model and get all the required constraints straight, that are required to keep data as clean as possible.

One more thing on NULLs…

One thing that comes play, when you actually DO allow NULLs in your database: how do you SORT the NULL value?

If your table contains NULLs in a column and you want to sort by this column, where would you want to see these NULLs?

At the end of the list? At the top?

Up to SPS4, there was just the default sort order available:

NULLs were dealt with as if they were smaller than the smallest value of the column.

So ‘naturally’ the place of the NULLs would be the top of the list (again, default order being ASCending).

While this is usually OK-ish if you know about it, there are for sure as much reasons to put NULLs at the end of the list as there are to put them to the top.

Starting with SPS5 you have the choice with that (see the docs here):

    insert into aaa values ('C', 5, 'F');
    insert into aaa values ('C', 5, NULL);
    select * from aaa;
    COLA    COLB    FLAG
    A       10      NULL
    A       0       NULL
    B       10      NULL
    C       5       F  
    C       5       NULL

NULL first please:

    select cola, colb, flag from aaa
    order by flag NULLS FIRST;
    COLA    COLB    FLAG
    A       10      NULL
    A       0       NULL
    B       10      NULL
    C       5       NULL
    C       5       F   

The other way ’round:

    select cola, colb, flag from aaa
    order by flag NULLS LAST;
    COLA    COLB    FLAG
    C       5       F  
    A       10      NULL
    A       0       NULL
    B       10      NULL
    C       5       NULL  

Attention though when counting/summing/aggregating!

    select cola, count(flag) from aaa
    group by cola
    order by count(flag) NULLS LAST;
    COLA    COUNT(FLAG)
    A       0            <<< NULLS treated as 0's (zeros)
    B       0         
    C       1         

Once again, that’s all folks!

Cheers,
Lars