Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix a corner-case bug in table-valued functions. Update the generate_series() virtual table to increase the performance estimate penalty for being underspecified. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
552bc9cb88bbe54b4cf5fdf66d1217e7 |
| User & Date: | drh 2015-08-21 17:14:48 |
Context
|
2015-08-21
| ||
| 17:39 | Reserve the SQLITE_IOERR_VNODE error code name. check-in: 53b593fc user: drh tags: trunk | |
| 17:16 | Merge in trunk fixes for table-valued functions. check-in: 67375f32 user: drh tags: json | |
| 17:14 | Fix a corner-case bug in table-valued functions. Update the generate_series() virtual table to increase the performance estimate penalty for being underspecified. check-in: 552bc9cb user: drh tags: trunk | |
| 12:37 | Fix typo in comment. No changes to code. check-in: 7b8d17dd user: drh tags: trunk | |
Changes
Changes to ext/misc/series.c.
330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 |
pIdxInfo->aConstraintUsage[stopIdx].argvIndex = ++nArg;
pIdxInfo->aConstraintUsage[stopIdx].omit = 1;
}
if( stepIdx>=0 ){
pIdxInfo->aConstraintUsage[stepIdx].argvIndex = ++nArg;
pIdxInfo->aConstraintUsage[stepIdx].omit = 1;
}
if( pIdxInfo->nOrderBy==1 ){
if( pIdxInfo->aOrderBy[0].desc ) idxNum |= 8;
pIdxInfo->orderByConsumed = 1;
}
if( (idxNum & 3)==3 ){
/* Both start= and stop= boundaries are available. This is the
** the preferred case */
pIdxInfo->estimatedCost = (double)1;
}else{
/* If either boundary is missing, we have to generate a huge span
** of numbers. Make this case very expensive so that the query
** planner will work hard to avoid it. */
pIdxInfo->estimatedCost = (double)2000000000;
}
pIdxInfo->idxNum = idxNum;
return SQLITE_OK;
}
/*
** This following structure defines all the methods for the
|
< < < < > > > > > | > |
330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 |
pIdxInfo->aConstraintUsage[stopIdx].argvIndex = ++nArg;
pIdxInfo->aConstraintUsage[stopIdx].omit = 1;
}
if( stepIdx>=0 ){
pIdxInfo->aConstraintUsage[stepIdx].argvIndex = ++nArg;
pIdxInfo->aConstraintUsage[stepIdx].omit = 1;
}
if( (idxNum & 3)==3 ){
/* Both start= and stop= boundaries are available. This is the
** the preferred case */
pIdxInfo->estimatedCost = (double)1;
pIdxInfo->estimatedRows = 1000;
if( pIdxInfo->nOrderBy==1 ){
if( pIdxInfo->aOrderBy[0].desc ) idxNum |= 8;
pIdxInfo->orderByConsumed = 1;
}
}else{
/* If either boundary is missing, we have to generate a huge span
** of numbers. Make this case very expensive so that the query
** planner will work hard to avoid it. */
pIdxInfo->estimatedCost = (double)2147483647;
pIdxInfo->estimatedRows = 2147483647;
}
pIdxInfo->idxNum = idxNum;
return SQLITE_OK;
}
/*
** This following structure defines all the methods for the
|
Changes to src/whereexpr.c.
1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 |
pTab->zName, j);
return;
}
pColRef = sqlite3PExpr(pParse, TK_COLUMN, 0, 0, 0);
if( pColRef==0 ) return;
pColRef->iTable = pItem->iCursor;
pColRef->iColumn = k++;
pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef,
sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0), 0);
whereClauseInsert(pWC, pTerm, TERM_DYNAMIC);
}
}
|
> |
1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 |
pTab->zName, j);
return;
}
pColRef = sqlite3PExpr(pParse, TK_COLUMN, 0, 0, 0);
if( pColRef==0 ) return;
pColRef->iTable = pItem->iCursor;
pColRef->iColumn = k++;
pColRef->pTab = pTab;
pTerm = sqlite3PExpr(pParse, TK_EQ, pColRef,
sqlite3ExprDup(pParse->db, pArgs->a[j].pExpr, 0), 0);
whereClauseInsert(pWC, pTerm, TERM_DYNAMIC);
}
}
|
Changes to test/tabfunc01.test.
60 61 62 63 64 65 66 67 68 69 |
INSERT INTO t1(x) VALUES(2),(3);
SELECT *, '|' FROM t1, generate_series(1,x) ORDER BY 1, 2
} {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}
do_execsql_test tabfunc01-2.2 {
SELECT * FROM generate_series() LIMIT 5;
} {0 1 2 3 4}
finish_test
|
> > > > |
60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
INSERT INTO t1(x) VALUES(2),(3);
SELECT *, '|' FROM t1, generate_series(1,x) ORDER BY 1, 2
} {2 1 | 2 2 | 3 1 | 3 2 | 3 3 |}
do_execsql_test tabfunc01-2.2 {
SELECT * FROM generate_series() LIMIT 5;
} {0 1 2 3 4}
do_execsql_test tabfunc01-3.1 {
SELECT DISTINCT value FROM generate_series(1,x), t1 ORDER BY 1;
} {1 2 3}
finish_test
|