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: |
f464d847af490dd3ec45565dcc4c2e6f |
| 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
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
|