Published at March 8, 2024by Jörg Brandeis

In SQL, NULL behaves like a black hole. It doesn't follow the "normal" rules of Boolean logic and swallows entire expressions. And NULL can also arise during the execution of CDS ABAP. But ABAP can't handle it and translates such values into the initial value. That's sometimes handy, but it causes developers to lose their feel for the NULL value.

What is NULL?

"In SQL, null or NULL is a special marker used to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL null serves to fulfil the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". "

So NULL is not meant to be an actual value, but to indicate the absence of a value. Therefore, NULL in the database also does not correspond to the initial value.

NULL swallows expressions

If an expression contains a NULL value, then the result is NULL as well.

  {
    NetAmount * ( Vat / 100 + 1 ) as GrossAmount
  }

So in this example, if AMOUNT or VAT has the value NULL, then GrossAmount is NULL as well. The same applies, for example, to

• concatenation of character strings
• calls to SQL functions
• CASE expressions

Since the conversion of NULL to INITIAL only happens in ABAP, the logic of the expressions is different. In the following example you might expect: if the text is not found, then the field StatusText contains the value "Status:". Instead, the entire expression becomes NULL and thus empty in ABAP.

define view entity zbc_demo_null_in_expression
  as select from    zbc_tasks       as t
    left outer join zbc_status_text as st on  t.status    = st.status
                                          and st.language = $session.system_language
{
  t.task_key                                  as TaskKey,
  t.status                                    as Status,
  CONCAT_WITH_SPACE( 'Status: ', st.text, 1 ) as StatusText
}

NULL can (almost) not be compared

Comparing with NULL in SQL always yields the logical value UNKNOWN. So it is neither TRUE nor FALSE. In the following places, the only thing that matters is whether a predicate is true, i.e. returns the value TRUE:

• WHERE clause
• ON condition in a join
• CASE WHEN condition

No distinction is made between UNKNOWN and FALSE. The problem becomes clear when these values are negated:

• NOT TRUE yields FALSE
• NOT FALSE yields TRUE
• NOT UNKNOWN yields UNKNOWN

The following example illustrates the dilemma. A database table contains the following data:

If I want to find all records whose name starts with P, then the following query gives me the desired result:

SELECT * 
  FROM Tabelle
 WHERE Name LIKE 'P%'

But if I now search for all records whose name does not start with P, then the following query returns only the 4th record:

SELECT * 
  FROM Tabelle
 WHERE Name NOT LIKE 'P%' 

So the 5th record is not found by either of the two queries.

The IS NULL predicate

Unfortunately, a normal comparison with NULL doesn't work either: NAME = NULL always yields UNKNOWN. Because it doesn't matter whether NULL is on the left or right of the comparison operator. The only predicate that can find a NULL is called IS NULL. And it can be negated with NOT if needed.

SELECT * 
  FROM Tabelle
 WHERE Name NOT LIKE 'P%' 
    OR Name IS NULL

Where does NULL come from?

In the database of the ABAP system, NULL normally does not occur. The only method I know of to get NULL into a column is to add columns afterwards without setting the flag for initial values. But where else can this happen?

OUTER JOIN

With an OUTER JOIN, NULL is always used whenever no partner can be found in the other table for a row.

CASE without ELSE

If no condition is true in a CASE expression and no value has been defined for ELSE, then the CASE expression returns NULL.

And where is the problem with ABAP?

Apart from the points discussed, which an ABAP developer might not necessarily expect, there is one very concrete problem with the conversion of NULL to INITIAL. And it occurs when aggregating. If grouping is to be done by a column, then the database produces a row for each distinct value. For the database, NULL and INITIAL are two different values. In ABAP, however, they are mapped to the same value.

define view entity zbc_demo_null_in_expression
  as select from    zbc_tasks       as t
    left outer join zbc_status_text as st on  t.status    = st.status
                                          and st.language = $session.system_language
{
  st.text as StatusText,
  count(*) as cnt
}
group by st.text

Results in:

Interpretation of the result:

• In 31 rows the text "New" appears
• In 7 rows the text "In Process" appears
• For 12 rows, no matching row was found in the text table. The value is set to NULL.
• For 34 rows, an entry was found in the text table. But the value of StatusText was empty there. However, it can also be the other way around, because you can't tell from the initial value how it was formed.

Conclusion

The logic of SQL does not match the experience of an ABAP developer. But in CDS ABAP you relatively rarely deal with problems involving NULL. However, you'll spend a long time searching if you haven't understood the concept of NULL.