Error while enabling compression for some field, saying dict chooser expression invalid

[renyuneyun]

I'm trying to enable compression for my sqlite database, following the README. I have several columns to compress. Executing SELECT zstd_enable_transparent(...); works fine for the first statement, but fails any further ones, with the following error:

sqlite> SELECT zstd_enable_transparent('{"table": "queryrecord", "column": "system_message", "compression_level": 19, "dict_chooser": "''a''"}');
[2024-09-27T22:40:29Z WARN  sqlite_zstd::transparent] Warning: It is recommended to set `pragma journal_mode=WAL;`
[2024-09-27T22:40:29Z WARN  sqlite_zstd::transparent] Warning: It is recommended to set `pragma auto_vacuum=full;`
[2024-09-27T22:40:29Z WARN  sqlite_zstd::transparent] Warning: It is recommended to set `pragma busy_timeout=2000;` or higher
Runtime error: Dict chooser expression does not seem to be valid. Make sure you return a string and get your escaping right: If you want an sqlite string inside a json string inside a sqlite string you need to do '{"foo": "''bar''"}'

Caused by:
    0: Tried to execute:
       select ('a') as dict_chooser from `queryrecord` limit 1
    1: unsafe use of zstd_decompress_col()
    2: Error code 1: SQL error or missing database
sqlite> select ('a') as dict_chooser from `queryrecord` limit 1

It happens as well if I try to execute multiple statements at once, connected by ,, as illustrated in the README.

Any ideas why this happens?

(I'm not an experienced sqlite user, so I may miss something obvious. Please do let me know if that's the case.)

[renyuneyun]

Ok, it appears to only happen for my manually compiled share object from master project source. If using the one compiled from corresponding Python library (and using it in Python; I didn't test from shell), no such errors are shown up.

(Though, it appears the two .so have exactly the same size. I'm not sure how to compare the internal of them to see if they are really the same. At least md5sum is different.)

In the meantime, if loading the share object from Python version (on commandline), and on a database with compression enabled, performing a simple SELECT query will also emit a related error:

sqlite> SELECT * FROM queryrecord;
Parse error: unsafe use of zstd_decompress_col()

However, it appears the Python program (using SQLAlchemy) runs normally with this database...

[renyuneyun]

It appears to be related to inconsistent default behaviour of sqlite commandline program and sqlite connection, as found from this SO question and answer.
Their solution also works for me for the sqlite shell SELECT query. I haven't tested re-enabling compression, but probably that's the same issue.

(Although, it doesn't help understand the real reason behind this. But I'm personally satisfied as it is fine for my use case.)

I'll keep this issue open until I have time to verify, or until others comment.

[arnfaldur]

I encountered this too and the solution described in the afforementioned SO question solved it.

Here it is for the lazy:

PRAGMA trusted_schema=1;