After 3.45 and the subsequent backwards compatibility fix almost all valid JSON texts work correctly with JSON_* functions when cast to BLOB, but not always.

The current wording implies that if a BLOB is not valid JSONB, then it should be interpreted as text:

For backwards compatibility, the (formerly incorrect) legacy behavior of interpreting BLOBs as text JSON if no other interpretation works is hereby documented and is be officially supported in version 3.45.1

We have found at least one compatibility-breaking instance where valid JSON text when cast to BLOB returns an error: JSON_VALID(cast('["й"]' as blob)) returns false since 3.45.1

When passed to other JSON_* functions, it causes an error.

Something similar has been mentioned before, but there was no further discussion.

Reproduction:

$ nix develop '.#sqlite3442' --command sqlite3
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT cast('["й"]' as blob), '["й"]',
        JSON_VALID(cast('["й"]' as blob)), JSON_VALID('["й"]'),
        JSON_VALID(cast('["q", "й"]' as blob)), JSON_VALID('["q", "й"]');   ...>    ...>
["й"]|["й"]|1|1|1|1
sqlite>
$ nix develop '.#sqlite3452' --command sqlite3
SQLite version 3.45.2 2024-03-12 11:06:23
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT cast('["й"]' as blob), '["й"]',
        JSON_VALID(cast('["й"]' as blob)), JSON_VALID('["й"]'),
        JSON_VALID(cast('["q", "й"]' as blob)), JSON_VALID('["q", "й"]');   ...>    ...>
["й"]|["й"]|0|1|1|1
sqlite>
$ nix develop '.' --command sqlite3
SQLite version 3.48.0 2025-01-14 11:05:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT cast('["й"]' as blob), '["й"]',
        JSON_VALID(cast('["й"]' as blob)), JSON_VALID('["й"]'),
        JSON_VALID(cast('["q", "й"]' as blob)), JSON_VALID('["q", "й"]');   ...>    ...>
["й"]|["й"]|0|1|1|1
sqlite>

flake.nix for completeness:

{
  inputs = {
    sqlite3442.url = "github:NixOS/nixpkgs/7a339d87931bba829f68e94621536cad9132971a";
    sqlite3452.url = "github:NixOS/nixpkgs/e89cf1c932006531f454de7d652163a9a5c86668";
    nixpkgs.url = "github:NixOS/nixpkgs";
    flake-utils.url = "github:numtide/flake-utils";
  };
  outputs = { self, sqlite3442, sqlite3452, nixpkgs, flake-utils }:
    flake-utils.lib.eachDefaultSystem
      (system:
        let
          pkgs1 = import sqlite3442 {
            inherit system;
          };
          pkgs2 = import sqlite3452 {
            inherit system;
          };
          pkgs = import nixpkgs {
            inherit system;
          };
        in
        {
          devShells.sqlite3442 = pkgs1.mkShell {
            buildInputs = [
              pkgs1.sqlite
            ];
          };
          devShells.sqlite3452 = pkgs2.mkShell {
            buildInputs = [
              pkgs2.sqlite
            ];
          };
          devShells.default = pkgs.mkShell {
            buildInputs = [
              pkgs.sqlite
            ];
          };
        }
      );
}