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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
b76f35b09235d44dc3d176377bbb9c18 |
User & Date: | dan 2018-07-07 17:30:44.852 |
Context
2018-07-07
| ||
17:38 | Add missing VdbeCoverage() macro to window.c. (check-in: 63f4d306ba user: dan tags: trunk) | |
17:30 | Fix a problem with the handling of NULL values in the min() window function. (check-in: b76f35b092 user: dan tags: trunk) | |
2018-07-06
| ||
17:19 | Try to improve the error messages for misformed frame specifications in window definitions. (check-in: 927b95a081 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 |