cockroachdb / cockroachdb/cockroach

sql: some ambiguous strict udfs should not error with null args

Open
#100,928 0 comments 0 reactions 0 assignees View on GitHub
A-sql-routine C-bug T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Some record-returning UDFs return NULL or no columns (if used with SETOF) instead of returning an "ambiguous call" error.

The following logic test shows some examples and notes the expected output in postgres.

```
statement ok
CREATE FUNCTION f_amb_setof(a INT8, b INT8) RETURNS SETOF RECORD AS
$$
SELECT a, b;
$$ LANGUAGE SQL;

statement ok
CREATE FUNCTION f_amb_setof(a INT8, b STRING) RETURNS SETOF RECORD AS
$$
SELECT a, b;
$$ LANGUAGE SQL;

# In postgres, calls to f_amb_setof should succeed and return 0 rows for 1 column.
statement error pq: ambiguous call: f_amb_setof\(int, unknown\), candidates are
SELECT f_amb_setof(1, NULL);

# In postgres, calls to f_amb_setof as a data source should succeed and return 0 rows for 2 columns.
statement error pq: ambiguous call: f_amb_setof\(int, unknown\), candidates are
SELECT * FROM f_amb_setof(1, NULL) as foo(a int, b int);

statement ok
CREATE FUNCTION f_amb(a INT, b INT) RETURNS RECORD STRICT AS
$$
SELECT a, b;
$$ LANGUAGE SQL;

statement ok
CREATE FUNCTION f_amb(a INT, b STRING) RETURNS RECORD STRICT AS
$$
SELECT a, b;
$$ LANGUAGE SQL;

# In postgres, calls to f_amb should succeed and return NULL.
statement error pq: ambiguous call: f_amb\(int, unknown\), candidates are
SELECT f_amb(1, NULL);

# In postgres, calls to f_amb as a data source should succeed and return NULL NULL.
statement error pq: ambiguous call: f_amb\(int, unknown\), candidates are
SELECT * FROM f_amb(1, NULL) as foo(a int, b int);
```

Jira issue: CRDB-26703

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.