SQLite

Check-in [f2aa7842c8]
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
Timelines: family | ancestors | descendants | both | partial-indices
Files: files | file ages | folders
SHA1: f2aa7842c8b9df24294f09e2bde27b3f08c455c7
User & Date: drh 2013-07-31 19:05:22.844
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: 8ca3eac111 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: f2aa7842c8 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: 6794b2dcb4 user: drh tags: partial-indices)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/build.c.
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551

  iDb = sqlite3SchemaToIndex(db, p->pSchema);

#ifndef SQLITE_OMIT_CHECK
  /* Resolve names in all CHECK constraint expressions.
  */
  if( p->pCheck ){
    SrcList sSrc;                   /* Fake SrcList for pParse->pNewTable */
    NameContext sNC;                /* Name context for pParse->pNewTable */
    ExprList *pList;                /* List of all CHECK constraints */
    int i;                          /* Loop counter */

    memset(&sNC, 0, sizeof(sNC));
    memset(&sSrc, 0, sizeof(sSrc));
    sSrc.nSrc = 1;
    sSrc.a[0].zName = p->zName;
    sSrc.a[0].pTab = p;
    sSrc.a[0].iCursor = -1;
    sNC.pParse = pParse;
    sNC.pSrcList = &sSrc;
    sNC.ncFlags = NC_IsCheck;
    pList = p->pCheck;
    for(i=0; i<pList->nExpr; i++){
      if( sqlite3ResolveExprNames(&sNC, pList->a[i].pExpr) ){
        return;
      }
    }
  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* If the db->init.busy is 1 it means we are reading the SQL off the
  ** "sqlite_master" or "sqlite_temp_master" table on the disk.
  ** So do not write to the disk again.  Extract the root page number
  ** for the table from the db->init.newTnum field.  (The page number







<
<
<
<
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







1518
1519
1520
1521
1522
1523
1524




1525















1526
1527
1528
1529
1530
1531
1532

  iDb = sqlite3SchemaToIndex(db, p->pSchema);

#ifndef SQLITE_OMIT_CHECK
  /* Resolve names in all CHECK constraint expressions.
  */
  if( p->pCheck ){




    sqlite3ResolveSelfReference(pParse, p, NC_IsCheck, 0, p->pCheck);















  }
#endif /* !defined(SQLITE_OMIT_CHECK) */

  /* If the db->init.busy is 1 it means we are reading the SQL off the
  ** "sqlite_master" or "sqlite_temp_master" table on the disk.
  ** So do not write to the disk again.  Extract the root page number
  ** for the table from the db->init.newTnum field.  (The page number
2698
2699
2700
2701
2702
2703
2704


2705
2706

2707
2708
2709
2710
2711
2712
2713
  memcpy(pIndex->zName, zName, nName+1);
  pIndex->pTable = pTab;
  pIndex->nColumn = pList->nExpr;
  pIndex->onError = (u8)onError;
  pIndex->uniqNotNull = onError==OE_Abort;
  pIndex->autoIndex = (u8)(pName==0);
  pIndex->pSchema = db->aDb[iDb].pSchema;


  pIndex->pPartIdxWhere = pPIWhere;
  pPIWhere = 0;

  assert( sqlite3SchemaMutexHeld(db, iDb, 0) );

  /* Check to see if we should honor DESC requests on index columns
  */
  if( pDb->pSchema->file_format>=4 ){
    sortOrderMask = -1;   /* Honor DESC */
  }else{







>
>
|
|
>







2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
  memcpy(pIndex->zName, zName, nName+1);
  pIndex->pTable = pTab;
  pIndex->nColumn = pList->nExpr;
  pIndex->onError = (u8)onError;
  pIndex->uniqNotNull = onError==OE_Abort;
  pIndex->autoIndex = (u8)(pName==0);
  pIndex->pSchema = db->aDb[iDb].pSchema;
  if( pPIWhere ){
    sqlite3ResolveSelfReference(pParse, pTab, NC_PartIdx, pPIWhere, 0);
    pIndex->pPartIdxWhere = pPIWhere;
    pPIWhere = 0;
  }
  assert( sqlite3SchemaMutexHeld(db, iDb, 0) );

  /* Check to see if we should honor DESC requests on index columns
  */
  if( pDb->pSchema->file_format>=4 ){
    sortOrderMask = -1;   /* Honor DESC */
  }else{
Changes to src/resolve.c.
517
518
519
520
521
522
523

































524
525
526
527
528
529
530
      testcase( iCol==BMS-1 );
      pItem->colUsed |= ((Bitmask)1)<<(iCol>=BMS ? BMS-1 : iCol);
    }
    ExprSetProperty(p, EP_Resolved);
  }
  return p;
}


































/*
** This routine is callback for sqlite3WalkExpr().
**
** Resolve symbolic names into TK_COLUMN operators for the current
** node in the expression tree.  Return 0 to continue the search down
** the tree or 2 to abort the tree walk.







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
      testcase( iCol==BMS-1 );
      pItem->colUsed |= ((Bitmask)1)<<(iCol>=BMS ? BMS-1 : iCol);
    }
    ExprSetProperty(p, EP_Resolved);
  }
  return p;
}

/*
** Report an error that an expression is not valid for a partial index WHERE
** clause.
*/
static void notValidPartIdxWhere(
  Parse *pParse,       /* Leave error message here */
  NameContext *pNC,    /* The name context */
  const char *zMsg     /* Type of error */
){
  if( (pNC->ncFlags & NC_PartIdx)!=0 ){
    sqlite3ErrorMsg(pParse, "%s prohibited in partial index WHERE clauses",
                    zMsg);
  }
}

#ifndef SQLITE_OMIT_CHECK
/*
** Report an error that an expression is not valid for a CHECK constraint.
*/
static void notValidCheckConstraint(
  Parse *pParse,       /* Leave error message here */
  NameContext *pNC,    /* The name context */
  const char *zMsg     /* Type of error */
){
  if( (pNC->ncFlags & NC_IsCheck)!=0 ){
    sqlite3ErrorMsg(pParse,"%s prohibited in CHECK constraints", zMsg);
  }
}
#else
# define notValidCheckConstraint(P,N,M)
#endif


/*
** This routine is callback for sqlite3WalkExpr().
**
** Resolve symbolic names into TK_COLUMN operators for the current
** node in the expression tree.  Return 0 to continue the search down
** the tree or 2 to abort the tree walk.
617
618
619
620
621
622
623

624
625
626
627
628
629
630
      int nId;                    /* Number of characters in function name */
      const char *zId;            /* The function name. */
      FuncDef *pDef;              /* Information about the function */
      u8 enc = ENC(pParse->db);   /* The database encoding */

      testcase( pExpr->op==TK_CONST_FUNC );
      assert( !ExprHasProperty(pExpr, EP_xIsSelect) );

      zId = pExpr->u.zToken;
      nId = sqlite3Strlen30(zId);
      pDef = sqlite3FindFunction(pParse->db, zId, nId, n, enc, 0);
      if( pDef==0 ){
        pDef = sqlite3FindFunction(pParse->db, zId, nId, -2, enc, 0);
        if( pDef==0 ){
          no_such_func = 1;







>







650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
      int nId;                    /* Number of characters in function name */
      const char *zId;            /* The function name. */
      FuncDef *pDef;              /* Information about the function */
      u8 enc = ENC(pParse->db);   /* The database encoding */

      testcase( pExpr->op==TK_CONST_FUNC );
      assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
      notValidPartIdxWhere(pParse, pNC, "functions");
      zId = pExpr->u.zToken;
      nId = sqlite3Strlen30(zId);
      pDef = sqlite3FindFunction(pParse->db, zId, nId, n, enc, 0);
      if( pDef==0 ){
        pDef = sqlite3FindFunction(pParse->db, zId, nId, -2, enc, 0);
        if( pDef==0 ){
          no_such_func = 1;
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
    case TK_SELECT:
    case TK_EXISTS:  testcase( pExpr->op==TK_EXISTS );
#endif
    case TK_IN: {
      testcase( pExpr->op==TK_IN );
      if( ExprHasProperty(pExpr, EP_xIsSelect) ){
        int nRef = pNC->nRef;
#ifndef SQLITE_OMIT_CHECK
        if( (pNC->ncFlags & NC_IsCheck)!=0 ){
          sqlite3ErrorMsg(pParse,"subqueries prohibited in CHECK constraints");
        }
#endif
        sqlite3WalkSelect(pWalker, pExpr->x.pSelect);
        assert( pNC->nRef>=nRef );
        if( nRef!=pNC->nRef ){
          ExprSetProperty(pExpr, EP_VarSelect);
        }
      }
      break;
    }
#ifndef SQLITE_OMIT_CHECK
    case TK_VARIABLE: {
      if( (pNC->ncFlags & NC_IsCheck)!=0 ){
        sqlite3ErrorMsg(pParse,"parameters prohibited in CHECK constraints");
      }
      break;
    }
#endif
  }
  return (pParse->nErr || pParse->db->mallocFailed) ? WRC_Abort : WRC_Continue;
}

/*
** pEList is a list of expressions which are really the result set of the
** a SELECT statement.  pE is a term in an ORDER BY or GROUP BY clause.







<
|
|
<
<








<

|
|
<


<







716
717
718
719
720
721
722

723
724


725
726
727
728
729
730
731
732

733
734
735

736
737

738
739
740
741
742
743
744
    case TK_SELECT:
    case TK_EXISTS:  testcase( pExpr->op==TK_EXISTS );
#endif
    case TK_IN: {
      testcase( pExpr->op==TK_IN );
      if( ExprHasProperty(pExpr, EP_xIsSelect) ){
        int nRef = pNC->nRef;

        notValidCheckConstraint(pParse, pNC, "subqueries");
        notValidPartIdxWhere(pParse, pNC, "subqueries");


        sqlite3WalkSelect(pWalker, pExpr->x.pSelect);
        assert( pNC->nRef>=nRef );
        if( nRef!=pNC->nRef ){
          ExprSetProperty(pExpr, EP_VarSelect);
        }
      }
      break;
    }

    case TK_VARIABLE: {
      notValidCheckConstraint(pParse, pNC, "parameters");
      notValidPartIdxWhere(pParse, pNC, "parameters");

      break;
    }

  }
  return (pParse->nErr || pParse->db->mallocFailed) ? WRC_Abort : WRC_Continue;
}

/*
** pEList is a list of expressions which are really the result set of the
** a SELECT statement.  pE is a term in an ORDER BY or GROUP BY clause.
1327
1328
1329
1330
1331
1332
1333










































  memset(&w, 0, sizeof(w));
  w.xExprCallback = resolveExprStep;
  w.xSelectCallback = resolveSelectStep;
  w.pParse = pParse;
  w.u.pNC = pOuterNC;
  sqlite3WalkSelect(&w, p);
}

















































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
  memset(&w, 0, sizeof(w));
  w.xExprCallback = resolveExprStep;
  w.xSelectCallback = resolveSelectStep;
  w.pParse = pParse;
  w.u.pNC = pOuterNC;
  sqlite3WalkSelect(&w, p);
}

/*
** Resolve names in expressions that can only reference a single table:
**
**    *   CHECK constraints
**    *   WHERE clauses on partial indices
**
** The Expr.iTable value for Expr.op==TK_COLUMN nodes of the expression
** is set to -1 and the Expr.iColumn value is set to the column number.
**
** Any errors cause an error message to be set in pParse.
*/
void sqlite3ResolveSelfReference(
  Parse *pParse,      /* Parsing context */
  Table *pTab,        /* The table being referenced */
  int type,           /* NC_IsCheck or NC_PartIdx */
  Expr *pExpr,        /* Expression to resolve.  May be NULL. */
  ExprList *pList     /* Expression list to resolve.  May be NUL. */
){
  SrcList sSrc;                   /* Fake SrcList for pParse->pNewTable */
  NameContext sNC;                /* Name context for pParse->pNewTable */
  int i;                          /* Loop counter */

  assert( type==NC_IsCheck || type==NC_PartIdx );
  memset(&sNC, 0, sizeof(sNC));
  memset(&sSrc, 0, sizeof(sSrc));
  sSrc.nSrc = 1;
  sSrc.a[0].zName = pTab->zName;
  sSrc.a[0].pTab = pTab;
  sSrc.a[0].iCursor = -1;
  sNC.pParse = pParse;
  sNC.pSrcList = &sSrc;
  sNC.ncFlags = type;
  if( sqlite3ResolveExprNames(&sNC, pExpr) ) return;
  if( pList ){
    for(i=0; i<pList->nExpr; i++){
      if( sqlite3ResolveExprNames(&sNC, pList->a[i].pExpr) ){
        return;
      }
    }
  }
}
Changes to src/sqliteInt.h.
2015
2016
2017
2018
2019
2020
2021

2022
2023
2024
2025
2026
2027
2028
*/
#define NC_AllowAgg  0x01    /* Aggregate functions are allowed here */
#define NC_HasAgg    0x02    /* One or more aggregate functions seen */
#define NC_IsCheck   0x04    /* True if resolving names in a CHECK constraint */
#define NC_InAggFunc 0x08    /* True if analyzing arguments to an agg func */
#define NC_AsMaybe   0x10    /* Resolve to AS terms of the result set only
                             ** if no other resolution is available */


/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**
** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
** If there is a LIMIT clause, the parser sets nLimit to the value of the







>







2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
*/
#define NC_AllowAgg  0x01    /* Aggregate functions are allowed here */
#define NC_HasAgg    0x02    /* One or more aggregate functions seen */
#define NC_IsCheck   0x04    /* True if resolving names in a CHECK constraint */
#define NC_InAggFunc 0x08    /* True if analyzing arguments to an agg func */
#define NC_AsMaybe   0x10    /* Resolve to AS terms of the result set only
                             ** if no other resolution is available */
#define NC_PartIdx   0x20    /* True if resolving a partial index WHERE */

/*
** An instance of the following structure contains all information
** needed to generate code for a single SELECT statement.
**
** nLimit is set to -1 if there is no LIMIT clause.  nOffset is set to 0.
** If there is a LIMIT clause, the parser sets nLimit to the value of the
3059
3060
3061
3062
3063
3064
3065

3066
3067
3068
3069
3070
3071
3072
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
int sqlite3CodeSubselect(Parse *, Expr *, int, int);
void sqlite3SelectPrep(Parse*, Select*, NameContext*);
int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
int sqlite3ResolveExprNames(NameContext*, Expr*);
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);

int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*);
char sqlite3AffinityType(const char*);
void sqlite3Analyze(Parse*, Token*, Token*);







>







3060
3061
3062
3063
3064
3065
3066
3067
3068
3069
3070
3071
3072
3073
3074
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*);
int sqlite3CodeSubselect(Parse *, Expr *, int, int);
void sqlite3SelectPrep(Parse*, Select*, NameContext*);
int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
int sqlite3ResolveExprNames(NameContext*, Expr*);
void sqlite3ResolveSelectNames(Parse*, Select*, NameContext*);
void sqlite3ResolveSelfReference(Parse*,Table*,int,Expr*,ExprList*);
int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
CollSeq *sqlite3GetCollSeq(Parse*, u8, CollSeq *, const char*);
char sqlite3AffinityType(const char*);
void sqlite3Analyze(Parse*, Token*, Token*);
Changes to test/index6.test.
25
26
27
28
29
30
31



























32
33
    CREATE VIRTUAL TABLE nums USING wholenumber;
    INSERT INTO t1(a,b)
       SELECT CASE WHEN value%3!=0 THEN value END, value
         FROM nums WHERE value<=20;
    SELECT count(a), count(b) FROM t1;
  }
} {14 20}




























finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
    CREATE VIRTUAL TABLE nums USING wholenumber;
    INSERT INTO t1(a,b)
       SELECT CASE WHEN value%3!=0 THEN value END, value
         FROM nums WHERE value<=20;
    SELECT count(a), count(b) FROM t1;
  }
} {14 20}

do_test index6-1.2 {
  catchsql {
    CREATE INDEX bad1 ON t1(a,b) WHERE c IS NOT NULL;
  }
} {1 {no such column: c}}
do_test index6-1.3 {
  catchsql {
    CREATE INDEX bad1 ON t1(a,b) WHERE EXISTS(SELECT * FROM t1);
  }
} {1 {subqueries prohibited in partial index WHERE clauses}}
do_test index6-1.4 {
  catchsql {
    CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1;
  }
} {1 {parameters prohibited in partial index WHERE clauses}}
do_test index6-1.5 {
  catchsql {
    CREATE INDEX bad1 ON t1(a,b) WHERE a!=random();
  }
} {1 {functions prohibited in partial index WHERE clauses}}
do_test index6-1.6 {
  catchsql {
    CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';
  }
} {1 {functions prohibited in partial index WHERE clauses}}


finish_test