AryaWu/sqlite
0
1# 2015-08-122#3# The author disclaims copyright to this source code. In place of4# 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 the12# SQLite library.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17 18do_execsql_test json101-1.1.00 {19 SELECT json_array(1,2.5,null,'hello');20} {[1,2.5,null,"hello"]}21do_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]}25do_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 JSON28} {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]}29do_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 JSON32} {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]}33do_execsql_test json101-1.2 {34 SELECT hex(json_array('String "\ Test'));35} {5B22537472696E67205C225C5C2054657374225D}36do_catchsql_test json101-1.3 {37 SELECT json_array(1,printf('%.1000c','x'),x'abcd',3);38} {1 {JSON cannot hold BLOB values}}39do_catchsql_test json101-1.3b {40 SELECT jsonb_array(1,printf('%.1000c','x'),x'abcd',3);41} {1 {JSON cannot hold BLOB values}}42do_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]}53do_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 65do_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"}}}68do_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"}}}71do_catchsql_test json101-2.2 {72 SELECT json_object('a',printf('%.1000c','x'),2,2.5);73} {1 {json_object() labels must be TEXT}}74do_catchsql_test json101-2.2b {75 SELECT jsonb_object('a',printf('%.1000c','x'),2,2.5);76} {1 {json_object() labels must be TEXT}}77do_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}}}80do_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}}}83do_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}}}86do_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}}}89do_catchsql_test json101-2.3 {90 SELECT json_object('a',1,'b');91} {1 {json_object() requires an even number of arguments}}92do_catchsql_test json101-2.4 {93 SELECT json_object('a',printf('%.1000c','x'),'b',x'abcd');94} {1 {JSON cannot hold BLOB values}}95do_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 99do_execsql_test json101-3.1 {100 SELECT json_replace('{"a":1,"b":2}','$.a','[3,4,5]');101} {{{"a":"[3,4,5]","b":2}}}102do_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}}}105do_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}}}108do_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}}}111do_execsql_test json101-3.3 {112 SELECT json_type(json_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b');113} {text}114do_execsql_test json101-3.3b {115 SELECT json_type(jsonb_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b');116} {text}117do_execsql_test json101-3.4 {118 SELECT json_type(json_set('{"a":1,"b":2}','$.b',json('{"x":3,"y":4}')),'$.b');119} {object}120do_execsql_test json101-3.4b {121 SELECT json_type(jsonb_set('{"a":1,"b":2}','$.b',jsonb('{"x":3,"y":4}')),'$.b');122} {object}123ifcapable 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 whitespace133# is permitting before and/or after that value.134#135do_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} {}144do_execsql_test json101-4.2 {145 SELECT * FROM j1 WHERE NOT json_valid(char(0x20,0x09,0x0a,0x0d)||x);146} {}147do_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#153do_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 their158# input unchanged.159#160do_execsql_test json101-4.5 {161 SELECT x FROM j1 WHERE json_remove(x)<>x;162} {}163do_execsql_test json101-4.6 {164 SELECT x FROM j1 WHERE json_replace(x)<>x;165} {}166do_execsql_test json101-4.7 {167 SELECT x FROM j1 WHERE json_set(x)<>x;168} {}169do_execsql_test json101-4.8 {170 SELECT x FROM j1 WHERE json_insert(x)<>x;171} {}172do_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#178do_execsql_test json101-4.10 {179 SELECT count(*) FROM j1 WHERE json_type(x) IN ('object','array');180 SELECT x FROM j1181 WHERE json_extract(x,'$')<>x182 AND json_type(x) IN ('object','array');183} {4}184do_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 j1b188 WHERE json_extract(x,'$')<>json(x)189 AND json_type(x) IN ('object','array');190} {4}191 192do_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": null218 }','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 323do_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 |}326do_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 330ifcapable !vtab {331 finish_test332 return333}334 335# fullkey is always the same as path+key (with appropriate formatting)336#337do_execsql_test json101-5.3 {338 SELECT j2.rowid, jx.rowid, fullkey, path, key339 FROM j2, json_tree(j2.json) AS jx340 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']'341 ELSE '.'||key END);342} {}343do_execsql_test json101-5.3b {344 SELECT j2b.rowid, jx.rowid, fullkey, path, key345 FROM j2b, json_tree(j2b.json) AS jx346 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']'347 ELSE '.'||key END);348} {}349do_execsql_test json101-5.4 {350 SELECT j2.rowid, jx.rowid, fullkey, path, key351 FROM j2, json_each(j2.json) AS jx352 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 the358# same as input.359#360do_execsql_test json101-5.5 {361 SELECT j2.rowid, jx.rowid, fullkey, path, key362 FROM j2, json_each(j2.json) AS jx363 WHERE jx.json<>j2.json;364} {}365do_execsql_test json101-5.6 {366 SELECT j2.rowid, jx.rowid, fullkey, path, key367 FROM j2, json_tree(j2.json) AS jx368 WHERE jx.json<>j2.json;369} {}370do_execsql_test json101-5.7 {371 SELECT j2.rowid, jx.rowid, fullkey, path, key372 FROM j2, json_each(j2.json) AS jx373 WHERE jx.value<>jx.atom AND type NOT IN ('array','object');374} {}375do_execsql_test json101-5.8 {376 SELECT j2.rowid, jx.rowid, fullkey, path, key377 FROM j2, json_tree(j2.json) AS jx378 WHERE jx.value<>jx.atom AND type NOT IN ('array','object');379} {}380 381# 2024-02-16 https://sqlite.org/forum/forumpost/ecb94cd210382# Regression in json_tree()/json_each(). The value column383# should have the "J" subtype if the value is an array or384# object.385#386do_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 fix390 391do_execsql_test json101-5.11 {392 SELECT json_insert('{}','$.a',value) FROM json_tree('"[1,2,3]"');393} {{{"a":"[1,2,3]"}}}394 395do_execsql_test json101-6.1 {396 SELECT json_valid('{"a":55,"b":72,}');397} {0}398do_execsql_test json101-6.2 {399 SELECT json_error_position('{"a":55,"b":72,}');400} {0}401do_execsql_test json101-6.3 {402 SELECT json_valid(json('{"a":55,"b":72,}'));403} {1}404do_execsql_test json101-6.4 {405 SELECT json_valid('{"a":55,"b":72 , }');406} {0}407do_execsql_test json101-6.5 {408 SELECT json_error_position('{"a":55,"b":72 , }');409} {0}410do_execsql_test json101-6.6 {411 SELECT json_error_position('{"a":55,"b":72,,}');412} {16}413do_execsql_test json101-6.7 {414 SELECT json_valid('{"a":55,"b":72}');415} {1}416do_execsql_test json101-6.8 {417 SELECT json_error_position('["a",55,"b",72,]');418} {0}419do_execsql_test json101-6.9 {420 SELECT json_error_position('["a",55,"b",72 , ]');421} {0}422do_execsql_test json101-6.10 {423 SELECT json_error_position('["a",55,"b",72,,]');424} {16}425do_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# 432foreach {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 $isvalid445}446 447# Ticket https://sqlite.org/src/info/ad2559db380abf8e448# Control characters must be escaped in JSON strings.449#450do_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"]}}457do_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"]}}464do_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 Ade469#470do_execsql_test json101-8.3 {471 SELECT json_valid(char(0x22,0xe4,0x22));472} {1}473do_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 values479# are rendered as the unquoted string "null".480#481do_execsql_test json101-9.1 {482 SELECT json_quote('abc"xyz');483} {{"abc\"xyz"}}484do_execsql_test json101-9.2 {485 SELECT json_quote(3.14159);486} {3.14159}487do_execsql_test json101-9.3 {488 SELECT json_quote(12345);489} {12345}490do_execsql_test json101-9.4 {491 SELECT json_quote(null);492} {"null"}493do_catchsql_test json101-9.5 {494 SELECT json_quote(x'3031323334');495} {1 {JSON cannot hold BLOB values}}496do_catchsql_test json101-9.6 {497 SELECT json_quote(123,456)498} {1 {wrong number of arguments to function json_quote()}}499do_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#505do_execsql_test json101-10.1 {506 SELECT json_valid('" \ "');507} {0}508do_execsql_test json101-10.2 {509 SELECT json_valid('" \! "');510} {0}511do_execsql_test json101-10.3 {512 SELECT json_valid('" \" "');513} {1}514do_execsql_test json101-10.4 {515 SELECT json_valid('" \# "');516} {0}517do_execsql_test json101-10.5 {518 SELECT json_valid('" \$ "');519} {0}520do_execsql_test json101-10.6 {521 SELECT json_valid('" \% "');522} {0}523do_execsql_test json101-10.7 {524 SELECT json_valid('" \& "');525} {0}526do_execsql_test json101-10.8 {527 SELECT json_valid('" \'' "');528} {0}529do_execsql_test json101-10.9 {530 SELECT json_valid('" \( "');531} {0}532do_execsql_test json101-10.10 {533 SELECT json_valid('" \) "');534} {0}535do_execsql_test json101-10.11 {536 SELECT json_valid('" \* "');537} {0}538do_execsql_test json101-10.12 {539 SELECT json_valid('" \+ "');540} {0}541do_execsql_test json101-10.13 {542 SELECT json_valid('" \, "');543} {0}544do_execsql_test json101-10.14 {545 SELECT json_valid('" \- "');546} {0}547do_execsql_test json101-10.15 {548 SELECT json_valid('" \. "');549} {0}550do_execsql_test json101-10.16 {551 SELECT json_valid('" \/ "');552} {1}553do_execsql_test json101-10.17 {554 SELECT json_valid('" \0 "');555} {0}556do_execsql_test json101-10.18 {557 SELECT json_valid('" \1 "');558} {0}559do_execsql_test json101-10.19 {560 SELECT json_valid('" \2 "');561} {0}562do_execsql_test json101-10.20 {563 SELECT json_valid('" \3 "');564} {0}565do_execsql_test json101-10.21 {566 SELECT json_valid('" \4 "');567} {0}568do_execsql_test json101-10.22 {569 SELECT json_valid('" \5 "');570} {0}571do_execsql_test json101-10.23 {572 SELECT json_valid('" \6 "');573} {0}574do_execsql_test json101-10.24 {575 SELECT json_valid('" \7 "');576} {0}577do_execsql_test json101-10.25 {578 SELECT json_valid('" \8 "');579} {0}580do_execsql_test json101-10.26 {581 SELECT json_valid('" \9 "');582} {0}583do_execsql_test json101-10.27 {584 SELECT json_valid('" \: "');585} {0}586do_execsql_test json101-10.28 {587 SELECT json_valid('" \; "');588} {0}589do_execsql_test json101-10.29 {590 SELECT json_valid('" \< "');591} {0}592do_execsql_test json101-10.30 {593 SELECT json_valid('" \= "');594} {0}595do_execsql_test json101-10.31 {596 SELECT json_valid('" \> "');597} {0}598do_execsql_test json101-10.32 {599 SELECT json_valid('" \? "');600} {0}601do_execsql_test json101-10.33 {602 SELECT json_valid('" \@ "');603} {0}604do_execsql_test json101-10.34 {605 SELECT json_valid('" \A "');606} {0}607do_execsql_test json101-10.35 {608 SELECT json_valid('" \B "');609} {0}610do_execsql_test json101-10.36 {611 SELECT json_valid('" \C "');612} {0}613do_execsql_test json101-10.37 {614 SELECT json_valid('" \D "');615} {0}616do_execsql_test json101-10.38 {617 SELECT json_valid('" \E "');618} {0}619do_execsql_test json101-10.39 {620 SELECT json_valid('" \F "');621} {0}622do_execsql_test json101-10.40 {623 SELECT json_valid('" \G "');624} {0}625do_execsql_test json101-10.41 {626 SELECT json_valid('" \H "');627} {0}628do_execsql_test json101-10.42 {629 SELECT json_valid('" \I "');630} {0}631do_execsql_test json101-10.43 {632 SELECT json_valid('" \J "');633} {0}634do_execsql_test json101-10.44 {635 SELECT json_valid('" \K "');636} {0}637do_execsql_test json101-10.45 {638 SELECT json_valid('" \L "');639} {0}640do_execsql_test json101-10.46 {641 SELECT json_valid('" \M "');642} {0}643do_execsql_test json101-10.47 {644 SELECT json_valid('" \N "');645} {0}646do_execsql_test json101-10.48 {647 SELECT json_valid('" \O "');648} {0}649do_execsql_test json101-10.49 {650 SELECT json_valid('" \P "');651} {0}652do_execsql_test json101-10.50 {653 SELECT json_valid('" \Q "');654} {0}655do_execsql_test json101-10.51 {656 SELECT json_valid('" \R "');657} {0}658do_execsql_test json101-10.52 {659 SELECT json_valid('" \S "');660} {0}661do_execsql_test json101-10.53 {662 SELECT json_valid('" \T "');663} {0}664do_execsql_test json101-10.54 {665 SELECT json_valid('" \U "');666} {0}667do_execsql_test json101-10.55 {668 SELECT json_valid('" \V "');669} {0}670do_execsql_test json101-10.56 {671 SELECT json_valid('" \W "');672} {0}673do_execsql_test json101-10.57 {674 SELECT json_valid('" \X "');675} {0}676do_execsql_test json101-10.58 {677 SELECT json_valid('" \Y "');678} {0}679do_execsql_test json101-10.59 {680 SELECT json_valid('" \Z "');681} {0}682do_execsql_test json101-10.60 {683 SELECT json_valid('" \[ "');684} {0}685do_execsql_test json101-10.61 {686 SELECT json_valid('" \\ "');687} {1}688do_execsql_test json101-10.62 {689 SELECT json_valid('" \] "');690} {0}691do_execsql_test json101-10.63 {692 SELECT json_valid('" \^ "');693} {0}694do_execsql_test json101-10.64 {695 SELECT json_valid('" \_ "');696} {0}697do_execsql_test json101-10.65 {698 SELECT json_valid('" \` "');699} {0}700do_execsql_test json101-10.66 {701 SELECT json_valid('" \a "');702} {0}703do_execsql_test json101-10.67 {704 SELECT json_valid('" \b "');705} {1}706do_execsql_test json101-10.68 {707 SELECT json_valid('" \c "');708} {0}709do_execsql_test json101-10.69 {710 SELECT json_valid('" \d "');711} {0}712do_execsql_test json101-10.70 {713 SELECT json_valid('" \e "');714} {0}715do_execsql_test json101-10.71 {716 SELECT json_valid('" \f "');717} {1}718do_execsql_test json101-10.72 {719 SELECT json_valid('" \g "');720} {0}721do_execsql_test json101-10.73 {722 SELECT json_valid('" \h "');723} {0}724do_execsql_test json101-10.74 {725 SELECT json_valid('" \i "');726} {0}727do_execsql_test json101-10.75 {728 SELECT json_valid('" \j "');729} {0}730do_execsql_test json101-10.76 {731 SELECT json_valid('" \k "');732} {0}733do_execsql_test json101-10.77 {734 SELECT json_valid('" \l "');735} {0}736do_execsql_test json101-10.78 {737 SELECT json_valid('" \m "');738} {0}739do_execsql_test json101-10.79 {740 SELECT json_valid('" \n "');741} {1}742do_execsql_test json101-10.80 {743 SELECT json_valid('" \o "');744} {0}745do_execsql_test json101-10.81 {746 SELECT json_valid('" \p "');747} {0}748do_execsql_test json101-10.82 {749 SELECT json_valid('" \q "');750} {0}751do_execsql_test json101-10.83 {752 SELECT json_valid('" \r "');753} {1}754do_execsql_test json101-10.84 {755 SELECT json_valid('" \s "');756} {0}757do_execsql_test json101-10.85 {758 SELECT json_valid('" \t "');759} {1}760do_execsql_test json101-10.86.0 {761 SELECT json_valid('" \u "');762} {0}763do_execsql_test json101-10.86.1 {764 SELECT json_valid('" \ua "');765} {0}766do_execsql_test json101-10.86.2 {767 SELECT json_valid('" \uab "');768} {0}769do_execsql_test json101-10.86.3 {770 SELECT json_valid('" \uabc "');771} {0}772do_execsql_test json101-10.86.4 {773 SELECT json_valid('" \uabcd "');774} {1}775do_execsql_test json101-10.86.5 {776 SELECT json_valid('" \uFEDC "');777} {1}778do_execsql_test json101-10.86.6 {779 SELECT json_valid('" \u1234 "');780} {1}781do_execsql_test json101-10.87 {782 SELECT json_valid('" \v "');783} {0}784do_execsql_test json101-10.88 {785 SELECT json_valid('" \w "');786} {0}787do_execsql_test json101-10.89 {788 SELECT json_valid('" \x "');789} {0}790do_execsql_test json101-10.90 {791 SELECT json_valid('" \y "');792} {0}793do_execsql_test json101-10.91 {794 SELECT json_valid('" \z "');795} {0}796do_execsql_test json101-10.92 {797 SELECT json_valid('" \{ "');798} {0}799do_execsql_test json101-10.93 {800 SELECT json_valid('" \| "');801} {0}802do_execsql_test json101-10.94 {803 SELECT json_valid('" \} "');804} {0}805do_execsql_test json101-10.95 {806 SELECT json_valid('" \~ "');807} {0}808 809#--------------------------------------------------------------------------810# 2017-04-11. https://sqlite.org/src/info/981329adeef51011811# Stack overflow on deeply nested JSON.812#813# The following tests confirm that deeply nested JSON is considered invalid.814#815do_execsql_test json101-11.0 {816 /* Shallow enough to be parsed */817 SELECT json_valid(printf('%.1000c0%.1000c','[',']'));818} {1}819do_execsql_test json101-11.1 {820 /* Too deep by one */821 SELECT json_valid(printf('%.1001c0%.1001c','[',']'));822} {0}823do_execsql_test json101-11.2 {824 /* Shallow enough to be parsed { */825 SELECT json_valid(replace(printf('%.1000c0%.1000c','[','}'),'[','{"a":'));826 /* } */827} {1}828do_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 from835# a json structure even though the element name constains a "."836# character, by quoting the element name in the path.837#838do_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 863do_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}}}}}}867do_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}}}}}}871do_execsql_test json101-12.120 {872 SELECT json_extract(x, '$.settings.layer2."tris.legomenon"."summary.report"')873 FROM t12;874} {0}875do_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-26881# ticket https://sqlite.org/src/tktview/80177f0c226ff54f6ddd41882# Make sure the query planner knows about the arguments to table-valued functions.883#884do_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 t2896 WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z897 WHERE Z.value==t2.id);898} {1 {{"items":[3,5]}} 3 {{"value":3}} NL 1 {{"items":[3,5]}} 5 {{"value":5}} NL}899do_execsql_test json101-13.110 {900 SELECT *, 'NL' FROM t2 CROSS JOIN t1901 WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z902 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-16906# Incorrect fullkey output from json_each()907# when the input JSON is not an array or object.908#909do_execsql_test json101-14.100 {910 SELECT fullkey FROM json_each('123');911} {$}912do_execsql_test json101-14.110 {913 SELECT fullkey FROM json_each('123.56');914} {$}915do_execsql_test json101-14.120 {916 SELECT fullkey FROM json_each('"hello"');917} {$}918do_execsql_test json101-14.130 {919 SELECT fullkey FROM json_each('null');920} {$}921do_execsql_test json101-14.140 {922 SELECT fullkey FROM json_tree('123');923} {$}924do_execsql_test json101-14.150 {925 SELECT fullkey FROM json_tree('123.56');926} {$}927do_execsql_test json101-14.160 {928 SELECT fullkey FROM json_tree('"hello"');929} {$}930do_execsql_test json101-14.170 {931 SELECT fullkey FROM json_tree('null');932} {$}933 934# 2018-12-03935# Make sure the table-valued functions contained within parentheses936# work correctly.937#938# Bug reported via private email. See TH3 for more information.939#940do_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} {$}}943do_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} {$}}946do_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} {$}}949do_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-10954# Mailing list bug report on the handling of surrogate pairs955# in JSON.956#957do_execsql_test json101-16.10 {958 SELECT length(json_extract('"abc\uD834\uDD1Exyz"','$'));959} {7}960do_execsql_test json101-16.20 {961 SELECT length(json_extract('"\uD834\uDD1E"','$'));962} {1}963do_execsql_test json101-16.30 {964 SELECT unicode(json_extract('"\uD834\uDD1E"','$'));965} {119070}966 967# 2022-01-30 dbsqlfuzz 4678cf825d27f87c9b8343720121e12cf944b71a968do_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/c082aeab43977do_execsql_test json101-18.1 {978 SELECT json_valid('{"":5}');979} {1}980do_execsql_test json101-18.2 {981 SELECT json_extract('{"":5}', '$.""');982} {5}983do_execsql_test json101-18.3 {984 SELECT json_extract('[3,{"a":4,"":[5,{"hi":6},7]},8]', '$[1].""[1].hi');985} {6}986do_execsql_test json101-18.4 {987 SELECT json_extract('[3,{"a":4,"":[5,{"hi":6},7]},8]', '$[1].""[1]."hi"');988} {6}989do_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/9b9e4716c0d7bbd1994# This is not a problem specifically with JSON functions. It is995# a problem with transaction control. But the json() function makes996# the problem more easily accessible, so it is tested here.997#998do_execsql_test json101-19.1 {999 DROP TABLE IF EXISTS t1;1000 CREATE TABLE t1(x);1001} {}1002do_catchsql_test json101-19.2 {1003 BEGIN;1004 INSERT INTO t1 VALUES(0), (json('not-valid-json'));1005} {1 {malformed JSON}}1006do_execsql_test json101-19.3 {1007 COMMIT;1008 SELECT * FROM t1;1009} {}1010 1011# 2023-03-17 positive and negative infinities1012#1013do_execsql_test json101-20.1 {1014 SELECT json_object('a',2e370,'b',-3e380);1015} {{{"a":9.0e+999,"b":-9.0e+999}}}1016do_execsql_test json101-20.2 {1017 SELECT json_object('a',2e370,'b',-3e380)->>'a';1018} Inf1019do_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/06c63344121024# JSON functions should normally return NULL when given1025# a NULL value as the JSON input.1026#1027db null NULL1028if {[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 } 01032} else {1033 do_execsql_test json101-21.1-correct {1034 SELECT json_valid(NULL);1035 } NULL1036}1037do_execsql_test json101-21.2 {1038 SELECT json_error_position(NULL);1039} NULL1040do_execsql_test json101-21.3 {1041 SELECT json(NULL);1042} NULL1043do_execsql_test json101-21.4 {1044 SELECT json_array(NULL);1045} {[null]}1046do_execsql_test json101-21.5 {1047 SELECT json_extract(NULL);1048} NULL1049do_execsql_test json101-21.6 {1050 SELECT json_insert(NULL,'$',123);1051} NULL1052do_execsql_test json101-21.7 {1053 SELECT NULL->0;1054} NULL1055do_execsql_test json101-21.8 {1056 SELECT NULL->>0;1057} NULL1058do_execsql_test json101-21.9 {1059 SELECT '{a:5}'->NULL;1060} NULL1061do_execsql_test json101-21.10 {1062 SELECT '{a:5}'->>NULL;1063} NULL1064do_catchsql_test json101-21.11 {1065 SELECT json_object(NULL,5);1066} {1 {json_object() labels must be TEXT}}1067do_execsql_test json101-21.12 {1068 SELECT json_patch(NULL,'{a:5}');1069} NULL1070do_execsql_test json101-21.13 {1071 SELECT json_patch('{a:5}',NULL);1072} NULL1073do_execsql_test json101-21.14 {1074 SELECT json_patch(NULL,NULL);1075} NULL1076do_execsql_test json101-21.15 {1077 SELECT json_remove(NULL,'$');1078} NULL1079do_execsql_test json101-21.16 {1080 SELECT json_remove('{a:5,b:7}',NULL);1081} NULL1082do_execsql_test json101-21.17 {1083 SELECT json_replace(NULL,'$.a',123);1084} NULL1085do_execsql_test json101-21.18 {1086 SELECT json_replace('{a:5,b:7}',NULL,NULL);1087} {{{"a":5,"b":7}}}1088do_execsql_test json101-21.19 {1089 SELECT json_set(NULL,'$.a',123);1090} NULL1091do_execsql_test json101-21.20 {1092 SELECT json_set('{a:5,b:7}',NULL,NULL);1093} {{{"a":5,"b":7}}}1094do_execsql_test json101-21.21 {1095 SELECT json_type(NULL);1096} NULL1097do_execsql_test json101-21.22 {1098 SELECT json_type('{a:5,b:7}',NULL);1099} NULL1100do_execsql_test json101-21.23 {1101 SELECT json_quote(NULL);1102} null1103do_execsql_test json101-21.24 {1104 SELECT count(*) FROM json_each(NULL);1105} 01106do_execsql_test json101-21.25 {1107 SELECT count(*) FROM json_tree(NULL);1108} 01109do_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"]}1113do_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/b25edc1d461119# UAF due to JSON cache overflow1120#1121do_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}}}1132do_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/fc0e3f1e2a1145# Incorrect accesss to '$[0]' in parsed + edited JSON.1146#1147do_execsql_test json101-23.1 {1148 SELECT j, j->>0, j->>11149 FROM (SELECT json_set(json_set('[]','$[#]',0), '$[#]',1) AS j);1150} {{[0,1]} 0 1}1151do_execsql_test json101-23.2 {1152 SELECT j, j->>0, j->>11153 FROM (SELECT json_set('[]','$[#]',0,'$[#]',1) AS j);1154} {{[0,1]} 0 1}1155 1156# Insert/Set/Replace where the path specifies substructure that1157# does not yet exist1158#1159proc tx x {return [string map [list ( \173 ) \175 ' \042 < \133 > \135] $x]}1160foreach {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 1181finish_test1182