Is there a character length limit on TEXT NOT NULL UNIQUE columns in SQLite?

Question

I want to avoid duplicates in a simple SpatiaLite database with 2 spatial tables and non-unique coordinates. For each table I've created a TEXT NOT NULL UNIQUE column for an identifier. One table has a 14 character identifier and it works fine but the 2nd table has a 51 character identifier and the text gets corrupted to 6 unprintable characters. After much trial and error I've determined that I have to limit these identifiers to 15 characters. Between 16 and 22 characters I get a "UNIQUE constraint failed" error. Above 22 characters I get the corruption described above and only occassionally the constraint failure. Colleagues and I have searched and found no documentation on any such limit particular to TEXT NOT NULL UNIQUE columns, with or without the SpatiaLite extensions. So what is this?

A bug in SQLite or SpatiaLite?
A known 15 character limit for TEXT NOT NULL UNIQUE columns?
A bad assumption on my part somewhere?

I'm using:

SQLite version: 3.22.0
SpatiaLite version: 4.3.0a

The default maximum string length is 1 billion bytes. See sqlite.org/limits.html — Shawn
We've tried to consider possible encoding issues but they don't seem fit with the observations, especially that problems only arise for text over 15 characters. Besides, we're developing on Linux, "PRAGMA encoding;" returns "UTF-8", and we're using only ASCII characters. — Guy Lancaster

Guy Lancaster Guy Lancaster · Accepted Answer · 2021-02-08T18:23:03

The problem appears to be the use of ostringstream to generate and contain the identifier string. Here's the code:

std::ostringstream blid;
blid << ...    
sqlite3_bind_text(pStmt, 1, blid.str().c_str(), blid.str().length(), nullptr);

The str() method returns a temporary copy of ostringstream's internal string object and that copy is destroyed after the statement in which it it used. So it looks like the bind call is retaining the string pointer until a later statement, probably the step call, at which point the temporary has been destroyed. The tricky point is that this code worked for short identifiers up to 15 bytes and that can be explained if the string object contains a special 16 byte array to store short strings and it survives uncorrupted long enough for the code to work with short strings. The solution is to keep a copy of the identifier long enough for SQLite to finish with it. Something like:

std::ostringstream idBuf;
idBuf << ...    
std::string blid = idBuf.str();
sqlite3_bind_text(pStmt, 1, blid.str().c_str(), blid.str().length(), nullptr);
...
int rc = sqlite3_step(pStmt);

Is there a character length limit on TEXT NOT NULL UNIQUE columns in SQLite?

1 Answers