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

[lukaseder]

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

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:

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:

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

Or turn the value into a numeric literal:

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

Use union instead:

select 1
union all
select 1.1
union all
select 1.10

Jira issue: CRDB-42860

[blathers-crl[bot]]

Hi @lukaseder, please add branch-* labels to identify which branch(es) this C-bug affects.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}