Unexpected/undocumented REPLACE() behavior

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Unexpected/undocumented REPLACE() behavior

Tony Papadimitriou
Example:

--- CUT ---
create table t(s text);
insert into t values ('1'),('null'),('3');

.print 'BEFORE'
select rowid,* from t;
update t set s = replace(s,'null',null)
--where s = 'null'  --adding this works of course but that’s not my point
;

.print 'AFTER'
select rowid,* from t;
--- CUT ---

The documentation says: “The replace(X,Y,Z) function returns a string formed by substituting string Z for every occurrence of string Y in string X. The BINARY collating sequence is used for comparisons. If Y is an empty string then return X unchanged. If Z is not initially a string, it is cast to a UTF-8 string prior to processing.”

“substituting string Z for every occurrence of string Y” implies that if there is no occurrence of string Y nothing should happen to the original string, right?

Accordingly, my expectation is that either:
1. null will remain null as there is not really a string (even empty) that can truly represent it, or
2. we allow null to be converted to empty string so that the “returns a string” requirement can be satisfied.

or, maybe
3. using null for the Z part gives an error.

In either [1] or [2] above, however, if the target string (Y part) is not found, the result should be unaltered.
The replacement seems to occur regardless of the target being found or not.

... and everything becomes null.

Note: I know SQLite3 tries to mimic in some ways Postgres, so I tried with it also, and got the same unexpected behavior.
From a quick look, I haven’t seen anything in their documentation on REPLACE to justify it either.

I consider this behavior wrong, or (easier way out) the documentation should make a special note about null behaving the way it does.

Thank you.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Unexpected/undocumented REPLACE() behavior

Shawn Wagner
With most functions, including replace(), if any of the arguments are null,
it returns null.

On Mon, Jun 10, 2019 at 4:29 PM Tony Papadimitriou <[hidden email]> wrote:

> Example:
>
> --- CUT ---
> create table t(s text);
> insert into t values ('1'),('null'),('3');
>
> .print 'BEFORE'
> select rowid,* from t;
> update t set s = replace(s,'null',null)
> --where s = 'null'  --adding this works of course but that’s not my point
> ;
>
> .print 'AFTER'
> select rowid,* from t;
> --- CUT ---
>
> The documentation says: “The replace(X,Y,Z) function returns a string
> formed by substituting string Z for every occurrence of string Y in string
> X. The BINARY collating sequence is used for comparisons. If Y is an empty
> string then return X unchanged. If Z is not initially a string, it is cast
> to a UTF-8 string prior to processing.”
>
> “substituting string Z for every occurrence of string Y” implies that if
> there is no occurrence of string Y nothing should happen to the original
> string, right?
>
> Accordingly, my expectation is that either:
> 1. null will remain null as there is not really a string (even empty) that
> can truly represent it, or
> 2. we allow null to be converted to empty string so that the “returns a
> string” requirement can be satisfied.
>
> or, maybe
> 3. using null for the Z part gives an error.
>
> In either [1] or [2] above, however, if the target string (Y part) is not
> found, the result should be unaltered.
> The replacement seems to occur regardless of the target being found or not.
>
> ... and everything becomes null.
>
> Note: I know SQLite3 tries to mimic in some ways Postgres, so I tried with
> it also, and got the same unexpected behavior.
> From a quick look, I haven’t seen anything in their documentation on
> REPLACE to justify it either.
>
> I consider this behavior wrong, or (easier way out) the documentation
> should make a special note about null behaving the way it does.
>
> Thank you.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users