/ Check-in [f2aa7842]
Login

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

Overview
Comment:Resolve names in CREATE INDEX WHERE clauses and detect errors. Disallow expressions that contain variables, subqueries, or functions. The expression is still not used for anything, however. still unused.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | partial-indices
Files: files | file ages | folders
SHA1:f2aa7842c8b9df24294f09e2bde27b3f08c455c7
User & Date: drh 2013-07-31 19:05:22
Context
2013-07-31
23:22
Add logic to the query planner to only use partial indices if the WHERE clause constrains the search to rows covered by the partial index. This is just infrastructure. The key routine, sqlite3ExprImpliesExpr(), is currently a no-op so that partial indices will never be used. check-in: 8ca3eac1 user: drh tags: partial-indices
19:05
Resolve names in CREATE INDEX WHERE clauses and detect errors. Disallow expressions that contain variables, subqueries, or functions. The expression is still not used for anything, however. still unused. check-in: f2aa7842 user: drh tags: partial-indices
18:12
Here begins an experimental branch for exploring the idea of a partial index. This check-in is able to parse a WHERE clause on a CREATE INDEX statement, but does not actually do anythingn with that WHERE clause yet. check-in: 6794b2dc user: drh tags: partial-indices
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

  1518   1518   
  1519   1519     iDb = sqlite3SchemaToIndex(db, p->pSchema);
  1520   1520   
  1521   1521   #ifndef SQLITE_OMIT_CHECK
  1522   1522     /* Resolve names in all CHECK constraint expressions.
  1523   1523     */
  1524   1524     if( p->pCheck ){
  1525         -    SrcList sSrc;                   /* Fake SrcList for pParse->pNewTable */
  1526         -    NameContext sNC;                /* Name context for pParse->pNewTable */
  1527         -    ExprList *pList;                /* List of all CHECK constraints */
  1528         -    int i;                          /* Loop counter */
  1529         -
  1530         -    memset(&sNC, 0, sizeof(sNC));
  1531         -    memset(&sSrc, 0, sizeof(sSrc));
  1532         -    sSrc.nSrc = 1;
  1533         -    sSrc.a[0].zName = p->zName;
  1534         -    sSrc.a[0].pTab = p;
  1535         -    sSrc.a[0].iCursor = -1;
  1536         -    sNC.pParse = pParse;
  1537         -    sNC.pSrcList = &sSrc;
  1538         -    sNC.ncFlags = NC_IsCheck;
  1539         -    pList = p->pCheck;
  1540         -    for(i=0; i<pList->nExpr; i++){
  1541         -      if( sqlite3ResolveExprNames(&sNC, pList->a[i].pExpr) ){
  1542         -        return;
  1543         -      }
  1544         -    }
         1525  +    sqlite3ResolveSelfReference(pParse, p, NC_IsCheck, 0, p->pCheck);
  1545   1526     }
  1546   1527   #endif /* !defined(SQLITE_OMIT_CHECK) */
  1547   1528   
  1548   1529     /* If the db->init.busy is 1 it means we are reading the SQL off the
  1549   1530     ** "sqlite_master" or "sqlite_temp_master" table on the disk.
  1550   1531     ** So do not write to the disk again.  Extract the root page number
  1551   1532     ** for the table from the db->init.newTnum field.  (The page number
................................................................................
  2698   2679     memcpy(pIndex->zName, zName, nName+1);
  2699   2680     pIndex->pTable = pTab;
  2700   2681     pIndex->nColumn = pList->nExpr;
  2701   2682     pIndex->onError = (u8)onError;
  2702   2683     pIndex->uniqNotNull = onError==OE_Abort;
  2703   2684     pIndex->autoIndex = (u8)(pName==0);
  2704   2685     pIndex->pSchema = db->aDb[iDb].pSchema;
  2705         -  pIndex->pPartIdxWhere = pPIWhere;
  2706         -  pPIWhere = 0;
         2686  +  if( pPIWhere ){
         2687  +    sqlite3ResolveSelfReference(pParse, pTab, NC_PartIdx, pPIWhere, 0);
         2688  +    pIndex->pPartIdxWhere = pPIWhere;
         2689  +    pPIWhere = 0;
         2690  +  }
  2707   2691     assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
  2708   2692   
  2709   2693     /* Check to see if we should honor DESC requests on index columns
  2710   2694     */
  2711   2695     if( pDb->pSchema->file_format>=4 ){
  2712   2696       sortOrderMask = -1;   /* Honor DESC */
  2713   2697     }else{

Changes to src/resolve.c.

   517    517         testcase( iCol==BMS-1 );
   518    518         pItem->colUsed |= ((Bitmask)1)<<(iCol>=BMS ? BMS-1 : iCol);
   519    519       }
   520    520       ExprSetProperty(p, EP_Resolved);
   521    521     }
   522    522     return p;
   523    523   }
          524  +
          525  +/*
          526  +** Report an error that an expression is not valid for a partial index WHERE
          527  +** clause.
          528  +*/
          529  +static void notValidPartIdxWhere(
          530  +  Parse *pParse,       /* Leave error message here */
          531  +  NameContext *pNC,    /* The name context */
          532  +  const char *zMsg     /* Type of error */
          533  +){
          534  +  if( (pNC->ncFlags & NC_PartIdx)!=0 ){
          535  +    sqlite3ErrorMsg(pParse, "%s prohibited in partial index WHERE clauses",
          536  +                    zMsg);
          537  +  }
          538  +}
          539  +
          540  +#ifndef SQLITE_OMIT_CHECK
          541  +/*
          542  +** Report an error that an expression is not valid for a CHECK constraint.
          543  +*/
          544  +static void notValidCheckConstraint(
          545  +  Parse *pParse,       /* Leave error message here */
          546  +  NameContext *pNC,    /* The name context */
          547  +  const char *zMsg     /* Type of error */
          548  +){
          549  +  if( (pNC->ncFlags & NC_IsCheck)!=0 ){
          550  +    sqlite3ErrorMsg(pParse,"%s prohibited in CHECK constraints", zMsg);
          551  +  }
          552  +}
          553  +#else
          554  +# define notValidCheckConstraint(P,N,M)
          555  +#endif
          556  +
   524    557   
   525    558   /*
   526    559   ** This routine is callback for sqlite3WalkExpr().
   527    560   **
   528    561   ** Resolve symbolic names into TK_COLUMN operators for the current
   529    562   ** node in the expression tree.  Return 0 to continue the search down
   530    563   ** the tree or 2 to abort the tree walk.
................................................................................
   617    650         int nId;                    /* Number of characters in function name */
   618    651         const char *zId;            /* The function name. */
   619    652         FuncDef *pDef;              /* Information about the function */
   620    653         u8 enc = ENC(pParse->db);   /* The database encoding */
   621    654   
   622    655         testcase( pExpr->op==TK_CONST_FUNC );
   623    656         assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
          657  +      notValidPartIdxWhere(pParse, pNC, "functions");
   624    658         zId = pExpr->u.zToken;
   625    659         nId = sqlite3Strlen30(zId);
   626    660         pDef = sqlite3FindFunction(pParse->db, zId, nId, n, enc, 0);
   627    661         if( pDef==0 ){
   628    662           pDef = sqlite3FindFunction(pParse->db, zId, nId, -2, enc, 0);
   629    663           if( pDef==0 ){
   630    664             no_such_func = 1;
................................................................................
   682    716       case TK_SELECT:
   683    717       case TK_EXISTS:  testcase( pExpr->op==TK_EXISTS );
   684    718   #endif
   685    719       case TK_IN: {
   686    720         testcase( pExpr->op==TK_IN );
   687    721         if( ExprHasProperty(pExpr, EP_xIsSelect) ){
   688    722           int nRef = pNC->nRef;
   689         -#ifndef SQLITE_OMIT_CHECK
   690         -        if( (pNC->ncFlags & NC_IsCheck)!=0 ){
   691         -          sqlite3ErrorMsg(pParse,"subqueries prohibited in CHECK constraints");
   692         -        }
   693         -#endif
          723  +        notValidCheckConstraint(pParse, pNC, "subqueries");
          724  +        notValidPartIdxWhere(pParse, pNC, "subqueries");
   694    725           sqlite3WalkSelect(pWalker, pExpr->x.pSelect);
   695    726           assert( pNC->nRef>=nRef );
   696    727           if( nRef!=pNC->nRef ){
   697    728             ExprSetProperty(pExpr, EP_VarSelect);
   698    729           }
   699    730         }
   700    731         break;
   701    732       }
   702         -#ifndef SQLITE_OMIT_CHECK
   703    733       case TK_VARIABLE: {
   704         -      if( (pNC->ncFlags & NC_IsCheck)!=0 ){
   705         -        sqlite3ErrorMsg(pParse,"parameters prohibited in CHECK constraints");
   706         -      }
          734  +      notValidCheckConstraint(pParse, pNC, "parameters");
          735  +      notValidPartIdxWhere(pParse, pNC, "parameters");
   707    736         break;
   708    737       }
   709         -#endif
   710    738     }
   711    739     return (pParse->nErr || pParse->db->mallocFailed) ? WRC_Abort : WRC_Continue;
   712    740   }
   713    741   
   714    742   /*
   715    743   ** pEList is a list of expressions which are really the result set of the
   716    744   ** a SELECT statement.  pE is a term in an ORDER BY or GROUP BY clause.
................................................................................
  1327   1355     memset(&w, 0, sizeof(w));
  1328   1356     w.xExprCallback = resolveExprStep;
  1329   1357     w.xSelectCallback = resolveSelectStep;
  1330   1358     w.pParse = pParse;
  1331   1359     w.u.pNC = pOuterNC;
  1332   1360     sqlite3WalkSelect(&w, p);
  1333   1361   }
         1362  +
         1363  +/*
         1364  +** Resolve names in expressions that can only reference a single table:
         1365  +**
         1366  +**    *   CHECK constraints
         1367  +**    *   WHERE clauses on partial indices
         1368  +**
         1369  +** The Expr.iTable value for Expr.op==TK_COLUMN nodes of the expression
         1370  +** is set to -1 and the Expr.iColumn value is set to the column number.
         1371  +**
         1372  +** Any errors cause an error message to be set in pParse.
         1373  +*/
         1374  +void sqlite3ResolveSelfReference(
         1375  +  Parse *pParse,      /* Parsing context */
         1376  +  Table *pTab,        /* The table being referenced */
         1377  +  int type,           /* NC_IsCheck or NC_PartIdx */
         1378  +  Expr *pExpr,        /* Expression to resolve.  May be NULL. */
         1379  +  ExprList *pList     /* Expression list to resolve.  May be NUL. */
         1380  +){
         1381  +  SrcList sSrc;                   /* Fake SrcList for pParse->pNewTable */
         1382  +  NameContext sNC;                /* Name context for pParse->pNewTable */
         1383  +  int i;                          /* Loop counter */
         1384  +
         1385  +  assert( type==NC_IsCheck || type==NC_PartIdx );
         1386  +  memset(&sNC, 0, sizeof(sNC));
         1387  +  memset(&sSrc, 0, sizeof(sSrc));
         1388  +  sSrc.nSrc = 1;
         1389  +  sSrc.a[0].zName = pTab->zName;
         1390  +  sSrc.a[0].pTab = pTab;
         1391  +  sSrc.a[0].iCursor = -1;
         1392  +  sNC.pParse = pParse;
         1393  +  sNC.pSrcList = &sSrc;
         1394  +  sNC.ncFlags = type;
         1395  +  if( sqlite3ResolveExprNames(&sNC, pExpr) ) return;
         1396  +  if( pList ){
         1397  +    for(i=0; i<pList->nExpr; i++){
         1398  +      if( sqlite3ResolveExprNames(&sNC, pList->a[i].pExpr) ){
         1399  +        return;
         1400  +      }
         1401  +    }
         1402  +  }
         1403  +}

