Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a problem with views that use window functions as part of complex expressions. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
507d892c3a40a0bacbd47ed3c4fe2d89 |
| User & Date: | dan 2018-09-24 14:51:59 |
References
|
2018-09-24
| ||
| 15:39 | Slightly smaller and faster alternative to [507d892c3a40a0bacbd47] that fixes a problem with views that use window function as part of complex expressions. check-in: d7c816ae user: drh tags: trunk | |
Context
|
2018-09-24
| ||
| 18:29 | Fix a problem with views that use window functions as part of complex expressions. check-in: 1c0ecbbd user: drh tags: branch-3.25 | |
| 15:39 | Slightly smaller and faster alternative to [507d892c3a40a0bacbd47] that fixes a problem with views that use window function as part of complex expressions. check-in: d7c816ae user: drh tags: trunk | |
| 14:51 | Fix a problem with views that use window functions as part of complex expressions. check-in: 507d892c user: dan tags: trunk | |
| 14:10 | Allow a writable virtual table to have a schema with an INTEGER PRIMARY KEY and WITHOUT ROWID. This fixes ticket [f25d5ceebe1d710ff61a571e395356869d8272ef]. Test case in TH3. check-in: 5a38d928 user: drh tags: trunk | |
Changes
Changes to src/expr.c.
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
....
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
|
pNew->x.pSelect = sqlite3SelectDup(db, p->x.pSelect, dupFlags);
}else{
pNew->x.pList = sqlite3ExprListDup(db, p->x.pList, dupFlags);
}
}
/* Fill in pNew->pLeft and pNew->pRight. */
if( ExprHasProperty(pNew, EP_Reduced|EP_TokenOnly) ){
zAlloc += dupedExprNodeSize(p, dupFlags);
if( !ExprHasProperty(pNew, EP_TokenOnly|EP_Leaf) ){
pNew->pLeft = p->pLeft ?
exprDup(db, p->pLeft, EXPRDUP_REDUCE, &zAlloc) : 0;
pNew->pRight = p->pRight ?
exprDup(db, p->pRight, EXPRDUP_REDUCE, &zAlloc) : 0;
}
if( pzBuffer ){
*pzBuffer = zAlloc;
}
}else{
#ifndef SQLITE_OMIT_WINDOWFUNC
if( ExprHasProperty(p, EP_WinFunc) ){
pNew->y.pWin = sqlite3WindowDup(db, pNew, p->y.pWin);
assert( ExprHasProperty(pNew, EP_WinFunc) );
}
#endif /* SQLITE_OMIT_WINDOWFUNC */
................................................................................
assert( p->pRight==0 || p->pRight==p->pLeft );
}else{
pNew->pLeft = sqlite3ExprDup(db, p->pLeft, 0);
}
pNew->pRight = sqlite3ExprDup(db, p->pRight, 0);
}
}
}
return pNew;
}
/*
** Create and return a deep copy of the object passed as the second
** argument. If an OOM condition is encountered, NULL is returned
|
<
|
>
<
<
<
>
>
>
|
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
....
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
|
pNew->x.pSelect = sqlite3SelectDup(db, p->x.pSelect, dupFlags);
}else{
pNew->x.pList = sqlite3ExprListDup(db, p->x.pList, dupFlags);
}
}
/* Fill in pNew->pLeft and pNew->pRight. */
zAlloc += dupedExprNodeSize(p, dupFlags);
if( ExprHasProperty(pNew, EP_Reduced|EP_TokenOnly) ){
if( !ExprHasProperty(pNew, EP_TokenOnly|EP_Leaf) ){
pNew->pLeft = p->pLeft ?
exprDup(db, p->pLeft, EXPRDUP_REDUCE, &zAlloc) : 0;
pNew->pRight = p->pRight ?
exprDup(db, p->pRight, EXPRDUP_REDUCE, &zAlloc) : 0;
}
}else{
#ifndef SQLITE_OMIT_WINDOWFUNC
if( ExprHasProperty(p, EP_WinFunc) ){
pNew->y.pWin = sqlite3WindowDup(db, pNew, p->y.pWin);
assert( ExprHasProperty(pNew, EP_WinFunc) );
}
#endif /* SQLITE_OMIT_WINDOWFUNC */
................................................................................
assert( p->pRight==0 || p->pRight==p->pLeft );
}else{
pNew->pLeft = sqlite3ExprDup(db, p->pLeft, 0);
}
pNew->pRight = sqlite3ExprDup(db, p->pRight, 0);
}
}
if( pzBuffer ){
*pzBuffer = zAlloc;
}
}
return pNew;
}
/*
** Create and return a deep copy of the object passed as the second
** argument. If an OOM condition is encountered, NULL is returned
|
Changes to test/window4.tcl.
354 355 356 357 358 359 360 361 362 363 364 |
execsql_test 10.2 {
SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
}
execsql_test 10.3 {
SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > |
354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 |
execsql_test 10.2 {
SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
}
execsql_test 10.3 {
SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
}
execsql_test 11.0 {
DROP VIEW IF EXISTS v8;
DROP TABLE IF EXISTS t8;
CREATE TABLE t8(t INT, total INT);
INSERT INTO t8 VALUES(0,2);
INSERT INTO t8 VALUES(5,1);
INSERT INTO t8 VALUES(10,1);
}
execsql_test 11.1 {
SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
}
execsql_test 11.2 {
CREATE VIEW v8 AS SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
}
execsql_test 11.3 {
SELECT * FROM v8;
}
execsql_test 11.4 {
SELECT * FROM (
SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8
) sub;
}
finish_test
|
Changes to test/window4.test.
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
....
1264
1265
1266
1267
1268
1269
1270
1271
1272
|
do_execsql_test 9.1 {
SELECT rank() OVER () FROM t2
} {1 1 1 1 1 1 1}
do_execsql_test 9.2 {
SELECT dense_rank() OVER (PARTITION BY x) FROM t2
} {1 1 1 1 1 1 1}
do_test 9.3 {
set myres {}
foreach r [db eval {SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2}] {
lappend myres [format %.2f [set r]]
}
set myres
} {1.00 0.00 1.00 0.00 1.00 0.00 4.00 0.00 4.00 0.00 6.00 0.00 7.00 0.00}
do_execsql_test 9.4 {
SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
} {1 1 1 1 1 1 4 4 4 4 6 6 7 7}
do_execsql_test 9.5 {
SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
} {1 1 4 4 6 6 7 7}
do_test 9.6 {
set myres {}
foreach r [db eval {SELECT percent_rank() OVER () FROM t1}] {
lappend myres [format %.2f [set r]]
}
set myres
} {0.00 0.00 0.00}
do_test 9.7 {
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.2 {
SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
} {1 {} 2 2 3 {} 4 {} 5 {} 6 8}
do_execsql_test 10.3 {
SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
} {1 {} 2 4 3 {} 4 8 5 1 6 {}}
finish_test
|
>
|
>
>
>
>
>
>
|
<
>
>
|
>
>
>
>
>
>
|
<
>
>
|
>
>
>
>
>
>
|
<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
....
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
|
do_execsql_test 9.1 {
SELECT rank() OVER () FROM t2
} {1 1 1 1 1 1 1}
do_execsql_test 9.2 {
SELECT dense_rank() OVER (PARTITION BY x) FROM t2
} {1 1 1 1 1 1 1}
do_test 9.3 {
set myres {}
foreach r [db eval {SELECT x, percent_rank() OVER (PARTITION BY x ORDER BY x) FROM t2}] {
lappend myres [format %.4f [set r]]
}
set res2 {1.0000 0.0000 1.0000 0.0000 1.0000 0.0000 4.0000 0.0000 4.0000 0.0000 6.0000 0.0000 7.0000 0.0000}
foreach r [set myres] r2 [set res2] {
if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
error "list element [set i] does not match: got=[set r] expected=[set r2]"
}
}
set {} {}
} {}
do_execsql_test 9.4 {
SELECT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
} {1 1 1 1 1 1 4 4 4 4 6 6 7 7}
do_execsql_test 9.5 {
SELECT DISTINCT x, rank() OVER (ORDER BY x) FROM t2 ORDER BY 1,2
} {1 1 4 4 6 6 7 7}
do_test 9.6 {
set myres {}
foreach r [db eval {SELECT percent_rank() OVER () FROM t1}] {
lappend myres [format %.4f [set r]]
}
set res2 {0.0000 0.0000 0.0000}
foreach r [set myres] r2 [set res2] {
if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
error "list element [set i] does not match: got=[set r] expected=[set r2]"
}
}
set {} {}
} {}
do_test 9.7 {
set myres {}
foreach r [db eval {SELECT cume_dist() OVER () FROM t1}] {
lappend myres [format %.4f [set r]]
}
set res2 {1.0000 1.0000 1.0000}
foreach r [set myres] r2 [set res2] {
if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} {
error "list element [set i] does not match: got=[set r] expected=[set r2]"
}
}
set {} {}
} {}
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.2 {
SELECT id, lead(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
} {1 {} 2 2 3 {} 4 {} 5 {} 6 8}
do_execsql_test 10.3 {
SELECT id, lag(b, -1) OVER (PARTITION BY a ORDER BY id) FROM t7;
} {1 {} 2 4 3 {} 4 8 5 1 6 {}}
do_execsql_test 11.0 {
DROP VIEW IF EXISTS v8;
DROP TABLE IF EXISTS t8;
CREATE TABLE t8(t INT, total INT);
INSERT INTO t8 VALUES(0,2);
INSERT INTO t8 VALUES(5,1);
INSERT INTO t8 VALUES(10,1);
} {}
do_execsql_test 11.1 {
SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
} {0 1 2}
do_execsql_test 11.2 {
CREATE VIEW v8 AS SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8;
} {}
do_execsql_test 11.3 {
SELECT * FROM v8;
} {0 1 2}
do_execsql_test 11.4 {
SELECT * FROM (
SELECT NTILE(256) OVER (ORDER BY total) - 1 AS nt FROM t8
) sub;
} {0 1 2}
finish_test
|