Thread: [BUGS] plpgsql bug: `EXECUTE(sql_command) INTO rec` returns sometimes aNULL-able, sometimes not
[BUGS] plpgsql bug: `EXECUTE(sql_command) INTO rec` returns sometimes aNULL-able, sometimes not
From
Manuel Pradal
Date:
Hi,
Using PL/SQL language, I saw a strange behavior using "EXECUTE(sql_command) INTO", then "IF rec IS NOT NULL THEN" statement.
It seems that record content infers with existence test of whole record.
You can see in attached file the possible bug in action.
Should I use "IF NOT FOUND" syntax? Is it more reliable?
Thank you in advance!
Manuel PRADAL
Attachment
Re: [BUGS] plpgsql bug: `EXECUTE(sql_command) INTO rec` returnssometimes a NULL-able, sometimes not
From
Pavel Stehule
Date:
2017-04-24 17:48 GMT+02:00 Manuel Pradal <manuel.pradal@gmail.com>:
Hi,Using PL/SQL language, I saw a strange behavior using "EXECUTE(sql_command) INTO", then "IF rec IS NOT NULL THEN" statement.It seems that record content infers with existence test of whole record.You can see in attached file the possible bug in action.Should I use "IF NOT FOUND" syntax? Is it more reliable?
The result should be NULL - but test on NULL is true, only when all fields are NULL. Can you send some examples?
you should to use GET DIAGNOSTICS statement. Variable FOUND is not related to dynamic SQL
Regards
Pavel
Thank you in advance!Manuel PRADAL
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] plpgsql bug: `EXECUTE(sql_command) INTO rec` returnssometimes a NULL-able, sometimes not
From
Manuel Pradal
Date:
Hi Pavel,
Thanks for your quick answer.
I sent you in the attached file of previous email an example (note that I'm running version 9.3).
When I use "*" in the SELECT statement ("test_record1" function), "rec IS NOT NULL" returns TRUE ("test_record2" function), whereas when I explicit "id", "rec IS NOT NULL" returns FALSE.
In the both cases there is a row in the table.
I just want to know if this behavious is normal or not!
Thanks!
Manuel
2017-04-24 18:53 GMT+02:00 Pavel Stehule <pavel.stehule@gmail.com>:
2017-04-24 17:48 GMT+02:00 Manuel Pradal <manuel.pradal@gmail.com>:Hi,Using PL/SQL language, I saw a strange behavior using "EXECUTE(sql_command) INTO", then "IF rec IS NOT NULL THEN" statement.It seems that record content infers with existence test of whole record.You can see in attached file the possible bug in action.Should I use "IF NOT FOUND" syntax? Is it more reliable?The result should be NULL - but test on NULL is true, only when all fields are NULL. Can you send some examples?you should to use GET DIAGNOSTICS statement. Variable FOUND is not related to dynamic SQLRegardsPavelThank you in advance!Manuel PRADAL
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] plpgsql bug: `EXECUTE(sql_command) INTO rec` returnssometimes a NULL-able, sometimes not
From
"David G. Johnston"
Date:
Hi,Using PL/SQL language, I saw a strange behavior using "EXECUTE(sql_command) INTO", then "IF rec IS NOT NULL THEN" statement.It seems that record content infers with existence test of whole record.You can see in attached file the possible bug in action.Should I use "IF NOT FOUND" syntax? Is it more reliable?
tl/dr; rec IS DISTINCT FROM NULL
This does not seem like a bug.
You're reported what does happen but not what you expect to happen and why.
As Pavel points out the docs for "Obtaining the Result Status" (pl/pgsql) make an effort to point out:
"Other PL/pgSQL statements do not change the state of FOUND. Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND."
You could also try:
NOT (rec IS NULL)
which is the a better way to determine whether a composite record is absent/present.
Even then that only works if at least one column of the record is guaranteed to be not null. See the docs for more details:
Reading those the best solution is to simply compare for distinctness to null.
rec IS DISTINCT FROM NULL
David J.
Re: [BUGS] plpgsql bug: `EXECUTE(sql_command) INTO rec` returnssometimes a NULL-able, sometimes not
From
Manuel Pradal
Date:
Hi,
Thank you very much for your answer and your time. I found on https://www.postgresql.org/docs/9.1/static/functions-comparison.html that
> Because of this behavior, IS NULL and IS NOT NULL do not always return inverse results for row-valued expressions; in particular, a row-valued expression that contains both null and non-null fields will return false for both tests.
It explains my "strange" results.
Thanks again!
Manuel
Le 24 avr. 2017 7:19 PM, "David G. Johnston" <david.g.johnston@gmail.com> a écrit :
Hi,Using PL/SQL language, I saw a strange behavior using "EXECUTE(sql_command) INTO", then "IF rec IS NOT NULL THEN" statement.It seems that record content infers with existence test of whole record.You can see in attached file the possible bug in action.Should I use "IF NOT FOUND" syntax? Is it more reliable?tl/dr; rec IS DISTINCT FROM NULLThis does not seem like a bug.You're reported what does happen but not what you expect to happen and why.
As Pavel points out the docs for "Obtaining the Result Status" (pl/pgsql) make an effort to point out:"Other PL/pgSQL statements do not change the state of FOUND. Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND."You could also try:NOT (rec IS NULL)which is the a better way to determine whether a composite record is absent/present.Even then that only works if at least one column of the record is guaranteed to be not null. See the docs for more details:Reading those the best solution is to simply compare for distinctness to null.rec IS DISTINCT FROM NULLDavid J.