Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | Fix a problem with "EXPLAIN QUERY PLAN SELECT count(*) FROM tbl". |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
9f9f32882501ac9b6e60f81195a64bdb |
| User & Date: | dan 2011-02-21 17:49:49 |
|
2011-04-15
| ||
| 15:18 | Backport check-in [9f9f32882501ac9] to provide EXPLAIN QUERY PLAN output for the count(*) optimization. Also backport check-in [a8761a9128de945aa] to prevent unordered indices from being used on a full table scan. The first backport was necessary in order to test the second. (check-in: 8d924e16 user: drh tags: branch-3.7.2) | |
|
2011-02-22
| ||
| 03:34 | When a stale schema-cookie is seen, expire only the one statement that encountered the bad cookie, not every statement on the database connection. Ticket [b72787b1a7cea1f] (check-in: 1bca0a7e user: drh tags: trunk) | |
|
2011-02-21
| ||
| 17:49 | Fix a problem with "EXPLAIN QUERY PLAN SELECT count(*) FROM tbl". (check-in: 9f9f3288 user: dan tags: trunk) | |
| 11:46 | Have os_unix.c call sqlite3_log() following errors in certain system calls. (check-in: 01076528 user: dan tags: trunk) | |
install-sh became a regular file.
Changes to src/select.c.
3614 3614 for(i=0, pC=pAggInfo->aCol; i<pAggInfo->nAccumulator; i++, pC++){ 3615 3615 sqlite3ExprCode(pParse, pC->pExpr, pC->iMem); 3616 3616 } 3617 3617 pAggInfo->directMode = 0; 3618 3618 sqlite3ExprCacheClear(pParse); 3619 3619 } 3620 3620 3621 +/* 3622 +** Add a single OP_Explain instruction to the VDBE to explain a simple 3623 +** count(*) query ("SELECT count(*) FROM pTab"). 3624 +*/ 3625 +#ifndef SQLITE_OMIT_EXPLAIN 3626 +static void explainSimpleCount( 3627 + Parse *pParse, /* Parse context */ 3628 + Table *pTab, /* Table being queried */ 3629 + Index *pIdx /* Index used to optimize scan, or NULL */ 3630 +){ 3631 + if( pParse->explain==2 ){ 3632 + char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s %s%s(~%d rows)", 3633 + pTab->zName, 3634 + pIdx ? "USING COVERING INDEX " : "", 3635 + pIdx ? pIdx->zName : "", 3636 + pTab->nRowEst 3637 + ); 3638 + sqlite3VdbeAddOp4( 3639 + pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC 3640 + ); 3641 + } 3642 +} 3643 +#else 3644 +# define explainSimpleCount(a,b,c) 3645 +#endif 3646 + 3621 3647 /* 3622 3648 ** Generate code for the SELECT statement given in the p argument. 3623 3649 ** 3624 3650 ** The results are distributed in various ways depending on the 3625 3651 ** contents of the SelectDest structure pointed to by argument pDest 3626 3652 ** as follows: 3627 3653 ** ................................................................................ 4225 4251 /* Open a read-only cursor, execute the OP_Count, close the cursor. */ 4226 4252 sqlite3VdbeAddOp3(v, OP_OpenRead, iCsr, iRoot, iDb); 4227 4253 if( pKeyInfo ){ 4228 4254 sqlite3VdbeChangeP4(v, -1, (char *)pKeyInfo, P4_KEYINFO_HANDOFF); 4229 4255 } 4230 4256 sqlite3VdbeAddOp2(v, OP_Count, iCsr, sAggInfo.aFunc[0].iMem); 4231 4257 sqlite3VdbeAddOp1(v, OP_Close, iCsr); 4258 + explainSimpleCount(pParse, pTab, pBest); 4232 4259 }else 4233 4260 #endif /* SQLITE_OMIT_BTREECOUNT */ 4234 4261 { 4235 4262 /* Check if the query is of one of the following forms: 4236 4263 ** 4237 4264 ** SELECT min(x) FROM ... 4238 4265 ** SELECT max(x) FROM ...
Changes to test/eqp.test.
17 17 18 18 #------------------------------------------------------------------------- 19 19 # 20 20 # eqp-1.*: Assorted tests. 21 21 # eqp-2.*: Tests for single select statements. 22 22 # eqp-3.*: Select statements that execute sub-selects. 23 23 # eqp-4.*: Compound select statements. 24 +# ... 25 +# eqp-7.*: "SELECT count(*) FROM tbl" statements (VDBE code OP_Count). 24 26 # 25 27 26 28 proc det {args} { uplevel do_eqp_test $args } 27 29 28 30 do_execsql_test 1.1 { 29 31 CREATE TABLE t1(a, b); 30 32 CREATE INDEX i1 ON t1(a); ................................................................................ 503 505 } 504 506 sqlite3 db test.db 505 507 explain_query_plan db {%SQL%} 506 508 db close 507 509 exit 508 510 } 509 511 512 +# Do a "Print Explain Query Plan" test. 510 513 proc do_peqp_test {tn sql res} { 511 514 set fd [open script.tcl w] 512 515 puts $fd [string map [list %SQL% $sql] $::boilerplate] 513 516 close $fd 514 517 515 518 uplevel do_test $tn [list { 516 519 set fd [open "|[info nameofexec] script.tcl"] ................................................................................ 525 528 } [string trimleft { 526 529 1 0 0 SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) 527 530 2 0 0 SCAN TABLE t2 (~1000000 rows) 528 531 2 0 0 USE TEMP B-TREE FOR ORDER BY 529 532 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) 530 533 }] 531 534 535 +#------------------------------------------------------------------------- 536 +# The following tests - eqp-7.* - test that queries that use the OP_Count 537 +# optimization return something sensible with EQP. 538 +# 539 +drop_all_tables 540 + 541 +do_execsql_test 7.0 { 542 + CREATE TABLE t1(a, b); 543 + CREATE TABLE t2(a, b); 544 + CREATE INDEX i1 ON t2(a); 545 +} 546 + 547 +det 7.1 "SELECT count(*) FROM t1" { 548 + 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 549 +} 550 + 551 +det 7.2 "SELECT count(*) FROM t2" { 552 + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~1000000 rows)} 553 +} 554 + 555 +do_execsql_test 7.3 { 556 + INSERT INTO t1 VALUES(1, 2); 557 + INSERT INTO t1 VALUES(3, 4); 558 + 559 + INSERT INTO t2 VALUES(1, 2); 560 + INSERT INTO t2 VALUES(3, 4); 561 + INSERT INTO t2 VALUES(5, 6); 562 + 563 + ANALYZE; 564 +} 565 + 566 +db close 567 +sqlite3 db test.db 568 + 569 +det 7.4 "SELECT count(*) FROM t1" { 570 + 0 0 0 {SCAN TABLE t1 (~2 rows)} 571 +} 532 572 573 +det 7.5 "SELECT count(*) FROM t2" { 574 + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~3 rows)} 575 +} 533 576 534 577 535 578 finish_test
test/progress.test became executable.
tool/mkopts.tcl became executable.