Closed nil4 closed 7 months ago
The root cause appears similar to that described and handled for sqlite3_bind_blob
at:
This was confirmed by testing the SQLite C API behavior with the program below.
Running this produces expected results:
Literal empty string: got sql_type=3, byte_count=0, ptr=0x0 Bound empty string: got sql_type=3, byte_count=0, ptr=0x0
However, if line 40 is changed to pass in a NULL
pointer, i.e.:
- rc = sqlite3_bind_text(query, 1, "", 0, NULL);
+ rc = sqlite3_bind_text(query, 1, NULL, 0, NULL);
Then the behavior changes and SQLITE_NULL (5
) is inserted:
Literal empty string: got sql_type=3, byte_count=0, ptr=0x0 Assertion failed: (sql_type == SQLITE_TEXT), function verify_text_inserted, file test.c, line 67. Expected to find SQLITE_TEXT (3) but got 5
This suggests that the issue reported here is caused by:
ReadOnlySpan
is passed-infixed
statement produces a null
pointer for empty spansnull
pointer is passed in to the native C APISQLITE_NULL
for the bound parameter Pull request https://github.com/ericsink/SQLitePCL.raw/pull/558 suggests a potential fix for this corner case in the sqlite3_bind_text*
functions.
Hmmm. Thanks for the report. The behavior you've described does seem incorrect.
The code has been this way for years, so I wonder why the problem has not been noticed earlier. I need to look a bit more closely before I make the code change.
The issue popped up on our radar when inserting UTF-8 byte spans (empty strings) into SQLite columns declared NOT NULL
-- the unexpected conversions to SQLITE_NULL
caused the statements to fail right away.
I can only guess that other (more common?) use cases might bind C# (UTF-16) strings or utf8z
parameters instead, which could avoid this issue on those code paths. But in our case, ReadOnlySpan
was required, and so we need to work around it.
I understand that a detailed review is needed; happy to help in any way I can to see the fix proposed in the PR (or something similar to it) go through.
This was included in 2.1.7, which has been pushed up to nuget.
The method
sqlite3_bind_text(sqlite3_stmt stmt, int index, ReadOnlySpan<byte> val)
, called withReadOnlySpan.Empty
when inserting into atext
column, results innull
values, instead of empty strings.With reference to the SQLite docs, namely:
The current behaviour seems surprising and unexpected. Passing an empty span with non-negative length, it seems reasonable to expect the same result as having a literal empty string (
''
) in the SQL text, i.e. an empty string to be bound, and notnull
.A repro program is provided below. It inserts an empty string, once using a SQL literal string, and again calling
sqlite3_bind_text
with an empty span.The literal value is stored and can be retrieved as expected. However, the bound empty span results in
null
being stored.SQLitePCLRaw.bundle_green
2.1.6Reproduces on MacOS Sonoma (14.0) and Windows 11 (22H2, 10.0.22621.2283), both ARM64.
.NET (Core) 8.0.100-rc.1.23455.8
Reproduces from the command line, as well as IDE (Rider), thus I assume IDE version may not be relevant.
PackageReference
None.
In a .NET console app project, add a package reference to
SQLitePCLRaw.bundle_green
2.1.6, e.g.:Replace
Program.cs
with the code below.An assertion is triggered when SQLITE_NULL (
5
) is unexpectedly found after insert: