firebird icon indicating copy to clipboard operation
firebird copied to clipboard

"Unique scan" is incorrectly reported in the explained plan for unique index and IS NULL predicate

Open sim1984 opened this issue 1 year ago • 0 comments

SELECT *
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME IS NULL

Explain plan

Select Expression
    -> Filter
        -> Table "RDB$RELATIONS" Access By ID
            -> Bitmap
                -> Index "RDB$INDEX_0" Unique Scan

Unique indexes can generally contain multiple NULL values, so "Unique scan" is only possible for pure equality (=) comparisons, but not for IS NULL and IS NOT DISTINCT FROM predicates. For such predicates, "Range Scan (full match)" is expected.

Select Expression
    -> Filter
        -> Table "RDB$RELATIONS" Access By ID
            -> Bitmap
                -> Index "RDB$INDEX_0" Range Scan (full match)

sim1984 avatar Oct 21 '24 14:10 sim1984