/ Check-in [f464d847]
Login

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 Unified Diffs Ignore Whitespace Patch

Changes to ext/misc/json1.c.

1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
....
1866
1867
1868
1869
1870
1871
1872

1873

1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885



1886
1887
1888
1889



1890
1891
1892
1893
1894
1895
1896
....
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
  JsonString *pStr;
  UNUSED_PARAM(argc);
  pStr = (JsonString*)sqlite3_aggregate_context(ctx, sizeof(*pStr));
  if( pStr ){
    if( pStr->zBuf==0 ){
      jsonInit(pStr, ctx);
      jsonAppendChar(pStr, '[');
    }else{
      jsonAppendChar(pStr, ',');
      pStr->pCtx = ctx;
    }
    jsonAppendValue(pStr, argv[0]);
  }
}
static void jsonArrayCompute(sqlite3_context *ctx, int isFinal){
................................................................................
static void jsonGroupInverse(
  sqlite3_context *ctx,
  int argc,
  sqlite3_value **argv
){
  int i;
  int inStr = 0;

  char *z;

  JsonString *pStr;
  UNUSED_PARAM(argc);
  UNUSED_PARAM(argv);
  pStr = (JsonString*)sqlite3_aggregate_context(ctx, 0);
#ifdef NEVER
  /* pStr is always non-NULL since jsonArrayStep() or jsonObjectStep() will
  ** always have been called to initalize it */
  if( NEVER(!pStr) ) return;
#endif
  z = pStr->zBuf;
  for(i=1; z[i]!=',' || inStr; i++){
    assert( i<pStr->nUsed );



    if( z[i]=='"' ){
      inStr = !inStr;
    }else if( z[i]=='\\' ){
      i++;



    }
  }
  pStr->nUsed -= i;      
  memmove(&z[1], &z[i+1], (size_t)pStr->nUsed-1);
}
#else
# define jsonGroupInverse 0
................................................................................
  u32 n;
  UNUSED_PARAM(argc);
  pStr = (JsonString*)sqlite3_aggregate_context(ctx, sizeof(*pStr));
  if( pStr ){
    if( pStr->zBuf==0 ){
      jsonInit(pStr, ctx);
      jsonAppendChar(pStr, '{');
    }else{
      jsonAppendChar(pStr, ',');
      pStr->pCtx = ctx;
    }
    z = (const char*)sqlite3_value_text(argv[0]);
    n = (u32)sqlite3_value_bytes(argv[0]);
    jsonAppendString(pStr, z, n);
    jsonAppendChar(pStr, ':');







|







 







>

>










|
|
>
>
>
|

|

>
>
>







 







|







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

Changes to test/windowB.test.

79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
...
103
104
105
106
107
108
109
110

111
112
113
114
115
116
117
...
134
135
136
137
138
139
140
141







142
143





144
145
146
147



148
149
150
151
152









153
154


155
156
157

158
159
160
161
162
163
164
165
166
167
168
169
170









171
172
173
174
  " {1 9   2 {}  3 {}  4 9  5 9  6 13  7 13  8 9}
}

#-------------------------------------------------------------------------
ifcapable json1 {
  reset_db
  do_execsql_test 3.0 {
    CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT);
    INSERT INTO testjson VALUES(1, '{"a":1}');
    INSERT INTO testjson VALUES(2, '{"b":2}');
    INSERT INTO testjson VALUES(3, '{"c":3}');
    INSERT INTO testjson VALUES(4, '{"d":4}');
  }
  
  do_execsql_test 3.1 {
    SELECT json_group_array(json(j)) FROM testjson;
  } {
    {[{"a":1},{"b":2},{"c":3},{"d":4}]}
  }
................................................................................
    {[{"a":1},{"b":2}]}
    {[{"a":1},{"b":2},{"c":3}]}
    {[{"a":1},{"b":2},{"c":3},{"d":4}]}
  }
  
  do_execsql_test 3.3 {
    SELECT json_group_array(json(j)) OVER (
      ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES

    ) FROM testjson;
  } {
    {[{"a":1}]}
    {[{"a":1},{"b":2}]}
    {[{"a":1},{"b":2},{"c":3}]}
    {[{"a":1},{"b":2},{"c":3},{"d":4}]}
  }
