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 | SQL 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 |
Context
|
2018-06-15
| ||
| 19:01 | Fix another problem in lead()/lag(). And some errors that could occur following OOM faults. (check-in: fadd4dc1 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: 3839fb18 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: 567e09ef user: dan tags: exp-window-functions) | |
Changes
Changes to src/window.c.
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
....
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
|
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 ? pPart->nExpr : 0);
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);
................................................................................
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 = pWin->iEphCsr;
int csr = pWin->csrApp;
int lbl = sqlite3VdbeMakeLabel(v);
int tmpReg = sqlite3GetTempReg(pParse);
if( nArg<3 ){
sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
}else{
|
|
|
|
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
....
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
|
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);
................................................................................
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 253 254 255 256 257 258 259 260 |
} {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}}
finish_test
|
< > > > > > > > > > > > |
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
|