cockroachdb / cockroachdb/cockroach

sql: functional notation to select attribute of record type returned by UDF doesn't work

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

Description

**Describe the problem**

If I try to use functional notation to select an attribute of a record type that is returned by a UDF, the entire record is returned.

**To Reproduce**

On cockroach demo, run the following:

```
CREATE TABLE emp (name TEXT, salary INT);

CREATE FUNCTION new_emp() RETURNS emp AS $$
SELECT ROW('None', 1000.0)::emp;
$$ LANGUAGE SQL;

SELECT name(new_emp());
```
On Postgres, this returns just the `name` column, but on CRDB, the whole record is returned:
```
demo@127.0.0.1:26257/movr> SELECT name(new_emp());
name
---------------
(None,1000)
(1 row)
```

**Expected behavior**

Like Postgres, we should just return the `name` column:
```
postgres=# SELECT name(new_emp());
name
------
None
(1 row)
```

**Environment:**
- CockroachDB version: master (i.e. 23.1 alpha)
- Server OS: MacOS Ventura (Apple M1 Pro)
- Client app: cockroach demo

Jira issue: CRDB-26111

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the reported CREATE TABLE, CREATE FUNCTION, and SELECT name(new_emp()) statements with cockroach demo, then compare the result with PostgreSQL. Done means functional notation on a record returned by a SQL UDF returns only the selected attribute, such as None, rather than the whole record.

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
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.