/ Check-in [9f9f3288]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
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: 9f9f32882501ac9b6e60f81195a64bdbf6e4497b
User & Date: dan 2011-02-21 17:49:49
References
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
Context
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
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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.