Re: BUG #17860: Possible Bugs in PL/pgSQL Functions' Return Values - Mailing list pgsql-bugs
| From | David G. Johnston |
|---|---|
| Subject | Re: BUG #17860: Possible Bugs in PL/pgSQL Functions' Return Values |
| Date | |
| Msg-id | CAKFQuwbQuy5j_n1kk36KbrsFK2z6q=8zBwdycAFht6kbGMC1rw@mail.gmail.com Whole thread Raw |
| In response to | BUG #17860: Possible Bugs in PL/pgSQL Functions' Return Values (PG Bug reporting form <noreply@postgresql.org>) |
| Responses |
Re: BUG #17860: Possible Bugs in PL/pgSQL Functions' Return Values
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-bugs |
On Wed, Mar 22, 2023 at 6:15 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 17860
Logged by: Jiangshan Liu
Email address: jiangshan.liu@tju.edu.cn
PostgreSQL version: 15.2
Operating system: Ubuntu 18.04.6 LTS
Description:
I am writing to report a possible bug that I have encountered with the
execution of two PL/pgSQL functions. The results that they produce have left
me confused and I would appreciate your insights and assistance with this
matter.
In short - a set producing function creates an explicit table that may contain zero, one, or many rows. A non-set producing function creates a single value, always, which if you happen to place it in a place that wants a table reference will result in there always being a single row table. But typically one does not place non-set producing functions in a FROM clause.
The first function is:
CREATE OR REPLACE FUNCTION test1() RETURNS SETOF BOOLEAN AS $$
BEGIN
RETURN;
END;
$$ LANGUAGE plpgsql;
select count(*) from test1();
The result of executing this function is:
count
-------
0
(1 row)
The second function is:
CREATE OR REPLACE FUNCTION test2() RETURNS VOID AS $$
BEGIN
RETURN;
END;
$$ LANGUAGE plpgsql;
select count(*) from test2();
The result of executing this function is:
count
-------
1
(1 row)
At the same time, I discovered that the returned 1 row data result is of
type "void":
select pg_typeof(test2());
pg_typeof
-----------
void
(1 row)
I believe that there is a confusing bug present here. For the function
test1(), according to the documentation,
"The individual items to return are specified by a sequence of RETURN NEXT
or RETURN QUERY commands, and then a final RETURN command with no argument
is used to indicate that the function has finished executing[1]."
Since there are no actual RETURN NEXT or RETURN QUERY executed, the behavior
should be equivalent to a RETURN command with no argument, similar to the
function test2().
This is an incorrect conclusion.
However, the results of test1() and test2() are different. I am unsure if
the empty table returned by test1() is a deliberate design or not, but it
does not seem to align with the intention of SETOF return values.
A table, empty or not, is exactly what "SET" means in SQL.
The documentation also mentions that
"The SETOF modifier indicates that the function will return a set of items,
rather than a single item[2]."
Sets can be empty, we need not make that point explicitly whenever we use the word.
Regardless of whether the length of the returned set is zero or non-zero, we
expect to receive a single entity as a return value, rather than an empty
table.
Your "single entity" is a table-like structure of which there is indeed exactly one. And when you count the number of rows in the table-like (set) structure you correctly count zero rows.
Moreover, regarding function test2(), the result is also perplexing.
According to the documentation,
"Data type void Indicates that a function returns no value."
Therefore, it is reasonable to expect that the returned value should be an
empty table, as it should not be returning any value. However, in reality,
it returns a table with one row.
A non-SET returning function doesn't return a table-like structure, it returns a value (one row, one column - possibly composite). If you place that value into a FROM clause it looks like a single row table but that is just a detail of how SQL generally works when it tries to accommodate your oddly written query where a non-table producing function is used in a FROM clause.
The system retains the data type "void" for that singular value as the representation of the fact that the function that was called did not itself return any particular value.
David J.
pgsql-bugs by date: