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

Changes to src/expr.c.

  1206   1206       sqlite3DbFree(db, pItem->zSpan);
  1207   1207     }
  1208   1208     sqlite3DbFree(db, pList->a);
  1209   1209     sqlite3DbFree(db, pList);
  1210   1210   }
  1211   1211   
  1212   1212   /*
  1213         -** These routines are Walker callbacks.  Walker.u.pi is a pointer
  1214         -** to an integer.  These routines are checking an expression to see
  1215         -** if it is a constant.  Set *Walker.u.i to 0 if the expression is
  1216         -** not constant.
         1213  +** These routines are Walker callbacks used to check expressions to
         1214  +** see if they are "constant" for some definition of constant.  The
         1215  +** Walker.eCode value determines the type of "constant" we are looking
         1216  +** for.
  1217   1217   **
  1218   1218   ** These callback routines are used to implement the following:
  1219   1219   **
  1220         -**     sqlite3ExprIsConstant()                  pWalker->u.i==1
  1221         -**     sqlite3ExprIsConstantNotJoin()           pWalker->u.i==2
  1222         -**     sqlite3ExprIsConstantOrFunction()        pWalker->u.i==3 or 4
         1220  +**     sqlite3ExprIsConstant()                  pWalker->eCode==1
         1221  +**     sqlite3ExprIsConstantNotJoin()           pWalker->eCode==2
         1222  +**     sqlite3ExprRefOneTableOnly()             pWalker->eCode==3
         1223  +**     sqlite3ExprIsConstantOrFunction()        pWalker->eCode==4 or 5
         1224  +**
         1225  +** In all cases, the callbacks set Walker.eCode=0 and abort if the expression
         1226  +** is found to not be a constant.
  1223   1227   **
  1224   1228   ** The sqlite3ExprIsConstantOrFunction() is used for evaluating expressions
  1225         -** in a CREATE TABLE statement.  The Walker.u.i value is 4 when parsing
  1226         -** an existing schema and 3 when processing a new statement.  A bound
         1229  +** in a CREATE TABLE statement.  The Walker.eCode value is 5 when parsing
         1230  +** an existing schema and 4 when processing a new statement.  A bound
  1227   1231   ** parameter raises an error for new statements, but is silently converted
  1228   1232   ** to NULL for existing schemas.  This allows sqlite_master tables that 
  1229   1233   ** contain a bound parameter because they were generated by older versions
  1230   1234   ** of SQLite to be parsed by newer versions of SQLite without raising a
  1231   1235   ** malformed schema error.
  1232   1236   */
  1233   1237   static int exprNodeIsConstant(Walker *pWalker, Expr *pExpr){
  1234   1238   
  1235         -  /* If pWalker->u.i is 2 then any term of the expression that comes from
  1236         -  ** the ON or USING clauses of a join disqualifies the expression
         1239  +  /* If pWalker->eCode is 2 then any term of the expression that comes from
         1240  +  ** the ON or USING clauses of a left join disqualifies the expression
  1237   1241     ** from being considered constant. */
  1238         -  if( pWalker->u.i==2 && ExprHasProperty(pExpr, EP_FromJoin) ){
  1239         -    pWalker->u.i = 0;
         1242  +  if( pWalker->eCode==2 && ExprHasProperty(pExpr, EP_FromJoin) ){
         1243  +    pWalker->eCode = 0;
  1240   1244       return WRC_Abort;
  1241   1245     }
  1242   1246   
  1243   1247     switch( pExpr->op ){
  1244   1248       /* Consider functions to be constant if all their arguments are constant
  1245         -    ** and either pWalker->u.i==3 or 4 or the function as the SQLITE_FUNC_CONST
  1246         -    ** flag. */
         1249  +    ** and either pWalker->eCode==4 or 5 or the function has the
         1250  +    ** SQLITE_FUNC_CONST flag. */
  1247   1251       case TK_FUNCTION:
  1248         -      if( pWalker->u.i>=3 || ExprHasProperty(pExpr,EP_Constant) ){
         1252  +      if( pWalker->eCode>=4 || ExprHasProperty(pExpr,EP_Constant) ){
  1249   1253           return WRC_Continue;
         1254  +      }else{
         1255  +        pWalker->eCode = 0;
         1256  +        return WRC_Abort;
  1250   1257         }
  1251         -      /* Fall through */
  1252   1258       case TK_ID:
  1253   1259       case TK_COLUMN:
  1254   1260       case TK_AGG_FUNCTION:
  1255   1261       case TK_AGG_COLUMN:
  1256   1262         testcase( pExpr->op==TK_ID );
  1257   1263         testcase( pExpr->op==TK_COLUMN );
  1258   1264         testcase( pExpr->op==TK_AGG_FUNCTION );
  1259   1265         testcase( pExpr->op==TK_AGG_COLUMN );
  1260         -      pWalker->u.i = 0;
         1266  +      if( pWalker->eCode==3 && pExpr->iTable==pWalker->u.iCur ){
         1267  +        return WRC_Continue;
         1268  +      }else{
         1269  +        pWalker->eCode = 0;
  1261   1270         return WRC_Abort;
         1271  +      }
  1262   1272       case TK_VARIABLE:
  1263         -      if( pWalker->u.i==4 ){
         1273  +      if( pWalker->eCode==5 ){
  1264   1274           /* Silently convert bound parameters that appear inside of CREATE
  1265   1275           ** statements into a NULL when parsing the CREATE statement text out
  1266   1276           ** of the sqlite_master table */
  1267   1277           pExpr->op = TK_NULL;
  1268         -      }else if( pWalker->u.i==3 ){
         1278  +      }else if( pWalker->eCode==4 ){
  1269   1279           /* A bound parameter in a CREATE statement that originates from
  1270   1280           ** sqlite3_prepare() causes an error */
  1271         -        pWalker->u.i = 0;
         1281  +        pWalker->eCode = 0;
  1272   1282           return WRC_Abort;
  1273   1283         }
  1274   1284         /* Fall through */
  1275   1285       default:
  1276   1286         testcase( pExpr->op==TK_SELECT ); /* selectNodeIsConstant will disallow */
  1277   1287         testcase( pExpr->op==TK_EXISTS ); /* selectNodeIsConstant will disallow */
  1278   1288         return WRC_Continue;
  1279   1289     }
  1280   1290   }
  1281   1291   static int selectNodeIsConstant(Walker *pWalker, Select *NotUsed){
  1282   1292     UNUSED_PARAMETER(NotUsed);
  1283         -  pWalker->u.i = 0;
         1293  +  pWalker->eCode = 0;
  1284   1294     return WRC_Abort;
  1285   1295   }
  1286         -static int exprIsConst(Expr *p, int initFlag){
         1296  +static int exprIsConst(Expr *p, int initFlag, int iCur){
  1287   1297     Walker w;
  1288   1298     memset(&w, 0, sizeof(w));
  1289         -  w.u.i = initFlag;
         1299  +  w.eCode = initFlag;
  1290   1300     w.xExprCallback = exprNodeIsConstant;
  1291   1301     w.xSelectCallback = selectNodeIsConstant;
         1302  +  w.u.iCur = iCur;
  1292   1303     sqlite3WalkExpr(&w, p);
  1293         -  return w.u.i;
         1304  +  return w.eCode;
  1294   1305   }
  1295   1306   
  1296   1307   /*
  1297         -** Walk an expression tree.  Return 1 if the expression is constant
         1308  +** Walk an expression tree.  Return non-zero if the expression is constant
  1298   1309   ** and 0 if it involves variables or function calls.
  1299   1310   **
  1300   1311   ** For the purposes of this function, a double-quoted string (ex: "abc")
  1301   1312   ** is considered a variable but a single-quoted string (ex: 'abc') is
  1302   1313   ** a constant.
  1303   1314   */
  1304   1315   int sqlite3ExprIsConstant(Expr *p){
  1305         -  return exprIsConst(p, 1);
         1316  +  return exprIsConst(p, 1, 0);
  1306   1317   }
  1307   1318   
  1308   1319   /*
  1309         -** Walk an expression tree.  Return 1 if the expression is constant
         1320  +** Walk an expression tree.  Return non-zero if the expression is constant
  1310   1321   ** that does no originate from the ON or USING clauses of a join.
  1311   1322   ** Return 0 if it involves variables or function calls or terms from
  1312   1323   ** an ON or USING clause.
  1313   1324   */
  1314   1325   int sqlite3ExprIsConstantNotJoin(Expr *p){
  1315         -  return exprIsConst(p, 2);
         1326  +  return exprIsConst(p, 2, 0);
  1316   1327   }
  1317   1328   
  1318   1329   /*
  1319         -** Walk an expression tree.  Return 1 if the expression is constant
         1330  +** Walk an expression tree.  Return non-zero if the expression constant
         1331  +** for any single row of the table with cursor iCur.  In other words, the
         1332  +** expression must not refer to any non-deterministic function nor any
         1333  +** table other than iCur.
         1334  +*/
         1335  +int sqlite3ExprIsTableConstant(Expr *p, int iCur){
         1336  +  return exprIsConst(p, 3, iCur);
         1337  +}
         1338  +
         1339  +/*
         1340  +** Walk an expression tree.  Return non-zero if the expression is constant
  1320   1341   ** or a function call with constant arguments.  Return and 0 if there
  1321   1342   ** are any variables.
  1322   1343   **
  1323   1344   ** For the purposes of this function, a double-quoted string (ex: "abc")
  1324   1345   ** is considered a variable but a single-quoted string (ex: 'abc') is
  1325   1346   ** a constant.
  1326   1347   */
  1327   1348   int sqlite3ExprIsConstantOrFunction(Expr *p, u8 isInit){
  1328   1349     assert( isInit==0 || isInit==1 );
  1329         -  return exprIsConst(p, 3+isInit);
         1350  +  return exprIsConst(p, 4+isInit, 0);
  1330   1351   }
  1331   1352   
  1332   1353   /*
  1333   1354   ** If the expression p codes a constant integer that is small enough
  1334   1355   ** to fit in a 32-bit integer, return 1 and put the value of the integer
  1335   1356   ** in *pValue.  If the expression is not an integer or if it is too big
  1336   1357   ** to fit in a signed 32-bit integer, return 0 and leave *pValue unchanged.

Changes to src/resolve.c.

    24     24   ** This needs to occur when copying a TK_AGG_FUNCTION node from an
    25     25   ** outer query into an inner subquery.
    26     26   **
    27     27   ** incrAggFunctionDepth(pExpr,n) is the main routine.  incrAggDepth(..)
    28     28   ** is a helper function - a callback for the tree walker.
    29     29   */
    30     30   static int incrAggDepth(Walker *pWalker, Expr *pExpr){
    31         -  if( pExpr->op==TK_AGG_FUNCTION ) pExpr->op2 += pWalker->u.i;
           31  +  if( pExpr->op==TK_AGG_FUNCTION ) pExpr->op2 += pWalker->u.n;
    32     32     return WRC_Continue;
    33     33   }
    34     34   static void incrAggFunctionDepth(Expr *pExpr, int N){
    35     35     if( N>0 ){
    36     36       Walker w;
    37     37       memset(&w, 0, sizeof(w));
    38     38       w.xExprCallback = incrAggDepth;
    39         -    w.u.i = N;
           39  +    w.u.n = N;
    40     40       sqlite3WalkExpr(&w, pExpr);
    41     41     }
    42     42   }
    43     43   
    44     44   /*
    45     45   ** Turn the pExpr expression into an alias for the iCol-th column of the
    46     46   ** result set in pEList.

Changes to src/sqliteInt.h.

  2888   2888   */
  2889   2889   struct Walker {
  2890   2890     int (*xExprCallback)(Walker*, Expr*);     /* Callback for expressions */
  2891   2891     int (*xSelectCallback)(Walker*,Select*);  /* Callback for SELECTs */
  2892   2892     void (*xSelectCallback2)(Walker*,Select*);/* Second callback for SELECTs */
  2893   2893     Parse *pParse;                            /* Parser context.  */
  2894   2894     int walkerDepth;                          /* Number of subqueries */
         2895  +  u8 eCode;                                 /* A small processing code */
  2895   2896     union {                                   /* Extra data for callback */
  2896   2897       NameContext *pNC;                          /* Naming context */
  2897         -    int i;                                     /* Integer value */
         2898  +    int n;                                     /* A counter */
         2899  +    int iCur;                                  /* A cursor number */
  2898   2900       SrcList *pSrcList;                         /* FROM clause */
  2899   2901       struct SrcCount *pSrcCount;                /* Counting column references */
  2900   2902     } u;
  2901   2903   };
  2902   2904   
  2903   2905   /* Forward declarations */
  2904   2906   int sqlite3WalkExpr(Walker*, Expr*);
................................................................................
  3291   3293   void sqlite3RollbackTransaction(Parse*);
  3292   3294   void sqlite3Savepoint(Parse*, int, Token*);
  3293   3295   void sqlite3CloseSavepoints(sqlite3 *);
  3294   3296   void sqlite3LeaveMutexAndCloseZombie(sqlite3*);
  3295   3297   int sqlite3ExprIsConstant(Expr*);
  3296   3298   int sqlite3ExprIsConstantNotJoin(Expr*);
  3297   3299   int sqlite3ExprIsConstantOrFunction(Expr*, u8);
         3300  +int sqlite3ExprIsTableConstant(Expr*,int);
  3298   3301   int sqlite3ExprIsInteger(Expr*, int*);
  3299   3302   int sqlite3ExprCanBeNull(const Expr*);
  3300   3303   int sqlite3ExprNeedsNoAffinityChange(const Expr*, char);
  3301   3304   int sqlite3IsRowid(const char*);
  3302   3305   void sqlite3GenerateRowDelete(Parse*,Table*,Trigger*,int,int,int,i16,u8,u8,u8);
  3303   3306   void sqlite3GenerateRowIndexDelete(Parse*, Table*, int, int, int*);
  3304   3307   int sqlite3GenerateIndexKey(Parse*, Index*, int, int, int, int*,Index*,int);

Changes to src/where.c.

  1590   1590     int mxBitCol;               /* Maximum column in pSrc->colUsed */
  1591   1591     CollSeq *pColl;             /* Collating sequence to on a column */
  1592   1592     WhereLoop *pLoop;           /* The Loop object */
  1593   1593     char *zNotUsed;             /* Extra space on the end of pIdx */
  1594   1594     Bitmask idxCols;            /* Bitmap of columns used for indexing */
  1595   1595     Bitmask extraCols;          /* Bitmap of additional columns */
  1596   1596     u8 sentWarning = 0;         /* True if a warnning has been issued */
         1597  +  Expr *pPartial = 0;         /* Partial Index Expression */
         1598  +  int iContinue = 0;          /* Jump here to skip excluded rows */
  1597   1599   
  1598   1600     /* Generate code to skip over the creation and initialization of the
  1599   1601     ** transient index on 2nd and subsequent iterations of the loop. */
  1600   1602     v = pParse->pVdbe;
  1601   1603     assert( v!=0 );
  1602   1604     addrInit = sqlite3CodeOnce(pParse); VdbeCoverage(v);
  1603   1605   
................................................................................
  1605   1607     ** and used to match WHERE clause constraints */
  1606   1608     nKeyCol = 0;
  1607   1609     pTable = pSrc->pTab;
  1608   1610     pWCEnd = &pWC->a[pWC->nTerm];
  1609   1611     pLoop = pLevel->pWLoop;
  1610   1612     idxCols = 0;
  1611   1613     for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
         1614  +    if( pLoop->prereq==0
         1615  +     && sqlite3ExprIsTableConstant(pTerm->pExpr, pSrc->iCursor) ){
         1616  +      pPartial = sqlite3ExprAnd(pParse->db, pPartial,
         1617  +                                sqlite3ExprDup(pParse->db, pTerm->pExpr, 0));
         1618  +    }
  1612   1619       if( termCanDriveIndex(pTerm, pSrc, notReady) ){
  1613   1620         int iCol = pTerm->u.leftColumn;
  1614   1621         Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
  1615   1622         testcase( iCol==BMS );
  1616   1623         testcase( iCol==BMS-1 );
  1617   1624         if( !sentWarning ){
  1618   1625           sqlite3_log(SQLITE_WARNING_AUTOINDEX,
  1619   1626               "automatic index on %s(%s)", pTable->zName,
  1620   1627               pTable->aCol[iCol].zName);
  1621   1628           sentWarning = 1;
  1622   1629         }
  1623   1630         if( (idxCols & cMask)==0 ){
  1624         -        if( whereLoopResize(pParse->db, pLoop, nKeyCol+1) ) return;
         1631  +        if( whereLoopResize(pParse->db, pLoop, nKeyCol+1) ){
         1632  +          goto end_auto_index_create;
         1633  +        }
  1625   1634           pLoop->aLTerm[nKeyCol++] = pTerm;
  1626   1635           idxCols |= cMask;
  1627   1636         }
  1628   1637       }
  1629   1638     }
  1630   1639     assert( nKeyCol>0 );
  1631   1640     pLoop->u.btree.nEq = pLoop->nLTerm = nKeyCol;
................................................................................
  1650   1659     if( pSrc->colUsed & MASKBIT(BMS-1) ){
  1651   1660       nKeyCol += pTable->nCol - BMS + 1;
  1652   1661     }
  1653   1662     pLoop->wsFlags |= WHERE_COLUMN_EQ | WHERE_IDX_ONLY;
  1654   1663   
  1655   1664     /* Construct the Index object to describe this index */
  1656   1665     pIdx = sqlite3AllocateIndexObject(pParse->db, nKeyCol+1, 0, &zNotUsed);
  1657         -  if( pIdx==0 ) return;
         1666  +  if( pIdx==0 ) goto end_auto_index_create;
  1658   1667     pLoop->u.btree.pIndex = pIdx;
  1659   1668     pIdx->zName = "auto-index";
  1660   1669     pIdx->pTable = pTable;
  1661   1670     n = 0;
  1662   1671     idxCols = 0;
  1663   1672     for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
  1664   1673       if( termCanDriveIndex(pTerm, pSrc, notReady) ){
................................................................................
  1702   1711     assert( pLevel->iIdxCur>=0 );
  1703   1712     pLevel->iIdxCur = pParse->nTab++;
  1704   1713     sqlite3VdbeAddOp2(v, OP_OpenAutoindex, pLevel->iIdxCur, nKeyCol+1);
  1705   1714     sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
  1706   1715     VdbeComment((v, "for %s", pTable->zName));
  1707   1716   
  1708   1717     /* Fill the automatic index with content */
         1718  +  sqlite3ExprCachePush(pParse);
  1709   1719     addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur); VdbeCoverage(v);
         1720  +  if( pPartial ){
         1721  +    iContinue = sqlite3VdbeMakeLabel(v);
         1722  +    sqlite3ExprIfFalse(pParse, pPartial, iContinue, SQLITE_JUMPIFNULL);
         1723  +  }
  1710   1724     regRecord = sqlite3GetTempReg(pParse);
  1711   1725     sqlite3GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 0, 0, 0, 0);
  1712   1726     sqlite3VdbeAddOp2(v, OP_IdxInsert, pLevel->iIdxCur, regRecord);
  1713   1727     sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
         1728  +  if( pPartial ) sqlite3VdbeResolveLabel(v, iContinue);
  1714   1729     sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1); VdbeCoverage(v);
  1715   1730     sqlite3VdbeChangeP5(v, SQLITE_STMTSTATUS_AUTOINDEX);
  1716   1731     sqlite3VdbeJumpHere(v, addrTop);
  1717   1732     sqlite3ReleaseTempReg(pParse, regRecord);
         1733  +  sqlite3ExprCachePop(pParse);
  1718   1734     
  1719   1735     /* Jump here when skipping the initialization */
  1720   1736     sqlite3VdbeJumpHere(v, addrInit);
         1737  +
         1738  +end_auto_index_create:
         1739  +  sqlite3ExprDelete(pParse->db, pPartial);
  1721   1740   }
  1722   1741   #endif /* SQLITE_OMIT_AUTOMATIC_INDEX */
  1723   1742   
  1724   1743   #ifndef SQLITE_OMIT_VIRTUALTABLE
  1725   1744   /*
  1726   1745   ** Allocate and populate an sqlite3_index_info structure. It is the 
  1727   1746   ** responsibility of the caller to eventually release the structure

Added test/autoindex4.test.

            1  +# 2014-10-24
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +#
           12  +# This file implements regression tests for SQLite library.  The
           13  +# focus of this script is testing automatic index creation logic,
           14  +# and specifically creation of automatic partial indexes.
           15  +#
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +do_execsql_test autoindex4-1.0 {
           21  +  CREATE TABLE t1(a,b);
           22  +  INSERT INTO t1 VALUES(123,'abc'),(234,'def'),(234,'ghi'),(345,'jkl');
           23  +  CREATE TABLE t2(x,y);
           24  +  INSERT INTO t2 VALUES(987,'zyx'),(654,'wvu'),(987,'rqp');
           25  +
           26  +  SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=987 ORDER BY +b;
           27  +} {234 def 987 rqp | 234 def 987 zyx | 234 ghi 987 rqp | 234 ghi 987 zyx |}
           28  +do_execsql_test autoindex4-1.1 {
           29  +  SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=555;
           30  +} {}
           31  +
           32  +do_execsql_test autoindex4-1.2 {
           33  +  SELECT *, '|' FROM t1 LEFT JOIN t2 ON a=234 AND x=555;
           34  +} {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |}
           35  +do_execsql_test autoindex4-1.3 {
           36  +  SELECT *, '|' FROM t1 LEFT JOIN t2 ON x=555 WHERE a=234;
           37  +} {234 def {} {} | 234 ghi {} {} |}
           38  +do_execsql_test autoindex4-1.4 {
           39  +  SELECT *, '|' FROM t1 LEFT JOIN t2 WHERE a=234 AND x=555;
           40  +} {}
           41  +
           42  +
           43  +do_execsql_test autoindex4-2.0 {
           44  +  CREATE TABLE t3(e,f);
           45  +  INSERT INTO t3 VALUES(123,654),(555,444),(234,987);
           46  +
           47  +  SELECT (SELECT count(*) FROM t1, t2 WHERE a=e AND x=f), e, f, '|'
           48  +    FROM t3
           49  +   ORDER BY rowid;
           50  +} {1 123 654 | 0 555 444 | 4 234 987 |}
           51  +
           52  +finish_test