16.20 - Recommendation for Handling Nulls - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
Release Date
March 2019
Content Type
Programming Reference
Publication ID
English (United States)

If you intend to support either of the following things with respect to UDT nulls, then you should specify a NOT NULL attribute for any column typed with that UDT:

  • Support null UDT attributes.
  • Code MAP ordering routines that can return nulls.

The reason for this recommendation probably is not obvious. The problem that must be dealt with is that if nulls are permitted in the column and either the MAP ordering routine or the system-generated observer routine can return nulls, the semantics are somewhat similar to the situation where you query a column that is specified with the NOT CASESPECIFIC attribute, where, for identical queries against static data, the results the system returns to the requestor can vary from request to request.

The ordering routine for a UDT determines both how comparisons of column values are made and the sort order for values having that user-defined data type.

If you do not follow this recommendation, then it is possible for a column null and a structured type containing null attributes whose MAP or observer routine returns NULL to be treated equally. This means that sometimes the system might return a column null in the result set and other times it might return the non-null structured UDT that contains null attributes in the result set.

Assuming that the structured UDT named myStructUdtWithNullAttributes was created with mapping and observer routines that can return nulls, and that the type supports null attributes (which can be inferred from its name), then a table defined along the lines of the following example, with all UDT columns specified with the NOT NULL attribute, cannot behave indeterminately with respect to nulls returned by a query against it:

    CREATE TABLE udtTable (
      id         INTEGER,
      udtColumn  myStructUdtWithNullAttributes NOT NULL);

If you create a structured UDT that might be used for a hash index definition, you must ensure that its ordering function does not return a null. Otherwise, Teradata Database returns an error to the requestor.