cockroachdb / cockroachdb/cockroach

"VALUES types decimal and int cannot be matched" when mixing integer and numeric literals

Open
#132,165 3 comments 0 reactions 0 assignees View on GitHub
A-sql-pgcompat branch-release-23.1 C-bug O-community T-sql-foundations
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Describe the problem**

When using the `VALUES` constructor mixing integer and numeric literals, there's an error:

> SQL Error [42804]: ERROR: VALUES types decimal and int cannot be matched

**To Reproduce**

```sql
values
(1),
(1.1),
(1.10);
```

**Expected behavior**

I'd expect the integer to be widened automatically to `numeric`, just like when it is too large to fit in an integer anyway, e.g. this works:

```sql
values
(1000000000000000000000000000000),
(1.1),
(1.10);
```

E.g. PostgreSQL handles this

**Environment:**
- CockroachDB version: CockroachDB CCL v24.2.3 (x86_64-pc-linux-gnu, built 2024/09/23 22:30:53, go1.22.5 X:nocoverageredesign)
- Server OS: Docker on Windows
- Client app: JDBC

**Workarounds**

Avoid the implicit data types with casts:

```sql
values
(cast(1 as numeric)),
(1.1),
(1.10);
```

Or turn the value into a numeric literal:

```sql
values
(1E0), -- also 1.0
(1.1),
(1.10);
```

Use union instead:

```sql
select 1
union all
select 1.1
union all
select 1.10
```

Jira issue: CRDB-42860

Epic CRDB-60813

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.