Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem with the handling of NULL values in the min() window function. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: | b76f35b09235d44dc3d176377bbb9c18 |
| User & Date: | dan 2018-07-07 17:30:44 |
Context
|
2018-07-07
| ||
| 17:38 | Add missing VdbeCoverage() macro to window.c. check-in: 63f4d306 user: dan tags: trunk | |
| 17:30 | Fix a problem with the handling of NULL values in the min() window function. check-in: b76f35b0 user: dan tags: trunk | |
|
2018-07-06
| ||
| 17:19 | Try to improve the error messages for misformed frame specifications in window definitions. check-in: 927b95a0 user: drh tags: trunk | |
Changes
Changes to src/window.c.
1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 |
assert( !(flags & SQLITE_FUNC_WINDOW_SIZE) );
regArg = reg + pWin->iArgCol;
}
if( (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX)
&& pWin->eStart!=TK_UNBOUNDED
){
if( bInverse==0 ){
sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1, 1);
sqlite3VdbeAddOp2(v, OP_SCopy, regArg, pWin->regApp);
sqlite3VdbeAddOp3(v, OP_MakeRecord, pWin->regApp, 2, pWin->regApp+2);
sqlite3VdbeAddOp2(v, OP_IdxInsert, pWin->csrApp, pWin->regApp+2);
}else{
sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1);
VdbeCoverage(v);
sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp);
sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
}
}else if( pWin->regApp ){
assert( pWin->pFunc->xSFunc==nth_valueStepFunc
|| pWin->pFunc->xSFunc==first_valueStepFunc
);
assert( bInverse==0 || bInverse==1 );
sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1);
}else if( pWin->pFunc->xSFunc==leadStepFunc
|
> > > |
1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 |
assert( !(flags & SQLITE_FUNC_WINDOW_SIZE) );
regArg = reg + pWin->iArgCol;
}
if( (pWin->pFunc->funcFlags & SQLITE_FUNC_MINMAX)
&& pWin->eStart!=TK_UNBOUNDED
){
int addrIsNull = sqlite3VdbeAddOp1(v, OP_IsNull, regArg);
VdbeCoverage(v);
if( bInverse==0 ){
sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1, 1);
sqlite3VdbeAddOp2(v, OP_SCopy, regArg, pWin->regApp);
sqlite3VdbeAddOp3(v, OP_MakeRecord, pWin->regApp, 2, pWin->regApp+2);
sqlite3VdbeAddOp2(v, OP_IdxInsert, pWin->csrApp, pWin->regApp+2);
}else{
sqlite3VdbeAddOp4Int(v, OP_SeekGE, pWin->csrApp, 0, regArg, 1);
VdbeCoverage(v);
sqlite3VdbeAddOp1(v, OP_Delete, pWin->csrApp);
sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
}
sqlite3VdbeJumpHere(v, addrIsNull);
}else if( pWin->regApp ){
assert( pWin->pFunc->xSFunc==nth_valueStepFunc
|| pWin->pFunc->xSFunc==first_valueStepFunc
);
assert( bInverse==0 || bInverse==1 );
sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1);
}else if( pWin->pFunc->xSFunc==leadStepFunc
|
Changes to test/window4.tcl.
335 336 337 338 339 340 341 342 343 344 |
execsql_float_test 9.6 {
SELECT percent_rank() OVER () FROM t1
}
execsql_float_test 9.7 {
SELECT cume_dist() OVER () FROM t1
}
finish_test
|
> > > > > > > > > > > > |
335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 |
execsql_float_test 9.6 {
SELECT percent_rank() OVER () FROM t1
}
execsql_float_test 9.7 {
SELECT cume_dist() OVER () FROM t1
}
execsql_test 10.0 {
DROP TABLE IF EXISTS t7;
CREATE TABLE t7(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
INSERT INTO t7(id, a, b) VALUES
(1, 1, 2), (2, 1, NULL), (3, 1, 4),
(4, 3, NULL), (5, 3, 8), (6, 3, 1);
}
execsql_test 10.1 {
SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
}
finish_test
|
Changes to test/window4.test.
1245 1246 1247 1248 1249 1250 1251 1252 |
set myres {}
foreach r [db eval {SELECT cume_dist() OVER () FROM t1}] {
lappend myres [format %.2f [set r]]
}
set myres
} {1.00 1.00 1.00}
finish_test
|
> > > > > > > > > > > > |
1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 |
set myres {}
foreach r [db eval {SELECT cume_dist() OVER () FROM t1}] {
lappend myres [format %.2f [set r]]
}
set myres
} {1.00 1.00 1.00}
do_execsql_test 10.0 {
DROP TABLE IF EXISTS t7;
CREATE TABLE t7(id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
INSERT INTO t7(id, a, b) VALUES
(1, 1, 2), (2, 1, NULL), (3, 1, 4),
(4, 3, NULL), (5, 3, 8), (6, 3, 1);
} {}
do_execsql_test 10.1 {
SELECT id, min(b) OVER (PARTITION BY a ORDER BY id) FROM t7;
} {1 2 2 2 3 2 4 {} 5 8 6 1}
finish_test
|