SQLite Forum

Bug of json_each if object has value field.
Login

Bug of json_each if object has value field.

(1) By Evgeny (evgskvdev) on 2021-05-28 19:07:49 [link] [source]

Observation of the bug:

sqlite> SELECT value from JSON_EACH(JSON_ARRAY(JSON_OBJECT("arg", 1, "value", 2, "z", 4))) as x_2;
sqlite> SELECT value from JSON_EACH(JSON_ARRAY(JSON_OBJECT("arg", 1, "valuex", 2))) as x_2;
value               
--------------------
{"arg":1,"valuex":2}
sqlite> 

Is this a known issue?

(2) By Richard Hipp (drh) on 2021-05-28 19:25:16 in reply to 1 [link] [source]

The string literal delimiter in the SQL language is a single-quote ' (0x27) not a double-quote " (0x22).

(3) By Evgeny (evgskvdev) on 2021-05-28 20:40:08 in reply to 2 [link] [source]

Oh, thank you! Double quotes almost always work in SQLite. Apparently not in this example. Thanks again!

(4) By Warren Young (wyoung) on 2021-05-28 21:13:48 in reply to 3 [link] [source]

You're warned against relying on the cases where it does work in the docs, here.

(5) By Ryan Smith (cuz) on 2021-05-28 23:21:16 in reply to 2 [link] [source]

me slightly waking from slumber - only enough so to whisper:
... S T R I C T _ M O D E ...

Even if the first order of business of a new

  pragma strict_mode = 1;
is simply to error on ANY incorrect usage of quotes, it would make a World of difference. Even save the Devs' time. (Perhaps a compile-option would be more efficient?)

A good 3% to 5% of posts here would solve themselves.

We could start saying: It isn't a bug unless it also fails in strict-mode, which would render noise like this post, cured.

Later maybe strict mode can error on inserting NULLs into PK's.
The World of current SQLite peculiarities and backward compatibility idiosyncrasies is our oyster!

(6.1) By Keith Medcalf (kmedcalf) on 2021-05-28 23:42:44 edited from 6.0 in reply to 5 [source]

There is already a compile-time option to fix quotes. However, it only (obviously) has the effect of requiring that double-quotes are identifier quotes only. It will not help necessarily help people who insist on using quotes improperly because there is no way to know that someone has used the wrong quote type when both are acceptable (but have different meanings) -- it only prevents (by spitting an error message) if someone uses identifier quotes when the identifier cannot be resolved.