/ Check-in [f464d847]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix the windows inverse function on the JSON aggregates.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: f464d847af490dd3ec45565dcc4c2e6ff4ed1ebb65036f30ca0b3ce2e73080e6
User & Date: drh 2019-09-14 00:21:34
Context
2019-09-14
16:21
Extra comments on fields of the Window object. check-in: 3dbed162 user: drh tags: trunk
00:21
Fix the windows inverse function on the JSON aggregates. check-in: f464d847 user: drh tags: trunk
2019-09-13
20:42
Fix a problem with using json1 window functions with an EXCLUDE clause. check-in: 4a197881 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/json1.c.

  1816   1816     JsonString *pStr;
  1817   1817     UNUSED_PARAM(argc);
  1818   1818     pStr = (JsonString*)sqlite3_aggregate_context(ctx, sizeof(*pStr));
  1819   1819     if( pStr ){
  1820   1820       if( pStr->zBuf==0 ){
  1821   1821         jsonInit(pStr, ctx);
  1822   1822         jsonAppendChar(pStr, '[');
  1823         -    }else{
         1823  +    }else if( pStr->nUsed>1 ){
  1824   1824         jsonAppendChar(pStr, ',');
  1825   1825         pStr->pCtx = ctx;
  1826   1826       }
  1827   1827       jsonAppendValue(pStr, argv[0]);
  1828   1828     }
  1829   1829   }
  1830   1830   static void jsonArrayCompute(sqlite3_context *ctx, int isFinal){
................................................................................
  1866   1866   static void jsonGroupInverse(
  1867   1867     sqlite3_context *ctx,
  1868   1868     int argc,
  1869   1869     sqlite3_value **argv
  1870   1870   ){
  1871   1871     int i;
  1872   1872     int inStr = 0;
         1873  +  int nNest = 0;
  1873   1874     char *z;
         1875  +  char c;
  1874   1876     JsonString *pStr;
  1875   1877     UNUSED_PARAM(argc);
  1876   1878     UNUSED_PARAM(argv);
  1877   1879     pStr = (JsonString*)sqlite3_aggregate_context(ctx, 0);
  1878   1880   #ifdef NEVER
  1879   1881     /* pStr is always non-NULL since jsonArrayStep() or jsonObjectStep() will
  1880   1882     ** always have been called to initalize it */
  1881   1883     if( NEVER(!pStr) ) return;
  1882   1884   #endif
  1883   1885     z = pStr->zBuf;
  1884         -  for(i=1; z[i]!=',' || inStr; i++){
  1885         -    assert( i<pStr->nUsed );
  1886         -    if( z[i]=='"' ){
         1886  +  for(i=1; (c = z[i])!=',' || inStr || nNest; i++){
         1887  +    if( i>=pStr->nUsed ){
         1888  +      pStr->nUsed = 1;
         1889  +      return;
         1890  +    }
         1891  +    if( c=='"' ){
  1887   1892         inStr = !inStr;
  1888         -    }else if( z[i]=='\\' ){
         1893  +    }else if( c=='\\' ){
  1889   1894         i++;
         1895  +    }else if( !inStr ){
         1896  +      if( c=='{' || c=='[' ) nNest++;
         1897  +      if( c=='}' || c==']' ) nNest--;
  1890   1898       }
  1891   1899     }
  1892   1900     pStr->nUsed -= i;      
  1893   1901     memmove(&z[1], &z[i+1], (size_t)pStr->nUsed-1);
  1894   1902   }
  1895   1903   #else
  1896   1904   # define jsonGroupInverse 0
................................................................................
  1912   1920     u32 n;
  1913   1921     UNUSED_PARAM(argc);
  1914   1922     pStr = (JsonString*)sqlite3_aggregate_context(ctx, sizeof(*pStr));
  1915   1923     if( pStr ){
  1916   1924       if( pStr->zBuf==0 ){
  1917   1925         jsonInit(pStr, ctx);
  1918   1926         jsonAppendChar(pStr, '{');
  1919         -    }else{
         1927  +    }else if( pStr->nUsed>1 ){
  1920   1928         jsonAppendChar(pStr, ',');
  1921   1929         pStr->pCtx = ctx;
  1922   1930       }
  1923   1931       z = (const char*)sqlite3_value_text(argv[0]);
  1924   1932       n = (u32)sqlite3_value_bytes(argv[0]);
  1925   1933       jsonAppendString(pStr, z, n);
  1926   1934       jsonAppendChar(pStr, ':');

Changes to test/windowB.test.

    79     79     " {1 9   2 {}  3 {}  4 9  5 9  6 13  7 13  8 9}
    80     80   }
    81     81   
    82     82   #-------------------------------------------------------------------------
    83     83   ifcapable json1 {
    84     84     reset_db
    85     85     do_execsql_test 3.0 {
    86         -    CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT);
    87         -    INSERT INTO testjson VALUES(1, '{"a":1}');
    88         -    INSERT INTO testjson VALUES(2, '{"b":2}');
    89         -    INSERT INTO testjson VALUES(3, '{"c":3}');
    90         -    INSERT INTO testjson VALUES(4, '{"d":4}');
           86  +    CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT, x TEXT);
           87  +    INSERT INTO testjson VALUES(1, '{"a":1}', 'a');
           88  +    INSERT INTO testjson VALUES(2, '{"b":2}', 'b');
           89  +    INSERT INTO testjson VALUES(3, '{"c":3}', 'c');
           90  +    INSERT INTO testjson VALUES(4, '{"d":4}', 'd');
    91     91     }
    92     92     
    93     93     do_execsql_test 3.1 {
    94     94       SELECT json_group_array(json(j)) FROM testjson;
    95     95     } {
    96     96       {[{"a":1},{"b":2},{"c":3},{"d":4}]}
    97     97     }
................................................................................
   103    103       {[{"a":1},{"b":2}]}
   104    104       {[{"a":1},{"b":2},{"c":3}]}
   105    105       {[{"a":1},{"b":2},{"c":3},{"d":4}]}
   106    106     }
   107    107     
   108    108     do_execsql_test 3.3 {
   109    109       SELECT json_group_array(json(j)) OVER (
   110         -      ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
          110  +      ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          111  +      EXCLUDE TIES
   111    112       ) FROM testjson;
   112    113     } {
   113    114       {[{"a":1}]}
   114    115       {[{"a":1},{"b":2}]}
   115    116       {[{"a":1},{"b":2},{"c":3}]}
   116    117       {[{"a":1},{"b":2},{"c":3},{"d":4}]}
   117    118     }
................................................................................
   134    135     } {
   135    136       {[]}
   136    137       {[{"a":1}]}
   137    138       {[{"a":1},{"b":2}]}
   138    139       {[{"b":2},{"c":3}]}
   139    140     }
   140    141     
   141         -  if 0 {
   142         -  
   143         -  do_execsql_test 3.5 {
          142  +  do_execsql_test 3.5a {
          143  +    UPDATE testjson SET j = replace(j,char(125),',"e":9'||char(125));
          144  +    SELECT j FROM testjson;
          145  +  } {
          146  +    {{"a":1,"e":9}}
          147  +    {{"b":2,"e":9}}
          148  +    {{"c":3,"e":9}}
          149  +    {{"d":4,"e":9}}
          150  +  }
          151  +  do_execsql_test 3.5b {
          152  +    SELECT group_concat(x,'') OVER (
          153  +      ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
          154  +    ) FROM testjson ORDER BY id;
          155  +  } {bc cd d {}}
          156  +  do_execsql_test 3.5c {
   144    157       SELECT json_group_array(json(j)) OVER (
   145    158         ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
   146    159       ) FROM testjson;
   147    160     } {
          161  +    {[{"b":2,"e":9},{"c":3,"e":9}]}
          162  +    {[{"c":3,"e":9},{"d":4,"e":9}]}
          163  +    {[{"d":4,"e":9}]}
   148    164       {[]}
   149         -    {[{"a":1}]}
   150         -    {[{"a":1},{"b":2}]}
   151         -    {[{"b":2},{"c":3}]}
          165  +  }
          166  +  do_execsql_test 3.5d {
          167  +    SELECT json_group_object(x,json(j)) OVER (
          168  +      ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
          169  +    ) FROM testjson;
          170  +  } {
          171  +    {{"b":{"b":2,"e":9},"c":{"c":3,"e":9}}}
          172  +    {{"c":{"c":3,"e":9},"d":{"d":4,"e":9}}}
          173  +    {{"d":{"d":4,"e":9}}}
          174  +    {{}}
   152    175     }
   153    176     
   154         -  explain_i {
   155         -    SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
   156         -      ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
   157         -    ) FROM testjson;
   158         -  }
   159         -  do_execsql_test 3.7 {
   160         -  PRAGMA vdbe_trace = 1;
   161         -    SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
          177  +  do_execsql_test 3.7b {
          178  +    SELECT group_concat(x,'') FILTER (WHERE id!=2) OVER (
   162    179         ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
   163    180       ) FROM testjson;
          181  +  } {{} a a c}
          182  +
          183  +  do_execsql_test 3.7c {
          184  +    SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
          185  +      ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
          186  +    ) FROM testjson
   164    187     } {
   165    188       {[]}
   166         -    {[{"a":1}]}
   167         -    {[{"a":1}]}
   168         -    {[{"c":3}]}
          189  +    {[{"a":1,"e":9}]}
          190  +    {[{"a":1,"e":9}]}
          191  +    {[{"c":3,"e":9}]}
   169    192     }
   170         -  
          193  +  do_execsql_test 3.7d {
          194  +    SELECT json_group_object(x,json(j)) FILTER (WHERE id!=2) OVER (
          195  +      ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
          196  +    ) FROM testjson
          197  +  } {
          198  +    {{}}
          199  +    {{"a":{"a":1,"e":9}}}
          200  +    {{"a":{"a":1,"e":9}}}
          201  +    {{"c":{"c":3,"e":9}}}
   171    202     }
   172    203   }
   173    204   
   174    205   finish_test