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.
