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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18ifcapable !json1 { 19 finish_test 20 return 21} 22 23do_execsql_test json101-1.1.00 { 24 SELECT json_array(1,2.5,null,'hello'); 25} {[1,2.5,null,"hello"]} 26do_execsql_test json101-1.1.01 { 27 SELECT json_array(1,'{"abc":2.5,"def":null,"ghi":hello}',99); 28 -- the second term goes in as a string: 29} {[1,"{\\"abc\\":2.5,\\"def\\":null,\\"ghi\\":hello}",99]} 30do_execsql_test json101-1.1.02 { 31 SELECT json_array(1,json('{"abc":2.5,"def":null,"ghi":"hello"}'),99); 32 -- the second term goes in as JSON 33} {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]} 34do_execsql_test json101-1.1.03 { 35 SELECT json_array(1,json_object('abc',2.5,'def',null,'ghi','hello'),99); 36 -- the second term goes in as JSON 37} {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]} 38do_execsql_test json101-1.2 { 39 SELECT hex(json_array('String "\ Test')); 40} {5B22537472696E67205C225C5C2054657374225D} 41do_catchsql_test json101-1.3 { 42 SELECT json_array(1,printf('%.1000c','x'),x'abcd',3); 43} {1 {JSON cannot hold BLOB values}} 44do_execsql_test json101-1.4 { 45 SELECT json_array(-9223372036854775808,9223372036854775807,0,1,-1, 46 0.0, 1.0, -1.0, -1e99, +2e100, 47 'one','two','three', 48 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 49 19, NULL, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 50 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ', 51 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ', 52 'abcdefghijklmnopqrstuvwyxzABCDEFGHIJKLMNOPQRSTUVWXYZ', 53 99); 54} {[-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]} 55 56do_execsql_test json101-2.1 { 57 SELECT json_object('a',1,'b',2.5,'c',null,'d','String Test'); 58} {{{"a":1,"b":2.5,"c":null,"d":"String Test"}}} 59do_catchsql_test json101-2.2 { 60 SELECT json_object('a',printf('%.1000c','x'),2,2.5); 61} {1 {json_object() labels must be TEXT}} 62do_catchsql_test json101-2.3 { 63 SELECT json_object('a',1,'b'); 64} {1 {json_object() requires an even number of arguments}} 65do_catchsql_test json101-2.4 { 66 SELECT json_object('a',printf('%.1000c','x'),'b',x'abcd'); 67} {1 {JSON cannot hold BLOB values}} 68 69do_execsql_test json101-3.1 { 70 SELECT json_replace('{"a":1,"b":2}','$.a','[3,4,5]'); 71} {{{"a":"[3,4,5]","b":2}}} 72do_execsql_test json101-3.2 { 73 SELECT json_replace('{"a":1,"b":2}','$.a',json('[3,4,5]')); 74} {{{"a":[3,4,5],"b":2}}} 75do_execsql_test json101-3.3 { 76 SELECT json_type(json_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b'); 77} {text} 78do_execsql_test json101-3.4 { 79 SELECT json_type(json_set('{"a":1,"b":2}','$.b',json('{"x":3,"y":4}')),'$.b'); 80} {object} 81ifcapable vtab { 82do_execsql_test json101-3.5 { 83 SELECT fullkey, atom, '|' FROM json_tree(json_set('{}','$.x',123,'$.x',456)); 84} {{$} {} | {$.x} 456 |} 85} 86 87# Per rfc7159, any JSON value is allowed at the top level, and whitespace 88# is permitting before and/or after that value. 89# 90do_execsql_test json101-4.1 { 91 CREATE TABLE j1(x); 92 INSERT INTO j1(x) 93 VALUES('true'),('false'),('null'),('123'),('-234'),('34.5e+6'), 94 ('""'),('"\""'),('"\\"'),('"abcdefghijlmnopqrstuvwxyz"'), 95 ('[]'),('{}'),('[true,false,null,123,-234,34.5e+6,{},[]]'), 96 ('{"a":true,"b":{"c":false}}'); 97 SELECT * FROM j1 WHERE NOT json_valid(x); 98} {} 99do_execsql_test json101-4.2 { 100 SELECT * FROM j1 WHERE NOT json_valid(char(0x20,0x09,0x0a,0x0d)||x); 101} {} 102do_execsql_test json101-4.3 { 103 SELECT * FROM j1 WHERE NOT json_valid(x||char(0x20,0x09,0x0a,0x0d)); 104} {} 105 106# But an empty string, or a string of pure whitespace is not valid JSON. 107# 108do_execsql_test json101-4.4 { 109 SELECT json_valid(''), json_valid(char(0x20,0x09,0x0a,0x0d)); 110} {0 0} 111 112# json_remove() and similar functions with no edit operations return their 113# input unchanged. 114# 115do_execsql_test json101-4.5 { 116 SELECT x FROM j1 WHERE json_remove(x)<>x; 117} {} 118do_execsql_test json101-4.6 { 119 SELECT x FROM j1 WHERE json_replace(x)<>x; 120} {} 121do_execsql_test json101-4.7 { 122 SELECT x FROM j1 WHERE json_set(x)<>x; 123} {} 124do_execsql_test json101-4.8 { 125 SELECT x FROM j1 WHERE json_insert(x)<>x; 126} {} 127 128# json_extract(JSON,'$') will return objects and arrays without change. 129# 130do_execsql_test json-4.10 { 131 SELECT count(*) FROM j1 WHERE json_type(x) IN ('object','array'); 132 SELECT x FROM j1 133 WHERE json_extract(x,'$')<>x 134 AND json_type(x) IN ('object','array'); 135} {4} 136 137do_execsql_test json-5.1 { 138 CREATE TABLE j2(id INTEGER PRIMARY KEY, json, src); 139 INSERT INTO j2(id,json,src) 140 VALUES(1,'{ 141 "firstName": "John", 142 "lastName": "Smith", 143 "isAlive": true, 144 "age": 25, 145 "address": { 146 "streetAddress": "21 2nd Street", 147 "city": "New York", 148 "state": "NY", 149 "postalCode": "10021-3100" 150 }, 151 "phoneNumbers": [ 152 { 153 "type": "home", 154 "number": "212 555-1234" 155 }, 156 { 157 "type": "office", 158 "number": "646 555-4567" 159 } 160 ], 161 "children": [], 162 "spouse": null 163 }','https://en.wikipedia.org/wiki/JSON'); 164 INSERT INTO j2(id,json,src) 165 VALUES(2, '{ 166 "id": "0001", 167 "type": "donut", 168 "name": "Cake", 169 "ppu": 0.55, 170 "batters": 171 { 172 "batter": 173 [ 174 { "id": "1001", "type": "Regular" }, 175 { "id": "1002", "type": "Chocolate" }, 176 { "id": "1003", "type": "Blueberry" }, 177 { "id": "1004", "type": "Devil''s Food" } 178 ] 179 }, 180 "topping": 181 [ 182 { "id": "5001", "type": "None" }, 183 { "id": "5002", "type": "Glazed" }, 184 { "id": "5005", "type": "Sugar" }, 185 { "id": "5007", "type": "Powdered Sugar" }, 186 { "id": "5006", "type": "Chocolate with Sprinkles" }, 187 { "id": "5003", "type": "Chocolate" }, 188 { "id": "5004", "type": "Maple" } 189 ] 190 }','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html'); 191 INSERT INTO j2(id,json,src) 192 VALUES(3,'[ 193 { 194 "id": "0001", 195 "type": "donut", 196 "name": "Cake", 197 "ppu": 0.55, 198 "batters": 199 { 200 "batter": 201 [ 202 { "id": "1001", "type": "Regular" }, 203 { "id": "1002", "type": "Chocolate" }, 204 { "id": "1003", "type": "Blueberry" }, 205 { "id": "1004", "type": "Devil''s Food" } 206 ] 207 }, 208 "topping": 209 [ 210 { "id": "5001", "type": "None" }, 211 { "id": "5002", "type": "Glazed" }, 212 { "id": "5005", "type": "Sugar" }, 213 { "id": "5007", "type": "Powdered Sugar" }, 214 { "id": "5006", "type": "Chocolate with Sprinkles" }, 215 { "id": "5003", "type": "Chocolate" }, 216 { "id": "5004", "type": "Maple" } 217 ] 218 }, 219 { 220 "id": "0002", 221 "type": "donut", 222 "name": "Raised", 223 "ppu": 0.55, 224 "batters": 225 { 226 "batter": 227 [ 228 { "id": "1001", "type": "Regular" } 229 ] 230 }, 231 "topping": 232 [ 233 { "id": "5001", "type": "None" }, 234 { "id": "5002", "type": "Glazed" }, 235 { "id": "5005", "type": "Sugar" }, 236 { "id": "5003", "type": "Chocolate" }, 237 { "id": "5004", "type": "Maple" } 238 ] 239 }, 240 { 241 "id": "0003", 242 "type": "donut", 243 "name": "Old Fashioned", 244 "ppu": 0.55, 245 "batters": 246 { 247 "batter": 248 [ 249 { "id": "1001", "type": "Regular" }, 250 { "id": "1002", "type": "Chocolate" } 251 ] 252 }, 253 "topping": 254 [ 255 { "id": "5001", "type": "None" }, 256 { "id": "5002", "type": "Glazed" }, 257 { "id": "5003", "type": "Chocolate" }, 258 { "id": "5004", "type": "Maple" } 259 ] 260 } 261 ]','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html'); 262 SELECT count(*) FROM j2; 263} {3} 264 265do_execsql_test json-5.2 { 266 SELECT id, json_valid(json), json_type(json), '|' FROM j2 ORDER BY id; 267} {1 1 object | 2 1 object | 3 1 array |} 268 269ifcapable !vtab { 270 finish_test 271 return 272} 273 274# fullkey is always the same as path+key (with appropriate formatting) 275# 276do_execsql_test json-5.3 { 277 SELECT j2.rowid, jx.rowid, fullkey, path, key 278 FROM j2, json_tree(j2.json) AS jx 279 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']' 280 ELSE '.'||key END); 281} {} 282do_execsql_test json-5.4 { 283 SELECT j2.rowid, jx.rowid, fullkey, path, key 284 FROM j2, json_each(j2.json) AS jx 285 WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']' 286 ELSE '.'||key END); 287} {} 288 289 290# Verify that the json_each.json and json_tree.json output is always the 291# same as input. 292# 293do_execsql_test json-5.5 { 294 SELECT j2.rowid, jx.rowid, fullkey, path, key 295 FROM j2, json_each(j2.json) AS jx 296 WHERE jx.json<>j2.json; 297} {} 298do_execsql_test json-5.6 { 299 SELECT j2.rowid, jx.rowid, fullkey, path, key 300 FROM j2, json_tree(j2.json) AS jx 301 WHERE jx.json<>j2.json; 302} {} 303do_execsql_test json-5.7 { 304 SELECT j2.rowid, jx.rowid, fullkey, path, key 305 FROM j2, json_each(j2.json) AS jx 306 WHERE jx.value<>jx.atom AND type NOT IN ('array','object'); 307} {} 308do_execsql_test json-5.8 { 309 SELECT j2.rowid, jx.rowid, fullkey, path, key 310 FROM j2, json_tree(j2.json) AS jx 311 WHERE jx.value<>jx.atom AND type NOT IN ('array','object'); 312} {} 313 314do_execsql_test json-6.1 { 315 SELECT json_valid('{"a":55,"b":72,}'); 316} {0} 317do_execsql_test json-6.2 { 318 SELECT json_valid('{"a":55,"b":72}'); 319} {1} 320do_execsql_test json-6.3 { 321 SELECT json_valid('["a",55,"b",72,]'); 322} {0} 323do_execsql_test json-6.4 { 324 SELECT json_valid('["a",55,"b",72]'); 325} {1} 326 327# White-space tests. Note that form-feed is not white-space in JSON. 328# ticket [57eec374ae1d0a1d4a23077a95f4e173fe269113] 329# 330foreach {tn isvalid ws} { 331 7.1 1 char(0x20) 332 7.2 1 char(0x09) 333 7.3 1 char(0x0A) 334 7.4 1 char(0x0D) 335 7.5 0 char(0x0C) 336 7.6 1 char(0x20,0x09,0x0a,0x0d,0x20) 337 7.7 0 char(0x20,0x09,0x0a,0x0c,0x0d,0x20) 338} { 339 do_execsql_test json-$tn.1 \ 340 "SELECT json_valid(printf('%s{%s\"x\"%s:%s9%s}%s', 341 $::ws,$::ws,$::ws,$::ws,$::ws,$::ws));" \ 342 $isvalid 343} 344 345# Ticket https://www.sqlite.org/src/info/ad2559db380abf8e 346# Control characters must be escaped in JSON strings. 347# 348do_execsql_test json-8.1 { 349 DROP TABLE IF EXISTS t8; 350 CREATE TABLE t8(a,b); 351 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'); 352 UPDATE t8 SET b=json_array(a); 353 SELECT b FROM t8; 354} {{["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"]}} 355do_execsql_test json-8.2 { 356 SELECT a=json_extract(b,'$[0]') FROM t8; 357} {1} 358 359# 2017-04-12. Regression reported on the mailing list by Rolf Ade 360# 361do_execsql_test json-8.3 { 362 SELECT json_valid(char(0x22,0xe4,0x22)); 363} {1} 364do_execsql_test json-8.4 { 365 SELECT unicode(json_extract(char(0x22,228,0x22),'$')); 366} {228} 367 368# The json_quote() function transforms an SQL value into a JSON value. 369# String values are quoted and interior quotes are escaped. NULL values 370# are rendered as the unquoted string "null". 371# 372do_execsql_test json-9.1 { 373 SELECT json_quote('abc"xyz'); 374} {{"abc\"xyz"}} 375do_execsql_test json-9.2 { 376 SELECT json_quote(3.14159); 377} {3.14159} 378do_execsql_test json-9.3 { 379 SELECT json_quote(12345); 380} {12345} 381do_execsql_test json-9.4 { 382 SELECT json_quote(null); 383} {"null"} 384do_catchsql_test json-9.5 { 385 SELECT json_quote(x'30313233'); 386} {1 {JSON cannot hold BLOB values}} 387do_catchsql_test json-9.6 { 388 SELECT json_quote(123,456) 389} {1 {wrong number of arguments to function json_quote()}} 390do_catchsql_test json-9.7 { 391 SELECT json_quote() 392} {1 {wrong number of arguments to function json_quote()}} 393 394# Make sure only valid backslash-escapes are accepted. 395# 396do_execsql_test json-10.1 { 397 SELECT json_valid('" \ "'); 398} {0} 399do_execsql_test json-10.2 { 400 SELECT json_valid('" \! "'); 401} {0} 402do_execsql_test json-10.3 { 403 SELECT json_valid('" \" "'); 404} {1} 405do_execsql_test json-10.4 { 406 SELECT json_valid('" \# "'); 407} {0} 408do_execsql_test json-10.5 { 409 SELECT json_valid('" \$ "'); 410} {0} 411do_execsql_test json-10.6 { 412 SELECT json_valid('" \% "'); 413} {0} 414do_execsql_test json-10.7 { 415 SELECT json_valid('" \& "'); 416} {0} 417do_execsql_test json-10.8 { 418 SELECT json_valid('" \'' "'); 419} {0} 420do_execsql_test json-10.9 { 421 SELECT json_valid('" \( "'); 422} {0} 423do_execsql_test json-10.10 { 424 SELECT json_valid('" \) "'); 425} {0} 426do_execsql_test json-10.11 { 427 SELECT json_valid('" \* "'); 428} {0} 429do_execsql_test json-10.12 { 430 SELECT json_valid('" \+ "'); 431} {0} 432do_execsql_test json-10.13 { 433 SELECT json_valid('" \, "'); 434} {0} 435do_execsql_test json-10.14 { 436 SELECT json_valid('" \- "'); 437} {0} 438do_execsql_test json-10.15 { 439 SELECT json_valid('" \. "'); 440} {0} 441do_execsql_test json-10.16 { 442 SELECT json_valid('" \/ "'); 443} {1} 444do_execsql_test json-10.17 { 445 SELECT json_valid('" \0 "'); 446} {0} 447do_execsql_test json-10.18 { 448 SELECT json_valid('" \1 "'); 449} {0} 450do_execsql_test json-10.19 { 451 SELECT json_valid('" \2 "'); 452} {0} 453do_execsql_test json-10.20 { 454 SELECT json_valid('" \3 "'); 455} {0} 456do_execsql_test json-10.21 { 457 SELECT json_valid('" \4 "'); 458} {0} 459do_execsql_test json-10.22 { 460 SELECT json_valid('" \5 "'); 461} {0} 462do_execsql_test json-10.23 { 463 SELECT json_valid('" \6 "'); 464} {0} 465do_execsql_test json-10.24 { 466 SELECT json_valid('" \7 "'); 467} {0} 468do_execsql_test json-10.25 { 469 SELECT json_valid('" \8 "'); 470} {0} 471do_execsql_test json-10.26 { 472 SELECT json_valid('" \9 "'); 473} {0} 474do_execsql_test json-10.27 { 475 SELECT json_valid('" \: "'); 476} {0} 477do_execsql_test json-10.28 { 478 SELECT json_valid('" \; "'); 479} {0} 480do_execsql_test json-10.29 { 481 SELECT json_valid('" \< "'); 482} {0} 483do_execsql_test json-10.30 { 484 SELECT json_valid('" \= "'); 485} {0} 486do_execsql_test json-10.31 { 487 SELECT json_valid('" \> "'); 488} {0} 489do_execsql_test json-10.32 { 490 SELECT json_valid('" \? "'); 491} {0} 492do_execsql_test json-10.33 { 493 SELECT json_valid('" \@ "'); 494} {0} 495do_execsql_test json-10.34 { 496 SELECT json_valid('" \A "'); 497} {0} 498do_execsql_test json-10.35 { 499 SELECT json_valid('" \B "'); 500} {0} 501do_execsql_test json-10.36 { 502 SELECT json_valid('" \C "'); 503} {0} 504do_execsql_test json-10.37 { 505 SELECT json_valid('" \D "'); 506} {0} 507do_execsql_test json-10.38 { 508 SELECT json_valid('" \E "'); 509} {0} 510do_execsql_test json-10.39 { 511 SELECT json_valid('" \F "'); 512} {0} 513do_execsql_test json-10.40 { 514 SELECT json_valid('" \G "'); 515} {0} 516do_execsql_test json-10.41 { 517 SELECT json_valid('" \H "'); 518} {0} 519do_execsql_test json-10.42 { 520 SELECT json_valid('" \I "'); 521} {0} 522do_execsql_test json-10.43 { 523 SELECT json_valid('" \J "'); 524} {0} 525do_execsql_test json-10.44 { 526 SELECT json_valid('" \K "'); 527} {0} 528do_execsql_test json-10.45 { 529 SELECT json_valid('" \L "'); 530} {0} 531do_execsql_test json-10.46 { 532 SELECT json_valid('" \M "'); 533} {0} 534do_execsql_test json-10.47 { 535 SELECT json_valid('" \N "'); 536} {0} 537do_execsql_test json-10.48 { 538 SELECT json_valid('" \O "'); 539} {0} 540do_execsql_test json-10.49 { 541 SELECT json_valid('" \P "'); 542} {0} 543do_execsql_test json-10.50 { 544 SELECT json_valid('" \Q "'); 545} {0} 546do_execsql_test json-10.51 { 547 SELECT json_valid('" \R "'); 548} {0} 549do_execsql_test json-10.52 { 550 SELECT json_valid('" \S "'); 551} {0} 552do_execsql_test json-10.53 { 553 SELECT json_valid('" \T "'); 554} {0} 555do_execsql_test json-10.54 { 556 SELECT json_valid('" \U "'); 557} {0} 558do_execsql_test json-10.55 { 559 SELECT json_valid('" \V "'); 560} {0} 561do_execsql_test json-10.56 { 562 SELECT json_valid('" \W "'); 563} {0} 564do_execsql_test json-10.57 { 565 SELECT json_valid('" \X "'); 566} {0} 567do_execsql_test json-10.58 { 568 SELECT json_valid('" \Y "'); 569} {0} 570do_execsql_test json-10.59 { 571 SELECT json_valid('" \Z "'); 572} {0} 573do_execsql_test json-10.60 { 574 SELECT json_valid('" \[ "'); 575} {0} 576do_execsql_test json-10.61 { 577 SELECT json_valid('" \\ "'); 578} {1} 579do_execsql_test json-10.62 { 580 SELECT json_valid('" \] "'); 581} {0} 582do_execsql_test json-10.63 { 583 SELECT json_valid('" \^ "'); 584} {0} 585do_execsql_test json-10.64 { 586 SELECT json_valid('" \_ "'); 587} {0} 588do_execsql_test json-10.65 { 589 SELECT json_valid('" \` "'); 590} {0} 591do_execsql_test json-10.66 { 592 SELECT json_valid('" \a "'); 593} {0} 594do_execsql_test json-10.67 { 595 SELECT json_valid('" \b "'); 596} {1} 597do_execsql_test json-10.68 { 598 SELECT json_valid('" \c "'); 599} {0} 600do_execsql_test json-10.69 { 601 SELECT json_valid('" \d "'); 602} {0} 603do_execsql_test json-10.70 { 604 SELECT json_valid('" \e "'); 605} {0} 606do_execsql_test json-10.71 { 607 SELECT json_valid('" \f "'); 608} {1} 609do_execsql_test json-10.72 { 610 SELECT json_valid('" \g "'); 611} {0} 612do_execsql_test json-10.73 { 613 SELECT json_valid('" \h "'); 614} {0} 615do_execsql_test json-10.74 { 616 SELECT json_valid('" \i "'); 617} {0} 618do_execsql_test json-10.75 { 619 SELECT json_valid('" \j "'); 620} {0} 621do_execsql_test json-10.76 { 622 SELECT json_valid('" \k "'); 623} {0} 624do_execsql_test json-10.77 { 625 SELECT json_valid('" \l "'); 626} {0} 627do_execsql_test json-10.78 { 628 SELECT json_valid('" \m "'); 629} {0} 630do_execsql_test json-10.79 { 631 SELECT json_valid('" \n "'); 632} {1} 633do_execsql_test json-10.80 { 634 SELECT json_valid('" \o "'); 635} {0} 636do_execsql_test json-10.81 { 637 SELECT json_valid('" \p "'); 638} {0} 639do_execsql_test json-10.82 { 640 SELECT json_valid('" \q "'); 641} {0} 642do_execsql_test json-10.83 { 643 SELECT json_valid('" \r "'); 644} {1} 645do_execsql_test json-10.84 { 646 SELECT json_valid('" \s "'); 647} {0} 648do_execsql_test json-10.85 { 649 SELECT json_valid('" \t "'); 650} {1} 651do_execsql_test json-10.86.0 { 652 SELECT json_valid('" \u "'); 653} {0} 654do_execsql_test json-10.86.1 { 655 SELECT json_valid('" \ua "'); 656} {0} 657do_execsql_test json-10.86.2 { 658 SELECT json_valid('" \uab "'); 659} {0} 660do_execsql_test json-10.86.3 { 661 SELECT json_valid('" \uabc "'); 662} {0} 663do_execsql_test json-10.86.4 { 664 SELECT json_valid('" \uabcd "'); 665} {1} 666do_execsql_test json-10.86.5 { 667 SELECT json_valid('" \uFEDC "'); 668} {1} 669do_execsql_test json-10.86.6 { 670 SELECT json_valid('" \u1234 "'); 671} {1} 672do_execsql_test json-10.87 { 673 SELECT json_valid('" \v "'); 674} {0} 675do_execsql_test json-10.88 { 676 SELECT json_valid('" \w "'); 677} {0} 678do_execsql_test json-10.89 { 679 SELECT json_valid('" \x "'); 680} {0} 681do_execsql_test json-10.90 { 682 SELECT json_valid('" \y "'); 683} {0} 684do_execsql_test json-10.91 { 685 SELECT json_valid('" \z "'); 686} {0} 687do_execsql_test json-10.92 { 688 SELECT json_valid('" \{ "'); 689} {0} 690do_execsql_test json-10.93 { 691 SELECT json_valid('" \| "'); 692} {0} 693do_execsql_test json-10.94 { 694 SELECT json_valid('" \} "'); 695} {0} 696do_execsql_test json-10.95 { 697 SELECT json_valid('" \~ "'); 698} {0} 699 700#-------------------------------------------------------------------------- 701# 2017-04-11. https://www.sqlite.org/src/info/981329adeef51011 702# Stack overflow on deeply nested JSON. 703# 704# The following tests confirm that deeply nested JSON is considered invalid. 705# 706do_execsql_test json-11.0 { 707 /* Shallow enough to be parsed */ 708 SELECT json_valid(printf('%.2000c0%.2000c','[',']')); 709} {1} 710do_execsql_test json-11.1 { 711 /* Too deep by one */ 712 SELECT json_valid(printf('%.2001c0%.2001c','[',']')); 713} {0} 714do_execsql_test json-11.2 { 715 /* Shallow enough to be parsed { */ 716 SELECT json_valid(replace(printf('%.2000c0%.2000c','[','}'),'[','{"a":')); 717 /* } */ 718} {1} 719do_execsql_test json-11.3 { 720 /* Too deep by one { */ 721 SELECT json_valid(replace(printf('%.2001c0%.2001c','[','}'),'[','{"a":')); 722 /* } */ 723} {0} 724 725# 2017-10-27. Demonstrate the ability to access an element from 726# a json structure even though the element name constains a "." 727# character, by quoting the element name in the path. 728# 729do_execsql_test json-12.100 { 730 CREATE TABLE t12(x); 731 INSERT INTO t12(x) VALUES( 732 '{"settings": 733 {"layer2": 734 {"hapax.legomenon": 735 {"forceDisplay":true, 736 "transliterate":true, 737 "add.footnote":true, 738 "summary.report":true}, 739 "dis.legomenon": 740 {"forceDisplay":true, 741 "transliterate":false, 742 "add.footnote":false, 743 "summary.report":true}, 744 "tris.legomenon": 745 {"forceDisplay":true, 746 "transliterate":false, 747 "add.footnote":false, 748 "summary.report":false} 749 } 750 } 751 }'); 752} {} 753do_execsql_test json-12.110 { 754 SELECT json_remove(x, '$.settings.layer2."dis.legomenon".forceDisplay') 755 FROM t12; 756} {{{"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}}}}}} 757do_execsql_test json-12.120 { 758 SELECT json_extract(x, '$.settings.layer2."tris.legomenon"."summary.report"') 759 FROM t12; 760} {0} 761 762# 2018-01-26 763# ticket https://www.sqlite.org/src/tktview/80177f0c226ff54f6ddd41 764# Make sure the query planner knows about the arguments to table-valued functions. 765# 766do_execsql_test json-13.100 { 767 DROP TABLE IF EXISTS t1; 768 DROP TABLE IF EXISTS t2; 769 CREATE TABLE t1(id, json); 770 INSERT INTO t1(id,json) VALUES(1,'{"items":[3,5]}'); 771 CREATE TABLE t2(id, json); 772 INSERT INTO t2(id,json) VALUES(2,'{"value":2}'); 773 INSERT INTO t2(id,json) VALUES(3,'{"value":3}'); 774 INSERT INTO t2(id,json) VALUES(4,'{"value":4}'); 775 INSERT INTO t2(id,json) VALUES(5,'{"value":5}'); 776 INSERT INTO t2(id,json) VALUES(6,'{"value":6}'); 777 SELECT * FROM t1 CROSS JOIN t2 778 WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z 779 WHERE Z.value==t2.id); 780} {1 {{"items":[3,5]}} 3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}}} 781do_execsql_test json-13.110 { 782 SELECT * FROM t2 CROSS JOIN t1 783 WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z 784 WHERE Z.value==t2.id); 785} {3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}} 1 {{"items":[3,5]}}} 786 787# 2018-05-16 788# Incorrect fullkey output from json_each() 789# when the input JSON is not an array or object. 790# 791do_execsql_test json-14.100 { 792 SELECT fullkey FROM json_each('123'); 793} {$} 794do_execsql_test json-14.110 { 795 SELECT fullkey FROM json_each('123.56'); 796} {$} 797do_execsql_test json-14.120 { 798 SELECT fullkey FROM json_each('"hello"'); 799} {$} 800do_execsql_test json-14.130 { 801 SELECT fullkey FROM json_each('null'); 802} {$} 803do_execsql_test json-14.140 { 804 SELECT fullkey FROM json_tree('123'); 805} {$} 806do_execsql_test json-14.150 { 807 SELECT fullkey FROM json_tree('123.56'); 808} {$} 809do_execsql_test json-14.160 { 810 SELECT fullkey FROM json_tree('"hello"'); 811} {$} 812do_execsql_test json-14.170 { 813 SELECT fullkey FROM json_tree('null'); 814} {$} 815 816# 2018-12-03 817# Make sure the table-valued functions contained within parentheses 818# work correctly. 819# 820# Bug reported via private email. See TH3 for more information. 821# 822do_execsql_test json-15.100 { 823 SELECT * FROM JSON_EACH('{"a":1, "b":2}'); 824} {a 1 integer 1 2 {} {$.a} {$} b 2 integer 2 4 {} {$.b} {$}} 825do_execsql_test json-15.110 { 826 SELECT xyz.* FROM JSON_EACH('{"a":1, "b":2}') AS xyz; 827} {a 1 integer 1 2 {} {$.a} {$} b 2 integer 2 4 {} {$.b} {$}} 828do_execsql_test json-15.120 { 829 SELECT * FROM (JSON_EACH('{"a":1, "b":2}')); 830} {a 1 integer 1 2 {} {$.a} {$} b 2 integer 2 4 {} {$.b} {$}} 831do_execsql_test json-15.130 { 832 SELECT xyz.* FROM (JSON_EACH('{"a":1, "b":2}')) AS xyz; 833} {a 1 integer 1 2 {} {$.a} {$} b 2 integer 2 4 {} {$.b} {$}} 834 835# 2019-11-10 836# Mailing list bug report on the handling of surrogate pairs 837# in JSON. 838# 839do_execsql_test json-16.10 { 840 SELECT length(json_extract('"abc\uD834\uDD1Exyz"','$')); 841} {7} 842do_execsql_test json-16.20 { 843 SELECT length(json_extract('"\uD834\uDD1E"','$')); 844} {1} 845do_execsql_test json-16.30 { 846 SELECT unicode(json_extract('"\uD834\uDD1E"','$')); 847} {119070} 848 849 850finish_test 851