[sql] Import reference version of SQLite 3.10.2.

third_party/sqlite/sqlite-src-3100200/test/json101.test

Index: third_party/sqlite/sqlite-src-3100200/test/json101.test
diff --git a/third_party/sqlite/sqlite-src-3100200/test/json101.test b/third_party/sqlite/sqlite-src-3100200/test/json101.test
new file mode 100644
index 0000000000000000000000000000000000000000..8c62c8b16c6410eed41dd24ae901230b1c3e7f8f
--- /dev/null
+++ b/third_party/sqlite/sqlite-src-3100200/test/json101.test
@@ -0,0 +1,345 @@
+# 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
+
+ifcapable !json1 {
+  finish_test
+  return
+}
+
+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 json-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 json-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 json-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 json-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 json-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 json-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 json-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 json-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 json-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 json-6.1 {
+  SELECT json_valid('{"a":55,"b":72,}');
+} {0}
+do_execsql_test json-6.2 {
+  SELECT json_valid('{"a":55,"b":72}');
+} {1}
+do_execsql_test json-6.3 {
+  SELECT json_valid('["a",55,"b",72,]');
+} {0}
+do_execsql_test json-6.4 {
+  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 json-$tn.1 \
+    "SELECT json_valid(printf('%s{%s\"x\"%s:%s9%s}%s',
+         $::ws,$::ws,$::ws,$::ws,$::ws,$::ws));" \
+  $isvalid
+}
+
+finish_test