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.

Sign up for GitHub

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

Jump to bottom

sql: UDFs with implicit record return types should produce a row, not a tuple, when referenced as a data source #97059

Closed
mgartner opened this issue Feb 13, 2023 · 1 comment · Fixed by #98162
Closed

sql: UDFs with implicit record return types should produce a row, not a tuple, when referenced as a data source #97059

mgartner opened this issue Feb 13, 2023 · 1 comment · Fixed by #98162
Assignees
@rharding6373
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

@mgartner
Copy link
Collaborator

mgartner commented Feb 13, 2023 •
edited by cockroach-jira-scripts
Loading

For example, consider the SQL:

DROP FUNCTION IF EXISTS f;
DROP FUNCTION IF EXISTS t;

CREATE TABLE t (a INT, b INT);
CREATE FUNCTION f() RETURNS t LANGUAGE SQL AS 'select 1, 2';

SELECT * FROM f();

Postgres returns the row:

 a | b
---+---
 1 | 2
(1 row)

But CRDB returns the tuple:

    f
---------
  (1,2)
(1 row)

Jira issue: CRDB-24485
@mgartner mgartner 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 Feb 13, 2023
@blathers-crl blathers-crl bot added the T-sql-queries SQL Queries Team label Feb 13, 2023
@rharding6373
Copy link
Collaborator

rharding6373 commented Feb 22, 2023 •
edited
Loading

Additionally, when used as a data source, functions returning records require a column definition list.

Postgres example courtesy of @DrewKimball :

CREATE FUNCTION f() RETURNS RECORD LANGUAGE SQL BEGIN ATOMIC; SELECT 1; END;

SELECT * FROM f();
ERROR:  a column definition list is required for functions returning "record"
LINE 1: select * from f();
                      ^
SELECT * FROM f() AS foo(a INT);
 a 
---
 1
(1 row)

@rharding6373 rharding6373 self-assigned this Feb 22, 2023
@rharding6373 rharding6373 mentioned this issue Mar 7, 2023
Merged
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Mar 9, 2023
@rharding6373
sql: fix record-returning udfs when used as data source
22deff9 
When record-returning UDFs (both implicit and `RECORD` return types) are
used as a data source in a query, the result should be treated as a row
with separate columns instead of a tuple, which is how UDF output is
normally treated. This PR closes this gap between CRDB and Postgres.

For example:

```
CREATE FUNCTION f() RETURNS RECORD AS
$$
  SELECT 1, 2, 3;
$$ LANGUAGE SQL

SELECT f()
    f
--------
 (1,2,3)

SELECT * FROM f() as foo(a int, b int, c int);
 a | b | c
---+---+---
 1 | 2 | 3
```

The behavior is the same for implicit record return types.

Epic: CRDB-19496
Fixes: cockroachdb#97059

Release note (sql change): UDFs with record-returning types now return a
row instead of a tuple.
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Mar 10, 2023
@rharding6373
sql: fix record-returning udfs when used as data source
0fb39a5 
When record-returning UDFs (both implicit and `RECORD` return types) are
used as a data source in a query, the result should be treated as a row
with separate columns instead of a tuple, which is how UDF output is
normally treated. This PR closes this gap between CRDB and Postgres.

For example:

```
CREATE FUNCTION f() RETURNS RECORD AS
$$
  SELECT 1, 2, 3;
$$ LANGUAGE SQL

SELECT f()
    f
--------
 (1,2,3)

SELECT * FROM f() as foo(a int, b int, c int);
 a | b | c
---+---+---
 1 | 2 | 3
```

The behavior is the same for implicit record return types.

Epic: CRDB-19496
Fixes: cockroachdb#97059

Release note (sql change): UDFs with record-returning types now return a
row instead of a tuple.
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Mar 16, 2023
@rharding6373
sql: fix record-returning udfs when used as data source
be790d0 
When record-returning UDFs (both implicit and `RECORD` return types) are
used as a data source in a query, the result should be treated as a row
with separate columns instead of a tuple, which is how UDF output is
normally treated. This PR closes this gap between CRDB and Postgres.

