json_set bug (3.43.2)

(1) By UnderKoen on 2023-10-17 12:10:32 [source]

The following query has an output that is not logical.

select json, json -> 0, json -> 1 from (select json_set(json_set('[]','$[#]',0),'$[#]',1) json);

It outputs

[0,1] | 1 | 1

Where it should output:

[0,1] | 0 | 1

(2) By Richard Hipp (drh) on 2023-10-17 13:54:08 in reply to 1 [link] [source]

Thanks for the bug report.

A simple one-line fix to the code is now available at https://sqlite.org/src/info/e5099c549a1d8959.

The problem was introduced in version 3.43.0, specifically by the check-in at https://sqlite.org/src/timeline?c=df099ad713011b67&y=ci&n=39. That check-in was a set of optimizations that helps JSON run much faster. Seems like it is always the optimizations that introduce bugs. As you can see for the details of that check-in, this is the third problem found (so far) associated with that optimization.

The patch is also available on the tip of branch-3.43. You can download a source tarball and recompile from that if you need a quick fix. Or just make the one-line patch to whatever version of SQLite you are currently using.