DB2 Version 9.7 for Linux, UNIX, and Windows

Untyped NULL keyword specifications no longer resolve to identifier names

Starting in Version 9.7, you can specify an untyped NULL keyword anywhere an expression is allowed. The behavior of existing expressions with NULL identifiers that are not qualified and not delimited might resolve to a null value instead of an identifier name and produce different results.

Details

To provide additional flexibility when creating expressions, you can now specify untyped NULL keyword specifications anywhere an expression is allowed. As a result, references to the NULL keyword that are not qualified and not delimited resolve to the null value when SQL statements are compiled rather than resolving to an identifier name as it was the case in earlier releases. For example, if a database identifier is named NULL and is used in an SQL statement without being fully qualified or delimited, the identifier specification might resolve to the keyword NULL instead of the identifier reference.

Assuming you have the following table and data:

CREATE TABLE MY_TAB (NULL INT)   
INSERT INTO MY_TAB VALUES (1)

When you issue the following statement:

SELECT NULL FROM MY_TAB

In previous releases, the null that is specified in the select list resolves to the column named NULL. Starting in Version 9.7, it resolves to the null value.

Also, when you issue the following statement:

SELECT NULL FROM TABLE(VALUES(1)) AS X(NULL)

In previous releases, this statement returns a value of 1. Starting in Version 9.7, this statement returns a null value.

User response

To avoid conflicts with the keyword NULL, columns named NULL should be fully qualified or delimited when used in SQL statements.

Review existing expressions that use untyped NULL keywords specifications, and update them if needed. You can use the db2ckupgrade command to check for identifiers named "NULL".

For example, the following expressions can be used to produce the behaviors from previous releases:

SELECT MY_TAB.NULL FROM MY_TAB
SELECT "NULL" FROM MY_TAB