For example:

```
CREATE FUNCTION f() RETURNS RECORD AS
$$
  SELECT 1, 2, 3;
$$ LANGUAGE SQL

SELECT f()
    f
--------
 (1,2,3)

SELECT * FROM f() as foo(a int, b int, c int);
 a | b | c
---+---+---
 1 | 2 | 3
```

The behavior is the same for implicit record return types.

Epic: CRDB-19496
Fixes: cockroachdb#97059

Release note: None
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Apr 7, 2023
@rharding6373
sql: fix record-returning udfs when used as data source
5656f70 
When record-returning UDFs (both implicit and `RECORD` return types) are
used as a data source in a query, the result should be treated as a row
with separate columns instead of a tuple, which is how UDF output is
normally treated. This PR closes this gap between CRDB and Postgres.

For example:

```
CREATE FUNCTION f() RETURNS RECORD AS
$$
  SELECT 1, 2, 3;
$$ LANGUAGE SQL

SELECT f()
    f
--------
 (1,2,3)

SELECT * FROM f() as foo(a int, b int, c int);
 a | b | c
---+---+---
 1 | 2 | 3
```

The behavior is the same for implicit record return types.

Epic: CRDB-19496
Fixes: cockroachdb#97059

Release note: None
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Apr 7, 2023
@rharding6373
sql: fix record-returning udfs when used as data source
1c35278 
When record-returning UDFs (both implicit and `RECORD` return types) are
used as a data source in a query, the result should be treated as a row
with separate columns instead of a tuple, which is how UDF output is
normally treated. This PR closes this gap between CRDB and Postgres.

For example:

```
CREATE FUNCTION f() RETURNS RECORD AS
$$
  SELECT 1, 2, 3;
$$ LANGUAGE SQL

SELECT f()
    f
--------
 (1,2,3)

SELECT * FROM f() as foo(a int, b int, c int);
 a | b | c
---+---+---
 1 | 2 | 3
```

The behavior is the same for implicit record return types.

Epic: CRDB-19496
Fixes: cockroachdb#97059

Release note: None
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Apr 7, 2023
@rharding6373
sql: fix record-returning udfs when used as data source
cb0fcd7 
When record-returning UDFs (both implicit and `RECORD` return types) are
used as a data source in a query, the result should be treated as a row
with separate columns instead of a tuple, which is how UDF output is
normally treated. This PR closes this gap between CRDB and Postgres.

For example:

```
CREATE FUNCTION f() RETURNS RECORD AS
$$
  SELECT 1, 2, 3;
$$ LANGUAGE SQL

SELECT f()
    f
--------
 (1,2,3)

SELECT * FROM f() as foo(a int, b int, c int);
 a | b | c
---+---+---
 1 | 2 | 3
```

The behavior is the same for implicit record return types.

Epic: CRDB-19496
Fixes: cockroachdb#97059

Release note: None
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Apr 12, 2023
@rharding6373
sql: fix record-returning udfs when used as data source
90ff224 
When record-returning UDFs (both implicit and `RECORD` return types) are
used as a data source in a query, the result should be treated as a row
with separate columns instead of a tuple, which is how UDF output is
normally treated. This PR closes this gap between CRDB and Postgres.

For example:

```
CREATE FUNCTION f() RETURNS RECORD AS
$$
  SELECT 1, 2, 3;
$$ LANGUAGE SQL

SELECT f()
    f
--------
 (1,2,3)

SELECT * FROM f() as foo(a int, b int, c int);
 a | b | c
---+---+---
 1 | 2 | 3
```

The behavior is the same for implicit record return types.

Epic: CRDB-19496
Fixes: cockroachdb#97059

Release note: None
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Apr 13, 2023
@rharding6373
sql: fix record-returning udfs when used as data source
781366a 
When record-returning UDFs (both implicit and `RECORD` return types) are
used as a data source in a query, the result should be treated as a row
with separate columns instead of a tuple, which is how UDF output is
normally treated. This PR closes this gap between CRDB and Postgres.

