pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed

replace() using NULL
3+ messages / 3 participants
[nested] [flat]

* replace() using NULL
@ 2022-04-13 08:38  Susanne Holzgraefe <miracee@web.de>
  0 siblings, 1 reply; 3+ messages in thread

From: Susanne Holzgraefe @ 2022-04-13 08:38 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org

Hello,

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.

Regards,

miracee (Susanne)

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: replace() using NULL
@ 2022-04-13 11:34  Julien Rouhaud <rjuju123@gmail.com>
  parent: Susanne Holzgraefe <miracee@web.de>
  0 siblings, 1 reply; 3+ messages in thread

From: Julien Rouhaud @ 2022-04-13 11:34 UTC (permalink / raw)
  To: Susanne Holzgraefe <miracee@web.de>; +Cc: pgsql-bugs@lists.postgresql.org

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.





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: replace() using NULL
@ 2022-04-13 13:43  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Julien Rouhaud <rjuju123@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2022-04-13 13:43 UTC (permalink / raw)
  To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: Susanne Holzgraefe <miracee@web.de>; pgsql-bugs@lists.postgresql.org

Julien Rouhaud <rjuju123@gmail.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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2022-04-13 13:43 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-13 08:38 replace() using NULL Susanne Holzgraefe <miracee@web.de>
2022-04-13 11:34 ` Julien Rouhaud <rjuju123@gmail.com>
2022-04-13 13:43   ` Tom Lane <tgl@sss.pgh.pa.us>

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