Skip to content

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

Closed
Closed
"Unique scan" is incorrectly reported in the explained plan for unique index and IS NULL predicate#8290
Assignees
dyemanov
Labels
affect-version: 3.0.12affect-version: 4.0.5affect-version: 5.0.0affect-version: 5.0.1affect-version: 6.0 Initialcomponent: enginefix-version: 5.0.2fix-version: 6.0 Alpha 1priority: minorqa: done successfullyrlsnotes60: noIntentionally not added to the Firebird 6.0 release notes.type: bug
@sim1984

Description

@sim1984
sim1984
opened on Oct 21, 2024
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)

Metadata

Metadata