For example:

```
CREATE FUNCTION f() RETURNS RECORD AS
$$
  SELECT 1, 2, 3;
$$ LANGUAGE SQL

SELECT f()
    f
--------
 (1,2,3)

SELECT * FROM f() as foo(a int, b int, c int);
 a | b | c
---+---+---
 1 | 2 | 3
```

The behavior is the same for implicit record return types.

Epic: CRDB-19496
Fixes: cockroachdb#97059

Release note (bug fix): Fixes the behavior of UDFs to return its results
as a row instead of a tuple when UDFs are called in a query as a data
source. This is now compatible with postgres behavior.
@rharding6373 rharding6373 mentioned this issue Apr 17, 2023
Closed
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Apr 17, 2023
@rharding6373
sql: fix record-returning udfs when used as data source
4391b91 
When record-returning UDFs (both implicit and `RECORD` return types) are
used as a data source in a query, the result should be treated as a row
with separate columns instead of a tuple, which is how UDF output is
normally treated. This PR closes this gap between CRDB and Postgres.

For example:

```
CREATE FUNCTION f() RETURNS RECORD AS
$$
  SELECT 1, 2, 3;
$$ LANGUAGE SQL

SELECT f()
    f
--------
 (1,2,3)

SELECT * FROM f() as foo(a int, b int, c int);
 a | b | c
---+---+---
 1 | 2 | 3
```

The behavior is the same for implicit record return types.

Epic: CRDB-19496
Fixes: cockroachdb#97059

Release note (bug fix): Fixes the behavior of UDFs to return its results
as a row instead of a tuple when UDFs are called in a query as a data
source. This is now compatible with postgres behavior.
craig bot pushed a commit that referenced this issue Apr 24, 2023
@rharding6373
Merge #98162
a2f31ca 
98162: sql: fix record-returning udfs when used as data source r=rharding6373 a=rharding6373

When record-returning UDFs (both implicit and `RECORD` return types) are used as a data source in a query, the result should be treated as a row with separate columns instead of a tuple, which is how UDF output is normally treated. This PR closes this gap between CRDB and Postgres.

For example:

```
CREATE FUNCTION f() RETURNS RECORD AS
$$
  SELECT 1, 2, 3;
$$ LANGUAGE SQL

SELECT f()
    f
--------
 (1,2,3)

SELECT * FROM f() as foo(a int, b int, c int);
 a | b | c
---+---+---
 1 | 2 | 3
```

The behavior is the same for implicit record return types.

Epic: CRDB-19496
Fixes: #97059

Release note: None

Co-authored-by: rharding6373 <[email protected]>
@craig craig bot closed this as completed in 4539072 Apr 24, 2023
@rharding6373 rharding6373 mentioned this issue Apr 24, 2023
Merged
rharding6373 added a commit to rharding6373/cockroach that referenced this issue Apr 24, 2023
@rharding6373
sql: fix record-returning udfs when used as data source
a08f9a5 
When record-returning UDFs (both implicit and `RECORD` return types) are
used as a data source in a query, the result should be treated as a row
with separate columns instead of a tuple, which is how UDF output is
normally treated. This PR closes this gap between CRDB and Postgres.

For example:

```
CREATE FUNCTION f() RETURNS RECORD AS
$$
  SELECT 1, 2, 3;
$$ LANGUAGE SQL

SELECT f()
    f
--------
 (1,2,3)

SELECT * FROM f() as foo(a int, b int, c int);
 a | b | c
---+---+---
 1 | 2 | 3
```

The behavior is the same for implicit record return types.

Epic: CRDB-19496
Fixes: cockroachdb#97059

Release note (bug fix): Fixes the behavior of UDFs to return its results
as a row instead of a tuple when UDFs are called in a query as a data
source. This is now compatible with postgres behavior.
@mgartner mgartner moved this to Done in SQL Queries Jul 24, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@rharding6373 rharding6373

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
SQL Queries
Archived in project
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: fix record-returning udfs when used as data source rharding6373/cockroach
2 participants
@mgartner @rharding6373