FirebirdSQL / firebird

Firebird server, client and tools
https://firebirdsql.org
1.26k stars 217 forks source link

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

Closed sim1984 closed 3 weeks ago

sim1984 commented 1 month ago
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)
dyemanov commented 3 weeks ago

The fix wasn't fully correct, hence reopened.

For compound index (A, B) and condition A IS NULL AND B = <value> the plan is now reported as Range Scan while it should be Unique Scan as before the fix:

SQL> select count(*) from rdb$procedures where rdb$package_name is null and rdb$procedure_name = 'ABC';

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