Thread: BUG #18715: replace() function silently fails if 3rd argument is null

The following bug has been logged on the website:

Bug reference:      18715
Logged by:          Chris
Email address:      xpusostomos@gmail.com
PostgreSQL version: 16.5
Operating system:   Linux
Description:

Imagine I'm using the replace() to do variable substitution on database
fields..
replace(mytable.myfield1, '${m}', mytable2.myfield2)

At runtime, this might look like:
replace('abc${m}def', '${m}', 'hello!') => 'abchello!def';

OK, but now imagine that at runtime the 3rd argument is null... and we
expect it to be null because that variable is not used in that tuple:
replace('abcdef', '${m}', null) => null

so we'd expect the result 'abcdef', because '${m}' does not occur in the
string, thus we'd expect the 3rd argument to be ignored... however that's
not what happens, the result is null. No error is raised, it just silently
does something very odd.

we can get around it of course....
replace(mytable.myfield1, '${m}', coalesce(mytable2.myfield2, ''))

But surely that's needlessly and absurdly complicated for no reason. If the
2nd argument does not occur in the string, what business does replace() have
in molesting the string?

So the bug is that...
replace('ghi', 'jkl', null) ought to return 'ghi', because since 'jkl' does
not occur in 'ghi' the string should be unmolested, and the fact that the
3rd argument is null ought not to affect the outcome.

One might ask what ought to happen if the string does occur there and the
3rd argument is null. That does not directly concern me there, although in
my opinion, it makes more sense to consider null as empty string because
that's a rational expectation for a programmer, whereas returning null is an
"I give up" response. When people use replace() they are very much hoping
and expecting, if at all humanly possible, to get a string in return, and
not to give up to null when there is a rational useful interpretation of the
inputs.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, Nov 19, 2024 at 8:08 AM Chris BSomething <xpusostomos@gmail.com>
> wrote:
>> Nowhere (that I can see) does any documentation "define" that replace
>> returns null on null input to arg 3. Nor is it obvious that any "strict"
>> application of any principle should have it return null.

> Fair, I keep forgetting that we don't document the "strict" property of a
> function definition.  Absent that, I agree it's a documentation bug that we
> don't adequately explain the strictness behavior of this function.

I thought we documented somewhere that built-in functions are strict
unless explicitly stated otherwise ... but I sure can't find that
statement right now.

> "Replacing text with null is problematic, but no problem arises if there is
> nothing to be done." - while true I see little desire to make that
> conditionalized behavior reality.  IOW, you are correct, but it also isn't
> likely to change.  The current behavior benefits developers over users, but
> we make up for it by spending time elsewhere.

It's generally true in the SQL standard that functions yield null if
any of their inputs are null.  In some cases you could argue that a
particular input might not be consulted given the values of the other
inputs, but they've steered away from that sort of definitional and
implementation complexity.  And so have we.

            regards, tom lane

On 2024-11-19 17:47 +0100, Tom Lane wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Tue, Nov 19, 2024 at 8:08 AM Chris BSomething <xpusostomos@gmail.com>
> > wrote:
> >> Nowhere (that I can see) does any documentation "define" that replace
> >> returns null on null input to arg 3. Nor is it obvious that any "strict"
> >> application of any principle should have it return null.
> 
> > Fair, I keep forgetting that we don't document the "strict" property of a
> > function definition.  Absent that, I agree it's a documentation bug that we
> > don't adequately explain the strictness behavior of this function.
> 
> I thought we documented somewhere that built-in functions are strict
> unless explicitly stated otherwise ... but I sure can't find that
> statement right now.

Perhaps this one?:
> (Most internal functions expect to be declared “strict”.)
https://www.postgresql.org/docs/current/xfunc-internal.html

-- 
Erik

Erik Wienhold <ewie@ewie.name> writes:
> On 2024-11-19 17:47 +0100, Tom Lane wrote:
>> I thought we documented somewhere that built-in functions are strict
>> unless explicitly stated otherwise ... but I sure can't find that
>> statement right now.

> Perhaps this one?:
>> (Most internal functions expect to be declared “strict”.)
>> https://www.postgresql.org/docs/current/xfunc-internal.html

I did see that one, but it's not exactly in an obvious-to-novices
place.

David was working on a new docs section that would talk more about
SQL NULLs in general.  Maybe that would be a reasonable place?
An alternative could be somewhere early in func.sgml, but that chapter
doesn't really have anything I would call an overview section.

            regards, tom lane

On 2024-11-19 23:40 +0100, Tom Lane wrote:
> Erik Wienhold <ewie@ewie.name> writes:
> > On 2024-11-19 17:47 +0100, Tom Lane wrote:
> >> I thought we documented somewhere that built-in functions are strict
> >> unless explicitly stated otherwise ... but I sure can't find that
> >> statement right now.
> 
> > Perhaps this one?:
> >> (Most internal functions expect to be declared “strict”.)
> >> https://www.postgresql.org/docs/current/xfunc-internal.html
> 
> I did see that one, but it's not exactly in an obvious-to-novices
> place.
> 
> David was working on a new docs section that would talk more about
> SQL NULLs in general.  Maybe that would be a reasonable place?

Definitely.  Looking at David's v4 patch [1] right now, I see that it
already covers strict functions (new section "Null-Valued Arguments in
Normal Function Calls").

> An alternative could be somewhere early in func.sgml, but that chapter
> doesn't really have anything I would call an overview section.

We have this intro page [2] which explains the notation used throughout
the chapter.  How about mentioning there that all functions in this
chapter, unless stated otherwise, return NULL when called with any NULL
arguments?

[1] https://www.postgresql.org/message-id/CAKFQuwbHuT82b3jYwH%2ByhuKrpLoa0Ebr%2BsT6mTFS%2BnfjAAGUFQ%40mail.gmail.com
[2] https://www.postgresql.org/docs/current/functions.html

-- 
Erik