Setting PRAGMA case_sensitive_like causes unsafe error

(1.1) By Adrian (adrian.s) on 2023-08-15 08:42:34 edited from 1.0 [source]

While working on airsequel.com, I discovered following problem:
Once the case PRAGMA case_sensitive_like is set to any value, it causes an unsafe use of LIKE() error when LIKE is used.

Minimal example:

$ sqlite3 :memory:
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite> PRAGMA case_sensitive_like = False;
sqlite> CREATE VIEW test AS SELECT 'abc' LIKE 'a%';
sqlite> SELECT * FROM test;
Parse error: unsafe use of LIKE()

Why does PRAGMA case_sensitive_like = False change anything at all, if it's already the default?
PRAGMA case_sensitive_like = True apparently installs a new application-defined LIKE function. Why is this function not trusted?
Why is there an unsafe error at all? I didn't turn off PRAGMA trusted_schema.

(2) By Stephan Beal (stephan) on 2023-08-15 08:21:46 in reply to 1.0 [link] [source]

CREATE VIEW test AS SELECT "abc" LIKE "a%";

Double-quotes are for identifiers and single-quotes are for strings:

sqlite> CREATE VIEW test AS SELECT 'abc' LIKE 'a%';
sqlite> SELECT * FROM test;
'abc' LIKE 'a%'
1

(3.1) By Adrian (adrian.s) on 2023-08-15 08:44:42 edited from 3.0 in reply to 2 [link] [source]

Oops, I fixed it, thanks! However, it doesn't change anything. The error still comes up. (You have to set the PRAGMA case_sensitive_like to replicate it!)

(4) By anonymous on 2023-08-15 16:00:51 in reply to 1.1 [link] [source]

Fixed in this commit, which I find myself unable to resist nitpicking.

Surely, you can shave off a few bytes of code size and a few picoseconds of execution time by including the SQLITE_INNOCUOUS flag when registering the function instead of clearing the SQLITE_FUNC_UNSAFE flag afterwards?