/ Check-in [5cd07000]
Login

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

Overview
Comment:Add some support for OR terms to sqlite3_whereinfo_hook().
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA3-256: 5cd070000da1d9e399090677b4db75dc5639c33211385d6eb84f14a4d0b617cd
User & Date: dan 2017-04-04 17:50:31
Context
2017-04-06
18:44
Changes to allow indexes to be recommended for queries on SQL views. check-in: 0884ff1d user: dan tags: schemalint
2017-04-04
17:50
Add some support for OR terms to sqlite3_whereinfo_hook(). check-in: 5cd07000 user: dan tags: schemalint
04:23
Add the sqlite3_whereinfo_hook() API - an experimental API replacing the DBCONFIG_WHEREINFO hack on this branch. check-in: a54aef35 user: dan tags: schemalint
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell_indexes.c.

   171    171   
   172    172   #if 0
   173    173     const char *zOp = 
   174    174       eOp==SQLITE_WHEREINFO_TABLE ? "TABLE" :
   175    175       eOp==SQLITE_WHEREINFO_EQUALS ? "EQUALS" :
   176    176       eOp==SQLITE_WHEREINFO_RANGE ? "RANGE" :
   177    177       eOp==SQLITE_WHEREINFO_ORDERBY ? "ORDERBY" :
   178         -    eOp==SQLITE_WHEREINFO_NEXTOR ? "NEXTOR" :
   179         -    eOp==SQLITE_WHEREINFO_ENDOR ? "ENDOR" :
   180         -    eOp==SQLITE_WHEREINFO_BEGINOR ? "BEGINOR" :
   181    178       "!error!";
   182    179     printf("op=%s zVal=%s iVal=%d mask=%llx\n", zOp, zVal, iVal, mask);
   183    180   #endif
   184    181   
   185    182     if( p->rc==SQLITE_OK ){
   186    183       assert( eOp==SQLITE_WHEREINFO_TABLE || p->pScan!=0 );
   187    184       switch( eOp ){
................................................................................
   626    623           zFmt = "CREATE INDEX %s_idx_%08x ON %s(%s)";
   627    624         }
   628    625         zIdx = sqlite3_mprintf(zFmt, pScan->zTable, h, pScan->zTable, zCols);
   629    626         if( !zIdx ){
   630    627           rc = SQLITE_NOMEM;
   631    628         }else{
   632    629           rc = sqlite3_exec(dbm, zIdx, 0, 0, pCtx->pzErrmsg);
   633         -        /* printf("%s\n", zIdx); */
          630  +#if 0
          631  +        printf("CANDIDATE: %s\n", zIdx);
          632  +#endif
   634    633         }
   635    634       }
   636    635       if( rc==SQLITE_OK && pCtx->iIdxRowid==0 ){
   637    636         int rc2;
   638    637         sqlite3_stmt *pLast = 0;
   639    638         rc = idxPrepareStmt(dbm, &pLast, pCtx->pzErrmsg, 
   640    639             "SELECT max(rowid) FROM sqlite_master"

Changes to src/where.c.

  4274   4274   #endif
  4275   4275       return 1;
  4276   4276     }
  4277   4277     return 0;
  4278   4278   }
  4279   4279   
  4280   4280   #ifdef SQLITE_ENABLE_WHEREINFO_HOOK
         4281  +
  4281   4282   static void whereTraceWC(
  4282   4283     Parse *pParse, 
  4283   4284     struct SrcList_item *pItem,
  4284   4285     WhereClause *pWC
  4285   4286   ){
  4286   4287     sqlite3 *db = pParse->db;
  4287   4288     Table *pTab = pItem->pTab;
  4288         -  void (*x)(void*, int, const char*, int, i64) = db->xWhereInfo;
         4289  +  void (*x)(void*, int, const char*, int, u64) = db->xWhereInfo;
  4289   4290     void *pCtx = db->pWhereInfoCtx;
  4290   4291     int ii;
  4291   4292   
  4292   4293     /* Issue callbacks for WO_SINGLE constraints */
  4293   4294     for(ii=0; ii<pTab->nCol; ii++){
  4294   4295       int opMask = WO_SINGLE; 
  4295   4296       WhereScan scan;
................................................................................
  4306   4307         }else{
  4307   4308           eOp = SQLITE_WHEREINFO_RANGE;
  4308   4309         }
  4309   4310         x(pCtx, eOp, (pC ? pC->zName : "BINARY"), ii, pTerm->prereqRight);
  4310   4311       }
  4311   4312     }
  4312   4313   }
         4314  +
         4315  +/*
         4316  +** If there are any OR terms in WHERE clause pWC, make the associated
         4317  +** where-info hook callbacks.
         4318  +*/
         4319  +static void whereTraceOR(
         4320  +  Parse *pParse, 
         4321  +  struct SrcList_item *pItem,
         4322  +  WhereClause *pWC
         4323  +){
         4324  +  sqlite3 *db = pParse->db;
         4325  +  WhereClause tempWC;
         4326  +  struct TermAndIdx {
         4327  +    WhereTerm *pTerm;
         4328  +    int iIdx;
         4329  +  } aOr[4];
         4330  +  int nOr = 0;
         4331  +  Table *pTab = pItem->pTab;
         4332  +  int iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
         4333  +  int ii;
         4334  +
         4335  +  memset(aOr, 0, sizeof(aOr));
         4336  +
         4337  +  /* Iterate through OR nodes */
         4338  +  for(ii=0; ii<pWC->nTerm; ii++){
         4339  +    WhereTerm *pTerm = &pWC->a[ii];
         4340  +    if( pTerm->eOperator & WO_OR ){
         4341  +      /* Check that each branch of this OR term contains at least
         4342  +      ** one reference to the table currently being processed. If that
         4343  +      ** is not the case, this term can be ignored.  */
         4344  +      WhereClause * const pOrWC = &pTerm->u.pOrInfo->wc;
         4345  +      WhereTerm * const pOrWCEnd = &pOrWC->a[pOrWC->nTerm];
         4346  +      WhereTerm *pOrTerm;
         4347  +      WhereClause *pTermWC;
         4348  +      WhereScan scan;
         4349  +
         4350  +      for(pOrTerm=pOrWC->a; pOrTerm<pOrWCEnd; pOrTerm++){
         4351  +        int iCol;
         4352  +        if( (pOrTerm->eOperator & WO_AND)!=0 ){
         4353  +          pTermWC = &pOrTerm->u.pAndInfo->wc;
         4354  +        }else{
         4355  +          tempWC.pWInfo = pWC->pWInfo;
         4356  +          tempWC.pOuter = pWC;
         4357  +          tempWC.op = TK_AND;
         4358  +          tempWC.nTerm = 1;
         4359  +          tempWC.a = pOrTerm;
         4360  +          pTermWC = &tempWC;
         4361  +        }
         4362  +
         4363  +        for(iCol=0; iCol<pTab->nCol; iCol++){
         4364  +          int iCsr = pItem->iCursor;
         4365  +          if( !whereScanInit(&scan, pTermWC, iCsr, iCol, WO_SINGLE, 0) ){
         4366  +            break;
         4367  +          }
         4368  +        }
         4369  +        if( iCol==pTab->nCol ) break;
         4370  +      }
         4371  +
         4372  +      if( pOrTerm==pOrWCEnd ){
         4373  +        aOr[nOr].pTerm = pTerm;
         4374  +        aOr[nOr].iIdx = pOrWC->nTerm;
         4375  +        nOr++;
         4376  +        if( nOr==ArraySize(aOr) ) break;
         4377  +      }
         4378  +    }
         4379  +  }
         4380  +
         4381  +  while( 1 ){
         4382  +    for(ii=0; ii<nOr; ii++){
         4383  +      if( aOr[ii].iIdx==0 ){
         4384  +        aOr[ii].iIdx = aOr[ii].pTerm->u.pOrInfo->wc.nTerm;
         4385  +      }else{
         4386  +        aOr[ii].iIdx--;
         4387  +        break;
         4388  +      }
         4389  +    }
         4390  +    if( ii==nOr ) break;
         4391  +
         4392  +    /* Table name callback */
         4393  +    db->xWhereInfo(db->pWhereInfoCtx, 
         4394  +        SQLITE_WHEREINFO_TABLE, pTab->zName, iDb, pItem->colUsed
         4395  +    );
         4396  +    /* whereTraceWC(pParse, pItem, pWC); */
         4397  +    for(ii=0; ii<nOr; ii++){
         4398  +      WhereClause * const pOrWC = &aOr[ii].pTerm->u.pOrInfo->wc;
         4399  +      if( aOr[ii].iIdx<pOrWC->nTerm ){
         4400  +        WhereClause *pTermWC;
         4401  +        WhereTerm *pOrTerm = &pOrWC->a[aOr[ii].iIdx];
         4402  +        if( (pOrTerm->eOperator & WO_AND)!=0 ){
         4403  +          pTermWC = &pOrTerm->u.pAndInfo->wc;
         4404  +        }else{
         4405  +          tempWC.pWInfo = pWC->pWInfo;
         4406  +          tempWC.pOuter = pWC;
         4407  +          tempWC.op = TK_AND;
         4408  +          tempWC.nTerm = 1;
         4409  +          tempWC.a = pOrTerm;
         4410  +          pTermWC = &tempWC;
         4411  +        }
         4412  +        whereTraceWC(pParse, pItem, pTermWC);
         4413  +      }
         4414  +    }
         4415  +  }
         4416  +}
  4313   4417   
  4314   4418   /*
  4315   4419   ** If there is a where-info hook attached to the database handle, issue all
  4316   4420   ** required callbacks for the current sqlite3WhereBegin() call.
  4317   4421   */
  4318   4422   static void whereTraceBuilder(
  4319   4423     Parse *pParse,
  4320   4424     WhereLoopBuilder *p
  4321   4425   ){
  4322   4426     sqlite3 *db = pParse->db;
  4323   4427     if( db->xWhereInfo && db->init.busy==0 ){
  4324         -    void (*x)(void*, int, const char*, int, i64) = db->xWhereInfo;
         4428  +    void (*x)(void*, int, const char*, int, u64) = db->xWhereInfo;
  4325   4429       void *pCtx = db->pWhereInfoCtx;
  4326   4430       int ii;
  4327         -    int nTab = p->pWInfo->pTabList->nSrc;
         4431  +    SrcList *pTabList = p->pWInfo->pTabList;
  4328   4432   
  4329   4433       /* Loop through each element of the FROM clause. Ignore any sub-selects
  4330   4434       ** or views. Invoke the xWhereInfo() callback multiple times for each
  4331   4435       ** real table.  */
  4332         -    for(ii=0; ii<p->pWInfo->pTabList->nSrc; ii++){
  4333         -      struct SrcList_item *pItem = &p->pWInfo->pTabList->a[ii];
         4436  +    for(ii=0; ii<pTabList->nSrc; ii++){
         4437  +      struct SrcList_item *pItem = &pTabList->a[ii];
  4334   4438         if( pItem->pSelect==0 ){
  4335   4439           Table *pTab = pItem->pTab;
  4336   4440           int iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  4337   4441   
  4338   4442           /* Table name callback */
  4339   4443           x(pCtx, SQLITE_WHEREINFO_TABLE, pTab->zName, iDb, pItem->colUsed);
  4340   4444   
................................................................................
  4353   4457                 }
  4354   4458               }
  4355   4459             }
  4356   4460           }
  4357   4461   
  4358   4462           /* WHERE callbacks */
  4359   4463           whereTraceWC(pParse, pItem, p->pWC);
         4464  +
         4465  +        /* OR-clause processing */
         4466  +        whereTraceOR(pParse, pItem, p->pWC);
  4360   4467         }
  4361   4468       }
  4362   4469     }
  4363   4470   }
  4364   4471   #else
  4365   4472   # define whereTraceBuilder(x,y)
  4366   4473   #endif

Changes to test/shell6.test.

   183    183     SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
   184    184   } {
   185    185     CREATE INDEX t5_idx_000123a7 ON t5(a, b) 
   186    186     CREATE INDEX t6_idx_00000063 ON t6(c) 
   187    187     0|0|1|SEARCH TABLE t6 USING INDEX t6_idx_00000063 (c=?) 
   188    188     0|1|0|SEARCH TABLE t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
   189    189   }
          190  +
          191  +# OR terms.
          192  +#
          193  +do_setup_rec_test 11.1 {
          194  +  CREATE TABLE t7(a, b);
          195  +} {
          196  +  SELECT * FROM t7 WHERE a=? OR b=?
          197  +} {
          198  +  CREATE INDEX t7_idx_00000061 ON t7(a) 
          199  +  CREATE INDEX t7_idx_00000062 ON t7(b)
          200  +  0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?) 
          201  +  0|0|0|SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
          202  +}
   190    203   
   191    204   finish_test
          205  +