Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
rharding6373 opened this issue Apr 7, 2023 · 0 comments
Open

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

rharding6373 opened this issue Apr 7, 2023 · 0 comments
Labels
A-sql-routine UDFs and Stored Procedures C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-queries SQL Queries Team

Comments

@rharding6373
Copy link
Collaborator

rharding6373 commented Apr 7, 2023

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

@rharding6373 rharding6373 added C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. A-sql-routine UDFs and Stored Procedures labels Apr 7, 2023
@blathers-crl blathers-crl bot added the T-sql-queries SQL Queries Team label Apr 7, 2023
@mgartner mgartner moved this to Bugs to Fix in SQL Queries Jul 24, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
A-sql-routine UDFs and Stored Procedures C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. T-sql-queries SQL Queries Team
Projects
Status: Bugs to Fix
Development

No branches or pull requests

1 participant