Sqlite no results for query with a bound parameter, works with identical query with hardcoded value

I am coming up against a strange problem with Sqlite 3. I have a relatively simple query that takes a bound parameter in its WHERE predicate. When executing the query with the bound parameter, I get 0 results, but running the exact same query with the value hardcoded, I get the expected number of results (> 0).

I have double checked my query has been prepared as I expect by calling sqlite3_expanded_sql() and inspecting the resulting SQL string.

My query with the bound parameter:

SELECT
    b.name,
    Y(Transform(b.geom, 4326)) "y",
    X(Transform(b.geom, 4326)) "x"
FROM buildings b
WHERE Within(b.geom, Transform(PolygonFromText(?, 4326), 27700)) > 0

The value I am providing for the bound parameter:

let rectWkt = """
POLYGON((
    -0.1381030196154711 51.51132617405723,
    -0.12929698038450965 51.51132617405723,
    -0.12929698038450965 51.50863378616471,
    -0.1381030196154711 51.50863378616471,
    -0.1381030196154711 51.51132617405723
))
"""

And the query with the value hardcoded, which works:

SELECT
    b.name,
    Y(Transform(b.geom, 4326)) "y",
    X(Transform(b.geom, 4326)) "x"
FROM buildings b
WHERE Within(b.geom, Transform(PolygonFromText('POLYGON((
    -0.1381030196154711 51.51132617405723,
    -0.12929698038450965 51.51132617405723,
    -0.12929698038450965 51.50863378616471,
    -0.1381030196154711 51.50863378616471,
    -0.1381030196154711 51.51132617405723
))', 4326), 27700)) > 0

And lastly, here is the output from sqlite3_expanded_sql(), which I call after binding my value described above:

SELECT
    b.name,
    Y(Transform(b.geom, 4326)) "y",
    X(Transform(b.geom, 4326)) "x"
FROM buildings b
WHERE Within(b.geom, Transform(PolygonFromText('POLYGON((
    -0.1381030196154711 51.51132617405723,
    -0.12929698038450965 51.51132617405723,
    -0.12929698038450965 51.50863378616471,
    -0.1381030196154711 51.50863378616471,
    -0.1381030196154711 51.51132617405723
))', 4326), 27700)) > 0

Unless I am missing something, these are identical statements, yet I get a completely different result when executing them.

I am executing the query using the sqlite c API using the Swift bindings, and I have got the Spatialite extension loaded (for the geometric functions).

I check that the call to sqlite3_bind_text() with my value is SQLITE_OK. There is no error when I try to iterate over the resulting rows, I simply get SQLITE_DONE as the first result, ie. the result set is empty.

Answer

I managed to solve it: I needed to pass SQLITE_TRANSIENT as the last arg to my call to sqlite3_bind_text. I believe this is because my call to execute the query was in a different scope, and by that time the string value was not in scope. Passing SQLITE_TRANSIENT as the fifth arg instructs Sqlite to make its own copy of the value.

In Swift specifically, this needs to be manually defined as let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self) because it’s defined with a macro in the Sqlite header files (see: https://www.sqlite.org/c3ref/c_static.html).

My call to bind before was something like:

guard
    sqlite3_bind_text(stmnt, 1, rectangleWKT, -1, nil) == SQLITE_OK
else {
    throw DatabaseError.Bind(message: errorMessage)
}

But it should be more like:

let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
guard
    sqlite3_bind_text(stmnt, 1, rectangleWKT, -1, SQLITE_TRANSIENT) == SQLITE_OK
else {
    throw DatabaseError.Bind(message: errorMessage)
}

Then I can pass my prepared statement to another function to execute and fetch the results:

return loadBuildings(forStatement: stmnt)