Re: [sqlite] 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  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users