BLOB vs TEXT in SQLite: x'61'='a' is 0, x'61'=cast('a' as blob) is 1, 'a'<x'61' is 1; text||blob yields text; bytes comes back from text_factory=bytes; cast(x'ff' as text) fails in Python: Could ...
Full title: BLOB vs TEXT in SQLite: x'61'='a' is 0, x'61'=cast('a' as blob) is 1, 'a'<x'61' is 1; text||blob yields text; bytes comes back from text_factory=bytes; cast(x'ff' as text) fails in Python: Could not decode to UTF-8 column | Python 3.12.10, SQLite 3.49.1. select x'61'='a' returns 0 (different storage classes never equal), x'61'=cast('a' as blob) returns 1, 'a'<x'61' returns 1 (TEXT sorts before BLOB), typeof(x'61'||'a') is 'text'. Setting connection.text_factory = bytes makes select 'é' return a bytes object of two bytes, c3 and a9 (raw UTF-8). select cast(x'ff' as text) raises OperationalError: Could not decode to UTF-8 column 'cast(x'ff' as text)' with text '?' (replacement character). In node:sqlite and better-sqlite3 BLOB values arrive as Uint8Array and Buffer respectively.