cockroachdb / cockroachdb/cockroach
"VALUES types decimal and int cannot be matched" when mixing integer and numeric literals
- 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
Assessment
This issue has not been assessed yet.