Since a recent SQLite version (> 3.37.0) following error occurs when querying a view which includes json_extract() when the trusted_schema pragma is off:

$ sqlite3
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE VIEW test AS SELECT json_extract('{"a": 1}', '$.a');
sqlite> SELECT * FROM test;
1
sqlite> PRAGMA trusted_schema = False;
sqlite> SELECT * FROM test;
Parse error: unsafe use of json_extract()

Is this the supposed behavior? How can json_extract() be unsafe?

This is a defect in the definiton of the JFUNCTION macro in SqliteInt.h line 2080 which currently reads:

#define JFUNCTION(zName, nArg, iArg, xFunc) \
  {nArg, SQLITE_FUNC_BUILTIN|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS|\
   SQLITE_FUNC_CONSTANT|SQLITE_UTF8, \
   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, 0, #zName, {0} }

However, builtin function definitions should contain only the SQLITE_FUNC_* flags. Since SQLITE_INNOCUOUS == SQLITE_FUNC_UNSAFE, all the JSON functions are marked unsafe.

#define JFUNCTION(zName, nArg, iArg, xFunc) \
  {nArg, SQLITE_FUNC_BUILTIN|SQLITE_UTF8|SQLITE_FUNC_CONSTANT, \
   SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, 0, #zName, {0} }

will fix the issue for all the JSON functions. Presumably this error was introduced when the JSON functions were made internal.

In addition to Keith's answer, notice the lack of innocuous from 3.40.1:

PS: See https://sqlite.org/forum/forumpost/d014af10b6 for v$function_list

sqlite> select * from v$function_list where name like 'json%';
┌───────────────────┬─────────┬────────┬───────────┬─────────────────────────┬─────────────────────────┐
│       name        │ builtin │  type  │ arg-count │ preferred-text-encoding │          flags          │
├───────────────────┼─────────┼────────┼───────────┼─────────────────────────┼─────────────────────────┤
│ json              │ 1       │ scalar │ 1         │ utf8                    │ deterministic           │
│ json_array        │ 1       │ scalar │ -1        │ utf8                    │ deterministic           │
│ json_array_length │ 1       │ scalar │ 1         │ utf8                    │ deterministic           │
│ json_array_length │ 1       │ scalar │ 2         │ utf8                    │ deterministic           │
│ json_extract      │ 1       │ scalar │ -1        │ utf8                    │ deterministic           │
│ json_group_array  │ 1       │ window │ 1         │ utf8                    │ deterministic | subtype │
│ json_group_object │ 1       │ window │ 2         │ utf8                    │ deterministic | subtype │
│ json_insert       │ 1       │ scalar │ -1        │ utf8                    │ deterministic           │
│ json_object       │ 1       │ scalar │ -1        │ utf8                    │ deterministic           │
│ json_patch        │ 1       │ scalar │ 2         │ utf8                    │ deterministic           │
│ json_quote        │ 1       │ scalar │ 1         │ utf8                    │ deterministic           │
│ json_remove       │ 1       │ scalar │ -1        │ utf8                    │ deterministic           │
│ json_replace      │ 1       │ scalar │ -1        │ utf8                    │ deterministic           │
│ json_set          │ 1       │ scalar │ -1        │ utf8                    │ deterministic           │
│ json_type         │ 1       │ scalar │ 1         │ utf8                    │ deterministic           │
│ json_type         │ 1       │ scalar │ 2         │ utf8                    │ deterministic           │
│ json_valid        │ 1       │ scalar │ 1         │ utf8                    │ deterministic           │
└───────────────────┴─────────┴────────┴───────────┴─────────────────────────┴─────────────────────────┘

While 3.36 (for example) has it:

sqlite> select name, flags from v$function_list where name like 'json%';
┌───────────────────┬─────────────────────────────────────┐
│       name        │                flags                │
├───────────────────┼─────────────────────────────────────┤
│ json              │ deterministic | innocuous           │
│ json_array        │ deterministic | innocuous           │
│ json_array_length │ deterministic | innocuous           │
│ json_array_length │ deterministic | innocuous           │
│ json_extract      │ deterministic | innocuous           │
│ json_group_array  │ deterministic | subtype | innocuous │
│ json_group_object │ deterministic | subtype | innocuous │
│ json_insert       │ deterministic | innocuous           │
│ json_object       │ deterministic | innocuous           │
│ json_patch        │ deterministic | innocuous           │
│ json_quote        │ deterministic | innocuous           │
│ json_remove       │ deterministic | innocuous           │
│ json_replace      │ deterministic | innocuous           │
│ json_set          │ deterministic | innocuous           │
│ json_type         │ deterministic | innocuous           │
│ json_type         │ deterministic | innocuous           │
│ json_valid        │ deterministic | innocuous           │
└───────────────────┴─────────────────────────────────────┘

Tickets often reference a Forum Thread (which is great). But could the reverse also be done?

Fossil does that automatically if the ticket/forum is in the same repository as the check-in. But for SQLite, we've chosen to keep the forum in a separate repository from the code. Because the forum is separate, the code repository has no way of knowing that forum repository is referencing it.

It is useful, I suppose for us to manually add links to check-ins that close issues raised in the Forum. Sometimes I do that. Other times I get distracted by the next issue at hand... Thanks for fixing that oversight in your previous post.