cockroachdb / cockroachdb/cockroach
sql: functional notation to select attribute of record type returned by UDF doesn't work
- 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
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