sql: inequality filter on indexed computed column does not always become constrained scan

[michae2]

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:

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:

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:

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

[michae2]

Best idea I've had so far is to turn NormalizeCmpPlusConst into an exploration rule.

[mgartner]

We run into similar problems when these types of virtual computed columns (i.e., ones with simple arithmetic operators that are likely to be normalized) are referenced in partial index predicates. They can prevent us from proving partial index implication. See #123163 which is somewhat relevant.

Best idea I've had so far is to turn NormalizeCmpPlusConst into an exploration rule.

Another option would be to try to denormalize expressions into computed column expressions when building constraints and proving implication.