pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18715: replace() function silently fails if 3rd argument is null
8+ messages / 5 participants
[nested] [flat]
* BUG #18715: replace() function silently fails if 3rd argument is null
@ 2024-11-19 00:28 PG Bug reporting form <noreply@postgresql.org>
2024-11-19 13:07 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: PG Bug reporting form @ 2024-11-19 00:28 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: xpusostomos@gmail.com
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18715: replace() function silently fails if 3rd argument is null
2024-11-19 00:28 BUG #18715: replace() function silently fails if 3rd argument is null PG Bug reporting form <noreply@postgresql.org>
@ 2024-11-19 13:07 ` David G. Johnston <david.g.johnston@gmail.com>
2024-11-19 15:07 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Chris BSomething <xpusostomos@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2024-11-19 13:07 UTC (permalink / raw)
To: xpusostomos@gmail.com; pgsql-bugs@lists.postgresql.org
On Tue, Nov 19, 2024 at 5:52 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
> 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:
>
> 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
>
This is not a bug. Replace is defined to return null on null input (i.e.,
strict) and that is the behavior you are seeing.
Use Coalesce to convert your null into an empty string.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18715: replace() function silently fails if 3rd argument is null
2024-11-19 00:28 BUG #18715: replace() function silently fails if 3rd argument is null PG Bug reporting form <noreply@postgresql.org>
2024-11-19 13:07 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null David G. Johnston <david.g.johnston@gmail.com>
@ 2024-11-19 15:07 ` Chris BSomething <xpusostomos@gmail.com>
2024-11-19 15:30 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Chris BSomething @ 2024-11-19 15:07 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
The documentation says that if arg 2 occurs in arg 1, it is replaced with
arg 3. Replacing text with null is problematic, but no problem arises if
there is nothing to be done.
Who exactly benefits from a function that fails to return the sensible
result that most clearly is available?
On Tue, 19 Nov 2024 at 21:08, David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Tue, Nov 19, 2024 at 5:52 AM PG Bug reporting form <
> noreply@postgresql.org> wrote:
>
>> 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:
>>
>> 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
>>
>
> This is not a bug. Replace is defined to return null on null input (i.e.,
> strict) and that is the behavior you are seeing.
>
> Use Coalesce to convert your null into an empty string.
>
> David J.
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18715: replace() function silently fails if 3rd argument is null
2024-11-19 00:28 BUG #18715: replace() function silently fails if 3rd argument is null PG Bug reporting form <noreply@postgresql.org>
2024-11-19 13:07 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null David G. Johnston <david.g.johnston@gmail.com>
2024-11-19 15:07 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Chris BSomething <xpusostomos@gmail.com>
@ 2024-11-19 15:30 ` David G. Johnston <david.g.johnston@gmail.com>
2024-11-19 16:47 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2024-11-19 15:30 UTC (permalink / raw)
To: Chris BSomething <xpusostomos@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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.
"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.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18715: replace() function silently fails if 3rd argument is null
2024-11-19 00:28 BUG #18715: replace() function silently fails if 3rd argument is null PG Bug reporting form <noreply@postgresql.org>
2024-11-19 13:07 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null David G. Johnston <david.g.johnston@gmail.com>
2024-11-19 15:07 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Chris BSomething <xpusostomos@gmail.com>
2024-11-19 15:30 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null David G. Johnston <david.g.johnston@gmail.com>
@ 2024-11-19 16:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-11-19 22:34 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2024-11-19 16:47 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Chris BSomething <xpusostomos@gmail.com>; pgsql-bugs@lists.postgresql.org
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18715: replace() function silently fails if 3rd argument is null
2024-11-19 00:28 BUG #18715: replace() function silently fails if 3rd argument is null PG Bug reporting form <noreply@postgresql.org>
2024-11-19 13:07 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null David G. Johnston <david.g.johnston@gmail.com>
2024-11-19 15:07 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Chris BSomething <xpusostomos@gmail.com>
2024-11-19 15:30 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null David G. Johnston <david.g.johnston@gmail.com>
2024-11-19 16:47 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-11-19 22:34 ` Erik Wienhold <ewie@ewie.name>
2024-11-19 22:40 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Erik Wienhold @ 2024-11-19 22:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Chris BSomething <xpusostomos@gmail.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18715: replace() function silently fails if 3rd argument is null
2024-11-19 00:28 BUG #18715: replace() function silently fails if 3rd argument is null PG Bug reporting form <noreply@postgresql.org>
2024-11-19 13:07 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null David G. Johnston <david.g.johnston@gmail.com>
2024-11-19 15:07 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Chris BSomething <xpusostomos@gmail.com>
2024-11-19 15:30 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null David G. Johnston <david.g.johnston@gmail.com>
2024-11-19 16:47 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Tom Lane <tgl@sss.pgh.pa.us>
2024-11-19 22:34 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Erik Wienhold <ewie@ewie.name>
@ 2024-11-19 22:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-11-19 23:31 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2024-11-19 22:40 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Chris BSomething <xpusostomos@gmail.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18715: replace() function silently fails if 3rd argument is null
2024-11-19 00:28 BUG #18715: replace() function silently fails if 3rd argument is null PG Bug reporting form <noreply@postgresql.org>
2024-11-19 13:07 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null David G. Johnston <david.g.johnston@gmail.com>
2024-11-19 15:07 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Chris BSomething <xpusostomos@gmail.com>
2024-11-19 15:30 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null David G. Johnston <david.g.johnston@gmail.com>
2024-11-19 16:47 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Tom Lane <tgl@sss.pgh.pa.us>
2024-11-19 22:34 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Erik Wienhold <ewie@ewie.name>
2024-11-19 22:40 ` Re: BUG #18715: replace() function silently fails if 3rd argument is null Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-11-19 23:31 ` Erik Wienhold <ewie@ewie.name>
0 siblings, 0 replies; 8+ messages in thread
From: Erik Wienhold @ 2024-11-19 23:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Chris BSomething <xpusostomos@gmail.com>; pgsql-bugs@lists.postgresql.org
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%40ma...
[2] https://www.postgresql.org/docs/current/functions.html
--
Erik
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-11-19 23:31 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-19 00:28 BUG #18715: replace() function silently fails if 3rd argument is null PG Bug reporting form <noreply@postgresql.org>
2024-11-19 13:07 ` David G. Johnston <david.g.johnston@gmail.com>
2024-11-19 15:07 ` Chris BSomething <xpusostomos@gmail.com>
2024-11-19 15:30 ` David G. Johnston <david.g.johnston@gmail.com>
2024-11-19 16:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-11-19 22:34 ` Erik Wienhold <ewie@ewie.name>
2024-11-19 22:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-11-19 23:31 ` Erik Wienhold <ewie@ewie.name>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox