Thread: BUG #17860: Possible Bugs in PL/pgSQL Functions' Return Values
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.
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().
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.
The documentation also mentions that
"The SETOF modifier indicates that the function will return a set of items,
rather than a single item[2]."
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. Just like in other programming languages, such as Java, when we
expect an array of length zero, we still need to create that array to make
it an entity, otherwise it will be a null value.
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.
I would greatly appreciate your thoughts and feedback on this issue. Thank
you for your attention and assistance.
Sincerely, Jiangshan Liu
[1]
https://www.postgresql.org/docs/15/plpgsql-control-structures.html#:~:text=and%20then%20a%20final%20RETURN%20command%20with%20no%20argument%20is%20used%20to%20indicate%20that%20the%20function%20has%20finished%20executing
[2]
https://www.postgresql.org/docs/15/sql-createfunction.html#:~:text=The%20SETOF%20modifier%20indicates%20that%20the%20function%20will%20return%20a%20set%20of%20items%2C%20rather%20than%20a%20single%20item.
[3]
https://www.postgresql.org/docs/15/datatype-pseudo.html#:~:text=Indicates%20that%20a%20function%20returns%20no%20value.
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.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Mar 22, 2023 at 6:15 AM PG Bug reporting form <
> noreply@postgresql.org> wrote:
>> 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.
> 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.
"void" is a bit of a hack. As far as the system is concerned, it's
an actual scalar type, having no operations and exactly one value,
which prints as an empty string. The point of doing that rather than
just returning (say) an empty string is the lack of operations: if you
accidentally try to do something with the result of a void-returning
function, it'll fail because void can't be coerced to anything else.
As David says, this is not the same as a set result containing
zero rows.
regards, tom lane