CockroachNULL.md

Data

CREATE TABLE test.t1 (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name STRING NOT NULL,
    age INT NOT NULL,
    deleted BOOL DEFAULT NULL,
    INDEX name_age_deleted(name, age, deleted),
    INDEX name_deleted_age(name, deleted, age)
);

INSERT INTO test.t1(name, age) VALUES('mike', 39);
INSERT INTO test.t1(name, age) VALUES('mike', 45);
INSERT INTO test.t1(name, age, deleted) VALUES('mike', 35, TRUE);
INSERT INTO test.t1(name, age) VALUES('bob', 30);

Query

EXPLAIN (VERBOSE) SELECT * FROM test.t1@name_deleted_age
WHERE name='mike' AND age < 40 AND deleted IS NULL ORDER BY age;

+-------+------+--------+-------------------------+----------------------------+--------------+
| Level | Type | Field  |       Description       |          Columns           |   Ordering   |
+-------+------+--------+-------------------------+----------------------------+--------------+
|     0 | sort |        |                         | (id, "name", age, deleted) | ="name",+age |
|     0 |      | order  | +age                    |                            |              |
|     1 | scan |        |                         | (id, "name", age, deleted) | ="name"      |
|     1 |      | table  | t1@name_deleted_age     |                            |              |
|     1 |      | spans  | /"mike"-/"mike"/NULL/40 |                            |              |
|     1 |      | filter | age < 40                |                            |              |
+-------+------+--------+-------------------------+----------------------------+--------------+

Filtering on age instead of using index and also doing sort by age instead of natural ordering of index.