Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a bug in the lead() and lag() window functions causing them to fail when used in queries featuring multiple window functions. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | exp-window-functions |
Files: | files | file ages | folders |
SHA3-256: |
3839fb18f917e4f705821198d624b19d |
User & Date: | dan 2018-06-15 16:10:44.916 |
Context
2018-06-15
| ||
19:01 | Fix another problem in lead()/lag(). And some errors that could occur following OOM faults. (check-in: fadd4dc119 user: dan tags: exp-window-functions) | |
16:10 | Fix a bug in the lead() and lag() window functions causing them to fail when used in queries featuring multiple window functions. (check-in: 3839fb18f9 user: dan tags: exp-window-functions) | |
2018-06-14
| ||
20:52 | Fix a problem with handling of statements containing two or more different windows. (check-in: 567e09ef2a user: dan tags: exp-window-functions) | |
Changes
Changes to src/window.c.
︙ | ︙ | |||
1104 1105 1106 1107 1108 1109 1110 | sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, nSub, regRecord); /* Check if this is the start of a new partition. If so, call the ** flush_partition sub-routine. */ if( pMWin->pPartition ){ int addr; ExprList *pPart = pMWin->pPartition; | | | 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 | sqlite3VdbeAddOp3(v, OP_MakeRecord, reg, nSub, regRecord); /* Check if this is the start of a new partition. If so, call the ** flush_partition sub-routine. */ if( pMWin->pPartition ){ int addr; ExprList *pPart = pMWin->pPartition; int nPart = pPart->nExpr; int regNewPart = reg + pMWin->nBufferCol; KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0); addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart,nPart); sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO); sqlite3VdbeAddOp3(v, OP_Jump, addr+2, addr+4, addr+2); sqlite3VdbeAddOp3(v, OP_Copy, regNewPart, pMWin->regPart, nPart-1); |
︙ | ︙ | |||
1171 1172 1173 1174 1175 1176 1177 | sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, lbl, tmpReg); sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult); sqlite3VdbeResolveLabel(v, lbl); sqlite3ReleaseTempReg(pParse, tmpReg); } else if( pFunc->xSFunc==leadStepFunc || pFunc->xSFunc==lagStepFunc ){ int nArg = pWin->pOwner->x.pList->nExpr; | | | 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 | sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, lbl, tmpReg); sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol, pWin->regResult); sqlite3VdbeResolveLabel(v, lbl); sqlite3ReleaseTempReg(pParse, tmpReg); } else if( pFunc->xSFunc==leadStepFunc || pFunc->xSFunc==lagStepFunc ){ int nArg = pWin->pOwner->x.pList->nExpr; int iEph = pMWin->iEphCsr; int csr = pWin->csrApp; int lbl = sqlite3VdbeMakeLabel(v); int tmpReg = sqlite3GetTempReg(pParse); if( nArg<3 ){ sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult); }else{ |
︙ | ︙ |
Changes to test/window1.test.
︙ | ︙ | |||
246 247 248 249 250 251 252 | } {1 {misuse of window function nth_value()}} do_catchsql_test 7.1.5 { SELECT count(*) FROM t1 LIMIT nth_value(x, 1) OVER (ORDER BY y); } {1 {no such column: x}} do_catchsql_test 7.1.6 { SELECT trim(x) OVER (ORDER BY y) FROM t1; } {1 {trim() may not be used as a window function}} | < > > > > > > > > > > > | 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 | } {1 {misuse of window function nth_value()}} do_catchsql_test 7.1.5 { SELECT count(*) FROM t1 LIMIT nth_value(x, 1) OVER (ORDER BY y); } {1 {no such column: x}} do_catchsql_test 7.1.6 { SELECT trim(x) OVER (ORDER BY y) FROM t1; } {1 {trim() may not be used as a window function}} do_catchsql_test 7.1.7 { SELECT max(x) OVER abc FROM t1 WINDOW def AS (ORDER BY y); } {1 {no such window: abc}} do_execsql_test 7.2 { SELECT lead(y) OVER win, lead(y, 2) OVER win, lead(y, 3, 'default') OVER win FROM t1 WINDOW win AS (ORDER BY x) } { 4 6 8 6 8 10 8 10 default 10 {} default {} {} default } finish_test |
Changes to test/window4.tcl.
︙ | ︙ | |||
223 224 225 226 227 228 229 230 231 232 233 | set p1 [lindex $lPart 0] set p2 [lindex $lPart 0] foreach o1 $lOrder { foreach o2 $lOrder { execsql_test 4.5.$tn.1 [subst $SQL] execsql_test 4.5.$tn.2 [subst $SQL2] incr tn }} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 | set p1 [lindex $lPart 0] set p2 [lindex $lPart 0] foreach o1 $lOrder { foreach o2 $lOrder { execsql_test 4.5.$tn.1 [subst $SQL] execsql_test 4.5.$tn.2 [subst $SQL2] incr tn }} ========== execsql_test 7.0 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(x INTEGER, y INTEGER); INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(3, 4); INSERT INTO t1 VALUES(5, 6); INSERT INTO t1 VALUES(7, 8); INSERT INTO t1 VALUES(9, 10); } execsql_test 7.1 { SELECT lead(y) OVER win FROM t1 WINDOW win AS (ORDER BY x) } execsql_test 7.2 { SELECT lead(y, 2) OVER win FROM t1 WINDOW win AS (ORDER BY x) } execsql_test 7.3 { SELECT lead(y, 3, -1) OVER win FROM t1 WINDOW win AS (ORDER BY x) } execsql_test 7.4 { SELECT lead(y) OVER win, lead(y) OVER win FROM t1 WINDOW win AS (ORDER BY x) } execsql_test 7.5 { SELECT lead(y) OVER win, lead(y, 2) OVER win, lead(y, 3, -1) OVER win FROM t1 WINDOW win AS (ORDER BY x) } finish_test |
Changes to test/window4.test.
︙ | ︙ | |||
1118 1119 1120 1121 1122 1123 1124 1125 1126 | } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} do_execsql_test 4.5.73.2 { SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 | } {1 1 2 2 3 3 2 1 3 2 4 3 3 1 4 2 5 3} do_execsql_test 4.5.73.2 { SELECT sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), sum(c) OVER (PARTITION BY b ORDER BY b, a RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ttt ORDER BY a } {1 1 2 2 3 3 3 3 5 5 7 7 6 6 9 9 12 12} #========================================================================== do_execsql_test 7.0 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(x INTEGER, y INTEGER); INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(3, 4); INSERT INTO t1 VALUES(5, 6); INSERT INTO t1 VALUES(7, 8); INSERT INTO t1 VALUES(9, 10); } {} do_execsql_test 7.1 { SELECT lead(y) OVER win FROM t1 WINDOW win AS (ORDER BY x) } {4 6 8 10 {}} do_execsql_test 7.2 { SELECT lead(y, 2) OVER win FROM t1 WINDOW win AS (ORDER BY x) } {6 8 10 {} {}} do_execsql_test 7.3 { SELECT lead(y, 3, -1) OVER win FROM t1 WINDOW win AS (ORDER BY x) } {8 10 -1 -1 -1} do_execsql_test 7.4 { SELECT lead(y) OVER win, lead(y) OVER win FROM t1 WINDOW win AS (ORDER BY x) } {4 4 6 6 8 8 10 10 {} {}} do_execsql_test 7.5 { SELECT lead(y) OVER win, lead(y, 2) OVER win, lead(y, 3, -1) OVER win FROM t1 WINDOW win AS (ORDER BY x) } {4 6 8 6 8 10 8 10 -1 10 {} -1 {} {} -1} finish_test |