................................................................................
  } {
    {[]}
    {[{"a":1}]}
    {[{"a":1},{"b":2}]}
    {[{"b":2},{"c":3}]}
  }
  
  if 0 {







  
  do_execsql_test 3.5 {





    SELECT json_group_array(json(j)) OVER (
      ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
    ) FROM testjson;
  } {



    {[]}
    {[{"a":1}]}
    {[{"a":1},{"b":2}]}
    {[{"b":2},{"c":3}]}
  }









  
  explain_i {


    SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
      ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
    ) FROM testjson;

  }
  do_execsql_test 3.7 {
  PRAGMA vdbe_trace = 1;
    SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
      ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
    ) FROM testjson;
  } {
    {[]}
    {[{"a":1}]}
    {[{"a":1}]}
    {[{"c":3}]}
  }
  









  }
}

finish_test







|
|
|
|
|







 







|
>







 







|
>
>
>
>
>
>
>
|
|
>
>
>
>
>




>
>
>

<
<
<

>
>
>
>
>
>
>
>
>
|
<
>
>
|


>
|
|
<


|


|
|
|

<
>
>
>
>
>
>
>
>
>




79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
...
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
...
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164



165
166
167
168
169
170
171
172
173
174
175

176
177
178
179
180
181
182
183

184
185
186
187
188
189
190
191
192

193
194
195
196
197
198
199
200
201
202
203
204
205
  " {1 9   2 {}  3 {}  4 9  5 9  6 13  7 13  8 9}
}

#-------------------------------------------------------------------------
ifcapable json1 {
  reset_db
  do_execsql_test 3.0 {
    CREATE TABLE testjson(id INTEGER PRIMARY KEY, j TEXT, x TEXT);
    INSERT INTO testjson VALUES(1, '{"a":1}', 'a');
    INSERT INTO testjson VALUES(2, '{"b":2}', 'b');
    INSERT INTO testjson VALUES(3, '{"c":3}', 'c');
    INSERT INTO testjson VALUES(4, '{"d":4}', 'd');
  }
  
  do_execsql_test 3.1 {
    SELECT json_group_array(json(j)) FROM testjson;
  } {
    {[{"a":1},{"b":2},{"c":3},{"d":4}]}
  }
................................................................................
    {[{"a":1},{"b":2}]}
    {[{"a":1},{"b":2},{"c":3}]}
    {[{"a":1},{"b":2},{"c":3},{"d":4}]}
  }
  
  do_execsql_test 3.3 {
    SELECT json_group_array(json(j)) OVER (
      ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      EXCLUDE TIES
    ) FROM testjson;
  } {
    {[{"a":1}]}
    {[{"a":1},{"b":2}]}
    {[{"a":1},{"b":2},{"c":3}]}
    {[{"a":1},{"b":2},{"c":3},{"d":4}]}
  }
................................................................................
  } {
    {[]}
    {[{"a":1}]}
    {[{"a":1},{"b":2}]}
    {[{"b":2},{"c":3}]}
  }
  
  do_execsql_test 3.5a {
    UPDATE testjson SET j = replace(j,char(125),',"e":9'||char(125));
    SELECT j FROM testjson;
  } {
    {{"a":1,"e":9}}
    {{"b":2,"e":9}}
    {{"c":3,"e":9}}
    {{"d":4,"e":9}}
  }
  do_execsql_test 3.5b {
    SELECT group_concat(x,'') OVER (
      ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
    ) FROM testjson ORDER BY id;
  } {bc cd d {}}
  do_execsql_test 3.5c {
    SELECT json_group_array(json(j)) OVER (
      ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
    ) FROM testjson;
  } {
    {[{"b":2,"e":9},{"c":3,"e":9}]}
    {[{"c":3,"e":9},{"d":4,"e":9}]}
    {[{"d":4,"e":9}]}
    {[]}



  }
  do_execsql_test 3.5d {
    SELECT json_group_object(x,json(j)) OVER (
      ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
    ) FROM testjson;
  } {
    {{"b":{"b":2,"e":9},"c":{"c":3,"e":9}}}
    {{"c":{"c":3,"e":9},"d":{"d":4,"e":9}}}
    {{"d":{"d":4,"e":9}}}
    {{}}
  }

  
  do_execsql_test 3.7b {
    SELECT group_concat(x,'') FILTER (WHERE id!=2) OVER (
      ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
    ) FROM testjson;
  } {{} a a c}

  do_execsql_test 3.7c {

    SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
      ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
    ) FROM testjson
  } {
    {[]}
    {[{"a":1,"e":9}]}
    {[{"a":1,"e":9}]}
    {[{"c":3,"e":9}]}
  }

  do_execsql_test 3.7d {
    SELECT json_group_object(x,json(j)) FILTER (WHERE id!=2) OVER (
      ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
    ) FROM testjson
  } {
    {{}}
    {{"a":{"a":1,"e":9}}}
    {{"a":{"a":1,"e":9}}}
    {{"c":{"c":3,"e":9}}}
  }
}

finish_test