Test SQL:

select
hex(replace(x,'0'||x'00',z))=hex('_1'),
hex(replace(x,x'00',z))=hex('0_1'),
hex(replace(x,x'00'||'1',z))=hex('0_')
from
(select '0'||x'00'||'1' as x,'_' as z) t;

-- Or cast all arguments to BLOB.
select
hex(replace(x,cast('0'||x'00' as BLOB),z))=hex('_1'),
hex(replace(x,cast(x'00' as BLOB),z))=hex('0_1'),
hex(replace(x,cast(x'00'||'1' as BLOB),z))=hex('0_')
from
(select cast('0'||x'00'||'1' as BLOB) as x, cast('_' as BLOB) as z) t;

Expected result:

1|1|1

Actual result:

1|0|0

SQLite version:

3.39.4

Additional information:

The problem may be caused by this line func.c#L1270.

This patch fixes this issue：

diff --git a/src/func.c b/src/func.c
index ec50a17a3..3c50c9055 100644
--- a/src/func.c
+++ b/src/func.c
@@ -1267,12 +1267,12 @@ static void replaceFunc(
             || sqlite3_context_db_handle(context)->mallocFailed );
     return;
   }
-  if( zPattern[0]==0 ){
+  nPattern = sqlite3_value_bytes(argv[1]);
+  if( nPattern==0 ){
     assert( sqlite3_value_type(argv[1])!=SQLITE_NULL );
     sqlite3_result_value(context, argv[0]);
     return;
   }
-  nPattern = sqlite3_value_bytes(argv[1]);
   assert( zPattern==sqlite3_value_text(argv[1]) );  /* No encoding change */
   zRep = sqlite3_value_text(argv[2]);
   if( zRep==0 ) return;

Suggested documentation improvement: NUL Characters in Strings should mention that several other functions also treat a NUL as the end of the string.

Seems somewhat interesting, does it not, that the definition of a NULL terminated string is a sequence of non-NULL characters followed by a NULL character (where the length of a character is arbitrary).

Given that definition of a NUL terminated string, by what defect of congnition do you believe that you can put a NULL character inside a NULL terminated string, without teminating the string?

Local man discovers not everyone assumes strings are null-terminated.

Not every language does that, but they still have SQLite support. People not used to dealing with null-terminated strings are going to exist, like it or not.

The very definition of a C-String is that it is a "sequence of non-zero characters followed by a zero character".

A Pascal string, on the other hand, consists of an arbitrary bag-of-bytes of a specified length.

You simply need to be clear whether you are dealing with C Strings or Pascal Strings.

SQLite3 uses C Strings (so sayeth the documentation). This necessarily implies that an embedded null is not embedded (since that is impossible by defintion) and is therefore the string terminator.

One may decide that "inside" the library all strings are Pascal strings, and work with them that way, or, one may decide that "inside" the library all strings are C strings, and work with them that way.

SQLite3 uses NULL terminated strings internally. Since every string in SQLite3 has a specified length (stored string that is, not ephemeral strings), then it could implement Pascal string handling semantics internally. However, this would have to be done consistently and everywhere. If there is any one "spot" left-over that does not process everything using Pascal-style strings, then the whole re-writing process would be a massive waste of time.

Improving that documentation does not solve the problem of inconsistent behavior.

In fact, it works fine as long as the first character of the Y argument is not NUL, as the initial test case should have shown.

According to the Built-In Scalar SQL Functions documentation, the replace(X,Y,Z) function actually uses memcmp(), which can handle binary bytes. I speculate that when the Y argument starts with NUL, it is incorrectly treated as an empty string.

Interesting

The branch of logic causing the problem actually uses sqlite3_result_value() without going through sqlite3_result_text().

select typeof(replace(1,x'00','')),typeof(replace('1',x'00',''));

-- integer|text

Sorry I don't know much about SQLite C Interface, but this document seems to be for "Binding Values To Prepared Statements", if I use the prepared statement, I might replace x'00' with char(0). I think the problem is not here.

The problem is that you are creating strings with embedded NUL characters that are documented to deliver undefined results in string operations. Note that "undefined results" means that ANY result is the correct result, even if that result changes between releases or between executions.

SQLite is designed to make it easy to create functions (or even replace built in functions). If your application requires embedded-NUL safe string functions, you can always write them yourself.

My previous plan was to use only sqlcipher CLI to handle strings with NUL mixed in, I think Removing NUL Characters From A Text Field replace(b,char(0),'') is more useful than substr(b,1,instr(b,char(0))), but this problem just makes it almost impossible to be simple.

I think the robustness of the replace() function deserves improvement, and the patch should be relatively simple.