Debian Codegraph @

test/json101.test

raw
1 # 2015-08-12
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 # This file implements tests for JSON SQL functions extension to the
12 # SQLite library.
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17
18 do_execsql_test json101-1.1.00 {
19 SELECT json_array(1,2.5,null,'hello');
20 } {[1,2.5,null,"hello"]}
21 do_execsql_test json101-1.1.01 {
22 SELECT json_array(1,'{"abc":2.5,"def":null,"ghi":hello}',99);
23 -- the second term goes in as a string:
24 } {[1,"{\\"abc\\":2.5,\\"def\\":null,\\"ghi\\":hello}",99]}
25 do_execsql_test json101-1.1.02 {
26 SELECT json_array(1,json('{"abc":2.5,"def":null,"ghi":"hello"}'),99);
27 -- the second term goes in as JSON
28 } {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]}
29 do_execsql_test json101-1.1.03 {
30 SELECT json_array(1,json_object('abc',2.5,'def',null,'ghi','hello'),99);
31 -- the second term goes in as JSON
32 } {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]}
33 do_execsql_test json101-1.2 {
34 SELECT hex(json_array('String "\ Test'));
35 } {5B22537472696E67205C225C5C2054657374225D}
36 do_catchsql_test json101-1.3 {
37 SELECT json_array(1,printf('%.1000c','x'),x'abcd',3);
38 } {1 {JSON cannot hold BLOB values}}
39 do_catchsql_test json101-1.3b {
40 SELECT jsonb_array(1,printf('%.1000c','x'),x'abcd',3);
41 } {1 {JSON cannot hold BLOB values}}
42 do_execsql_test json101-1.4 {
43 SELECT json_array(-9223372036854775808,9223372036854775807,0,1,-1,
44 0.0, 1.0, -1.0, -1e99, +2e100,
45 'one','two','three',
46 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
47 19, NULL, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,
48 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
49 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
50 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
51 99);
52 } {[-9223372036854775808,9223372036854775807,0,1,-1,0.0,1.0,-1.0,-1.0e+99,2.0e+100,"one","two","three",4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,null,21,22,23,24,25,26,27,28,29,30,31,"abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ","abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ","abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ",99]}
53 do_execsql_test json101-1.4b {
54 SELECT json(jsonb_array(-9223372036854775808,9223372036854775807,0,1,-1,
55 0.0, 1.0, -1.0, -1e99, +2e100,
56 'one','two','three',
57 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18,
58 19, NULL, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,
59 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
60 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
61 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ',
62 99));
63 } {[-9223372036854775808,9223372036854775807,0,1,-1,0.0,1.0,-1.0,-1.0e+99,2.0e+100,"one","two","three",4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,null,21,22,23,24,25,26,27,28,29,30,31,"abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ","abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ","abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ",99]}
64
65 do_execsql_test json101-2.1 {
66 SELECT json_object('a',1,'b',2.5,'c',null,'d','String Test');
67 } {{{"a":1,"b":2.5,"c":null,"d":"String Test"}}}
68 do_execsql_test json101-2.1b {
69 SELECT json(jsonb_object('a',1,'b',2.5,'c',null,'d','String Test'));
70 } {{{"a":1,"b":2.5,"c":null,"d":"String Test"}}}
71 do_catchsql_test json101-2.2 {
72 SELECT json_object('a',printf('%.1000c','x'),2,2.5);
73 } {1 {json_object() labels must be TEXT}}
74 do_catchsql_test json101-2.2b {
75 SELECT jsonb_object('a',printf('%.1000c','x'),2,2.5);
76 } {1 {json_object() labels must be TEXT}}
77 do_execsql_test json101-2.2.2 {
78 SELECT json_object('a',json_array('xyx',77,4.5),'x',2.5);
79 } {{{"a":["xyx",77,4.5],"x":2.5}}}
80 do_execsql_test json101-2.2.2b {
81 SELECT json(jsonb_object('a',json_array('xyx',77,4.5),'x',2.5));
82 } {{{"a":["xyx",77,4.5],"x":2.5}}}
83 do_execsql_test json101-2.2.3 {
84 SELECT json_object('a',jsonb_array('xyx',77,4.5),'x',2.5);
85 } {{{"a":["xyx",77,4.5],"x":2.5}}}
86 do_execsql_test json101-2.2.3b {
87 SELECT json(jsonb_object('a',jsonb_array('xyx',77,4.5),'x',2.5));
88 } {{{"a":["xyx",77,4.5],"x":2.5}}}
89 do_catchsql_test json101-2.3 {
90 SELECT json_object('a',1,'b');
91 } {1 {json_object() requires an even number of arguments}}
92 do_catchsql_test json101-2.4 {
93 SELECT json_object('a',printf('%.1000c','x'),'b',x'abcd');
94 } {1 {JSON cannot hold BLOB values}}
95 do_execsql_test json101-2.5 {
96 SELECT json_object('a',printf('%.10c','x'),'b',jsonb_array(1,2,3));
97 } {{{"a":"xxxxxxxxxx","b":[1,2,3]}}}
98
99 do_execsql_test json101-3.1 {
100 SELECT json_replace('{"a":1,"b":2}','$.a','[3,4,5]');
101 } {{{"a":"[3,4,5]","b":2}}}
102 do_execsql_test json101-3.1b {
103 SELECT json(jsonb_replace('{"a":1,"b":2}','$.a','[3,4,5]'));
104 } {{{"a":"[3,4,5]","b":2}}}
105 do_execsql_test json101-3.2 {
106 SELECT json_replace('{"a":1,"b":2}','$.a',json('[3,4,5]'));
107 } {{{"a":[3,4,5],"b":2}}}
108 do_execsql_test json101-3.2b {
109 SELECT json_replace('{"a":1,"b":2}','$.a',jsonb('[3,4,5]'));
110 } {{{"a":[3,4,5],"b":2}}}
111 do_execsql_test json101-3.3 {
112 SELECT json_type(json_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b');
113 } {text}
114 do_execsql_test json101-3.3b {
115 SELECT json_type(jsonb_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b');
116 } {text}
117 do_execsql_test json101-3.4 {
118 SELECT json_type(json_set('{"a":1,"b":2}','$.b',json('{"x":3,"y":4}')),'$.b');
119 } {object}
120 do_execsql_test json101-3.4b {
121 SELECT json_type(jsonb_set('{"a":1,"b":2}','$.b',jsonb('{"x":3,"y":4}')),'$.b');
122 } {object}
123 ifcapable vtab {
124 do_execsql_test json101-3.5 {
125 SELECT fullkey, atom, '|' FROM json_tree(json_set('{}','$.x',123,'$.x',456));
126 } {{$} {} | {$.x} 456 |}
127 do_execsql_test json101-3.5b {
128 SELECT fullkey, atom, '|' FROM json_tree(jsonb_set('{}','$.x',123,'$.x',456));
129 } {{$} {} | {$.x} 456 |}
130 }
131
132 # Per rfc7159, any JSON value is allowed at the top level, and whitespace
133 # is permitting before and/or after that value.
134 #
135 do_execsql_test json101-4.1 {
136 CREATE TABLE j1(x);
137 INSERT INTO j1(x)
138 VALUES('true'),('false'),('null'),('123'),('-234'),('34.5e+6'),
139 ('""'),('"\""'),('"\\"'),('"abcdefghijlmnopqrstuvwxyz"'),
140 ('[]'),('{}'),('[true,false,null,123,-234,34.5e+6,{},[]]'),
141 ('{"a":true,"b":{"c":false}}');
142 SELECT * FROM j1 WHERE NOT json_valid(x);
143 } {}
144 do_execsql_test json101-4.2 {
145 SELECT * FROM j1 WHERE NOT json_valid(char(0x20,0x09,0x0a,0x0d)||x);
146 } {}
147 do_execsql_test json101-4.3 {
148 SELECT * FROM j1 WHERE NOT json_valid(x||char(0x20,0x09,0x0a,0x0d));
149 } {}
150
151 # But an empty string, or a string of pure whitespace is not valid JSON.
152 #
153 do_execsql_test json101-4.4 {
154 SELECT json_valid(''), json_valid(char(0x20,0x09,0x0a,0x0d));
155 } {0 0}
156
157 # json_remove() and similar functions with no edit operations return their
158 # input unchanged.
159 #
160 do_execsql_test json101-4.5 {
161 SELECT x FROM j1 WHERE json_remove(x)<>x;
162 } {}
163 do_execsql_test json101-4.6 {
164 SELECT x FROM j1 WHERE json_replace(x)<>x;
165 } {}
166 do_execsql_test json101-4.7 {
167 SELECT x FROM j1 WHERE json_set(x)<>x;
168 } {}
169 do_execsql_test json101-4.8 {
170 SELECT x FROM j1 WHERE json_insert(x)<>x;
171 } {}
172 do_execsql_test json101-4.9 {
173 SELECT json_insert('{"a":1}','$.b',CAST(x'0000' AS text));
174 } {{{"a":1,"b":"\u0000\u0000"}}}
175
176 # json_extract(JSON,'$') will return objects and arrays without change.
177 #
178 do_execsql_test json101-4.10 {
179 SELECT count(*) FROM j1 WHERE json_type(x) IN ('object','array');
180 SELECT x FROM j1
181 WHERE json_extract(x,'$')<>x
182 AND json_type(x) IN ('object','array');
183 } {4}
184 do_execsql_test json101-4.10b {
185 CREATE TABLE j1b AS SELECT jsonb(x) AS "x" FROM j1;
186 SELECT count(*) FROM j1b WHERE json_type(x) IN ('object','array');
187 SELECT json(x) FROM j1b
188 WHERE json_extract(x,'$')<>json(x)
189 AND json_type(x) IN ('object','array');
190 } {4}
191
192 do_execsql_test json101-5.1 {
193 CREATE TABLE j2(id INTEGER PRIMARY KEY, json, src);
194 INSERT INTO j2(id,json,src)
195 VALUES(1,'{
196 "firstName": "John",
197 "lastName": "Smith",
198 "isAlive": true,
199 "age": 25,
200 "address": {
201 "streetAddress": "21 2nd Street",
202 "city": "New York",
203 "state": "NY",
204 "postalCode": "10021-3100"
205 },
206 "phoneNumbers": [
207 {
208 "type": "home",
209 "number": "212 555-1234"
210 },
211 {
212 "type": "office",
213 "number": "646 555-4567"
214 }
215 ],
216 "children": [],
217 "spouse": null
218 }','https://en.wikipedia.org/wiki/JSON');
219 INSERT INTO j2(id,json,src)
220 VALUES(2, '{
221 "id": "0001",
222 "type": "donut",
223 "name": "Cake",
224 "ppu": 0.55,
225 "batters":
226 {
227 "batter":
228 [
229 { "id": "1001", "type": "Regular" },
230 { "id": "1002", "type": "Chocolate" },
231 { "id": "1003", "type": "Blueberry" },
232 { "id": "1004", "type": "Devil''s Food" }
233 ]
234 },
235 "topping":
236 [
237 { "id": "5001", "type": "None" },
238 { "id": "5002", "type": "Glazed" },
239 { "id": "5005", "type": "Sugar" },
240 { "id": "5007", "type": "Powdered Sugar" },
241 { "id": "5006", "type": "Chocolate with Sprinkles" },
242 { "id": "5003", "type": "Chocolate" },
243 { "id": "5004", "type": "Maple" }
244 ]
245 }','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html');
246 INSERT INTO j2(id,json,src)
247 VALUES(3,'[
248 {
249 "id": "0001",
250 "type": "donut",
251 "name": "Cake",
252 "ppu": 0.55,
253 "batters":
254 {
255 "batter":
256 [
257 { "id": "1001", "type": "Regular" },
258 { "id": "1002", "type": "Chocolate" },
259 { "id": "1003", "type": "Blueberry" },
260 { "id": "1004", "type": "Devil''s Food" }
261 ]
262 },
263 "topping":
264 [
265 { "id": "5001", "type": "None" },
266 { "id": "5002", "type": "Glazed" },
267 { "id": "5005", "type": "Sugar" },
268 { "id": "5007", "type": "Powdered Sugar" },
269 { "id": "5006", "type": "Chocolate with Sprinkles" },
270 { "id": "5003", "type": "Chocolate" },
271 { "id": "5004", "type": "Maple" }
272 ]
273 },
274 {
275 "id": "0002",
276 "type": "donut",
277 "name": "Raised",
278 "ppu": 0.55,
279 "batters":
280 {
281 "batter":
282 [
283 { "id": "1001", "type": "Regular" }
284 ]
285 },
286 "topping":
287 [
288 { "id": "5001", "type": "None" },
289 { "id": "5002", "type": "Glazed" },
290 { "id": "5005", "type": "Sugar" },
291 { "id": "5003", "type": "Chocolate" },
292 { "id": "5004", "type": "Maple" }
293 ]
294 },
295 {
296 "id": "0003",
297 "type": "donut",
298 "name": "Old Fashioned",
299 "ppu": 0.55,
300 "batters":
301 {
302 "batter":
303 [
304 { "id": "1001", "type": "Regular" },
305 { "id": "1002", "type": "Chocolate" }
306 ]
307 },
308 "topping":
309 [
310 { "id": "5001", "type": "None" },
311 { "id": "5002", "type": "Glazed" },
312 { "id": "5003", "type": "Chocolate" },
313 { "id": "5004", "type": "Maple" }
314 ]
315 }
316 ]','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html');
317 SELECT count(*) FROM j2;
318 CREATE TABLE j2b(id INTEGER PRIMARY KEY, json, src);
319 INSERT INTO J2b(id,json,src) SELECT id, jsonb(json), src FROM j2;
320 SELECT count(*) FROM j2b;
321 } {3 3}
322
323 do_execsql_test json101-5.2 {
324 SELECT id, json_valid(json), json_type(json), '|' FROM j2 ORDER BY id;
325 } {1 1 object | 2 1 object | 3 1 array |}
326 do_execsql_test json101-5.2b {
327 SELECT id, json_valid(json,5), json_type(json), '|' FROM j2b ORDER BY id;
328 } {1 1 object | 2 1 object | 3 1 array |}
329
330 ifcapable !vtab {
331 finish_test
332 return
333 }
334
335 # fullkey is always the same as path+key (with appropriate formatting)
336 #
337 do_execsql_test json101-5.3 {
338 SELECT j2.rowid, jx.rowid, fullkey, path, key
339 FROM j2, json_tree(j2.json) AS jx
340 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']'
341 ELSE '.'||key END);
342 } {}
343 do_execsql_test json101-5.3b {
344 SELECT j2b.rowid, jx.rowid, fullkey, path, key
345 FROM j2b, json_tree(j2b.json) AS jx
346 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']'
347 ELSE '.'||key END);
348 } {}
349 do_execsql_test json101-5.4 {
350 SELECT j2.rowid, jx.rowid, fullkey, path, key
351 FROM j2, json_each(j2.json) AS jx
352 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']'
353 ELSE '.'||key END);
354 } {}
355
356
357 # Verify that the json_each.json and json_tree.json output is always the
358 # same as input.
359 #
360 do_execsql_test json101-5.5 {
361 SELECT j2.rowid, jx.rowid, fullkey, path, key
362 FROM j2, json_each(j2.json) AS jx
363 WHERE jx.json<>j2.json;
364 } {}
365 do_execsql_test json101-5.6 {
366 SELECT j2.rowid, jx.rowid, fullkey, path, key
367 FROM j2, json_tree(j2.json) AS jx
368 WHERE jx.json<>j2.json;
369 } {}
370 do_execsql_test json101-5.7 {
371 SELECT j2.rowid, jx.rowid, fullkey, path, key
372 FROM j2, json_each(j2.json) AS jx
373 WHERE jx.value<>jx.atom AND type NOT IN ('array','object');
374 } {}
375 do_execsql_test json101-5.8 {
376 SELECT j2.rowid, jx.rowid, fullkey, path, key
377 FROM j2, json_tree(j2.json) AS jx
378 WHERE jx.value<>jx.atom AND type NOT IN ('array','object');
379 } {}
380
381 # 2024-02-16 https://sqlite.org/forum/forumpost/ecb94cd210
382 # Regression in json_tree()/json_each(). The value column
383 # should have the "J" subtype if the value is an array or
384 # object.
385 #
386 do_execsql_test json101-5.10 {
387 SELECT json_insert('{}','$.a',value) FROM json_tree('[1,2,3]') WHERE atom IS NULL;
388 } {{{"a":[1,2,3]}}}
389 # ^^^^^^^--- In double-quotes, a string literal, prior to bug fix
390
391 do_execsql_test json101-5.11 {
392 SELECT json_insert('{}','$.a',value) FROM json_tree('"[1,2,3]"');
393 } {{{"a":"[1,2,3]"}}}
394
395 do_execsql_test json101-6.1 {
396 SELECT json_valid('{"a":55,"b":72,}');
397 } {0}
398 do_execsql_test json101-6.2 {
399 SELECT json_error_position('{"a":55,"b":72,}');
400 } {0}
401 do_execsql_test json101-6.3 {
402 SELECT json_valid(json('{"a":55,"b":72,}'));
403 } {1}
404 do_execsql_test json101-6.4 {
405 SELECT json_valid('{"a":55,"b":72 , }');
406 } {0}
407 do_execsql_test json101-6.5 {
408 SELECT json_error_position('{"a":55,"b":72 , }');
409 } {0}
410 do_execsql_test json101-6.6 {
411 SELECT json_error_position('{"a":55,"b":72,,}');
412 } {16}
413 do_execsql_test json101-6.7 {
414 SELECT json_valid('{"a":55,"b":72}');
415 } {1}
416 do_execsql_test json101-6.8 {
417 SELECT json_error_position('["a",55,"b",72,]');
418 } {0}
419 do_execsql_test json101-6.9 {
420 SELECT json_error_position('["a",55,"b",72 , ]');
421 } {0}
422 do_execsql_test json101-6.10 {
423 SELECT json_error_position('["a",55,"b",72,,]');
424 } {16}
425 do_execsql_test json101-6.11 {
426 SELECT json_valid('["a",55,"b",72]');
427 } {1}
428
429 # White-space tests. Note that form-feed is not white-space in JSON.
430 # ticket [57eec374ae1d0a1d4a23077a95f4e173fe269113]
431 #
432 foreach {tn isvalid ws} {
433 7.1 1 char(0x20)
434 7.2 1 char(0x09)
435 7.3 1 char(0x0A)
436 7.4 1 char(0x0D)
437 7.5 0 char(0x0C)
438 7.6 1 char(0x20,0x09,0x0a,0x0d,0x20)
439 7.7 0 char(0x20,0x09,0x0a,0x0c,0x0d,0x20)
440 } {
441 do_execsql_test json101-$tn.1 \
442 "SELECT json_valid(printf('%s{%s\"x\"%s:%s9%s}%s',
443 $::ws,$::ws,$::ws,$::ws,$::ws,$::ws));" \
444 $isvalid
445 }
446
447 # Ticket https://sqlite.org/src/info/ad2559db380abf8e
448 # Control characters must be escaped in JSON strings.
449 #
450 do_execsql_test json101-8.1 {
451 DROP TABLE IF EXISTS t8;
452 CREATE TABLE t8(a,b);
453 INSERT INTO t8(a) VALUES('abc' || char(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35) || 'xyz');
454 UPDATE t8 SET b=json_array(a);
455 SELECT b FROM t8;
456 } {{["abc\u0001\u0002\u0003\u0004\u0005\u0006\u0007\b\t\n\u000b\f\r\u000e\u000f\u0010\u0011\u0012\u0013\u0014\u0015\u0016\u0017\u0018\u0019\u001a\u001b\u001c\u001d\u001e\u001f !\"#xyz"]}}
457 do_execsql_test json101-8.1b {
458 DROP TABLE IF EXISTS t8;
459 CREATE TABLE t8(a,b);
460 INSERT INTO t8(a) VALUES('abc' || char(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35) || 'xyz');
461 UPDATE t8 SET b=jsonb_array(a);
462 SELECT json(b) FROM t8;
463 } {{["abc\u0001\u0002\u0003\u0004\u0005\u0006\u0007\b\t\n\u000b\f\r\u000e\u000f\u0010\u0011\u0012\u0013\u0014\u0015\u0016\u0017\u0018\u0019\u001a\u001b\u001c\u001d\u001e\u001f !\"#xyz"]}}
464 do_execsql_test json101-8.2 {
465 SELECT a=json_extract(b,'$[0]') FROM t8;
466 } {1}
467
468 # 2017-04-12. Regression reported on the mailing list by Rolf Ade
469 #
470 do_execsql_test json101-8.3 {
471 SELECT json_valid(char(0x22,0xe4,0x22));
472 } {1}
473 do_execsql_test json101-8.4 {
474 SELECT unicode(json_extract(char(0x22,228,0x22),'$'));
475 } {228}
476
477 # The json_quote() function transforms an SQL value into a JSON value.
478 # String values are quoted and interior quotes are escaped. NULL values
479 # are rendered as the unquoted string "null".
480 #
481 do_execsql_test json101-9.1 {
482 SELECT json_quote('abc"xyz');
483 } {{"abc\"xyz"}}
484 do_execsql_test json101-9.2 {
485 SELECT json_quote(3.14159);
486 } {3.14159}
487 do_execsql_test json101-9.3 {
488 SELECT json_quote(12345);
489 } {12345}
490 do_execsql_test json101-9.4 {
491 SELECT json_quote(null);
492 } {"null"}
493 do_catchsql_test json101-9.5 {
494 SELECT json_quote(x'3031323334');
495 } {1 {JSON cannot hold BLOB values}}
496 do_catchsql_test json101-9.6 {
497 SELECT json_quote(123,456)
498 } {1 {wrong number of arguments to function json_quote()}}
499 do_catchsql_test json101-9.7 {
500 SELECT json_quote()
501 } {1 {wrong number of arguments to function json_quote()}}
502
503 # Make sure only valid backslash-escapes are accepted.
504 #
505 do_execsql_test json101-10.1 {
506 SELECT json_valid('" \ "');
507 } {0}
508 do_execsql_test json101-10.2 {
509 SELECT json_valid('" \! "');
510 } {0}
511 do_execsql_test json101-10.3 {
512 SELECT json_valid('" \" "');
513 } {1}
514 do_execsql_test json101-10.4 {
515 SELECT json_valid('" \# "');
516 } {0}
517 do_execsql_test json101-10.5 {
518 SELECT json_valid('" \$ "');
519 } {0}
520 do_execsql_test json101-10.6 {
521 SELECT json_valid('" \% "');
522 } {0}
523 do_execsql_test json101-10.7 {
524 SELECT json_valid('" \& "');
525 } {0}
526 do_execsql_test json101-10.8 {
527 SELECT json_valid('" \'' "');
528 } {0}
529 do_execsql_test json101-10.9 {
530 SELECT json_valid('" \( "');
531 } {0}
532 do_execsql_test json101-10.10 {
533 SELECT json_valid('" \) "');
534 } {0}
535 do_execsql_test json101-10.11 {
536 SELECT json_valid('" \* "');
537 } {0}
538 do_execsql_test json101-10.12 {
539 SELECT json_valid('" \+ "');
540 } {0}
541 do_execsql_test json101-10.13 {
542 SELECT json_valid('" \, "');
543 } {0}
544 do_execsql_test json101-10.14 {
545 SELECT json_valid('" \- "');
546 } {0}
547 do_execsql_test json101-10.15 {
548 SELECT json_valid('" \. "');
549 } {0}
550 do_execsql_test json101-10.16 {
551 SELECT json_valid('" \/ "');
552 } {1}
553 do_execsql_test json101-10.17 {
554 SELECT json_valid('" \0 "');
555 } {0}
556 do_execsql_test json101-10.18 {
557 SELECT json_valid('" \1 "');
558 } {0}
559 do_execsql_test json101-10.19 {
560 SELECT json_valid('" \2 "');
561 } {0}
562 do_execsql_test json101-10.20 {
563 SELECT json_valid('" \3 "');
564 } {0}
565 do_execsql_test json101-10.21 {
566 SELECT json_valid('" \4 "');
567 } {0}
568 do_execsql_test json101-10.22 {
569 SELECT json_valid('" \5 "');
570 } {0}
571 do_execsql_test json101-10.23 {
572 SELECT json_valid('" \6 "');
573 } {0}
574 do_execsql_test json101-10.24 {
575 SELECT json_valid('" \7 "');
576 } {0}
577 do_execsql_test json101-10.25 {
578 SELECT json_valid('" \8 "');
579 } {0}
580 do_execsql_test json101-10.26 {
581 SELECT json_valid('" \9 "');
582 } {0}
583 do_execsql_test json101-10.27 {
584 SELECT json_valid('" \: "');
585 } {0}
586 do_execsql_test json101-10.28 {
587 SELECT json_valid('" \; "');
588 } {0}
589 do_execsql_test json101-10.29 {
590 SELECT json_valid('" \< "');
591 } {0}
592 do_execsql_test json101-10.30 {
593 SELECT json_valid('" \= "');
594 } {0}
595 do_execsql_test json101-10.31 {
596 SELECT json_valid('" \> "');
597 } {0}
598 do_execsql_test json101-10.32 {
599 SELECT json_valid('" \? "');
600 } {0}
601 do_execsql_test json101-10.33 {
602 SELECT json_valid('" \@ "');
603 } {0}
604 do_execsql_test json101-10.34 {
605 SELECT json_valid('" \A "');
606 } {0}
607 do_execsql_test json101-10.35 {
608 SELECT json_valid('" \B "');
609 } {0}
610 do_execsql_test json101-10.36 {
611 SELECT json_valid('" \C "');
612 } {0}
613 do_execsql_test json101-10.37 {
614 SELECT json_valid('" \D "');
615 } {0}
616 do_execsql_test json101-10.38 {
617 SELECT json_valid('" \E "');
618 } {0}
619 do_execsql_test json101-10.39 {
620 SELECT json_valid('" \F "');
621 } {0}
622 do_execsql_test json101-10.40 {
623 SELECT json_valid('" \G "');
624 } {0}
625 do_execsql_test json101-10.41 {
626 SELECT json_valid('" \H "');
627 } {0}
628 do_execsql_test json101-10.42 {
629 SELECT json_valid('" \I "');
630 } {0}
631 do_execsql_test json101-10.43 {
632 SELECT json_valid('" \J "');
633 } {0}
634 do_execsql_test json101-10.44 {
635 SELECT json_valid('" \K "');
636 } {0}
637 do_execsql_test json101-10.45 {
638 SELECT json_valid('" \L "');
639 } {0}
640 do_execsql_test json101-10.46 {
641 SELECT json_valid('" \M "');
642 } {0}
643 do_execsql_test json101-10.47 {
644 SELECT json_valid('" \N "');
645 } {0}
646 do_execsql_test json101-10.48 {
647 SELECT json_valid('" \O "');
648 } {0}
649 do_execsql_test json101-10.49 {
650 SELECT json_valid('" \P "');
651 } {0}
652 do_execsql_test json101-10.50 {
653 SELECT json_valid('" \Q "');
654 } {0}
655 do_execsql_test json101-10.51 {
656 SELECT json_valid('" \R "');
657 } {0}
658 do_execsql_test json101-10.52 {
659 SELECT json_valid('" \S "');
660 } {0}
661 do_execsql_test json101-10.53 {
662 SELECT json_valid('" \T "');
663 } {0}
664 do_execsql_test json101-10.54 {
665 SELECT json_valid('" \U "');
666 } {0}
667 do_execsql_test json101-10.55 {
668 SELECT json_valid('" \V "');
669 } {0}
670 do_execsql_test json101-10.56 {
671 SELECT json_valid('" \W "');
672 } {0}
673 do_execsql_test json101-10.57 {
674 SELECT json_valid('" \X "');
675 } {0}
676 do_execsql_test json101-10.58 {
677 SELECT json_valid('" \Y "');
678 } {0}
679 do_execsql_test json101-10.59 {
680 SELECT json_valid('" \Z "');
681 } {0}
682 do_execsql_test json101-10.60 {
683 SELECT json_valid('" \[ "');
684 } {0}
685 do_execsql_test json101-10.61 {
686 SELECT json_valid('" \\ "');
687 } {1}
688 do_execsql_test json101-10.62 {
689 SELECT json_valid('" \] "');
690 } {0}
691 do_execsql_test json101-10.63 {
692 SELECT json_valid('" \^ "');
693 } {0}
694 do_execsql_test json101-10.64 {
695 SELECT json_valid('" \_ "');
696 } {0}
697 do_execsql_test json101-10.65 {
698 SELECT json_valid('" \` "');
699 } {0}
700 do_execsql_test json101-10.66 {
701 SELECT json_valid('" \a "');
702 } {0}
703 do_execsql_test json101-10.67 {
704 SELECT json_valid('" \b "');
705 } {1}
706 do_execsql_test json101-10.68 {
707 SELECT json_valid('" \c "');
708 } {0}
709 do_execsql_test json101-10.69 {
710 SELECT json_valid('" \d "');
711 } {0}
712 do_execsql_test json101-10.70 {
713 SELECT json_valid('" \e "');
714 } {0}
715 do_execsql_test json101-10.71 {
716 SELECT json_valid('" \f "');
717 } {1}
718 do_execsql_test json101-10.72 {
719 SELECT json_valid('" \g "');
720 } {0}
721 do_execsql_test json101-10.73 {
722 SELECT json_valid('" \h "');
723 } {0}
724 do_execsql_test json101-10.74 {
725 SELECT json_valid('" \i "');
726 } {0}
727 do_execsql_test json101-10.75 {
728 SELECT json_valid('" \j "');
729 } {0}
730 do_execsql_test json101-10.76 {
731 SELECT json_valid('" \k "');
732 } {0}
733 do_execsql_test json101-10.77 {
734 SELECT json_valid('" \l "');
735 } {0}
736 do_execsql_test json101-10.78 {
737 SELECT json_valid('" \m "');
738 } {0}
739 do_execsql_test json101-10.79 {
740 SELECT json_valid('" \n "');
741 } {1}
742 do_execsql_test json101-10.80 {
743 SELECT json_valid('" \o "');
744 } {0}
745 do_execsql_test json101-10.81 {
746 SELECT json_valid('" \p "');
747 } {0}
748 do_execsql_test json101-10.82 {
749 SELECT json_valid('" \q "');
750 } {0}
751 do_execsql_test json101-10.83 {
752 SELECT json_valid('" \r "');
753 } {1}
754 do_execsql_test json101-10.84 {
755 SELECT json_valid('" \s "');
756 } {0}
757 do_execsql_test json101-10.85 {
758 SELECT json_valid('" \t "');
759 } {1}
760 do_execsql_test json101-10.86.0 {
761 SELECT json_valid('" \u "');
762 } {0}
763 do_execsql_test json101-10.86.1 {
764 SELECT json_valid('" \ua "');
765 } {0}
766 do_execsql_test json101-10.86.2 {
767 SELECT json_valid('" \uab "');
768 } {0}
769 do_execsql_test json101-10.86.3 {
770 SELECT json_valid('" \uabc "');
771 } {0}
772 do_execsql_test json101-10.86.4 {
773 SELECT json_valid('" \uabcd "');
774 } {1}
775 do_execsql_test json101-10.86.5 {
776 SELECT json_valid('" \uFEDC "');
777 } {1}
778 do_execsql_test json101-10.86.6 {
779 SELECT json_valid('" \u1234 "');
780 } {1}
781 do_execsql_test json101-10.87 {
782 SELECT json_valid('" \v "');
783 } {0}
784 do_execsql_test json101-10.88 {
785 SELECT json_valid('" \w "');
786 } {0}
787 do_execsql_test json101-10.89 {
788 SELECT json_valid('" \x "');
789 } {0}
790 do_execsql_test json101-10.90 {
791 SELECT json_valid('" \y "');
792 } {0}
793 do_execsql_test json101-10.91 {
794 SELECT json_valid('" \z "');
795 } {0}
796 do_execsql_test json101-10.92 {
797 SELECT json_valid('" \{ "');
798 } {0}
799 do_execsql_test json101-10.93 {
800 SELECT json_valid('" \| "');
801 } {0}
802 do_execsql_test json101-10.94 {
803 SELECT json_valid('" \} "');
804 } {0}
805 do_execsql_test json101-10.95 {
806 SELECT json_valid('" \~ "');
807 } {0}
808
809 #--------------------------------------------------------------------------
810 # 2017-04-11. https://sqlite.org/src/info/981329adeef51011
811 # Stack overflow on deeply nested JSON.
812 #
813 # The following tests confirm that deeply nested JSON is considered invalid.
814 #
815 do_execsql_test json101-11.0 {
816 /* Shallow enough to be parsed */
817 SELECT json_valid(printf('%.1000c0%.1000c','[',']'));
818 } {1}
819 do_execsql_test json101-11.1 {
820 /* Too deep by one */
821 SELECT json_valid(printf('%.1001c0%.1001c','[',']'));
822 } {0}
823 do_execsql_test json101-11.2 {
824 /* Shallow enough to be parsed { */
825 SELECT json_valid(replace(printf('%.1000c0%.1000c','[','}'),'[','{"a":'));
826 /* } */
827 } {1}
828 do_execsql_test json101-11.3 {
829 /* Too deep by one { */
830 SELECT json_valid(replace(printf('%.1001c0%.1001c','[','}'),'[','{"a":'));
831 /* } */
832 } {0}
833
834 # 2017-10-27. Demonstrate the ability to access an element from
835 # a json structure even though the element name constains a "."
836 # character, by quoting the element name in the path.
837 #
838 do_execsql_test json101-12.100 {
839 CREATE TABLE t12(x);
840 INSERT INTO t12(x) VALUES(
841 '{"settings":
842 {"layer2":
843 {"hapax.legomenon":
844 {"forceDisplay":true,
845 "transliterate":true,
846 "add.footnote":true,
847 "summary.report":true},
848 "dis.legomenon":
849 {"forceDisplay":true,
850 "transliterate":false,
851 "add.footnote":false,
852 "summary.report":true},
853 "tris.legomenon":
854 {"forceDisplay":true,
855 "transliterate":false,
856 "add.footnote":false,
857 "summary.report":false}
858 }
859 }
860 }');
861 } {}
862
863 do_execsql_test json101-12.110 {
864 SELECT json_remove(x, '$.settings.layer2."dis.legomenon".forceDisplay')
865 FROM t12;
866 } {{{"settings":{"layer2":{"hapax.legomenon":{"forceDisplay":true,"transliterate":true,"add.footnote":true,"summary.report":true},"dis.legomenon":{"transliterate":false,"add.footnote":false,"summary.report":true},"tris.legomenon":{"forceDisplay":true,"transliterate":false,"add.footnote":false,"summary.report":false}}}}}}
867 do_execsql_test json101-12.110b {
868 SELECT json_remove(jsonb(x), '$.settings.layer2."dis.legomenon".forceDisplay')
869 FROM t12;
870 } {{{"settings":{"layer2":{"hapax.legomenon":{"forceDisplay":true,"transliterate":true,"add.footnote":true,"summary.report":true},"dis.legomenon":{"transliterate":false,"add.footnote":false,"summary.report":true},"tris.legomenon":{"forceDisplay":true,"transliterate":false,"add.footnote":false,"summary.report":false}}}}}}
871 do_execsql_test json101-12.120 {
872 SELECT json_extract(x, '$.settings.layer2."tris.legomenon"."summary.report"')
873 FROM t12;
874 } {0}
875 do_execsql_test json101-12.120b {
876 SELECT json_extract(jsonb(x), '$.settings.layer2."tris.legomenon"."summary.report"')
877 FROM t12;
878 } {0}
879
880 # 2018-01-26
881 # ticket https://sqlite.org/src/tktview/80177f0c226ff54f6ddd41
882 # Make sure the query planner knows about the arguments to table-valued functions.
883 #
884 do_execsql_test json101-13.100 {
885 DROP TABLE IF EXISTS t1;
886 DROP TABLE IF EXISTS t2;
887 CREATE TABLE t1(id, json);
888 INSERT INTO t1(id,json) VALUES(1,'{"items":[3,5]}');
889 CREATE TABLE t2(id, json);
890 INSERT INTO t2(id,json) VALUES(2,'{"value":2}');
891 INSERT INTO t2(id,json) VALUES(3,'{"value":3}');
892 INSERT INTO t2(id,json) VALUES(4,'{"value":4}');
893 INSERT INTO t2(id,json) VALUES(5,'{"value":5}');
894 INSERT INTO t2(id,json) VALUES(6,'{"value":6}');
895 SELECT *, 'NL' FROM t1 CROSS JOIN t2
896 WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z
897 WHERE Z.value==t2.id);
898 } {1 {{"items":[3,5]}} 3 {{"value":3}} NL 1 {{"items":[3,5]}} 5 {{"value":5}} NL}
899 do_execsql_test json101-13.110 {
900 SELECT *, 'NL' FROM t2 CROSS JOIN t1
901 WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z
902 WHERE Z.value==t2.id);
903 } {3 {{"value":3}} 1 {{"items":[3,5]}} NL 5 {{"value":5}} 1 {{"items":[3,5]}} NL}
904
905 # 2018-05-16
906 # Incorrect fullkey output from json_each()
907 # when the input JSON is not an array or object.
908 #
909 do_execsql_test json101-14.100 {
910 SELECT fullkey FROM json_each('123');
911 } {$}
912 do_execsql_test json101-14.110 {
913 SELECT fullkey FROM json_each('123.56');
914 } {$}
915 do_execsql_test json101-14.120 {
916 SELECT fullkey FROM json_each('"hello"');
917 } {$}
918 do_execsql_test json101-14.130 {
919 SELECT fullkey FROM json_each('null');
920 } {$}
921 do_execsql_test json101-14.140 {
922 SELECT fullkey FROM json_tree('123');
923 } {$}
924 do_execsql_test json101-14.150 {
925 SELECT fullkey FROM json_tree('123.56');
926 } {$}
927 do_execsql_test json101-14.160 {
928 SELECT fullkey FROM json_tree('"hello"');
929 } {$}
930 do_execsql_test json101-14.170 {
931 SELECT fullkey FROM json_tree('null');
932 } {$}
933
934 # 2018-12-03
935 # Make sure the table-valued functions contained within parentheses
936 # work correctly.
937 #
938 # Bug reported via private email. See TH3 for more information.
939 #
940 do_execsql_test json101-15.100 {
941 SELECT * FROM JSON_EACH('{"a":1, "b":2}');
942 } {a 1 integer 1 1 {} {$.a} {$} b 2 integer 2 5 {} {$.b} {$}}
943 do_execsql_test json101-15.110 {
944 SELECT xyz.* FROM JSON_EACH('{"a":1, "b":2}') AS xyz;
945 } {a 1 integer 1 1 {} {$.a} {$} b 2 integer 2 5 {} {$.b} {$}}
946 do_execsql_test json101-15.120 {
947 SELECT * FROM (JSON_EACH('{"a":1, "b":2}'));
948 } {a 1 integer 1 1 {} {$.a} {$} b 2 integer 2 5 {} {$.b} {$}}
949 do_execsql_test json101-15.130 {
950 SELECT xyz.* FROM (JSON_EACH('{"a":1, "b":2}')) AS xyz;
951 } {a 1 integer 1 1 {} {$.a} {$} b 2 integer 2 5 {} {$.b} {$}}
952
953 # 2019-11-10
954 # Mailing list bug report on the handling of surrogate pairs
955 # in JSON.
956 #
957 do_execsql_test json101-16.10 {
958 SELECT length(json_extract('"abc\uD834\uDD1Exyz"','$'));
959 } {7}
960 do_execsql_test json101-16.20 {
961 SELECT length(json_extract('"\uD834\uDD1E"','$'));
962 } {1}
963 do_execsql_test json101-16.30 {
964 SELECT unicode(json_extract('"\uD834\uDD1E"','$'));
965 } {119070}
966
967 # 2022-01-30 dbsqlfuzz 4678cf825d27f87c9b8343720121e12cf944b71a
968 do_execsql_test json101-17.1 {
969 DROP TABLE IF EXISTS t1;
970 DROP TABLE IF EXISTS t2;
971 CREATE TABLE t1(a,b,c);
972 CREATE TABLE t2(d);
973 SELECT * FROM t1 LEFT JOIN t2 ON (SELECT b FROM json_each ORDER BY 1);
974 } {}
975
976 # 2022-04-04 forum post https://sqlite.org/forum/forumpost/c082aeab43
977 do_execsql_test json101-18.1 {
978 SELECT json_valid('{"":5}');
979 } {1}
980 do_execsql_test json101-18.2 {
981 SELECT json_extract('{"":5}', '$.""');
982 } {5}
983 do_execsql_test json101-18.3 {
984 SELECT json_extract('[3,{"a":4,"":[5,{"hi":6},7]},8]', '$[1].""[1].hi');
985 } {6}
986 do_execsql_test json101-18.4 {
987 SELECT json_extract('[3,{"a":4,"":[5,{"hi":6},7]},8]', '$[1].""[1]."hi"');
988 } {6}
989 do_catchsql_test json101-18.5 {
990 SELECT json_extract('{"":8}', '$.');
991 } {1 {bad JSON path: '$.'}}
992
993 # 2022-08-29 https://sqlite.org/forum/forumpost/9b9e4716c0d7bbd1
994 # This is not a problem specifically with JSON functions. It is
995 # a problem with transaction control. But the json() function makes
996 # the problem more easily accessible, so it is tested here.
997 #
998 do_execsql_test json101-19.1 {
999 DROP TABLE IF EXISTS t1;
1000 CREATE TABLE t1(x);
1001 } {}
1002 do_catchsql_test json101-19.2 {
1003 BEGIN;
1004 INSERT INTO t1 VALUES(0), (json('not-valid-json'));
1005 } {1 {malformed JSON}}
1006 do_execsql_test json101-19.3 {
1007 COMMIT;
1008 SELECT * FROM t1;
1009 } {}
1010
1011 # 2023-03-17 positive and negative infinities
1012 #
1013 do_execsql_test json101-20.1 {
1014 SELECT json_object('a',2e370,'b',-3e380);
1015 } {{{"a":9.0e+999,"b":-9.0e+999}}}
1016 do_execsql_test json101-20.2 {
1017 SELECT json_object('a',2e370,'b',-3e380)->>'a';
1018 } Inf
1019 do_execsql_test json101-20.3 {
1020 SELECT json_object('a',2e370,'b',-3e380)->>'b';
1021 } {-Inf}
1022
1023 # 2023-05-02 https://sqlite.org/forum/forumpost/06c6334412
1024 # JSON functions should normally return NULL when given
1025 # a NULL value as the JSON input.
1026 #
1027 db null NULL
1028 if {[db exists {SELECT * FROM pragma_compile_options WHERE compile_options LIKE '%legacy_json_valid%'}]} {
1029 do_execsql_test json101-21.1-legacy {
1030 SELECT json_valid(NULL);
1031 } 0
1032 } else {
1033 do_execsql_test json101-21.1-correct {
1034 SELECT json_valid(NULL);
1035 } NULL
1036 }
1037 do_execsql_test json101-21.2 {
1038 SELECT json_error_position(NULL);
1039 } NULL
1040 do_execsql_test json101-21.3 {
1041 SELECT json(NULL);
1042 } NULL
1043 do_execsql_test json101-21.4 {
1044 SELECT json_array(NULL);
1045 } {[null]}
1046 do_execsql_test json101-21.5 {
1047 SELECT json_extract(NULL);
1048 } NULL
1049 do_execsql_test json101-21.6 {
1050 SELECT json_insert(NULL,'$',123);
1051 } NULL
1052 do_execsql_test json101-21.7 {
1053 SELECT NULL->0;
1054 } NULL
1055 do_execsql_test json101-21.8 {
1056 SELECT NULL->>0;
1057 } NULL
1058 do_execsql_test json101-21.9 {
1059 SELECT '{a:5}'->NULL;
1060 } NULL
1061 do_execsql_test json101-21.10 {
1062 SELECT '{a:5}'->>NULL;
1063 } NULL
1064 do_catchsql_test json101-21.11 {
1065 SELECT json_object(NULL,5);
1066 } {1 {json_object() labels must be TEXT}}
1067 do_execsql_test json101-21.12 {
1068 SELECT json_patch(NULL,'{a:5}');
1069 } NULL
1070 do_execsql_test json101-21.13 {
1071 SELECT json_patch('{a:5}',NULL);
1072 } NULL
1073 do_execsql_test json101-21.14 {
1074 SELECT json_patch(NULL,NULL);
1075 } NULL
1076 do_execsql_test json101-21.15 {
1077 SELECT json_remove(NULL,'$');
1078 } NULL
1079 do_execsql_test json101-21.16 {
1080 SELECT json_remove('{a:5,b:7}',NULL);
1081 } NULL
1082 do_execsql_test json101-21.17 {
1083 SELECT json_replace(NULL,'$.a',123);
1084 } NULL
1085 do_execsql_test json101-21.18 {
1086 SELECT json_replace('{a:5,b:7}',NULL,NULL);
1087 } {{{"a":5,"b":7}}}
1088 do_execsql_test json101-21.19 {
1089 SELECT json_set(NULL,'$.a',123);
1090 } NULL
1091 do_execsql_test json101-21.20 {
1092 SELECT json_set('{a:5,b:7}',NULL,NULL);
1093 } {{{"a":5,"b":7}}}
1094 do_execsql_test json101-21.21 {
1095 SELECT json_type(NULL);
1096 } NULL
1097 do_execsql_test json101-21.22 {
1098 SELECT json_type('{a:5,b:7}',NULL);
1099 } NULL
1100 do_execsql_test json101-21.23 {
1101 SELECT json_quote(NULL);
1102 } null
1103 do_execsql_test json101-21.24 {
1104 SELECT count(*) FROM json_each(NULL);
1105 } 0
1106 do_execsql_test json101-21.25 {
1107 SELECT count(*) FROM json_tree(NULL);
1108 } 0
1109 do_execsql_test json101-21.26 {
1110 WITH c(x) AS (VALUES(1),(2.0),(NULL),('three'))
1111 SELECT json_group_array(x) FROM c;
1112 } {[1,2.0,null,"three"]}
1113 do_execsql_test json101-21.27 {
1114 WITH c(x,y) AS (VALUES('a',1),('b',2.0),('c',NULL),(NULL,'three'),('e','four'))
1115 SELECT json_group_object(x,y) FROM c;
1116 } {{{"a":1,"b":2.0,"c":null,"e":"four"}}}
1117
1118 # 2023-10-09 https://sqlite.org/forum/forumpost/b25edc1d46
1119 # UAF due to JSON cache overflow
1120 #
1121 do_execsql_test json101-22.1 {
1122 SELECT json_set(
1123 '{}',
1124 '$.a', json('1'),
1125 '$.a', json('2'),
1126 '$.b', json('3'),
1127 '$.b', json('4'),
1128 '$.c', json('5'),
1129 '$.c', json('6')
1130 );
1131 } {{{"a":2,"b":4,"c":6}}}
1132 do_execsql_test json101-22.2 {
1133 SELECT json_replace(
1134 '{"a":7,"b":8,"c":9}',
1135 '$.a', json('1'),
1136 '$.a', json('2'),
1137 '$.b', json('3'),
1138 '$.b', json('4'),
1139 '$.c', json('5'),
1140 '$.c', json('6')
1141 );
1142 } {{{"a":2,"b":4,"c":6}}}
1143
1144 # 2023-10-17 https://sqlite.org/forum/forumpost/fc0e3f1e2a
1145 # Incorrect accesss to '$[0]' in parsed + edited JSON.
1146 #
1147 do_execsql_test json101-23.1 {
1148 SELECT j, j->>0, j->>1
1149 FROM (SELECT json_set(json_set('[]','$[#]',0), '$[#]',1) AS j);
1150 } {{[0,1]} 0 1}
1151 do_execsql_test json101-23.2 {
1152 SELECT j, j->>0, j->>1
1153 FROM (SELECT json_set('[]','$[#]',0,'$[#]',1) AS j);
1154 } {{[0,1]} 0 1}
1155
1156 # Insert/Set/Replace where the path specifies substructure that
1157 # does not yet exist
1158 #
1159 proc tx x {return [string map [list ( \173 ) \175 ' \042 < \133 > \135] $x]}
1160 foreach {id start path ins set repl} {
1161 1 {{}} {$.a.b.c} ('a':('b':('c':9))) ('a':('b':('c':9))) ()
1162 2 {{a:4}} {$.a.b.c} ('a':4) ('a':4) ('a':4)
1163 3 {{a:{}}} {$.a.b.c} ('a':('b':('c':9))) ('a':('b':('c':9))) ('a':())
1164 4 {[0,1,2]} {$[3].a[0].b} <0,1,2,('a':<('b':9)>)> <0,1,2,('a':<('b':9)>)> <0,1,2>
1165 5 {[0,1,2]} {$[1].a[0].b} <0,1,2> <0,1,2> <0,1,2>
1166 6 {[0,{},2]} {$[1].a[0].b} <0,('a':<('b':9)>),2> <0,('a':<('b':9)>),2> <0,(),2>
1167 7 {[0,1,2]} {$[3][0].b} <0,1,2,<('b':9)>> <0,1,2,<('b':9)>> <0,1,2>
1168 8 {[0,1,2]} {$[1][0].b} <0,1,2> <0,1,2> <0,1,2>
1169 } {
1170 do_execsql_test json101-24.$id.insert {
1171 SELECT json_insert($start,$path,9);
1172 } [list [tx $ins]]
1173 do_execsql_test json101-24.$id.set {
1174 SELECT json_set($start,$path,9);
1175 } [list [tx $set]]
1176 do_execsql_test json101-24.$id.replace {
1177 SELECT json_replace($start,$path,9);
1178 } [list [tx $repl]]
1179 }
1180
1181 # 2026-05-29
1182 # JSON floating point precision
1183 #
1184 do_execsql_test json101-25.1 {
1185 SELECT json_array(0.1234567890123456789)->>0 = 0.1234567890123456789;
1186 } 1
1187
1188 finish_test