| # 2015-08-12 |
| # |
| # The author disclaims copyright to this source code. In place of |
| # a legal notice, here is a blessing: |
| # |
| # May you do good and not evil. |
| # May you find forgiveness for yourself and forgive others. |
| # May you share freely, never taking more than you give. |
| # |
| #*********************************************************************** |
| # This file implements tests for JSON SQL functions extension to the |
| # SQLite library. |
| # |
| |
| set testdir [file dirname $argv0] |
| source $testdir/tester.tcl |
| |
| do_execsql_test json101-1.1.00 { |
| SELECT json_array(1,2.5,null,'hello'); |
| } {[1,2.5,null,"hello"]} |
| do_execsql_test json101-1.1.01 { |
| SELECT json_array(1,'{"abc":2.5,"def":null,"ghi":hello}',99); |
| -- the second term goes in as a string: |
| } {[1,"{\\"abc\\":2.5,\\"def\\":null,\\"ghi\\":hello}",99]} |
| do_execsql_test json101-1.1.02 { |
| SELECT json_array(1,json('{"abc":2.5,"def":null,"ghi":"hello"}'),99); |
| -- the second term goes in as JSON |
| } {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]} |
| do_execsql_test json101-1.1.03 { |
| SELECT json_array(1,json_object('abc',2.5,'def',null,'ghi','hello'),99); |
| -- the second term goes in as JSON |
| } {[1,{"abc":2.5,"def":null,"ghi":"hello"},99]} |
| do_execsql_test json101-1.2 { |
| SELECT hex(json_array('String "\ Test')); |
| } {5B22537472696E67205C225C5C2054657374225D} |
| do_catchsql_test json101-1.3 { |
| SELECT json_array(1,printf('%.1000c','x'),x'abcd',3); |
| } {1 {JSON cannot hold BLOB values}} |
| do_execsql_test json101-1.4 { |
| SELECT json_array(-9223372036854775808,9223372036854775807,0,1,-1, |
| 0.0, 1.0, -1.0, -1e99, +2e100, |
| '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); |
| } {[-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]} |
| |
| do_execsql_test json101-2.1 { |
| SELECT json_object('a',1,'b',2.5,'c',null,'d','String Test'); |
| } {{{"a":1,"b":2.5,"c":null,"d":"String Test"}}} |
| do_catchsql_test json101-2.2 { |
| SELECT json_object('a',printf('%.1000c','x'),2,2.5); |
| } {1 {json_object() labels must be TEXT}} |
| do_catchsql_test json101-2.3 { |
| SELECT json_object('a',1,'b'); |
| } {1 {json_object() requires an even number of arguments}} |
| do_catchsql_test json101-2.4 { |
| SELECT json_object('a',printf('%.1000c','x'),'b',x'abcd'); |
| } {1 {JSON cannot hold BLOB values}} |
| |
| do_execsql_test json101-3.1 { |
| SELECT json_replace('{"a":1,"b":2}','$.a','[3,4,5]'); |
| } {{{"a":"[3,4,5]","b":2}}} |
| do_execsql_test json101-3.2 { |
| SELECT json_replace('{"a":1,"b":2}','$.a',json('[3,4,5]')); |
| } {{{"a":[3,4,5],"b":2}}} |
| do_execsql_test json101-3.3 { |
| SELECT json_type(json_set('{"a":1,"b":2}','$.b','{"x":3,"y":4}'),'$.b'); |
| } {text} |
| do_execsql_test json101-3.4 { |
| SELECT json_type(json_set('{"a":1,"b":2}','$.b',json('{"x":3,"y":4}')),'$.b'); |
| } {object} |
| ifcapable vtab { |
| do_execsql_test json101-3.5 { |
| SELECT fullkey, atom, '|' FROM json_tree(json_set('{}','$.x',123,'$.x',456)); |
| } {{$} {} | {$.x} 456 |} |
| } |
| |
| # Per rfc7159, any JSON value is allowed at the top level, and whitespace |
| # is permitting before and/or after that value. |
| # |
| do_execsql_test json101-4.1 { |
| CREATE TABLE j1(x); |
| INSERT INTO j1(x) |
| VALUES('true'),('false'),('null'),('123'),('-234'),('34.5e+6'), |
| ('""'),('"\""'),('"\\"'),('"abcdefghijlmnopqrstuvwxyz"'), |
| ('[]'),('{}'),('[true,false,null,123,-234,34.5e+6,{},[]]'), |
| ('{"a":true,"b":{"c":false}}'); |
| SELECT * FROM j1 WHERE NOT json_valid(x); |
| } {} |
| do_execsql_test json101-4.2 { |
| SELECT * FROM j1 WHERE NOT json_valid(char(0x20,0x09,0x0a,0x0d)||x); |
| } {} |
| do_execsql_test json101-4.3 { |
| SELECT * FROM j1 WHERE NOT json_valid(x||char(0x20,0x09,0x0a,0x0d)); |
| } {} |
| |
| # But an empty string, or a string of pure whitespace is not valid JSON. |
| # |
| do_execsql_test json101-4.4 { |
| SELECT json_valid(''), json_valid(char(0x20,0x09,0x0a,0x0d)); |
| } {0 0} |
| |
| # json_remove() and similar functions with no edit operations return their |
| # input unchanged. |
| # |
| do_execsql_test json101-4.5 { |
| SELECT x FROM j1 WHERE json_remove(x)<>x; |
| } {} |
| do_execsql_test json101-4.6 { |
| SELECT x FROM j1 WHERE json_replace(x)<>x; |
| } {} |
| do_execsql_test json101-4.7 { |
| SELECT x FROM j1 WHERE json_set(x)<>x; |
| } {} |
| do_execsql_test json101-4.8 { |
| SELECT x FROM j1 WHERE json_insert(x)<>x; |
| } {} |
| |
| # json_extract(JSON,'$') will return objects and arrays without change. |
| # |
| do_execsql_test json101-4.10 { |
| SELECT count(*) FROM j1 WHERE json_type(x) IN ('object','array'); |
| SELECT x FROM j1 |
| WHERE json_extract(x,'$')<>x |
| AND json_type(x) IN ('object','array'); |
| } {4} |
| |
| do_execsql_test json101-5.1 { |
| CREATE TABLE j2(id INTEGER PRIMARY KEY, json, src); |
| INSERT INTO j2(id,json,src) |
| VALUES(1,'{ |
| "firstName": "John", |
| "lastName": "Smith", |
| "isAlive": true, |
| "age": 25, |
| "address": { |
| "streetAddress": "21 2nd Street", |
| "city": "New York", |
| "state": "NY", |
| "postalCode": "10021-3100" |
| }, |
| "phoneNumbers": [ |
| { |
| "type": "home", |
| "number": "212 555-1234" |
| }, |
| { |
| "type": "office", |
| "number": "646 555-4567" |
| } |
| ], |
| "children": [], |
| "spouse": null |
| }','https://en.wikipedia.org/wiki/JSON'); |
| INSERT INTO j2(id,json,src) |
| VALUES(2, '{ |
| "id": "0001", |
| "type": "donut", |
| "name": "Cake", |
| "ppu": 0.55, |
| "batters": |
| { |
| "batter": |
| [ |
| { "id": "1001", "type": "Regular" }, |
| { "id": "1002", "type": "Chocolate" }, |
| { "id": "1003", "type": "Blueberry" }, |
| { "id": "1004", "type": "Devil''s Food" } |
| ] |
| }, |
| "topping": |
| [ |
| { "id": "5001", "type": "None" }, |
| { "id": "5002", "type": "Glazed" }, |
| { "id": "5005", "type": "Sugar" }, |
| { "id": "5007", "type": "Powdered Sugar" }, |
| { "id": "5006", "type": "Chocolate with Sprinkles" }, |
| { "id": "5003", "type": "Chocolate" }, |
| { "id": "5004", "type": "Maple" } |
| ] |
| }','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html'); |
| INSERT INTO j2(id,json,src) |
| VALUES(3,'[ |
| { |
| "id": "0001", |
| "type": "donut", |
| "name": "Cake", |
| "ppu": 0.55, |
| "batters": |
| { |
| "batter": |
| [ |
| { "id": "1001", "type": "Regular" }, |
| { "id": "1002", "type": "Chocolate" }, |
| { "id": "1003", "type": "Blueberry" }, |
| { "id": "1004", "type": "Devil''s Food" } |
| ] |
| }, |
| "topping": |
| [ |
| { "id": "5001", "type": "None" }, |
| { "id": "5002", "type": "Glazed" }, |
| { "id": "5005", "type": "Sugar" }, |
| { "id": "5007", "type": "Powdered Sugar" }, |
| { "id": "5006", "type": "Chocolate with Sprinkles" }, |
| { "id": "5003", "type": "Chocolate" }, |
| { "id": "5004", "type": "Maple" } |
| ] |
| }, |
| { |
| "id": "0002", |
| "type": "donut", |
| "name": "Raised", |
| "ppu": 0.55, |
| "batters": |
| { |
| "batter": |
| [ |
| { "id": "1001", "type": "Regular" } |
| ] |
| }, |
| "topping": |
| [ |
| { "id": "5001", "type": "None" }, |
| { "id": "5002", "type": "Glazed" }, |
| { "id": "5005", "type": "Sugar" }, |
| { "id": "5003", "type": "Chocolate" }, |
| { "id": "5004", "type": "Maple" } |
| ] |
| }, |
| { |
| "id": "0003", |
| "type": "donut", |
| "name": "Old Fashioned", |
| "ppu": 0.55, |
| "batters": |
| { |
| "batter": |
| [ |
| { "id": "1001", "type": "Regular" }, |
| { "id": "1002", "type": "Chocolate" } |
| ] |
| }, |
| "topping": |
| [ |
| { "id": "5001", "type": "None" }, |
| { "id": "5002", "type": "Glazed" }, |
| { "id": "5003", "type": "Chocolate" }, |
| { "id": "5004", "type": "Maple" } |
| ] |
| } |
| ]','https://adobe.github.io/Spry/samples/data_region/JSONDataSetSample.html'); |
| SELECT count(*) FROM j2; |
| } {3} |
| |
| do_execsql_test json101-5.2 { |
| SELECT id, json_valid(json), json_type(json), '|' FROM j2 ORDER BY id; |
| } {1 1 object | 2 1 object | 3 1 array |} |
| |
| ifcapable !vtab { |
| finish_test |
| return |
| } |
| |
| # fullkey is always the same as path+key (with appropriate formatting) |
| # |
| do_execsql_test json101-5.3 { |
| SELECT j2.rowid, jx.rowid, fullkey, path, key |
| FROM j2, json_tree(j2.json) AS jx |
| WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']' |
| ELSE '.'||key END); |
| } {} |
| do_execsql_test json101-5.4 { |
| SELECT j2.rowid, jx.rowid, fullkey, path, key |
| FROM j2, json_each(j2.json) AS jx |
| WHERE fullkey!=(path || CASE WHEN typeof(key)=='integer' THEN '['||key||']' |
| ELSE '.'||key END); |
| } {} |
| |
| |
| # Verify that the json_each.json and json_tree.json output is always the |
| # same as input. |
| # |
| do_execsql_test json101-5.5 { |
| SELECT j2.rowid, jx.rowid, fullkey, path, key |
| FROM j2, json_each(j2.json) AS jx |
| WHERE jx.json<>j2.json; |
| } {} |
| do_execsql_test json101-5.6 { |
| SELECT j2.rowid, jx.rowid, fullkey, path, key |
| FROM j2, json_tree(j2.json) AS jx |
| WHERE jx.json<>j2.json; |
| } {} |
| do_execsql_test json101-5.7 { |
| SELECT j2.rowid, jx.rowid, fullkey, path, key |
| FROM j2, json_each(j2.json) AS jx |
| WHERE jx.value<>jx.atom AND type NOT IN ('array','object'); |
| } {} |
| do_execsql_test json101-5.8 { |
| SELECT j2.rowid, jx.rowid, fullkey, path, key |
| FROM j2, json_tree(j2.json) AS jx |
| WHERE jx.value<>jx.atom AND type NOT IN ('array','object'); |
| } {} |
| |
| do_execsql_test json101-6.1 { |
| SELECT json_valid('{"a":55,"b":72,}'); |
| } {0} |
| do_execsql_test json101-6.2 { |
| SELECT json_error_position('{"a":55,"b":72,}'); |
| } {0} |
| do_execsql_test json101-6.3 { |
| SELECT json_valid(json('{"a":55,"b":72,}')); |
| } {1} |
| do_execsql_test json101-6.4 { |
| SELECT json_valid('{"a":55,"b":72 , }'); |
| } {0} |
| do_execsql_test json101-6.5 { |
| SELECT json_error_position('{"a":55,"b":72 , }'); |
| } {0} |
| do_execsql_test json101-6.6 { |
| SELECT json_error_position('{"a":55,"b":72,,}'); |
| } {16} |
| do_execsql_test json101-6.7 { |
| SELECT json_valid('{"a":55,"b":72}'); |
| } {1} |
| do_execsql_test json101-6.8 { |
| SELECT json_error_position('["a",55,"b",72,]'); |
| } {0} |
| do_execsql_test json101-6.9 { |
| SELECT json_error_position('["a",55,"b",72 , ]'); |
| } {0} |
| do_execsql_test json101-6.10 { |
| SELECT json_error_position('["a",55,"b",72,,]'); |
| } {16} |
| do_execsql_test json101-6.11 { |
| SELECT json_valid('["a",55,"b",72]'); |
| } {1} |
| |
| # White-space tests. Note that form-feed is not white-space in JSON. |
| # ticket [57eec374ae1d0a1d4a23077a95f4e173fe269113] |
| # |
| foreach {tn isvalid ws} { |
| 7.1 1 char(0x20) |
| 7.2 1 char(0x09) |
| 7.3 1 char(0x0A) |
| 7.4 1 char(0x0D) |
| 7.5 0 char(0x0C) |
| 7.6 1 char(0x20,0x09,0x0a,0x0d,0x20) |
| 7.7 0 char(0x20,0x09,0x0a,0x0c,0x0d,0x20) |
| } { |
| do_execsql_test json101-$tn.1 \ |
| "SELECT json_valid(printf('%s{%s\"x\"%s:%s9%s}%s', |
| $::ws,$::ws,$::ws,$::ws,$::ws,$::ws));" \ |
| $isvalid |
| } |
| |
| # Ticket https://www.sqlite.org/src/info/ad2559db380abf8e |
| # Control characters must be escaped in JSON strings. |
| # |
| do_execsql_test json101-8.1 { |
| DROP TABLE IF EXISTS t8; |
| CREATE TABLE t8(a,b); |
| 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'); |
| UPDATE t8 SET b=json_array(a); |
| SELECT b FROM t8; |
| } {{["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"]}} |
| do_execsql_test json101-8.2 { |
| SELECT a=json_extract(b,'$[0]') FROM t8; |
| } {1} |
| |
| # 2017-04-12. Regression reported on the mailing list by Rolf Ade |
| # |
| do_execsql_test json101-8.3 { |
| SELECT json_valid(char(0x22,0xe4,0x22)); |
| } {1} |
| do_execsql_test json101-8.4 { |
| SELECT unicode(json_extract(char(0x22,228,0x22),'$')); |
| } {228} |
| |
| # The json_quote() function transforms an SQL value into a JSON value. |
| # String values are quoted and interior quotes are escaped. NULL values |
| # are rendered as the unquoted string "null". |
| # |
| do_execsql_test json101-9.1 { |
| SELECT json_quote('abc"xyz'); |
| } {{"abc\"xyz"}} |
| do_execsql_test json101-9.2 { |
| SELECT json_quote(3.14159); |
| } {3.14159} |
| do_execsql_test json101-9.3 { |
| SELECT json_quote(12345); |
| } {12345} |
| do_execsql_test json101-9.4 { |
| SELECT json_quote(null); |
| } {"null"} |
| do_catchsql_test json101-9.5 { |
| SELECT json_quote(x'30313233'); |
| } {1 {JSON cannot hold BLOB values}} |
| do_catchsql_test json101-9.6 { |
| SELECT json_quote(123,456) |
| } {1 {wrong number of arguments to function json_quote()}} |
| do_catchsql_test json101-9.7 { |
| SELECT json_quote() |
| } {1 {wrong number of arguments to function json_quote()}} |
| |
| # Make sure only valid backslash-escapes are accepted. |
| # |
| do_execsql_test json101-10.1 { |
| SELECT json_valid('" \ "'); |
| } {0} |
| do_execsql_test json101-10.2 { |
| SELECT json_valid('" \! "'); |
| } {0} |
| do_execsql_test json101-10.3 { |
| SELECT json_valid('" \" "'); |
| } {1} |
| do_execsql_test json101-10.4 { |
| SELECT json_valid('" \# "'); |
| } {0} |
| do_execsql_test json101-10.5 { |
| SELECT json_valid('" \$ "'); |
| } {0} |
| do_execsql_test json101-10.6 { |
| SELECT json_valid('" \% "'); |
| } {0} |
| do_execsql_test json101-10.7 { |
| SELECT json_valid('" \& "'); |
| } {0} |
| do_execsql_test json101-10.8 { |
| SELECT json_valid('" \'' "'); |
| } {0} |
| do_execsql_test json101-10.9 { |
| SELECT json_valid('" \( "'); |
| } {0} |
| do_execsql_test json101-10.10 { |
| SELECT json_valid('" \) "'); |
| } {0} |
| do_execsql_test json101-10.11 { |
| SELECT json_valid('" \* "'); |
| } {0} |
| do_execsql_test json101-10.12 { |
| SELECT json_valid('" \+ "'); |
| } {0} |
| do_execsql_test json101-10.13 { |
| SELECT json_valid('" \, "'); |
| } {0} |
| do_execsql_test json101-10.14 { |
| SELECT json_valid('" \- "'); |
| } {0} |
| do_execsql_test json101-10.15 { |
| SELECT json_valid('" \. "'); |
| } {0} |
| do_execsql_test json101-10.16 { |
| SELECT json_valid('" \/ "'); |
| } {1} |
| do_execsql_test json101-10.17 { |
| SELECT json_valid('" \0 "'); |
| } {0} |
| do_execsql_test json101-10.18 { |
| SELECT json_valid('" \1 "'); |
| } {0} |
| do_execsql_test json101-10.19 { |
| SELECT json_valid('" \2 "'); |
| } {0} |
| do_execsql_test json101-10.20 { |
| SELECT json_valid('" \3 "'); |
| } {0} |
| do_execsql_test json101-10.21 { |
| SELECT json_valid('" \4 "'); |
| } {0} |
| do_execsql_test json101-10.22 { |
| SELECT json_valid('" \5 "'); |
| } {0} |
| do_execsql_test json101-10.23 { |
| SELECT json_valid('" \6 "'); |
| } {0} |
| do_execsql_test json101-10.24 { |
| SELECT json_valid('" \7 "'); |
| } {0} |
| do_execsql_test json101-10.25 { |
| SELECT json_valid('" \8 "'); |
| } {0} |
| do_execsql_test json101-10.26 { |
| SELECT json_valid('" \9 "'); |
| } {0} |
| do_execsql_test json101-10.27 { |
| SELECT json_valid('" \: "'); |
| } {0} |
| do_execsql_test json101-10.28 { |
| SELECT json_valid('" \; "'); |
| } {0} |
| do_execsql_test json101-10.29 { |
| SELECT json_valid('" \< "'); |
| } {0} |
| do_execsql_test json101-10.30 { |
| SELECT json_valid('" \= "'); |
| } {0} |
| do_execsql_test json101-10.31 { |
| SELECT json_valid('" \> "'); |
| } {0} |
| do_execsql_test json101-10.32 { |
| SELECT json_valid('" \? "'); |
| } {0} |
| do_execsql_test json101-10.33 { |
| SELECT json_valid('" \@ "'); |
| } {0} |
| do_execsql_test json101-10.34 { |
| SELECT json_valid('" \A "'); |
| } {0} |
| do_execsql_test json101-10.35 { |
| SELECT json_valid('" \B "'); |
| } {0} |
| do_execsql_test json101-10.36 { |
| SELECT json_valid('" \C "'); |
| } {0} |
| do_execsql_test json101-10.37 { |
| SELECT json_valid('" \D "'); |
| } {0} |
| do_execsql_test json101-10.38 { |
| SELECT json_valid('" \E "'); |
| } {0} |
| do_execsql_test json101-10.39 { |
| SELECT json_valid('" \F "'); |
| } {0} |
| do_execsql_test json101-10.40 { |
| SELECT json_valid('" \G "'); |
| } {0} |
| do_execsql_test json101-10.41 { |
| SELECT json_valid('" \H "'); |
| } {0} |
| do_execsql_test json101-10.42 { |
| SELECT json_valid('" \I "'); |
| } {0} |
| do_execsql_test json101-10.43 { |
| SELECT json_valid('" \J "'); |
| } {0} |
| do_execsql_test json101-10.44 { |
| SELECT json_valid('" \K "'); |
| } {0} |
| do_execsql_test json101-10.45 { |
| SELECT json_valid('" \L "'); |
| } {0} |
| do_execsql_test json101-10.46 { |
| SELECT json_valid('" \M "'); |
| } {0} |
| do_execsql_test json101-10.47 { |
| SELECT json_valid('" \N "'); |
| } {0} |
| do_execsql_test json101-10.48 { |
| SELECT json_valid('" \O "'); |
| } {0} |
| do_execsql_test json101-10.49 { |
| SELECT json_valid('" \P "'); |
| } {0} |
| do_execsql_test json101-10.50 { |
| SELECT json_valid('" \Q "'); |
| } {0} |
| do_execsql_test json101-10.51 { |
| SELECT json_valid('" \R "'); |
| } {0} |
| do_execsql_test json101-10.52 { |
| SELECT json_valid('" \S "'); |
| } {0} |
| do_execsql_test json101-10.53 { |
| SELECT json_valid('" \T "'); |
| } {0} |
| do_execsql_test json101-10.54 { |
| SELECT json_valid('" \U "'); |
| } {0} |
| do_execsql_test json101-10.55 { |
| SELECT json_valid('" \V "'); |
| } {0} |
| do_execsql_test json101-10.56 { |
| SELECT json_valid('" \W "'); |
| } {0} |
| do_execsql_test json101-10.57 { |
| SELECT json_valid('" \X "'); |
| } {0} |
| do_execsql_test json101-10.58 { |
| SELECT json_valid('" \Y "'); |
| } {0} |
| do_execsql_test json101-10.59 { |
| SELECT json_valid('" \Z "'); |
| } {0} |
| do_execsql_test json101-10.60 { |
| SELECT json_valid('" \[ "'); |
| } {0} |
| do_execsql_test json101-10.61 { |
| SELECT json_valid('" \\ "'); |
| } {1} |
| do_execsql_test json101-10.62 { |
| SELECT json_valid('" \] "'); |
| } {0} |
| do_execsql_test json101-10.63 { |
| SELECT json_valid('" \^ "'); |
| } {0} |
| do_execsql_test json101-10.64 { |
| SELECT json_valid('" \_ "'); |
| } {0} |
| do_execsql_test json101-10.65 { |
| SELECT json_valid('" \` "'); |
| } {0} |
| do_execsql_test json101-10.66 { |
| SELECT json_valid('" \a "'); |
| } {0} |
| do_execsql_test json101-10.67 { |
| SELECT json_valid('" \b "'); |
| } {1} |
| do_execsql_test json101-10.68 { |
| SELECT json_valid('" \c "'); |
| } {0} |
| do_execsql_test json101-10.69 { |
| SELECT json_valid('" \d "'); |
| } {0} |
| do_execsql_test json101-10.70 { |
| SELECT json_valid('" \e "'); |
| } {0} |
| do_execsql_test json101-10.71 { |
| SELECT json_valid('" \f "'); |
| } {1} |
| do_execsql_test json101-10.72 { |
| SELECT json_valid('" \g "'); |
| } {0} |
| do_execsql_test json101-10.73 { |
| SELECT json_valid('" \h "'); |
| } {0} |
| do_execsql_test json101-10.74 { |
| SELECT json_valid('" \i "'); |
| } {0} |
| do_execsql_test json101-10.75 { |
| SELECT json_valid('" \j "'); |
| } {0} |
| do_execsql_test json101-10.76 { |
| SELECT json_valid('" \k "'); |
| } {0} |
| do_execsql_test json101-10.77 { |
| SELECT json_valid('" \l "'); |
| } {0} |
| do_execsql_test json101-10.78 { |
| SELECT json_valid('" \m "'); |
| } {0} |
| do_execsql_test json101-10.79 { |
| SELECT json_valid('" \n "'); |
| } {1} |
| do_execsql_test json101-10.80 { |
| SELECT json_valid('" \o "'); |
| } {0} |
| do_execsql_test json101-10.81 { |
| SELECT json_valid('" \p "'); |
| } {0} |
| do_execsql_test json101-10.82 { |
| SELECT json_valid('" \q "'); |
| } {0} |
| do_execsql_test json101-10.83 { |
| SELECT json_valid('" \r "'); |
| } {1} |
| do_execsql_test json101-10.84 { |
| SELECT json_valid('" \s "'); |
| } {0} |
| do_execsql_test json101-10.85 { |
| SELECT json_valid('" \t "'); |
| } {1} |
| do_execsql_test json101-10.86.0 { |
| SELECT json_valid('" \u "'); |
| } {0} |
| do_execsql_test json101-10.86.1 { |
| SELECT json_valid('" \ua "'); |
| } {0} |
| do_execsql_test json101-10.86.2 { |
| SELECT json_valid('" \uab "'); |
| } {0} |
| do_execsql_test json101-10.86.3 { |
| SELECT json_valid('" \uabc "'); |
| } {0} |
| do_execsql_test json101-10.86.4 { |
| SELECT json_valid('" \uabcd "'); |
| } {1} |
| do_execsql_test json101-10.86.5 { |
| SELECT json_valid('" \uFEDC "'); |
| } {1} |
| do_execsql_test json101-10.86.6 { |
| SELECT json_valid('" \u1234 "'); |
| } {1} |
| do_execsql_test json101-10.87 { |
| SELECT json_valid('" \v "'); |
| } {0} |
| do_execsql_test json101-10.88 { |
| SELECT json_valid('" \w "'); |
| } {0} |
| do_execsql_test json101-10.89 { |
| SELECT json_valid('" \x "'); |
| } {0} |
| do_execsql_test json101-10.90 { |
| SELECT json_valid('" \y "'); |
| } {0} |
| do_execsql_test json101-10.91 { |
| SELECT json_valid('" \z "'); |
| } {0} |
| do_execsql_test json101-10.92 { |
| SELECT json_valid('" \{ "'); |
| } {0} |
| do_execsql_test json101-10.93 { |
| SELECT json_valid('" \| "'); |
| } {0} |
| do_execsql_test json101-10.94 { |
| SELECT json_valid('" \} "'); |
| } {0} |
| do_execsql_test json101-10.95 { |
| SELECT json_valid('" \~ "'); |
| } {0} |
| |
| #-------------------------------------------------------------------------- |
| # 2017-04-11. https://www.sqlite.org/src/info/981329adeef51011 |
| # Stack overflow on deeply nested JSON. |
| # |
| # The following tests confirm that deeply nested JSON is considered invalid. |
| # |
| do_execsql_test json101-11.0 { |
| /* Shallow enough to be parsed */ |
| SELECT json_valid(printf('%.1000c0%.1000c','[',']')); |
| } {1} |
| do_execsql_test json101-11.1 { |
| /* Too deep by one */ |
| SELECT json_valid(printf('%.1001c0%.1001c','[',']')); |
| } {0} |
| do_execsql_test json101-11.2 { |
| /* Shallow enough to be parsed { */ |
| SELECT json_valid(replace(printf('%.1000c0%.1000c','[','}'),'[','{"a":')); |
| /* } */ |
| } {1} |
| do_execsql_test json101-11.3 { |
| /* Too deep by one { */ |
| SELECT json_valid(replace(printf('%.1001c0%.1001c','[','}'),'[','{"a":')); |
| /* } */ |
| } {0} |
| |
| # 2017-10-27. Demonstrate the ability to access an element from |
| # a json structure even though the element name constains a "." |
| # character, by quoting the element name in the path. |
| # |
| do_execsql_test json101-12.100 { |
| CREATE TABLE t12(x); |
| INSERT INTO t12(x) VALUES( |
| '{"settings": |
| {"layer2": |
| {"hapax.legomenon": |
| {"forceDisplay":true, |
| "transliterate":true, |
| "add.footnote":true, |
| "summary.report":true}, |
| "dis.legomenon": |
| {"forceDisplay":true, |
| "transliterate":false, |
| "add.footnote":false, |
| "summary.report":true}, |
| "tris.legomenon": |
| {"forceDisplay":true, |
| "transliterate":false, |
| "add.footnote":false, |
| "summary.report":false} |
| } |
| } |
| }'); |
| } {} |
| do_execsql_test json101-12.110 { |
| SELECT json_remove(x, '$.settings.layer2."dis.legomenon".forceDisplay') |
| FROM t12; |
| } {{{"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}}}}}} |
| do_execsql_test json101-12.120 { |
| SELECT json_extract(x, '$.settings.layer2."tris.legomenon"."summary.report"') |
| FROM t12; |
| } {0} |
| |
| # 2018-01-26 |
| # ticket https://www.sqlite.org/src/tktview/80177f0c226ff54f6ddd41 |
| # Make sure the query planner knows about the arguments to table-valued functions. |
| # |
| do_execsql_test json101-13.100 { |
| DROP TABLE IF EXISTS t1; |
| DROP TABLE IF EXISTS t2; |
| CREATE TABLE t1(id, json); |
| INSERT INTO t1(id,json) VALUES(1,'{"items":[3,5]}'); |
| CREATE TABLE t2(id, json); |
| INSERT INTO t2(id,json) VALUES(2,'{"value":2}'); |
| INSERT INTO t2(id,json) VALUES(3,'{"value":3}'); |
| INSERT INTO t2(id,json) VALUES(4,'{"value":4}'); |
| INSERT INTO t2(id,json) VALUES(5,'{"value":5}'); |
| INSERT INTO t2(id,json) VALUES(6,'{"value":6}'); |
| SELECT * FROM t1 CROSS JOIN t2 |
| WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z |
| WHERE Z.value==t2.id); |
| } {1 {{"items":[3,5]}} 3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}}} |
| do_execsql_test json101-13.110 { |
| SELECT * FROM t2 CROSS JOIN t1 |
| WHERE EXISTS(SELECT 1 FROM json_each(t1.json,'$.items') AS Z |
| WHERE Z.value==t2.id); |
| } {3 {{"value":3}} 1 {{"items":[3,5]}} 5 {{"value":5}} 1 {{"items":[3,5]}}} |
| |
| # 2018-05-16 |
| # Incorrect fullkey output from json_each() |
| # when the input JSON is not an array or object. |
| # |
| do_execsql_test json101-14.100 { |
| SELECT fullkey FROM json_each('123'); |
| } {$} |
| do_execsql_test json101-14.110 { |
| SELECT fullkey FROM json_each('123.56'); |
| } {$} |
| do_execsql_test json101-14.120 { |
| SELECT fullkey FROM json_each('"hello"'); |
| } {$} |
| do_execsql_test json101-14.130 { |
| SELECT fullkey FROM json_each('null'); |
| } {$} |
| do_execsql_test json101-14.140 { |
| SELECT fullkey FROM json_tree('123'); |
| } {$} |
| do_execsql_test json101-14.150 { |
| SELECT fullkey FROM json_tree('123.56'); |
| } {$} |
| do_execsql_test json101-14.160 { |
| SELECT fullkey FROM json_tree('"hello"'); |
| } {$} |
| do_execsql_test json101-14.170 { |
| SELECT fullkey FROM json_tree('null'); |
| } {$} |
| |
| # 2018-12-03 |
| # Make sure the table-valued functions contained within parentheses |
| # work correctly. |
| # |
| # Bug reported via private email. See TH3 for more information. |
| # |
| do_execsql_test json101-15.100 { |
| SELECT * FROM JSON_EACH('{"a":1, "b":2}'); |
| } {a 1 integer 1 2 {} {$.a} {$} b 2 integer 2 4 {} {$.b} {$}} |
| do_execsql_test json101-15.110 { |
| SELECT xyz.* FROM JSON_EACH('{"a":1, "b":2}') AS xyz; |
| } {a 1 integer 1 2 {} {$.a} {$} b 2 integer 2 4 {} {$.b} {$}} |
| do_execsql_test json101-15.120 { |
| SELECT * FROM (JSON_EACH('{"a":1, "b":2}')); |
| } {a 1 integer 1 2 {} {$.a} {$} b 2 integer 2 4 {} {$.b} {$}} |
| do_execsql_test json101-15.130 { |
| SELECT xyz.* FROM (JSON_EACH('{"a":1, "b":2}')) AS xyz; |
| } {a 1 integer 1 2 {} {$.a} {$} b 2 integer 2 4 {} {$.b} {$}} |
| |
| # 2019-11-10 |
| # Mailing list bug report on the handling of surrogate pairs |
| # in JSON. |
| # |
| do_execsql_test json101-16.10 { |
| SELECT length(json_extract('"abc\uD834\uDD1Exyz"','$')); |
| } {7} |
| do_execsql_test json101-16.20 { |
| SELECT length(json_extract('"\uD834\uDD1E"','$')); |
| } {1} |
| do_execsql_test json101-16.30 { |
| SELECT unicode(json_extract('"\uD834\uDD1E"','$')); |
| } {119070} |
| |
| # 2022-01-30 dbsqlfuzz 4678cf825d27f87c9b8343720121e12cf944b71a |
| do_execsql_test json101-17.1 { |
| DROP TABLE IF EXISTS t1; |
| DROP TABLE IF EXISTS t2; |
| CREATE TABLE t1(a,b,c); |
| CREATE TABLE t2(d); |
| SELECT * FROM t1 LEFT JOIN t2 ON (SELECT b FROM json_each ORDER BY 1); |
| } {} |
| |
| # 2022-04-04 forum post https://sqlite.org/forum/forumpost/c082aeab43 |
| do_execsql_test json101-18.1 { |
| SELECT json_valid('{"":5}'); |
| } {1} |
| do_execsql_test json101-18.2 { |
| SELECT json_extract('{"":5}', '$.""'); |
| } {5} |
| do_execsql_test json101-18.3 { |
| SELECT json_extract('[3,{"a":4,"":[5,{"hi":6},7]},8]', '$[1].""[1].hi'); |
| } {6} |
| do_execsql_test json101-18.4 { |
| SELECT json_extract('[3,{"a":4,"":[5,{"hi":6},7]},8]', '$[1].""[1]."hi"'); |
| } {6} |
| do_catchsql_test json101-18.5 { |
| SELECT json_extract('{"":8}', '$.'); |
| } {1 {JSON path error near ''}} |
| |
| # 2022-08-29 https://sqlite.org/forum/forumpost/9b9e4716c0d7bbd1 |
| # This is not a problem specifically with JSON functions. It is |
| # a problem with transaction control. But the json() function makes |
| # the problem more easily accessible, so it is tested here. |
| # |
| do_execsql_test json101-19.1 { |
| DROP TABLE IF EXISTS t1; |
| CREATE TABLE t1(x); |
| } {} |
| do_catchsql_test json101-19.2 { |
| BEGIN; |
| INSERT INTO t1 VALUES(0), (json('not-valid-json')); |
| } {1 {malformed JSON}} |
| do_execsql_test json101-19.3 { |
| COMMIT; |
| SELECT * FROM t1; |
| } {} |
| |
| # 2023-03-17 positive and negative infinities |
| # |
| do_execsql_test json101-20.1 { |
| SELECT json_object('a',2e370,'b',-3e380); |
| } {{{"a":9.0e+999,"b":-9.0e+999}}} |
| do_execsql_test json101-20.2 { |
| SELECT json_object('a',2e370,'b',-3e380)->>'a'; |
| } Inf |
| do_execsql_test json101-20.3 { |
| SELECT json_object('a',2e370,'b',-3e380)->>'b'; |
| } {-Inf} |
| |
| # 2023-05-02 https://sqlite.org/forum/forumpost/06c6334412 |
| # JSON functions should normally return NULL when given |
| # a NULL value as the JSON input. |
| # |
| db null NULL |
| if {[db exists {SELECT * FROM pragma_compile_options WHERE compile_options LIKE '%legacy_json_valid%'}]} { |
| do_execsql_test json101-21.1-legacy { |
| SELECT json_valid(NULL); |
| } 0 |
| } else { |
| do_execsql_test json101-21.1-correct { |
| SELECT json_valid(NULL); |
| } NULL |
| } |
| do_execsql_test json101-21.2 { |
| SELECT json_error_position(NULL); |
| } NULL |
| do_execsql_test json101-21.3 { |
| SELECT json(NULL); |
| } NULL |
| do_execsql_test json101-21.4 { |
| SELECT json_array(NULL); |
| } {[null]} |
| do_execsql_test json101-21.5 { |
| SELECT json_extract(NULL); |
| } NULL |
| do_execsql_test json101-21.6 { |
| SELECT json_insert(NULL,'$',123); |
| } NULL |
| do_execsql_test json101-21.7 { |
| SELECT NULL->0; |
| } NULL |
| do_execsql_test json101-21.8 { |
| SELECT NULL->>0; |
| } NULL |
| do_execsql_test json101-21.9 { |
| SELECT '{a:5}'->NULL; |
| } NULL |
| do_execsql_test json101-21.10 { |
| SELECT '{a:5}'->>NULL; |
| } NULL |
| do_catchsql_test json101-21.11 { |
| SELECT json_object(NULL,5); |
| } {1 {json_object() labels must be TEXT}} |
| do_execsql_test json101-21.12 { |
| SELECT json_patch(NULL,'{a:5}'); |
| } NULL |
| do_execsql_test json101-21.13 { |
| SELECT json_patch('{a:5}',NULL); |
| } NULL |
| do_execsql_test json101-21.14 { |
| SELECT json_patch(NULL,NULL); |
| } NULL |
| do_execsql_test json101-21.15 { |
| SELECT json_remove(NULL,'$'); |
| } NULL |
| do_execsql_test json101-21.16 { |
| SELECT json_remove('{a:5,b:7}',NULL); |
| } NULL |
| do_execsql_test json101-21.17 { |
| SELECT json_replace(NULL,'$.a',123); |
| } NULL |
| do_execsql_test json101-21.18 { |
| SELECT json_replace('{a:5,b:7}',NULL,NULL); |
| } {{{"a":5,"b":7}}} |
| do_execsql_test json101-21.19 { |
| SELECT json_set(NULL,'$.a',123); |
| } NULL |
| do_execsql_test json101-21.20 { |
| SELECT json_set('{a:5,b:7}',NULL,NULL); |
| } {{{"a":5,"b":7}}} |
| do_execsql_test json101-21.21 { |
| SELECT json_type(NULL); |
| } NULL |
| do_execsql_test json101-21.22 { |
| SELECT json_type('{a:5,b:7}',NULL); |
| } NULL |
| do_execsql_test json101-21.23 { |
| SELECT json_quote(NULL); |
| } null |
| do_execsql_test json101-21.24 { |
| SELECT count(*) FROM json_each(NULL); |
| } 0 |
| do_execsql_test json101-21.25 { |
| SELECT count(*) FROM json_tree(NULL); |
| } 0 |
| do_execsql_test json101-21.26 { |
| WITH c(x) AS (VALUES(1),(2.0),(NULL),('three')) |
| SELECT json_group_array(x) FROM c; |
| } {[1,2.0,null,"three"]} |
| do_execsql_test json101-21.27 { |
| WITH c(x,y) AS (VALUES('a',1),('b',2.0),('c',NULL),(NULL,'three'),('e','four')) |
| SELECT json_group_object(x,y) FROM c; |
| } {{{"a":1,"b":2.0,"c":null,:"three","e":"four"}}} |
| |
| |
| |
| |
| |
| finish_test |