cockroachdb / cockroachdb/cockroach

sql: type-checking is too strict for the final statement of UDFs

Open
#119,614 1 comment 0 reactions 0 assignees View on GitHub
A-sql-pgcompat A-sql-routine C-bug E-quick-win T-sql-queries
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.