/ Check-in [c6e9b942]
Login

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

Overview
Comment:Allow deterministic functions in the WHERE clause of a partial index.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c6e9b9421805c904b20900b711fa0e51773aef3e
User & Date: drh 2016-10-03 18:13:23
Context
2016-10-04
00:47
Do not bother to initialize unused fields in the BtreePayload object. check-in: b10d0f93 user: drh tags: trunk
2016-10-03
18:13
Allow deterministic functions in the WHERE clause of a partial index. check-in: c6e9b942 user: drh tags: trunk
16:33
Size and performance optimizations on the sqlite3ExprAssignVarNumber() routine. check-in: 109852e5 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/resolve.c.

   619    619         const char *zColumn;
   620    620         const char *zTable;
   621    621         const char *zDb;
   622    622         Expr *pRight;
   623    623   
   624    624         /* if( pSrcList==0 ) break; */
   625    625         notValid(pParse, pNC, "the \".\" operator", NC_IdxExpr);
   626         -      /*notValid(pParse, pNC, "the \".\" operator", NC_PartIdx|NC_IsCheck, 1);*/
   627    626         pRight = pExpr->pRight;
   628    627         if( pRight->op==TK_ID ){
   629    628           zDb = 0;
   630    629           zTable = pExpr->pLeft->u.zToken;
   631    630           zColumn = pRight->u.zToken;
   632    631         }else{
   633    632           assert( pRight->op==TK_DOT );
................................................................................
   648    647         int is_agg = 0;             /* True if is an aggregate function */
   649    648         int nId;                    /* Number of characters in function name */
   650    649         const char *zId;            /* The function name. */
   651    650         FuncDef *pDef;              /* Information about the function */
   652    651         u8 enc = ENC(pParse->db);   /* The database encoding */
   653    652   
   654    653         assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
   655         -      notValid(pParse, pNC, "functions", NC_PartIdx);
          654  +//      notValid(pParse, pNC, "functions", NC_PartIdx);
   656    655         zId = pExpr->u.zToken;
   657    656         nId = sqlite3Strlen30(zId);
   658    657         pDef = sqlite3FindFunction(pParse->db, zId, n, enc, 0);
   659    658         if( pDef==0 ){
   660    659           pDef = sqlite3FindFunction(pParse->db, zId, -2, enc, 0);
   661    660           if( pDef==0 ){
   662    661             no_such_func = 1;
................................................................................
   708    707             ** constant because they are constant for the duration of one query */
   709    708             ExprSetProperty(pExpr,EP_ConstFunc);
   710    709           }
   711    710           if( (pDef->funcFlags & SQLITE_FUNC_CONSTANT)==0 ){
   712    711             /* Date/time functions that use 'now', and other functions like
   713    712             ** sqlite_version() that might change over time cannot be used
   714    713             ** in an index. */
   715         -          notValid(pParse, pNC, "non-deterministic functions", NC_IdxExpr);
          714  +          notValid(pParse, pNC, "non-deterministic functions",
          715  +                   NC_IdxExpr|NC_PartIdx);
   716    716           }
   717    717         }
   718    718         if( is_agg && (pNC->ncFlags & NC_AllowAgg)==0 ){
   719    719           sqlite3ErrorMsg(pParse, "misuse of aggregate function %.*s()", nId,zId);
   720    720           pNC->nErr++;
   721    721           is_agg = 0;
   722    722         }else if( no_such_func && pParse->db->init.busy==0

Changes to test/index6.test.

    61     61       CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1;
    62     62     }
    63     63   } {1 {parameters prohibited in partial index WHERE clauses}}
    64     64   do_test index6-1.5 {
    65     65     catchsql {
    66     66       CREATE INDEX bad1 ON t1(a,b) WHERE a!=random();
    67     67     }
    68         -} {1 {functions prohibited in partial index WHERE clauses}}
           68  +} {1 {non-deterministic functions prohibited in partial index WHERE clauses}}
    69     69   do_test index6-1.6 {
    70     70     catchsql {
    71     71       CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';
    72     72     }
    73         -} {1 {functions prohibited in partial index WHERE clauses}}
           73  +} {0 {}}
           74  +do_execsql_test index6-1.7 {
           75  +  DROP INDEX IF EXISTS bad1;
           76  +}
    74     77   
    75     78   do_test index6-1.10 {
    76     79     execsql {
    77     80       ANALYZE;
    78     81       SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
    79     82       PRAGMA integrity_check;
    80     83     }

Changes to test/index7.test.

    95     95       CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1;
    96     96     }
    97     97   } {1 {parameters prohibited in partial index WHERE clauses}}
    98     98   do_test index7-1.5 {
    99     99     catchsql {
   100    100       CREATE INDEX bad1 ON t1(a,b) WHERE a!=random();
   101    101     }
   102         -} {1 {functions prohibited in partial index WHERE clauses}}
          102  +} {1 {non-deterministic functions prohibited in partial index WHERE clauses}}
   103    103   do_test index7-1.6 {
   104    104     catchsql {
   105    105       CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';
   106    106     }
   107         -} {1 {functions prohibited in partial index WHERE clauses}}
          107  +} {0 {}}
          108  +do_execsql_test index7-1.7 {
          109  +  INSERT INTO t1(a,b,c)
          110  +     VALUES('abcde',1,101),('abdef',2,102),('xyz',3,103),('abcz',4,104);
          111  +  SELECT c FROM t1 WHERE a NOT LIKE 'abc%' AND a=7 ORDER BY +b;
          112  +} {7}
          113  +do_execsql_test index7-1.7eqp {
          114  +  EXPLAIN QUERY PLAN
          115  +  SELECT b FROM t1 WHERE a NOT LIKE 'abc%' AND a=7 ORDER BY +b;
          116  +} {/SEARCH TABLE t1 USING COVERING INDEX bad1 /}
          117  +do_execsql_test index7-1.8 {
          118  +  DELETE FROM t1 WHERE c>=101;
          119  +  DROP INDEX IF EXISTS bad1;
          120  +} {}
   108    121   
   109    122   do_test index7-1.10 {
   110    123     execsql {
   111    124       ANALYZE;
   112    125       SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
   113    126       PRAGMA integrity_check;
   114    127     }