/
niceSOFT
/
sqlite
Обзор
Документация
Войти
/
niceSOFT
/
sqlite
Код
Задачи
Вики
Пакеты
0
Релизы
0
Аналитика
Безопасность
release
test/json102.test
843 строки
29 KB
drh
Avoid unsigned integer overflow when evaluating an array index in a
28 янв 2026, 02:33
28 янв 2026, 02:33
60229c2
Код
Авторство
О чём код?
# 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. # # This file contains tests automatically generated from the json1 # documentation. # set testdir [file dirname $argv0] source $testdir/tester.tcl do_execsql_test json102-100 { SELECT json_object('ex','[52,3.14159]'); } {{{"ex":"[52,3.14159]"}}} do_execsql_test json102-100b { SELECT json(jsonb_object('ex','[52,3.14159]')); } {{{"ex":"[52,3.14159]"}}} do_execsql_test json102-110 { SELECT json_object('ex',json('[52,3.14159]')); } {{{"ex":[52,3.14159]}}} do_execsql_test json102-110-2 { SELECT json(jsonb_object('ex',json('[52,3.14159]'))); } {{{"ex":[52,3.14159]}}} do_execsql_test json102-110-3 { SELECT json_object('ex',jsonb('[52,3.14159]')); } {{{"ex":[52,3.14159]}}} do_execsql_test json102-110-3 { SELECT json(jsonb_object('ex',jsonb('[52,3.14159]'))); } {{{"ex":[52,3.14159]}}} do_execsql_test json102-120 { SELECT json_object('ex',json_array(52,3.14159)); } {{{"ex":[52,3.14159]}}} do_execsql_test json102-120-2 { SELECT json(jsonb_object('ex',json_array(52,3.14159))); } {{{"ex":[52,3.14159]}}} do_execsql_test json102-120-3 { SELECT json_object('ex',jsonb_array(52,3.14159)); } {{{"ex":[52,3.14159]}}} do_execsql_test json102-120-4 { SELECT json(jsonb_object('ex',jsonb_array(52,3.14159))); } {{{"ex":[52,3.14159]}}} do_execsql_test json102-130 { SELECT json(' { "this" : "is", "a": [ "test" ] } '); } {{{"this":"is","a":["test"]}}} do_execsql_test json102-130b { SELECT json(jsonb(' { "this" : "is", "a": [ "test" ] } ')); } {{{"this":"is","a":["test"]}}} do_execsql_test json102-140 { SELECT json_array(1,2,'3',4); } {{[1,2,"3",4]}} do_execsql_test json102-140b { SELECT json(jsonb_array(1,2,'3',4)); } {{[1,2,"3",4]}} do_execsql_test json102-150 { SELECT json_array('[1,2]'); } {{["[1,2]"]}} do_execsql_test json102-150b { SELECT json(jsonb_array('[1,2]')); } {{["[1,2]"]}} do_execsql_test json102-160 { SELECT json_array(json_array(1,2)); } {{[[1,2]]}} do_execsql_test json102-160-2 { SELECT json_array(jsonb_array(1,2)); } {{[[1,2]]}} do_execsql_test json102-160-3 { SELECT json(jsonb_array(json_array(1,2))); } {{[[1,2]]}} do_execsql_test json102-160-4 { SELECT json(jsonb_array(jsonb_array(1,2))); } {{[[1,2]]}} do_execsql_test json102-170 { SELECT json_array(1,null,'3','[4,5]','{"six":7.7}'); } {{[1,null,"3","[4,5]","{\"six\":7.7}"]}} do_execsql_test json102-170b { SELECT json(jsonb_array(1,null,'3','[4,5]','{"six":7.7}')); } {{[1,null,"3","[4,5]","{\"six\":7.7}"]}} do_execsql_test json102-180 { SELECT json_array(1,null,'3',json('[4,5]'),json('{"six":7.7}')); } {{[1,null,"3",[4,5],{"six":7.7}]}} do_execsql_test json102-180-2 { SELECT json_array(1,null,'3',jsonb('[4,5]'),json('{"six":7.7}')); } {{[1,null,"3",[4,5],{"six":7.7}]}} do_execsql_test json102-180-3 { SELECT json(jsonb_array(1,null,'3',json('[4,5]'),json('{"six":7.7}'))); } {{[1,null,"3",[4,5],{"six":7.7}]}} do_execsql_test json102-180-4 { SELECT json(jsonb_array(1,null,'3',jsonb('[4,5]'),jsonb('{"six":7.7}'))); } {{[1,null,"3",[4,5],{"six":7.7}]}} do_execsql_test json102-190 { SELECT json_array_length('[1,2,3,4]'); } {{4}} do_execsql_test json102-190b { SELECT json_array_length(jsonb('[1,2,3,4]')); } {{4}} do_execsql_test json102-191 { SELECT json_array_length( json_remove('[1,2,3,4]','$[2]') ); } {{3}} do_execsql_test json102-191b { SELECT json_array_length( jsonb_remove('[1,2,3,4]','$[2]') ); } {{3}} do_execsql_test json102-200 { SELECT json_array_length('[1,2,3,4]', '$'); } {{4}} do_execsql_test json102-200b { SELECT json_array_length(jsonb('[1,2,3,4]'), '$'); } {{4}} do_execsql_test json102-210 { SELECT json_array_length('[1,2,3,4]', '$[2]'); } {{0}} do_execsql_test json102-210b { SELECT json_array_length(jsonb('[1,2,3,4]'), '$[2]'); } {{0}} do_execsql_test json102-220 { SELECT json_array_length('{"one":[1,2,3]}'); } {{0}} do_execsql_test json102-220 { SELECT json_array_length('{"one":[1,2,3]}'); } {{0}} do_execsql_test json102-230b { SELECT json_array_length(jsonb('{"one":[1,2,3]}'), '$.one'); } {{3}} do_execsql_test json102-240 { SELECT json_array_length('{"one":[1,2,3]}', '$.two'); } {{}} do_execsql_test json102-240b { SELECT json_array_length(jsonb('{"one":[1,2,3]}'), '$.two'); } {{}} do_execsql_test json102-250 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$'); } {{{"a":2,"c":[4,5,{"f":7}]}}} do_execsql_test json102-250-2 { SELECT json_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$'); } {{{"a":2,"c":[4,5,{"f":7}]}}} do_execsql_test json102-250-3 { SELECT json(jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$')); } {{{"a":2,"c":[4,5,{"f":7}]}}} do_execsql_test json102-250-4 { SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$')); } {{{"a":2,"c":[4,5,{"f":7}]}}} do_execsql_test json102-260 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c'); } {{[4,5,{"f":7}]}} do_execsql_test json102-260-2 { SELECT json_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.c'); } {{[4,5,{"f":7}]}} do_execsql_test json102-260-3 { SELECT json(jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c')); } {{[4,5,{"f":7}]}} do_execsql_test json102-260-4 { SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.c')); } {{[4,5,{"f":7}]}} do_execsql_test json102-270 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]'); } {{{"f":7}}} do_execsql_test json102-270-2 { SELECT json_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.c[2]'); } {{{"f":7}}} do_execsql_test json102-270-3 { SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.c[2]')); } {{{"f":7}}} do_execsql_test json102-270-4 { SELECT json(jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2]')); } {{{"f":7}}} do_execsql_test json102-280 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f'); } {{7}} do_execsql_test json102-280b { SELECT jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f'); } {{7}} do_execsql_test json102-290 { SELECT json_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a'); } {{[[4,5],2]}} do_execsql_test json102-290-2 { SELECT json_extract(jsonb('{"a":2,"c":[4,5],"f":7}'),'$.c','$.a'); } {{[[4,5],2]}} do_execsql_test json102-290-3 { SELECT json(jsonb_extract('{"a":2,"c":[4,5],"f":7}','$.c','$.a')); } {{[[4,5],2]}} do_execsql_test json102-290-4 { SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5],"f":7}'),'$.c','$.a')); } {{[[4,5],2]}} do_execsql_test json102-300 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x'); } {{}} do_execsql_test json102-300b { SELECT jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x'); } {{}} do_execsql_test json102-310 { SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a'); } {{[null,2]}} do_execsql_test json102-310-2 { SELECT json_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.x', '$.a'); } {{[null,2]}} do_execsql_test json102-310-3 { SELECT json(jsonb_extract(jsonb('{"a":2,"c":[4,5,{"f":7}]}'), '$.x', '$.a')); } {{[null,2]}} do_execsql_test json102-310-43 { SELECT json(jsonb_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x', '$.a')); } {{[null,2]}} do_execsql_test json102-320 { SELECT json_insert('{"a":2,"c":4}', '$.a', 99); } {{{"a":2,"c":4}}} do_execsql_test json102-320-2 { SELECT json_insert(jsonb('{"a":2,"c":4}'), '$.a', 99); } {{{"a":2,"c":4}}} do_execsql_test json102-320-3 { SELECT json(jsonb_insert('{"a":2,"c":4}', '$.a', 99)); } {{{"a":2,"c":4}}} do_execsql_test json102-320-4 { SELECT json(jsonb_insert(jsonb('{"a":2,"c":4}'), '$.a', 99)); } {{{"a":2,"c":4}}} do_execsql_test json102-330 { SELECT json_insert('{"a":2,"c":4}', '$.e', 99); } {{{"a":2,"c":4,"e":99}}} do_execsql_test json102-330-2 { SELECT json_insert(jsonb('{"a":2,"c":4}'), '$.e', 99); } {{{"a":2,"c":4,"e":99}}} do_execsql_test json102-330-3 { SELECT json(jsonb_insert('{"a":2,"c":4}', '$.e', 99)); } {{{"a":2,"c":4,"e":99}}} do_execsql_test json102-330-4 { SELECT json(jsonb_insert(jsonb('{"a":2,"c":4}'), '$.e', 99)); } {{{"a":2,"c":4,"e":99}}} do_execsql_test json102-340 { SELECT json_replace('{"a":2,"c":4}', '$.a', 99); } {{{"a":99,"c":4}}} do_execsql_test json102-340-2 { SELECT json_replace(jsonb('{"a":2,"c":4}'), '$.a', 99); } {{{"a":99,"c":4}}} do_execsql_test json102-340-3 { SELECT json(jsonb_replace('{"a":2,"c":4}', '$.a', 99)); } {{{"a":99,"c":4}}} do_execsql_test json102-340-4 { SELECT json(jsonb_replace(jsonb('{"a":2,"c":4}'), '$.a', 99)); } {{{"a":99,"c":4}}} do_execsql_test json102-350 { SELECT json_replace('{"a":2,"c":4}', '$.e', 99); } {{{"a":2,"c":4}}} do_execsql_test json102-350-2 { SELECT json_replace(jsonb('{"a":2,"c":4}'), '$.e', 99); } {{{"a":2,"c":4}}} do_execsql_test json102-350-3 { SELECT json(jsonb_replace('{"a":2,"c":4}', '$.e', 99)); } {{{"a":2,"c":4}}} do_execsql_test json102-350-4 { SELECT json(jsonb_replace(jsonb('{"a":2,"c":4}'), '$.e', 99)); } {{{"a":2,"c":4}}} do_execsql_test json102-360 { SELECT json_set('{"a":2,"c":4}', '$.a', 99); } {{{"a":99,"c":4}}} do_execsql_test json102-360-2 { SELECT json_set(jsonb('{"a":2,"c":4}'), '$.a', 99); } {{{"a":99,"c":4}}} do_execsql_test json102-360-3 { SELECT json(jsonb_set('{"a":2,"c":4}', '$.a', 99)); } {{{"a":99,"c":4}}} do_execsql_test json102-360-4 { SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.a', 99)); } {{{"a":99,"c":4}}} do_execsql_test json102-370 { SELECT json_set('{"a":2,"c":4}', '$.e', 99); } {{{"a":2,"c":4,"e":99}}} do_execsql_test json102-370-2 { SELECT json_set(jsonb('{"a":2,"c":4}'), '$.e', 99); } {{{"a":2,"c":4,"e":99}}} do_execsql_test json102-370-3 { SELECT json(jsonb_set('{"a":2,"c":4}', '$.e', 99)); } {{{"a":2,"c":4,"e":99}}} do_execsql_test json102-370-4 { SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.e', 99)); } {{{"a":2,"c":4,"e":99}}} do_execsql_test json102-380 { SELECT json_set('{"a":2,"c":4}', '$.c', '[97,96]'); } {{{"a":2,"c":"[97,96]"}}} do_execsql_test json102-380-2 { SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', '[97,96]'); } {{{"a":2,"c":"[97,96]"}}} do_execsql_test json102-380-3 { SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', '[97,96]')); } {{{"a":2,"c":"[97,96]"}}} do_execsql_test json102-380-4 { SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', '[97,96]')); } {{{"a":2,"c":"[97,96]"}}} do_execsql_test json102-390 { SELECT json_set('{"a":2,"c":4}', '$.c', json('[97,96]')); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-390-2 { SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', json('[97,96]')); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-390-3 { SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', json('[97,96]'))); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-390-4 { SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', json('[97,96]'))); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-390-5 { SELECT json_set('{"a":2,"c":4}', '$.c', jsonb('[97,96]')); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-390-6 { SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', jsonb('[97,96]')); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-390-7 { SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', jsonb('[97,96]'))); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-390-8 { SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', jsonb('[97,96]'))); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-400 { SELECT json_set('{"a":2,"c":4}', '$.c', json_array(97,96)); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-400-2 { SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', json_array(97,96)); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-400-3 { SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', json_array(97,96))); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-400-4 { SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', json_array(97,96))); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-400-5 { SELECT json_set('{"a":2,"c":4}', '$.c', jsonb_array(97,96)); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-400-6 { SELECT json_set(jsonb('{"a":2,"c":4}'), '$.c', jsonb_array(97,96)); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-400-7 { SELECT json(jsonb_set('{"a":2,"c":4}', '$.c', jsonb_array(97,96))); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-400-8 { SELECT json(jsonb_set(jsonb('{"a":2,"c":4}'), '$.c', jsonb_array(97,96))); } {{{"a":2,"c":[97,96]}}} do_execsql_test json102-410 { SELECT json_object('a',2,'c',4); } {{{"a":2,"c":4}}} do_execsql_test json102-410b { SELECT json(jsonb_object('a',2,'c',4)); } {{{"a":2,"c":4}}} do_execsql_test json102-420 { SELECT json_object('a',2,'c','{e:5}'); } {{{"a":2,"c":"{e:5}"}}} do_execsql_test json102-420b { SELECT json(jsonb_object('a',2,'c','{e:5}')); } {{{"a":2,"c":"{e:5}"}}} do_execsql_test json102-430 { SELECT json_object('a',2,'c',json_object('e',5)); } {{{"a":2,"c":{"e":5}}}} do_execsql_test json102-430-2 { SELECT json(jsonb_object('a',2,'c',json_object('e',5))); } {{{"a":2,"c":{"e":5}}}} do_execsql_test json102-430-3 { SELECT json_object('a',2,'c',jsonb_object('e',5)); } {{{"a":2,"c":{"e":5}}}} do_execsql_test json102-430-4 { SELECT json(jsonb_object('a',2,'c',jsonb_object('e',5))); } {{{"a":2,"c":{"e":5}}}} do_execsql_test json102-440 { SELECT json_remove('[0,1,2,3,4]','$[2]'); } {{[0,1,3,4]}} do_execsql_test json102-440-2 { SELECT json_remove(jsonb('[0,1,2,3,4]'),'$[2]'); } {{[0,1,3,4]}} do_execsql_test json102-440-3 { SELECT json(jsonb_remove('[0,1,2,3,4]','$[2]')); } {{[0,1,3,4]}} do_execsql_test json102-440-4 { SELECT json(jsonb_remove(jsonb('[0,1,2,3,4]'),'$[2]')); } {{[0,1,3,4]}} do_execsql_test json102-445-1 { SELECT json_remove('[0,1,2,3,4]','$[5]'); } {{[0,1,2,3,4]}} do_execsql_test json102-445-2 { SELECT json_remove('[0,1,2,3,4]','$[6]'); } {{[0,1,2,3,4]}} do_execsql_test json102-445-3 { SELECT json_remove('[0,1,2,3,4]','$[4294967295]'); } {{[0,1,2,3,4]}} do_execsql_test json102-445-4 { SELECT json_remove('[0,1,2,3,4]','$[4294967296]'); } {{[0,1,2,3,4]}} do_execsql_test json102-445-5 { SELECT json_remove('[0,1,2,3,4]','$[4294967297]'); } {{[0,1,2,3,4]}} do_execsql_test json102-445-6 { SELECT json_remove('[0,1,2,3,4]','$[42949672950]'); } {{[0,1,2,3,4]}} do_execsql_test json102-445-7 { SELECT json_remove('[0,1,2,3,4]','$[42949672960]'); } {{[0,1,2,3,4]}} do_execsql_test json102-450 { SELECT json_remove('[0,1,2,3,4]','$[2]','$[0]'); } {{[1,3,4]}} do_execsql_test json102-450-2 { SELECT json_remove(jsonb('[0,1,2,3,4]'),'$[2]','$[0]'); } {{[1,3,4]}} do_execsql_test json102-450-3 { SELECT json(jsonb_remove('[0,1,2,3,4]','$[2]','$[0]')); } {{[1,3,4]}} do_execsql_test json102-450-4 { SELECT json(jsonb_remove(jsonb('[0,1,2,3,4]'),'$[2]','$[0]')); } {{[1,3,4]}} do_execsql_test json102-460 { SELECT json_remove('[0,1,2,3,4]','$[0]','$[2]'); } {{[1,2,4]}} do_execsql_test json102-460-2 { SELECT json_remove(jsonb('[0,1,2,3,4]'),'$[0]','$[2]'); } {{[1,2,4]}} do_execsql_test json102-460-3 { SELECT json(jsonb_remove('[0,1,2,3,4]','$[0]','$[2]')); } {{[1,2,4]}} do_execsql_test json102-460-4 { SELECT json(jsonb_remove(jsonb('[0,1,2,3,4]'),'$[0]','$[2]')); } {{[1,2,4]}} do_execsql_test json102-470 { SELECT json_remove('{"x":25,"y":42}'); } {{{"x":25,"y":42}}} do_execsql_test json102-470-2 { SELECT json_remove(jsonb('{"x":25,"y":42}')); } {{{"x":25,"y":42}}} do_execsql_test json102-470-3 { SELECT json(jsonb_remove('{"x":25,"y":42}')); } {{{"x":25,"y":42}}} do_execsql_test json102-470-4 { SELECT json(jsonb_remove(jsonb('{"x":25,"y":42}'))); } {{{"x":25,"y":42}}} do_execsql_test json102-480 { SELECT json_remove('{"x":25,"y":42}','$.z'); } {{{"x":25,"y":42}}} do_execsql_test json102-480-2 { SELECT json_remove(jsonb('{"x":25,"y":42}'),'$.z'); } {{{"x":25,"y":42}}} do_execsql_test json102-480-3 { SELECT json(jsonb_remove('{"x":25,"y":42}','$.z')); } {{{"x":25,"y":42}}} do_execsql_test json102-480-4 { SELECT json(jsonb_remove(jsonb('{"x":25,"y":42}'),'$.z')); } {{{"x":25,"y":42}}} do_execsql_test json102-490 { SELECT json_remove('{"x":25,"y":42}','$.y'); } {{{"x":25}}} do_execsql_test json102-490-2 { SELECT json_remove(jsonb('{"x":25,"y":42}'),'$.y'); } {{{"x":25}}} do_execsql_test json102-490-3 { SELECT json(jsonb_remove('{"x":25,"y":42}','$.y')); } {{{"x":25}}} do_execsql_test json102-490-4 { SELECT json(jsonb_remove(jsonb('{"x":25,"y":42}'),'$.y')); } {{{"x":25}}} do_execsql_test json102-500 { SELECT json_remove('{"x":25,"y":42}','$'); } {{}} do_execsql_test json102-500-2 { SELECT json_remove(jsonb('{"x":25,"y":42}'),'$'); } {{}} do_execsql_test json102-500-3 { SELECT json(jsonb_remove('{"x":25,"y":42}','$')); } {{}} do_execsql_test json102-500-4 { SELECT json(jsonb_remove(jsonb('{"x":25,"y":42}'),'$')); } {{}} do_execsql_test json102-510 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}'); } {{object}} do_execsql_test json102-510b { SELECT json_type(x'cc0f1761cb0b133235332e350102001778'); } {{object}} do_execsql_test json102-520 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$'); } {{object}} do_execsql_test json102-520b { SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$'); } {{object}} do_execsql_test json102-530 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a'); } {{array}} do_execsql_test json102-530b { SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a'); } {{array}} do_execsql_test json102-540 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[0]'); } {{integer}} do_execsql_test json102-540b { SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[0]'); } {{integer}} do_execsql_test json102-550 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[1]'); } {{real}} do_execsql_test json102-550b { SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[1]'); } {{real}} do_execsql_test json102-560 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[2]'); } {{true}} do_execsql_test json102-560b { SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[2]'); } {{true}} do_execsql_test json102-570 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[3]'); } {{false}} do_execsql_test json102-570b { SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[3]'); } {{false}} do_execsql_test json102-580 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[4]'); } {{null}} do_execsql_test json102-580b { SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[4]'); } {{null}} do_execsql_test json102-590 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[5]'); } {{text}} do_execsql_test json102-590b { SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[5]'); } {{text}} do_execsql_test json102-600 { SELECT json_type('{"a":[2,3.5,true,false,null,"x"]}','$.a[6]'); } {{}} do_execsql_test json102-600b { SELECT json_type(x'cc0f1761cb0b133235332e350102001778','$.a[6]'); } {{}} do_execsql_test json102-610 { SELECT json_valid(char(123)||'"x":35'||char(125)); } {{1}} do_execsql_test json102-620 { SELECT json_valid(char(123)||'"x":35'); } {{0}} ifcapable vtab { do_execsql_test json102-1000 { CREATE TABLE user(name,phone,phoneb); INSERT INTO user(name,phone) VALUES ('Alice','["919-555-2345","804-555-3621"]'), ('Bob','["201-555-8872"]'), ('Cindy','["704-555-9983"]'), ('Dave','["336-555-8421","704-555-4321","803-911-4421"]'); UPDATE user SET phoneb=jsonb(phone); SELECT DISTINCT user.name FROM user, json_each(user.phone) WHERE json_each.value LIKE '704-%' ORDER BY 1; } {Cindy Dave} do_execsql_test json102-1000b { SELECT DISTINCT user.name FROM user, json_each(user.phoneb) WHERE json_each.value LIKE '704-%' ORDER BY 1; } {Cindy Dave} do_execsql_test json102-1010 { UPDATE user SET phone=json_extract(phone,'$[0]') WHERE json_array_length(phone)<2; SELECT name, substr(phone,1,5) FROM user ORDER BY name; } {Alice {["919} Bob 201-5 Cindy 704-5 Dave {["336}} do_execsql_test json102-1011 { SELECT name FROM user WHERE phone LIKE '704-%' UNION SELECT user.name FROM user, json_each(user.phone) WHERE json_valid(user.phone) AND json_each.value LIKE '704-%'; } {Cindy Dave} do_execsql_test json102-1100 { CREATE TABLE big(json JSON); INSERT INTO big(json) VALUES('{ "id":123, "stuff":[1,2,3,4], "partlist":[ {"uuid":"bb108722-572e-11e5-9320-7f3b63a4ca74"}, {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"}, {"subassembly":[ {"uuid":"6fa5181e-5721-11e5-a04e-57f3d7b32808"} ]} ] }'); INSERT INTO big(json) VALUES('{ "id":456, "stuff":["hello","world","xyzzy"], "partlist":[ {"uuid":false}, {"uuid":"c690dc14-572e-11e5-95f9-dfc8861fd535"} ] }'); } {} set correct_answer [list \ 1 {$.id} 123 \ 1 {$.stuff[0]} 1 \ 1 {$.stuff[1]} 2 \ 1 {$.stuff[2]} 3 \ 1 {$.stuff[3]} 4 \ 1 {$.partlist[0].uuid} bb108722-572e-11e5-9320-7f3b63a4ca74 \ 1 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535 \ 1 {$.partlist[2].subassembly[0].uuid} 6fa5181e-5721-11e5-a04e-57f3d7b32808 \ 2 {$.id} 456 \ 2 {$.stuff[0]} hello \ 2 {$.stuff[1]} world \ 2 {$.stuff[2]} xyzzy \ 2 {$.partlist[0].uuid} 0 \ 2 {$.partlist[1].uuid} c690dc14-572e-11e5-95f9-dfc8861fd535] do_execsql_test json102-1110 { SELECT big.rowid, fullkey, value FROM big, json_tree(big.json) WHERE json_tree.type NOT IN ('object','array') ORDER BY +big.rowid, +json_tree.id } $correct_answer do_execsql_test json102-1110b { SELECT big.rowid, fullkey, value FROM big, json_tree(jsonb(big.json)) WHERE json_tree.type NOT IN ('object','array') ORDER BY +big.rowid, +json_tree.id } $correct_answer do_execsql_test json102-1120 { SELECT big.rowid, fullkey, atom FROM big, json_tree(big.json) WHERE atom IS NOT NULL ORDER BY +big.rowid, +json_tree.id } $correct_answer do_execsql_test json102-1130 { SELECT DISTINCT json_extract(big.json,'$.id') FROM big, json_tree(big.json,'$.partlist') WHERE json_tree.key='uuid' AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808'; } {123} do_execsql_test json102-1131 { SELECT DISTINCT json_extract(big.json,'$.id') FROM big, json_tree(big.json,'$') WHERE json_tree.key='uuid' AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808'; } {123} do_execsql_test json102-1132 { SELECT DISTINCT json_extract(big.json,'$.id') FROM big, json_tree(big.json) WHERE json_tree.key='uuid' AND json_tree.value='6fa5181e-5721-11e5-a04e-57f3d7b32808'; } {123} } ;# end ifcapable vtab #------------------------------------------------------------------------- # Test that json_valid() correctly identifies non-ascii range # characters as non-whitespace. # do_execsql_test json102-1201 { SELECT json_valid(char(32) || '"xyz"') } 1 do_execsql_test json102-1202 { SELECT json_valid(char(200) || '"xyz"') } 0 # Off-by-one error in jsonAppendString() # for {set i 0} {$i<100} {incr i} { set str abcdef[string repeat \" [expr {$i+50}]]uvwxyz do_test json102-[format %d [expr {$i+1300}]] { db eval {SELECT json_extract(json_array($::str),'$[0]')==$::str} } {1} } #------------------------------------------------------------------------- # 2017-04-08 ticket b93be8729a895a528e2849fca99f7 # JSON extension accepts invalid numeric values # # JSON does not allow leading zeros. But the JSON extension was # allowing them. The following tests verify that the problem is now # fixed. # foreach {id j x0 x5} { 1401 {'{"x":01}'} 0 0 1402 {'{"x":-01}'} 0 0 1403 {'{"x":0}'} 1 1 1404 {'{"x":-0}'} 1 1 1405 {'{"x":0.1}'} 1 1 1406 {'{"x":-0.1}'} 1 1 1407 {'{"x":0.0000}'} 1 1 1408 {'{"x":-0.0000}'} 1 1 1409 {'{"x":01.5}'} 0 0 1410 {'{"x":-01.5}'} 0 0 1411 {'{"x":00}'} 0 0 1412 {'{"x":-00}'} 0 0 1413 {'{"x":+0}'} 0 1 1414 {'{"x":+5}'} 0 1 1415 {'{"x":+5.5}'} 0 1 } { do_execsql_test json102-$id " SELECT json_valid($j), NOT json_error_position($j); " [list $x0 $x5] } #------------------------------------------------------------------------ # 2017-04-10 ticket 6c9b5514077fed34551f98e64c09a10dc2fc8e16 # JSON extension accepts strings containing control characters. # # The JSON spec requires that all control characters be escaped. # do_execsql_test json102-1500 { WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<0x20) SELECT x FROM c WHERE json_valid(printf('{"a":"x%sz"}', char(x))) ORDER BY x; } {32} # All control characters are escaped # do_execsql_test json102-1501 { WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<0x1f) SELECT sum(json_valid(json_quote('a'||char(x)||'z'))) FROM c ORDER BY x; } {31} # 2022-01-10 tests for -> and ->> operators # reset_db do_execsql_test json102-1600 { CREATE TABLE t1(id INTEGER PRIMARY KEY, x JSON); INSERT INTO t1(id,x) VALUES (1, '{"a":null}'), (2, '{"a":123}'), (3, '{"a":4.5}'), (4, '{"a":"six"}'), (5, '{"a":[7,8]}'), (6, '{"a":{"b":9}}'), (7, '{"b":999}'); SELECT id, x->'a' AS '->', CASE WHEN subtype(x->'a') THEN 'json' ELSE typeof(x->'a') END AS 'type', x->>'a' AS '->>', CASE WHEN subtype(x->>'a') THEN 'json' ELSE typeof(x->>'a') END AS 'type', json_extract(x,'$.a') AS 'json_extract', CASE WHEN subtype(json_extract(x,'$.a')) THEN 'json' ELSE typeof(json_extract(x,'$.a')) END AS 'type' FROM t1 ORDER BY id; } [list \ 1 null json {} null {} null \ 2 123 json 123 integer 123 integer \ 3 4.5 json 4.5 real 4.5 real \ 4 {"six"} json six text six text \ 5 {[7,8]} json {[7,8]} text {[7,8]} json \ 6 {{"b":9}} json {{"b":9}} text {{"b":9}} json \ 7 {} null {} null {} null ] do_execsql_test json102-1610 { DELETE FROM t1; INSERT INTO t1(x) VALUES('[null,123,4.5,"six",[7,8],{"b":9}]'); WITH c(y) AS (VALUES(0),(1),(2),(3),(4),(5),(6)) SELECT y, x->y AS '->', CASE WHEN subtype(x->y) THEN 'json' ELSE typeof(x->y) END AS 'type', x->>y AS '->>', CASE WHEN subtype(x->>y) THEN 'json' ELSE typeof(x->>y) END AS 'type', json_extract(x,format('$[%d]',y)) AS 'json_extract', CASE WHEN subtype(json_extract(x,format('$[%d]',y))) THEN 'json' ELSE typeof(json_extract(x,format('$[%d]',y))) END AS 'type' FROM c, t1 ORDER BY y; } [list \ 0 null json {} null {} null \ 1 123 json 123 integer 123 integer \ 2 4.5 json 4.5 real 4.5 real \ 3 {"six"} json six text six text \ 4 {[7,8]} json {[7,8]} text {[7,8]} json \ 5 {{"b":9}} json {{"b":9}} text {{"b":9}} json \ 6 {} null {} null {} null ] do_execsql_test json102-1620 { DELETE FROM t1; INSERT INTO t1(x) VALUES('[null,123,4.5,"six",[7,8],{"b":9}]'); WITH c(y) AS (VALUES(0),(1),(2),(3),(4),(5),(6)) SELECT y, x->y AS '->', CASE WHEN subtype(if(json_valid(x),x->y)) THEN 'json' ELSE typeof(x->y) END AS 'type', x->>y AS '->>', CASE WHEN subtype(x->>y) THEN 'json' ELSE typeof(x->>y) END AS 'type', json_extract(x,format('$[%d]',y)) AS 'json_extract', CASE WHEN subtype(json_extract(x,format('$[%d]',y))) THEN 'json' ELSE typeof(json_extract(x,format('$[%d]',y))) END AS 'type' FROM c, t1 ORDER BY y; } [list \ 0 null json {} null {} null \ 1 123 json 123 integer 123 integer \ 2 4.5 json 4.5 real 4.5 real \ 3 {"six"} json six text six text \ 4 {[7,8]} json {[7,8]} text {[7,8]} json \ 5 {{"b":9}} json {{"b":9}} text {{"b":9}} json \ 6 {} null {} null {} null ] reset_db do_execsql_test json102-1700 { CREATE TABLE t1(a1 DATE, a2 INTEGER PRIMARY KEY, a3 INTEGER, memo TEXT); CREATE INDEX t1x1 ON t1(a3, a1, memo->>'y'); INSERT INTO t1(a2,a1,a3,memo) VALUES (876, '2023-08-03', 5, '{"x":77,"y":4}'); } do_execsql_test json102-1710 { UPDATE t1 SET memo = JSON_REMOVE(memo, '$.y'); PRAGMA integrity_check; SELECT * FROM t1; } {ok 2023-08-03 876 5 {{"x":77}}} do_execsql_test json102-1720 { UPDATE t1 SET memo = JSON_SET(memo, '$.y', 6) WHERE a2 IN (876) AND JSON_TYPE(memo, '$.y') IS NULL; PRAGMA integrity_check; SELECT * FROM t1; } {ok 2023-08-03 876 5 {{"x":77,"y":6}}} # 2024-05-21 https://sqlite.org/forum/forumpost/9e52cdfe15c3926e # What if the RHS of the -> or ->> operator is a string that looks # like a number? PostgreSQL treats it as a string. # do_execsql_test json102-1800 { SELECT '{"1":"one","2":"two","3":"three"}'->>'2'; } two db null NULL do_execsql_test json102-1801 { SELECT '{"1":"one","2":"two","3":"three"}'->>2; } NULL do_execsql_test json102-1810 { SELECT '["zero","one","two"]'->>'1'; } NULL do_execsql_test json102-1811 { SELECT '["zero","one","two"]'->>1; } one do_execsql_test json102-1820 { SELECT '{"1":"one","2":"two","3":"three"}'->'2'; } {{"two"}} do_execsql_test json102-1821 { SELECT '{"1":"one","2":"two","3":"three"}'->2; } {NULL} do_execsql_test json102-1830 { SELECT '["zero","one","two"]'->'1'; } {NULL} do_execsql_test json102-1831 { SELECT '["zero","one","two"]'->1; } {{"one"}} finish_test