cockroachdb / cockroachdb/cockroach
sql: table-returning UDF loses declared column name when aliased in FROM
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Describe the problem**
A SQL-language UDF declared with `RETURNS TABLE (col ...)` loses its declared output column name when the function call is given a table alias in the `FROM` clause. Instead of keeping the declared column name, the single output column is renamed to the table alias itself.
This diverges from PostgreSQL, where a table alias renames the relation but leaves the column names intact (column names are only overridden by an explicit column alias list, e.g. `AS ul(x)`).
As a consequence, references of the form `alias.declared_column` fail to resolve, which breaks the common pattern of selecting a specific column out of an aliased table-function call.
**To Reproduce**
```sql
CREATE TABLE t (
id INT PRIMARY KEY,
v INT DEFAULT NULL
);
CREATE OR REPLACE FUNCTION f1()
RETURNS TABLE (out_col INT)
LANGUAGE SQL AS $$
SELECT v AS out_col FROM t LIMIT 2;
$$;
-- Correct: the output column is named `out_col`.
SELECT * FROM f1();
-- out_col
-- -----------
-- (0 rows)
-- BUG: the output column is named `f` (the alias) instead of `out_col`.
SELECT * FROM f1() AS f;
-- f
-- -----
-- (0 rows)
-- BUG (downstream consequence): fails with
-- column "f.out_col" does not exist
CREATE OR REPLACE FUNCTION f2()
RETURNS TABLE (out_col INT)
LANGUAGE SQL AS $$
SELECT f.out_col FROM f1() AS f;
$$;
```
**Expected behavior**
Consistent with PostgreSQL: a bare table alias (`AS f`) should rename only the relation, leaving the declared column name `out_col` intact. `SELECT * FROM f1() AS f` should return a column named `out_col`, and `f.out_col` should resolve. The column should only be renamed when an explicit column-alias list is supplied (e.g. `AS f(x)`).
**Environment:**
- CockroachDB version: reproduced on current `master`
**Additional context**
Discovered from a customer case where a table-returning UDF was invoked from another UDF using an alias; the wrapping function failed to create with `column "." does not exist`.
Related but distinct: #167213 covers dereferencing a table-valued function by its function name when *no* alias is given; this issue is about the case where an alias *is* given.
Jira issue: CRDB-67017
Contributor guide
Research direction
Start with the SQL reproduction in this issue and trace CockroachDB's handling of table aliases for SQL-language, table-returning UDFs. Compare bare aliases with explicit column-alias lists and verify that the declared column name remains available through the alias, including in the nested f2() example.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 58/100