cockroachdb / cockroachdb/cockroach

opt: eliminate all arguments of coalesce after the first argument that is guaranteed to be non-null

Open
#103,596 7 comments 0 reactions 0 assignees View on GitHub
C-enhancement T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

An expression `COALESCE(a1, a2, ..., an)` can be simplified to `a1` if a1 is guaranteed to not be null.

For example:

```sql
CREATE TABLE ij (i INT NOT NULL, j INT);

SELECT COALESCE(i, j) FROM ij;
```

The query plan in v22.2.6 is:

```
project
├── columns: coalesce:6(int)
├── stats: [rows=1000]
├── cost: 1145.04
├── distribution: us-east1
├── prune: (6)
├── scan i
│ ├── columns: i:1(int!null) j:2(int)
│ ├── stats: [rows=1000]
│ ├── cost: 1125.02
│ ├── distribution: us-east1
│ └── prune: (1,2)
└── projections
└── coalesce [as=coalesce:6, type=int, outer=(1,2)]
├── variable: i:1 [type=int]
└── variable: j:2 [type=int]
```

This can be simplified to simply:

```
scan i
├── columns: i:1(int!null)
├── stats: [rows=1]
├── cost: 25.9
├── distribution: us-east1
└── prune: (1)
```

Jira issue: CRDB-28093

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.