this was written up with the help of Claude Opus 4.8, but i also manually verified both the bug and the bugfix in a local build.

Indexed lt / lte returns an empty result when the column contains any null value

Version

• nitrite: 4.4.0 (latest release; also present in 4.2.x / 4.3.x — the same defect, in slightly different code)
• store: nitrite-mvstore-adapter 4.4.0 (in-memory MVStore in the repro)
• JDK: tested on 17 and 25

Summary

On an indexed field, where(field).lt(v) (and lte(v)) return no documents as soon as the field is null (or absent) in even one document — even though many documents have a non-null value < v. gt/gte/eq on the same field are unaffected, and the same lt filter works correctly if the field is not indexed (or contains no nulls). This makes a range filter like "value < X" silently drop all results on real-world data that has missing values.

Root cause

org.dizitart.no2.filters.LesserThanFilter#applyOnIndex (forward / non-reverse branch), current main/4.4.0:

DBValue firstKey = indexMap.firstKey();
while (firstKey != DBNull.getInstance()
        && Comparables.compare(firstKey, dbValue) < 0) {
    Object value = indexMap.get(firstKey);
    processIndexValue(value, subMap, nitriteIds);
    firstKey = indexMap.higherKey(firstKey);
}

When the index contains nulls, the leading key is the null key, and
IndexMap#firstKey() returns the DBNull sentinel for it:

public DBValue firstKey() {
    ...
    return dbKey == null ? DBNull.getInstance() : dbKey;   // leading null key -> DBNull.getInstance()
}

So firstKey == DBNull.getInstance(), the loop guard firstKey != DBNull.getInstance() is immediately false, and the ascending scan terminates before visiting any real key — returning an empty list. GreaterThanFilter seeds from higherKey(comparable) and EqualsFilter from get(value), so neither touches the leading null key — which is why only < / <= break.

(In 4.3.x the same defect reads slightly differently: firstKey() returned null for the leading null key and the guard was firstKey != null.)

Minimal reproduction

Nitrite db = Nitrite.builder()
    .loadModule(MVStoreModule.withConfig().build())
    .openOrCreate();
NitriteCollection c = db.getCollection("c");

c.insert(Document.createDocument("idx", 0).put("value", null));   // one null
for (int i = 1; i <= 10; i++)
    c.insert(Document.createDocument("idx", i).put("value", (double) i));

c.createIndex(IndexOptions.indexOptions(IndexType.NON_UNIQUE), "value");

long lt = c.find(where("value").lt(5.0)).size();   // expected 4  (values 1..4)
long gt = c.find(where("value").gt(5.0)).size();   // 5  (values 6..10)  OK
System.out.println(lt + " / " + gt);               // prints "0 / 5"

Expected

lt(5.0) returns the 4 documents with value in {1,2,3,4} (nulls are never < v, so they are correctly excluded — but the non-null values below v must still be returned).

Actual

lt(5.0) returns 0 documents. Dropping the index, or removing the null document, makes it return 4. Verified on the 4.4.0 release jars.

Suggested fix (one line)

Start the forward scan at the first non-null key instead of firstKey():

// LesserThanFilter#applyOnIndex, forward (non-reverse) branch
DBValue firstKey = indexMap.higherKey(DBNull.getInstance());   // was: indexMap.firstKey()

higherKey(DBNull.getInstance()) returns the first key strictly greater than the null sentinel — i.e. the first non-null key (or DBNull.getInstance() if the column is entirely null/empty, which the existing != DBNull.getInstance() guard then treats as "no matches"). This mirrors how GreaterThanFilter#applyOnIndex already scans (it seeds from higherKey(comparable) and never calls firstKey(), which is why > is unaffected).

Verified against the actual DBValue/DBNull ordering: with keys {DBNull, 1.0, 3.0, 9.0}, firstKey() returns DBNull.getInstance(), whereas higherKey(DBNull.getInstance()) returns 1.0; scanning < 3.0 from there correctly yields {1.0}. The reverse-scan branch (which seeds from lowerKey(comparable) and walks down) is already correct.

LesserEqualFilter#applyOnIndex has the identical forward-scan pattern and the identical one-line fix.