cockroachdb / cockroachdb/cockroach
sql: inequality filter on indexed computed column does not always become constrained scan
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Usually `GenerateConstrainedScans` is able to turn a filter on a computed column into a constrained scan of an index on that computed column. Here's a simple example:
```sql
CREATE TABLE ab (a INT PRIMARY KEY, b INT NOT NULL AS (a % 10) VIRTUAL, INDEX (b));
EXPLAIN (OPT) SELECT * FROM ab@ab_b_idx WHERE b > 5;
```
Produces the constrained scan we expect:
```
project
├── scan ab@ab_b_idx
│ ├── constraint: /2/1: [/6 - ]
│ └── flags: force-index=ab_b_idx
└── projections
└── a % 10
```
But if I change the expression from `PK % 10` to `PK + 1`, then `NormalizeCmpPlusConst` simplifies the filter to a constant `c > 4` and this messes up some part of `GenerateConstrainedScans`:
```sql
CREATE TABLE cd (c INT PRIMARY KEY, d INT NOT NULL AS (c + 1) VIRTUAL, INDEX (d));
EXPLAIN (OPT) SELECT * FROM cd@cd_d_idx WHERE d > 5;
```
Produces a full scan and a filter:
```
project
├── select
│ ├── scan cd@cd_d_idx
│ │ └── flags: force-index=cd_d_idx
│ └── filters
│ └── c > 4
└── projections
└── c + 1
```
Interestingly, if I change the query to use equality instead of an inequality it _is_ able to generate a constrained scan:
```sql
EXPLAIN (OPT) SELECT * FROM cd@cd_d_idx WHERE d = 5;
```
Produces:
```
project
├── scan cd@cd_d_idx
│ ├── constraint: /2/1: [/5/4 - /5/4]
│ └── flags: force-index=cd_d_idx
└── projections
└── c + 1
```
This behavior reproduces on both v23.2.0 and tip of master (v24.1.0-alpha.2).
Jira issue: CRDB-36337
Contributor guide
Assessment
This issue has not been assessed yet.