cockroachdb / cockroachdb/cockroach
sql: type-checking is too strict for the final statement of UDFs
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
In postgres, it is possible for the result column types of a UDF to be non-equivalent to its return type. Function creation should fail if an assignment cast is impossible, but otherwise it should succeed. Currently, we handle this for the single-column case. However, we fail in the multiple result column case even if an assignment cast is possible.
Postgres:
```
postgres=# CREATE FUNCTION f(OUT x FLOAT, OUT y TEXT) LANGUAGE SQL AS $$ SELECT 1, 2; $$;
CREATE FUNCTION
postgres=# SELECT * FROM f();
x | y
---+---
1 | 2
(1 row)
postgres=# SELECT *, pg_typeof(x), pg_typeof(y) FROM f();
x | y | pg_typeof | pg_typeof
---+---+------------------+-----------
1 | 2 | double precision | text
(1 row)
```
CRDB:
```
root@localhost:26257/defaultdb> CREATE FUNCTION f(OUT x FLOAT, OUT y TEXT) LANGUAGE SQL AS $$ SELECT 1, 2; $$;
ERROR: return type mismatch in function declared to return record
SQLSTATE: 42P13
DETAIL: Final statement returns int instead of float at column 1
```
Jira issue: CRDB-36239
Contributor guide
Assessment
This issue has not been assessed yet.