SQLite

Check-in [f464d847af]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: f464d847af490dd3ec45565dcc4c2e6ff4ed1ebb65036f30ca0b3ce2e73080e6
User & Date: drh 2019-09-14 00:21:34.282
Context
2019-09-14
16:21
Extra comments on fields of the Window object. (check-in: 3dbed16251 user: drh tags: trunk)
00:21
Fix the windows inverse function on the JSON aggregates. (check-in: f464d847af user: drh tags: trunk)
2019-09-13
20:42
Fix a problem with using json1 window functions with an EXCLUDE clause. (check-in: 4a1978814d user: dan tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to ext/misc/json1.c.
︙
1816
1817
1818
1819
1820
1821
1822
1823

1824
1825
1826
1827
1828
1829
1830

1816
1817
1818
1819
1820
1821
1822

1823
1824
1825
1826
1827
1828
1829
1830








-
+








  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{
    }else if( pStr->nUsed>1 ){
      jsonAppendChar(pStr, ',');
      pStr->pCtx = ctx;
    }
    jsonAppendValue(pStr, argv[0]);
  }
}
static void jsonArrayCompute(sqlite3_context *ctx, int isFinal){
︙
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

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








+

+










-
-
-
+
+
+
+
+
+

-
+

+
+
+








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; z[i]!=',' || inStr; i++){
    assert( i<pStr->nUsed );
    if( z[i]=='"' ){
  for(i=1; (c = z[i])!=',' || inStr || nNest; i++){
    if( i>=pStr->nUsed ){
      pStr->nUsed = 1;
      return;
    }
    if( c=='"' ){
      inStr = !inStr;
    }else if( z[i]=='\\' ){
    }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
︙
1912
1913
1914
1915
1916
1917
1918
1919

1920
1921
1922
1923
1924
1925
1926

1920
1921
1922
1923
1924
1925
1926

1927
1928
1929
1930
1931
1932
1933
1934








-
+








  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{
    }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
98
99
100
101
102
103
104
105
106
107
108
109
110


111
112
113
114
115
116
117

79
80
81
82
83
84
85





86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

110
111
112
113
114
115
116
117
118








-
-
-
-
-
+
+
+
+
+



















-
+
+








  " {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}');
    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}]}
  }
  
  do_execsql_test 3.2 {
    SELECT json_group_array(json(j)) OVER (ORDER BY id) FROM testjson;
  } {
    {[{"a":1}]}
    {[{"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
      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}]}
  }
︙
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

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








+
+
+
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+




+
+
+

+
+
+
+
+
-
-
-
+
+
+
+
+


-
-
+
+


+
-
-
+
+
-


-
+


-
-
-
+
+
+

-
+
+
+
+
+
+
+
+
+





  } {
    {[]}
    {[{"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;
  if 0 {
  
  do_execsql_test 3.5 {
  } {
    {{"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;
    {[{"a":1}]}
    {[{"a":1},{"b":2}]}
    {[{"b":2},{"c":3}]}
  } {
    {{"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}}}
    {{}}
  }
  
  explain_i {
    SELECT json_group_array(json(j)) FILTER (WHERE id!=2) OVER (
  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.7 {

  do_execsql_test 3.7c {
  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;
    ) FROM testjson
  } {
    {[]}
    {[{"a":1}]}
    {[{"a":1}]}
    {[{"c":3}]}
    {[{"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