Recommendation for Handling Nulls | CREATE ORDERING | Teradata Vantage - 17.10 - Recommendation for Handling Nulls - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
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, the database returns an error to the requestor.