Runtime error: out of memory (7) when using Json funtions in triggers
(1.1) By beetlejuice (coleifer) on 2023-05-01 21:29:47 edited from 1.0 [source]
I use the JSON extension to provide changelog-like functionality.
I just updated my local sqlite repo, as I tend to track closely to try and catch issues early (running 342af5b4fa0bd7c699e5497161db13d0cf795c7a). I am seeing the following when using some triggers that rely on Json functions:
Runtime error: out of memory (7)
Here is the example that is failing for me in a test-case:
CREATE TABLE IF NOT EXISTS "person" ( "id" INTEGER NOT NULL PRIMARY KEY, "name" TEXT NOT NULL, "dob" DATE NOT NULL); CREATE TABLE IF NOT EXISTS "changelog" ( "id" INTEGER NOT NULL PRIMARY KEY, "timestamp" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "action" TEXT NOT NULL, "table" TEXT NOT NULL, "primary_key" INTEGER NOT NULL, "changes" JSON NOT NULL); CREATE TRIGGER IF NOT EXISTS person_changes_INSERT AFTER INSERT ON person BEGIN INSERT INTO changelog ("action", "table", "primary_key", "changes") SELECT 'INSERT', 'person', NEW."id", "changes" FROM ( SELECT json_group_object( col, json_array( case when json_valid("oldval") then json("oldval") else "oldval" end, case when json_valid("newval") then json("newval") else "newval" end) ) AS "changes" FROM ( SELECT json_extract(value, '$') as "col", json_extract(value, '$') as "oldval", json_extract(value, '$') as "newval" FROM json_each(json_array( json_array('name', NULL, NEW."name"), json_array('dob', NULL, NEW."dob"))) WHERE "oldval" IS NOT "newval" )); END; INSERT INTO "person" ("name", "dob") VALUES ('Somebody', '2000-01-01');
Note I had to de-dent all the trigger code before running it interactively via the shell, since the shell seemed to try to do some autocomplete and threw a parse error. I have left it indented in the hopes this will help with readability.
The jist of the trigger is it is building a json object of changes from the previous data -> inserted data, and then storing the json object representing the changes in a changelog table.
(2) By Richard Hipp (drh) on 2023-05-02 11:13:39 in reply to 1.1 [link] [source]
Simplified test case:
The above should return NULL, not raise an out-of-memory error. The problem is fixed in check-in ab78e6946ba8125a
(3) By beetlejuice (coleifer) on 2023-05-02 15:38:30 in reply to 2 [link] [source]
Thank you Richard, I got busy with kids and family stuff right after I posted so I didn't have any time to investigate. Glad it was an isolated change!
(4.1) By Richard Hipp (drh) on 2023-08-11 10:51:41 edited from 4.0 in reply to 2 [link] [source]
The historical behavior of json_valid() is to return FALSE (0) when given a NULL argument. Version 3.43.0 will have a new compile-time option -DSQLITE_LEGACY_JSON_VALID that will restore the (incorrect) legacy behavior.