Date: Wed, 13 Apr 2022 19:34:29 +0800
From: Julien Rouhaud <rjuju123@gmail.com>
To: Susanne Holzgraefe <miracee@web.de>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: replace() using NULL
Message-ID: <Yla1RQYrF9Qn3GR3@jrouhaud>
References: <29A17362-1756-422E-9BD3-2CBE9F8C990F@web.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <29A17362-1756-422E-9BD3-2CBE9F8C990F@web.de>
Archived-At: 
 <https://www.postgresql.org/message-id/Yla1RQYrF9Qn3GR3%40jrouhaud>
Precedence: bulk

Hi,

On Wed, Apr 13, 2022 at 10:38:28AM +0200, Susanne Holzgraefe wrote:
>
> tested by using postgresql 13.5:
>
> SELECT REPLACE('xyz‘, ’y’, ’a’); => xaz => expected
> SELECT REPLACE('xyz', 'a‘, 'b‘); => xyz => expected
>
> SELECT REPLACE('xyz‘, 'a‘, NULL); => NULL
>
> Also tested by using MariaDB. Same behavior like in PG.
> Tested with Oracle, result is ‚xyz‘.
>
> That is what I would expect because xyz includes no a.
>
> I neither can find any documentation about this nor a bug report.

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.

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