/ Check-in [e721975f]
Login

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

Overview
Comment:Extend [3e9ed1ae] so that covering indexes on WITHOUT ROWID tables are also identified.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e721975faa0925be4029330550ff2a9666041ff7
User & Date: dan 2016-02-24 20:16:28
Context
2016-02-25
13:33
In the command-line shell: When the ".import" command is creating a new table using column names from the first row of CSV input, make sure double-quotes in the name are properly escaped. check-in: 2e67a1c8 user: drh tags: trunk
2016-02-24
21:42
Initial work on an automated VSIX testing tool. Not working or tested yet. check-in: 496e4ac9 user: mistachkin tags: vsixTest
20:16
Extend [3e9ed1ae] so that covering indexes on WITHOUT ROWID tables are also identified. check-in: e721975f user: dan tags: trunk
19:57
Change a char* to const char* in order to suppress some harmless compiler warnings. check-in: 56f62e34 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  3188   3188           i++;
  3189   3189         }
  3190   3190       }
  3191   3191       assert( i==pIndex->nColumn );
  3192   3192     }else{
  3193   3193       pIndex->aiColumn[i] = XN_ROWID;
  3194   3194       pIndex->azColl[i] = sqlite3StrBINARY;
  3195         -    /* If this index contains every column of its table, then mark
  3196         -    ** it as a covering index */
  3197         -    if( pTblName!=0 && pIndex->nColumn>=pTab->nCol ){
  3198         -      pIndex->isCovering = 1;
  3199         -      for(j=0; j<pTab->nCol; j++){
  3200         -        if( j==pTab->iPKey ) continue;
  3201         -        if( sqlite3ColumnOfIndex(pIndex,j)>=0 ) continue;
  3202         -        pIndex->isCovering = 0;
  3203         -        break;
  3204         -      }
  3205         -    }
  3206   3195     }
  3207   3196     sqlite3DefaultRowEst(pIndex);
  3208   3197     if( pParse->pNewTable==0 ) estimateIndexWidth(pIndex);
         3198  +
         3199  +  /* If this index contains every column of its table, then mark
         3200  +  ** it as a covering index */
         3201  +  assert( HasRowid(pTab) 
         3202  +      || pTab->iPKey<0 || sqlite3ColumnOfIndex(pIndex, pTab->iPKey)>=0 );
         3203  +  if( pTblName!=0 && pIndex->nColumn>=pTab->nCol ){
         3204  +    pIndex->isCovering = 1;
         3205  +    for(j=0; j<pTab->nCol; j++){
         3206  +      if( j==pTab->iPKey ) continue;
         3207  +      if( sqlite3ColumnOfIndex(pIndex,j)>=0 ) continue;
         3208  +      pIndex->isCovering = 0;
         3209  +      break;
         3210  +    }
         3211  +  }
  3209   3212   
  3210   3213     if( pTab==pParse->pNewTable ){
  3211   3214       /* This routine has been called to create an automatic index as a
  3212   3215       ** result of a PRIMARY KEY or UNIQUE clause on a column definition, or
  3213   3216       ** a PRIMARY KEY or UNIQUE clause following the column definitions.
  3214   3217       ** i.e. one of:
  3215   3218       **

Changes to test/coveridxscan.test.

    84     84   } {3 4 5}
    85     85   do_test 4.2 {
    86     86     db eval {SELECT a, c FROM t1}
    87     87   } {5 3 4 2 3 1}
    88     88   do_test 4.3 {
    89     89     db eval {SELECT b FROM t1}
    90     90   } {2 4 8}
           91  +
           92  +#-------------------------------------------------------------------------
           93  +# Test that indexes with large numbers of columns can be correctly 
           94  +# identified as covering indexes.
           95  +reset_db
           96  +set L [list]
           97  +for {set i 1} {$i<120} {incr i} {
           98  +  lappend L "c$i"
           99  +}
          100  +set cols [join $L ,]
          101  +
          102  +do_execsql_test 5.1.0 "
          103  +  CREATE TABLE t1(a, b, c, $cols, PRIMARY KEY(a, b, c)) WITHOUT ROWID;
          104  +  CREATE INDEX i1 ON t1($cols);
          105  +
          106  +  CREATE TABLE t2(i INTEGER PRIMARY KEY, $cols);
          107  +  CREATE INDEX i2 ON t2($cols);
          108  +"
          109  +
          110  +do_eqp_test 5.1.1 {
          111  +  SELECT * FROM t1 ORDER BY c1, c2;
          112  +} {
          113  +  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}
          114  +}
          115  +
          116  +do_eqp_test 5.1.2 {
          117  +  SELECT * FROM t2 ORDER BY c1, c2;
          118  +} {
          119  +  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i2}
          120  +}
          121  +
    91    122   
    92    123   
    93    124   finish_test