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