Re: replace() using NULL

Julien Rouhaud <rjuju123(at)gmail(dot)com> writes:
> On Wed, Apr 13, 2022 at 10:38:28AM +0200, Susanne Holzgraefe wrote:
>> SELECT REPLACE('xyz‘, 'a‘, NULL); => NULL

> The function is declared as STRICT, which means that it won't be called at all
> and just returns NULL if any of its parameters is NULL.

Right. The SQL standard doesn't seem to have a REPLACE function, so it's
hard to settle this by appealing to the standard; but it's reasonable to
suppose that if they did define it then it would be specified to work like
the existing substring manipulation functions --- and those are all strict
per spec. For example, the spec for SUBSTRING says

d) If at least one of C, S, and L is the null value, then the result of
the <character substring function> is the null value.

and there is equivalent verbiage in every other subparagraph of
SQL:2021 6.32 <string value function>.

>> Tested with Oracle, result is ‚xyz‘.

> Maybe oracle supports such calls as it supports NULL bytes in strings (I
> think), but postgres doesn't.

Oracle is a very suspect reference when it comes to behaviors involving
NULL, because they are not even a little bit spec-compliant there.
My understanding is that they can't really represent a NULL string at
all, and fake it as being an empty string ('') ... which of course
yields that result in this case.

regards, tom lane