Expected:
INDEX ON ... FOR ... CANDIDATE don't fire a null error when a FOR filter it filters the null keys.
Observed:
a filtered index uses two different ways:
- way 1: it applies FOR and only if it is true it inserts the key in the file.
this is used in the commands
INDEX ON
REINDEX
( this is ok for a performance gain )
- way 2: insert/update the key and after mark it with the flag gotten from
the FOR filter;
this has as a result that changing the order
of the lines different results are gotten.
( this is inevitable however,
but the filter can be calculated before and to mark the keys as "out of set")
Repro:
Write the same program into two ways:
clear
CREATE CURSOR test (f1 I NULL)
TRY
INSERT INTO test VALUES (NULL)
INDEX ON F1 FOR F1>0 TAG T1 CANDIDATE
SET ORDER TO 0
CATCH
? MESSAGE()
ENDTRY
BROWSE
clear
CREATE CURSOR test (f1 I NULL)
TRY
INDEX ON F1 FOR F1>0 TAG T1 CANDIDATE
INSERT INTO test VALUES (NULL)
SET ORDER TO 0
CATCH
? MESSAGE()
ENDTRY
BROWSE
solutions:
- CANDIDATE reject nullable keys ( F1 is nullable ) NO GOOD FOR ME
- CANDIDATE it accepts filter null keys GOOD FOR ME
Enhancement:
- CANDIDATE it accept one null not filtered VERY GOOD for work with a editing new row