sql: value type decimal doesn't match type float

[maesenka]

Describe the problem

This came up in the Hibernate ORM tests. A SQL like this:

with HTE_Human (id, bodyWeight, heightInches) as (select (c1_0.mammal * 10), c1_1.body_weight, 180.0
                                                  from Cat c1_0
                                                           join Animal c1_1 on c1_0.mammal = c1_1.id),
     dml_cte_Animal (id)
         as (insert into Animal (id, body_weight) select e.id, e.bodyWeight from HTE_Human e returning id),
     dml_cte_Mammal (id) as (insert into Mammal (animal) select e.id from HTE_Human e returning animal),
     dml_cte_Human (id)
         as (insert into Human (mammal, height_centimeters) select e.id, e.heightInches from HTE_Human e returning mammal)
select count(*)
from dml_cte_Animal id;

throws the error: [42804] ERROR: value type decimal doesn't match type float of column "height_centimeters"

To Reproduce

See above

Expected behavior
As in Postgresql, automatic type conversion between compatible types.

Environment:

• CockroachDB version 20.2.10
• Server OS: Linux
• Client app JDBC

This was submitted as part of work to fix the Hibernate ORM CI test suite for CockroachDB

Jira issue: CRDB-16096

[blathers-crl]

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 have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-experience (found keywords: ORM,ORM,Hibernate)

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.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[otan]

unfortunately this is a problem with "implicit casts" and is not something we support right now.

if you need an issue number to reference, it would be #75101. in the meantime, the only solution is to explicitly cast a type.

i am closing this as a duplicate