Implicit typecast literals to correct type (postgres compatibility)

[stevecookform3]

Looks like postgres will auto-type cast literals based on the context (more info here: https://www.postgresql.org/docs/10/typeconv.html). That isnt done by CockroachDB.

Thats causing some incompatibilities when we try to migrate postgres over to cockroach.

Here are some simple examples - the following work fine in postgres 9.5, but fail with cockroach 19.1.3 as cockroach doesnt cast them to the expected type.

-- auto cast jsonb & text[] types from strings
SELECT jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);
Postgres -> [{"f1":[2,3,4],"f2":null},2,null,3]
Cockroach -> ERROR:  unknown signature: jsonb_set(string, string, string, bool)

-- auto cast numeric type from string
SELECT sign('180');
Postgres -> 1
Cockroach -> ERROR:  unknown signature: sign(string)

-- auto cast string from numeric
SELECT 123 || '456';
Postgres -> 123456
Cockroach -> ERROR:  unsupported binary operator: <int> || <string>

Looks like some specific examples have been mentioned already here:
#33341 and #23299

However the underlying issue I think is more generic than mentioned on other issues.

Epic CRDB-2474
Jira issue: CRDB-5598

[jordanlewis]

@stevecookform3, we do coerce many literals already. Text arrays are coming soon: #38869. String <->numeric is tricky to get right, so we haven't done it yet. Json actually does get automatically coerced:

[email protected]:54720/defaultdb> select '{"a":3}'->'a';
  ?column?
+----------+
         3
(1 row)

[jordanlewis]

But point taken that this is irritating. What ORM or framework do you use? Would you be willing to give us a sample of your application that runs into these issues?

Thanks!

[stevecookform3]

(github seems to have lost my previous comment, so apologies if this is a duplicate)
@jordanlewis we use an inhouse framework, so mostly this is just raw SQL similar to the cases above.
We can potentially just add explicit casts where we hit issues, which should work in both postgres & cockroach.
I thought it worth creating this as a separate issue as I could only find some specific examples in other tickets.

[mgartner]

-- auto cast jsonb & text[] types from strings
SELECT jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);
Postgres -> [{"f1":[2,3,4],"f2":null},2,null,3]
Cockroach -> ERROR:  unknown signature: jsonb_set(string, string, string, bool)

This works in CockroachDB version 19.2 and later.

-- auto cast numeric type from string
SELECT sign('180');
Postgres -> 1
Cockroach -> ERROR:  unknown signature: sign(string)

-- auto cast string from numeric
SELECT 123 || '456';
Postgres -> 123456
Cockroach -> ERROR:  unsupported binary operator: <int> || <string>

I believe these examples will require type resolution of functions as described in the Postgres type conversion docs. This is related to implicit casts (see #75101).

[jordanlewis]

These examples work now.

[mgartner]

@jordanlewis SELECT 123 || '456'; works but SELECT sign('180'); does not. I'm still getting an error:

defaultdb> SELECT sign('180');
ERROR: ambiguous call: sign(string), candidates are:
sign(float) -> float
sign(decimal) -> decimal
sign(int) -> int
SQLSTATE: 42725

[jordanlewis]

Isn't that a new kind of problem though? Should we open a new issue for that one?

[mgartner]

That example was given in the orginal PR description. I created #94718 to track part of what would be require to fix it. Fixing it would also require fixing our function overload resolution logic. The meta issue #75101 tracks some of this work.