cockroachdb / cockroachdb/cockroach

sql: table-returning UDF loses declared column name when aliased in FROM

Open
#173,710 2 comments 0 reactions 0 assignees View on GitHub
A-sql-pgcompat A-sql-routine C-bug O-agent T-sql-queries
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.