dolthub / dolthub/dolt

Bad index plan for lookup on NULL

Open
#8,885 2 comments 0 reactions 0 assignees View on GitHub
analyzer sql
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

```
create table test(pk int primary key, c0 int, key idx1(c0, pk));
insert into test values (1, 0), (2, 1), (3, 2), (4, 1), (5, 2), (6, 0), (7, 2), (8, 0), (9, 1), (10, NULL);
describe plan select * from test where c0 is NULL and pk > 1;
```

Expected plan:

```
+------------------------------------------+
| plan |
+------------------------------------------+
| IndexedTableAccess(test) |
| ├─ index: [test.c0,test.pk] |
| ├─ filters: [{[NULL, 1], [NULL, ∞)}] |
| └─ columns: [pk c0] |
+------------------------------------------+
```

Actual plan:

```
+------------------------------+
| plan |
+------------------------------+
| Filter |
| ├─ test.c0 IS NULL |
| └─ IndexedTableAccess(test) |
| ├─ index: [test.pk] |
| ├─ filters: [{(1, ∞)}] |
| └─ columns: [pk c0] |
+------------------------------+
```

We should be able to use `idx1` to implement the query with a single indexed table access, no filter required. And if the query uses a non-NULL value for `c0`, we do. But when`c0` is being compared to `NULL`, the engine won't pick the secondary index.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.