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.