How Strategy Functions Handle Null Values

If you specify a null value for any of the arguments of a strategy function in the WHERE clause of a query, the query always returns 0 rows. This is true even if you specified that the strategy function handles nulls when you created the strategy function with the CREATE FUNCTION statement.

For example, assume you previously inserted a null value into the circle_tab table with the following INSERT statement:

INSERT INTO circle_tab VALUES (1, NULL);

The following query that uses the Equal strategy function to search for null values always returns 0 rows, even though a null value does exist in the table:

SELECT * FROM circle_tab WHERE Equal (circles, NULL);

Zero rows are always returned because null values are never part of an R-tree index; they are stored only in the table. To search for null values in a column on which you created an R-tree index, use the IS NULL condition in the WHERE clause of the query, as the following example shows:

SELECT * FROM circle_tab WHERE circles IS NULL;

The preceding query does not use the R-tree index, and thus the database server must perform a full table scan. However, because the query is searching the table, the query returns what you expect: those rows whose circles column is null.