/ Check-in [2e630879]
Login

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

Overview
Comment:Fix problems with handling constraints on the rowid column in sqlite3expert.c.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA3-256: 2e6308798ae2db30564deb35ba3896597448edabbcac6efc4ff084552e42de30
User & Date: dan 2017-04-15 16:52:12
Context
2017-04-17
17:03
Add support for analyzing trigger programs to the sqlite3_expert code. check-in: 159e8022 user: dan tags: schemalint
2017-04-15
16:52
Fix problems with handling constraints on the rowid column in sqlite3expert.c. check-in: 2e630879 user: dan tags: schemalint
15:47
Merge latest trunk changes into this branch. check-in: 2d0c458e user: dan tags: schemalint
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/expert/expert1.test.

    74     74       }
    75     75     }
    76     76   } {
    77     77   
    78     78     eval $setup
    79     79   
    80     80   
    81         -do_setup_rec_test $tn.1.1 { CREATE TABLE t1(a, b, c) } {
           81  +do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } {
    82     82     SELECT * FROM t1
    83     83   } {
    84     84     (no new indexes)
    85     85     0|0|0|SCAN TABLE t1
    86     86   }
    87     87   
    88         -do_setup_rec_test $tn.1.2 {
           88  +do_setup_rec_test $tn.2 {
    89     89     CREATE TABLE t1(a, b, c);
    90     90   } {
    91     91     SELECT * FROM t1 WHERE b>?;
    92     92   } {
    93     93     CREATE INDEX t1_idx_00000062 ON t1(b);
    94     94     0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000062 (b>?)
    95     95   }
    96     96   
    97         -do_setup_rec_test $tn.1.3 {
           97  +do_setup_rec_test $tn.3 {
    98     98     CREATE TABLE t1(a, b, c);
    99     99   } {
   100    100     SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
   101    101   } {
   102    102     CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE);
   103    103     0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?)
   104    104   }
   105    105   
   106         -do_setup_rec_test $tn.1.4 {
          106  +do_setup_rec_test $tn.4 {
   107    107     CREATE TABLE t1(a, b, c);
   108    108   } {
   109    109     SELECT a FROM t1 ORDER BY b;
   110    110   } {
   111    111     CREATE INDEX t1_idx_00000062 ON t1(b);
   112    112     0|0|0|SCAN TABLE t1 USING INDEX t1_idx_00000062
   113    113   }
   114    114   
   115         -do_setup_rec_test $tn.1.5 {
          115  +do_setup_rec_test $tn.5 {
   116    116     CREATE TABLE t1(a, b, c);
   117    117   } {
   118    118     SELECT a FROM t1 WHERE a=? ORDER BY b;
   119    119   } {
   120    120     CREATE INDEX t1_idx_000123a7 ON t1(a, b);
   121    121     0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_000123a7 (a=?)
   122    122   }
   123    123   
   124         -do_setup_rec_test $tn.1.6 {
          124  +do_setup_rec_test $tn.6 {
   125    125     CREATE TABLE t1(a, b, c);
   126    126   } {
   127    127     SELECT min(a) FROM t1
   128    128   } {
   129    129     CREATE INDEX t1_idx_00000061 ON t1(a);
   130    130     0|0|0|SEARCH TABLE t1 USING COVERING INDEX t1_idx_00000061
   131    131   }
   132    132   
   133         -do_setup_rec_test $tn.1.7 {
          133  +do_setup_rec_test $tn.7 {
   134    134     CREATE TABLE t1(a, b, c);
   135    135   } {
   136    136     SELECT * FROM t1 ORDER BY a, b, c;
   137    137   } {
   138    138     CREATE INDEX t1_idx_033e95fe ON t1(a, b, c);
   139    139     0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_033e95fe
   140    140   }
................................................................................
   144    144   #} {
   145    145   #  SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
   146    146   #} {
   147    147   #  CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c);
   148    148   #  0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5be6e222
   149    149   #}
   150    150   
   151         -do_setup_rec_test $tn.1.9 {
          151  +do_setup_rec_test $tn.8.1 {
   152    152     CREATE TABLE t1(a COLLATE NOCase, b, c);
   153    153   } {
   154    154     SELECT * FROM t1 WHERE a=?
   155    155   } {
   156    156     CREATE INDEX t1_idx_00000061 ON t1(a);
   157    157     0|0|0|SEARCH TABLE t1 USING INDEX t1_idx_00000061 (a=?)
   158    158   }
   159         -
   160         -do_setup_rec_test $tn.1.10 {
          159  +do_setup_rec_test $tn.8.2 {
   161    160     CREATE TABLE t1(a, b COLLATE nocase, c);
   162    161   } {
   163    162     SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC;
   164    163   } {
   165    164     CREATE INDEX t1_idx_5cb97285 ON t1(a, b DESC, c);
   166    165     0|0|0|SCAN TABLE t1 USING COVERING INDEX t1_idx_5cb97285
   167    166   }
   168    167   
   169    168   
   170    169   # Tables with names that require quotes.
   171    170   #
   172         -do_setup_rec_test $tn.8.1 {
          171  +do_setup_rec_test $tn.9.1 {
   173    172     CREATE TABLE "t t"(a, b, c);
   174    173   } {
   175    174     SELECT * FROM "t t" WHERE a=?
   176    175   } {
   177    176     CREATE INDEX 't t_idx_00000061' ON 't t'(a);
   178    177     0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000061 (a=?) 
   179    178   }
   180    179   
   181         -do_setup_rec_test $tn.8.2 {
          180  +do_setup_rec_test $tn.9.2 {
   182    181     CREATE TABLE "t t"(a, b, c);
   183    182   } {
   184    183     SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
   185    184   } {
   186    185     CREATE INDEX 't t_idx_00000062' ON 't t'(b);
   187    186     0|0|0|SEARCH TABLE t t USING INDEX t t_idx_00000062 (b>? AND b<?)
   188    187   }
   189    188   
   190    189   # Columns with names that require quotes.
   191    190   #
   192         -do_setup_rec_test $tn.9.1 {
          191  +do_setup_rec_test $tn.10.1 {
   193    192     CREATE TABLE t3(a, "b b", c);
   194    193   } {
   195    194     SELECT * FROM t3 WHERE "b b" = ?
   196    195   } {
   197    196     CREATE INDEX t3_idx_00050c52 ON t3('b b');
   198    197     0|0|0|SEARCH TABLE t3 USING INDEX t3_idx_00050c52 (b b=?)
   199    198   }
   200    199   
   201         -do_setup_rec_test $tn.9.2 {
          200  +do_setup_rec_test $tn.10.2 {
   202    201     CREATE TABLE t3(a, "b b", c);
   203    202   } {
   204    203     SELECT * FROM t3 ORDER BY "b b"
   205    204   } {
   206    205     CREATE INDEX t3_idx_00050c52 ON t3('b b');
   207    206     0|0|0|SCAN TABLE t3 USING INDEX t3_idx_00050c52
   208    207   }
   209    208   
   210    209   # Transitive constraints
   211    210   #
   212         -do_setup_rec_test $tn.10.1 {
          211  +do_setup_rec_test $tn.11.1 {
   213    212     CREATE TABLE t5(a, b);
   214    213     CREATE TABLE t6(c, d);
   215    214   } {
   216    215     SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
   217    216   } {
   218    217     CREATE INDEX t5_idx_000123a7 ON t5(a, b);
   219    218     CREATE INDEX t6_idx_00000063 ON t6(c);
   220    219     0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) 
   221    220     0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
   222    221   }
   223    222   
   224    223   # OR terms.
   225    224   #
   226         -do_setup_rec_test $tn.11.1 {
          225  +do_setup_rec_test $tn.12.1 {
   227    226     CREATE TABLE t7(a, b);
   228    227   } {
   229    228     SELECT * FROM t7 WHERE a=? OR b=?
   230    229   } {
   231    230     CREATE INDEX t7_idx_00000062 ON t7(b);
   232    231     CREATE INDEX t7_idx_00000061 ON t7(a);
   233    232     0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) 
   234    233     0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
   235    234   }
          235  +
          236  +# rowid terms.
          237  +#
          238  +do_setup_rec_test $tn.13.1 {
          239  +  CREATE TABLE t8(a, b);
          240  +} {
          241  +  SELECT * FROM t8 WHERE rowid=?
          242  +} {
          243  +  (no new indexes)
          244  +  0|0|0|SEARCH TABLE t8 USING INTEGER PRIMARY KEY (rowid=?)
          245  +}
          246  +do_setup_rec_test $tn.13.2 {
          247  +  CREATE TABLE t8(a, b);
          248  +} {
          249  +  SELECT * FROM t8 ORDER BY rowid
          250  +} {
          251  +  (no new indexes)
          252  +  0|0|0|SCAN TABLE t8
          253  +}
          254  +do_setup_rec_test $tn.13.3 {
          255  +  CREATE TABLE t8(a, b);
          256  +} {
          257  +  SELECT * FROM t8 WHERE a=? ORDER BY rowid
          258  +} {
          259  +  CREATE INDEX t8_idx_00000061 ON t8(a); 
          260  +  0|0|0|SEARCH TABLE t8 USING INDEX t8_idx_00000061 (a=?)
          261  +}
   236    262   
   237    263   }
   238    264   
   239    265   finish_test
   240    266   

Changes to ext/expert/sqlite3expert.c.

   361    361       pScan->pTab = p->pTab;
   362    362       pScan->pNextScan = p->pExpert->pScan;
   363    363       p->pExpert->pScan = pScan;
   364    364   
   365    365       /* Add the constraints to the IdxScan object */
   366    366       for(i=0; i<pIdxInfo->nConstraint; i++){
   367    367         struct sqlite3_index_constraint *pCons = &pIdxInfo->aConstraint[i];
   368         -      if( pCons->usable && (pCons->op & opmask) ){
          368  +      if( pCons->usable && pCons->iColumn>=0 && (pCons->op & opmask) ){
   369    369           IdxConstraint *pNew;
   370    370           const char *zColl = sqlite3_vtab_collation(dbv, i);
   371    371           pNew = idxNewConstraint(&rc, zColl);
   372    372           if( pNew ){
   373    373             pNew->iCol = pCons->iColumn;
   374    374             if( pCons->op==SQLITE_INDEX_CONSTRAINT_EQ ){
   375    375               pNew->pNext = pScan->pEq;
................................................................................
   384    384           pIdxInfo->aConstraintUsage[i].argvIndex = n;
   385    385         }
   386    386       }
   387    387   
   388    388       /* Add the ORDER BY to the IdxScan object */
   389    389       for(i=pIdxInfo->nOrderBy-1; i>=0; i--){
   390    390         int iCol = pIdxInfo->aOrderBy[i].iColumn;
   391         -      IdxConstraint *pNew = idxNewConstraint(&rc, p->pTab->aCol[iCol].zColl);
   392         -      if( pNew ){
   393         -        pNew->iCol = iCol;
   394         -        pNew->bDesc = pIdxInfo->aOrderBy[i].desc;
   395         -        pNew->pNext = pScan->pOrder;
   396         -        pNew->pLink = pScan->pOrder;
   397         -        pScan->pOrder = pNew;
   398         -        n++;
          391  +      if( iCol>=0 ){
          392  +        IdxConstraint *pNew = idxNewConstraint(&rc, p->pTab->aCol[iCol].zColl);
          393  +        if( pNew ){
          394  +          pNew->iCol = iCol;
          395  +          pNew->bDesc = pIdxInfo->aOrderBy[i].desc;
          396  +          pNew->pNext = pScan->pOrder;
          397  +          pNew->pLink = pScan->pOrder;
          398  +          pScan->pOrder = pNew;
          399  +          n++;
          400  +        }
   399    401         }
   400    402       }
   401    403     }
   402    404   
   403    405     pIdxInfo->estimatedCost = 1000000.0 / n;
   404    406     return rc;
   405    407   }