modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/json103.test (about)

     1  # 2015-12-30
     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 aggregate SQL functions
    12  #
    13  
    14  set testdir [file dirname $argv0]
    15  source $testdir/tester.tcl
    16  
    17  ifcapable !json1 {
    18    finish_test
    19    return
    20  }
    21  
    22  do_execsql_test json103-100 {
    23    CREATE TABLE t1(a,b,c);
    24    WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<100)
    25    INSERT INTO t1(a,b,c) SELECT x, x%3, printf('n%d',x)  FROM c;
    26    UPDATE t1 SET a='orange' WHERE rowid=39;
    27    UPDATE t1 SET a=32.5 WHERE rowid=31;
    28    UPDATE t1 SET a=x'303132' WHERE rowid=29;
    29    UPDATE t1 SET a=NULL WHERE rowid=37;
    30    SELECT json_group_array(a) FROM t1 WHERE a<0 AND typeof(a)!='blob';
    31  } {{[]}}
    32  do_catchsql_test json103-101 {
    33    SELECT json_group_array(a) FROM t1;
    34  } {1 {JSON cannot hold BLOB values}}
    35  do_execsql_test json103-110 {
    36    SELECT json_group_array(a) FROM t1
    37     WHERE rowid BETWEEN 31 AND 39;
    38  } {{[32.5,32,33,34,35,36,null,38,"orange"]}}
    39  do_execsql_test json103-111 {
    40    SELECT json_array_length(json_group_array(a)) FROM t1
    41     WHERE rowid BETWEEN 31 AND 39;
    42  } {9}
    43  do_execsql_test json103-120 {
    44    SELECT b, json_group_array(a) FROM t1 WHERE rowid<10 GROUP BY b ORDER BY b;
    45  } {0 {[3,6,9]} 1 {[1,4,7]} 2 {[2,5,8]}}
    46  
    47  do_execsql_test json103-200 {
    48    SELECT json_group_object(c,a) FROM t1 WHERE a<0 AND typeof(a)!='blob';
    49  } {{{}}}
    50  do_catchsql_test json103-201 {
    51    SELECT json_group_object(c,a) FROM t1;
    52  } {1 {JSON cannot hold BLOB values}}
    53  
    54  do_execsql_test json103-210 {
    55    SELECT json_group_object(c,a) FROM t1
    56     WHERE rowid BETWEEN 31 AND 39 AND rowid%2==1;
    57  } {{{"n31":32.5,"n33":33,"n35":35,"n37":null,"n39":"orange"}}}
    58  do_execsql_test json103-220 {
    59    SELECT b, json_group_object(c,a) FROM t1
    60     WHERE rowid<7 GROUP BY b ORDER BY b;
    61  } {0 {{"n3":3,"n6":6}} 1 {{"n1":1,"n4":4}} 2 {{"n2":2,"n5":5}}}
    62  
    63  # ticket https://www.sqlite.org/src/info/f45ac567eaa9f93c 2016-01-30
    64  # Invalid JSON generated by json_group_array() 
    65  #
    66  # The underlying problem is a failure to reset Mem.eSubtype
    67  #
    68  do_execsql_test json103-300 {
    69    DROP TABLE IF EXISTS t1;
    70    CREATE TABLE t1(x);
    71    INSERT INTO t1 VALUES(1),('abc');
    72    SELECT
    73       json_group_array(x),
    74       json_group_array(json_object('x',x))
    75      FROM t1;
    76  } {{[1,"abc"]} {[{"x":1},{"x":"abc"}]}}
    77  
    78  finish_test