Changes to src/sqliteInt.h.

  2015   2015   */
  2016   2016   #define NC_AllowAgg  0x01    /* Aggregate functions are allowed here */
  2017   2017   #define NC_HasAgg    0x02    /* One or more aggregate functions seen */
  2018   2018   #define NC_IsCheck   0x04    /* True if resolving names in a CHECK constraint */
  2019   2019   #define NC_InAggFunc 0x08    /* True if analyzing arguments to an agg func */
  2020   2020   #define NC_AsMaybe   0x10    /* Resolve to AS terms of the result set only
  2021   2021                                ** if no other resolution is available */
         2022  +#define NC_PartIdx   0x20    /* True if resolving a partial index WHERE */
  2022   2023   
  2023   2024   /*
  2024   2025   ** An instance of the following structure contains all information
  2025   2026   ** needed to generate code for a single SELECT statement.
  2026   2027   **
  2027   2028   ** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
  2028   2029   ** If there is a LIMIT clause, the parser sets nLimit to the value of the
................................................................................
  3059   3060   void sqlite3NestedParse(Parse*, const char*, ...);
  3060   3061   void sqlite3ExpirePreparedStatements(sqlite3*);
  3061   3062   int sqlite3CodeSubselect(Parse *, Expr *, int, int);
  3062   3063   void sqlite3SelectPrep(Parse*, Select*, NameContext*);
  3063   3064   int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
  3064   3065   int sqlite3ResolveExprNames(NameContext*, Expr*);
  3065   3066   void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
         3067  +void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*);
  3066   3068   int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
  3067   3069   void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
  3068   3070   void sqlite3AlterFinishAddColumn(Parse *, Token *);
  3069   3071   void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
  3070   3072   CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*);
  3071   3073   char sqlite3AffinityType(const char*);
  3072   3074   void sqlite3Analyze(Parse*, Token*, Token*);

Changes to test/index6.test.

    25     25       CREATE VIRTUAL TABLE nums USING wholenumber;
    26     26       INSERT INTO t1(a,b)
    27     27          SELECT CASE WHEN value%3!=0 THEN value END, value
    28     28            FROM nums WHERE value<=20;
    29     29       SELECT count(a), count(b) FROM t1;
    30     30     }
    31     31   } {14 20}
           32  +
           33  +do_test index6-1.2 {
           34  +  catchsql {
           35  +    CREATE INDEX bad1 ON t1(a,b) WHERE c IS NOT NULL;
           36  +  }
           37  +} {1 {no such column: c}}
           38  +do_test index6-1.3 {
           39  +  catchsql {
           40  +    CREATE INDEX bad1 ON t1(a,b) WHERE EXISTS(SELECT * FROM t1);
           41  +  }
           42  +} {1 {subqueries prohibited in partial index WHERE clauses}}
           43  +do_test index6-1.4 {
           44  +  catchsql {
           45  +    CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1;
           46  +  }
           47  +} {1 {parameters prohibited in partial index WHERE clauses}}
           48  +do_test index6-1.5 {
           49  +  catchsql {
           50  +    CREATE INDEX bad1 ON t1(a,b) WHERE a!=random();
           51  +  }
           52  +} {1 {functions prohibited in partial index WHERE clauses}}
           53  +do_test index6-1.6 {
           54  +  catchsql {
           55  +    CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';
           56  +  }
           57  +} {1 {functions prohibited in partial index WHERE clauses}}
           58  +
    32     59   
    33     60   finish_test