SQLite

Check-in [c6e9b94218]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c6e9b9421805c904b20900b711fa0e51773aef3e
User & Date: drh 2016-10-03 18:13:23.577
Context
2016-10-04
00:47
Do not bother to initialize unused fields in the BtreePayload object. (check-in: b10d0f939c user: drh tags: trunk)
2016-10-03
18:13
Allow deterministic functions in the WHERE clause of a partial index. (check-in: c6e9b94218 user: drh tags: trunk)
16:33
Size and performance optimizations on the sqlite3ExprAssignVarNumber() routine. (check-in: 109852e51e user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/resolve.c.
︙︙
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633

      const char *zColumn;
      const char *zTable;
      const char *zDb;
      Expr *pRight;

      /* if( pSrcList==0 ) break; */
      notValid(pParse, pNC, "the \".\" operator", NC_IdxExpr);
      /*notValid(pParse, pNC, "the \".\" operator", NC_PartIdx|NC_IsCheck, 1);*/
      pRight = pExpr->pRight;
      if( pRight->op==TK_ID ){
        zDb = 0;
        zTable = pExpr->pLeft->u.zToken;
        zColumn = pRight->u.zToken;
      }else{
        assert( pRight->op==TK_DOT );








<








619
620
621
622
623
624
625

626
627
628
629
630
631
632

      const char *zColumn;
      const char *zTable;
      const char *zDb;
      Expr *pRight;

      /* if( pSrcList==0 ) break; */
      notValid(pParse, pNC, "the \".\" operator", NC_IdxExpr);

      pRight = pExpr->pRight;
      if( pRight->op==TK_ID ){
        zDb = 0;
        zTable = pExpr->pLeft->u.zToken;
        zColumn = pRight->u.zToken;
      }else{
        assert( pRight->op==TK_DOT );
︙︙
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662

      int is_agg = 0;             /* True if is an aggregate function */
      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 */

      assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
      notValid(pParse, pNC, "functions", NC_PartIdx);
      zId = pExpr->u.zToken;
      nId = sqlite3Strlen30(zId);
      pDef = sqlite3FindFunction(pParse->db, zId, n, enc, 0);
      if( pDef==0 ){
        pDef = sqlite3FindFunction(pParse->db, zId, -2, enc, 0);
        if( pDef==0 ){
          no_such_func = 1;








|








647
648
649
650
651
652
653
654
655
656
657
658
659
660
661

      int is_agg = 0;             /* True if is an aggregate function */
      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 */

      assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
//      notValid(pParse, pNC, "functions", NC_PartIdx);
      zId = pExpr->u.zToken;
      nId = sqlite3Strlen30(zId);
      pDef = sqlite3FindFunction(pParse->db, zId, n, enc, 0);
      if( pDef==0 ){
        pDef = sqlite3FindFunction(pParse->db, zId, -2, enc, 0);
        if( pDef==0 ){
          no_such_func = 1;
︙︙
708
709
710
711
712
713
714
715

716
717
718
719
720
721
722

          ** constant because they are constant for the duration of one query */
          ExprSetProperty(pExpr,EP_ConstFunc);
        }
        if( (pDef->funcFlags & SQLITE_FUNC_CONSTANT)==0 ){
          /* Date/time functions that use 'now', and other functions like
          ** sqlite_version() that might change over time cannot be used
          ** in an index. */
          notValid(pParse, pNC, "non-deterministic functions", NC_IdxExpr);

        }
      }
      if( is_agg && (pNC->ncFlags & NC_AllowAgg)==0 ){
        sqlite3ErrorMsg(pParse, "misuse of aggregate function %.*s()", nId,zId);
        pNC->nErr++;
        is_agg = 0;
      }else if( no_such_func && pParse->db->init.busy==0








|
>








707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722

          ** constant because they are constant for the duration of one query */
          ExprSetProperty(pExpr,EP_ConstFunc);
        }
        if( (pDef->funcFlags & SQLITE_FUNC_CONSTANT)==0 ){
          /* Date/time functions that use 'now', and other functions like
          ** sqlite_version() that might change over time cannot be used
          ** in an index. */
          notValid(pParse, pNC, "non-deterministic functions",
                   NC_IdxExpr|NC_PartIdx);
        }
      }
      if( is_agg && (pNC->ncFlags & NC_AllowAgg)==0 ){
        sqlite3ErrorMsg(pParse, "misuse of aggregate function %.*s()", nId,zId);
        pNC->nErr++;
        is_agg = 0;
      }else if( no_such_func && pParse->db->init.busy==0
︙︙
Changes to test/index6.test.
︙︙
61
62
63
64
65
66
67
68
69
70
71
72

73


74
75
76
77
78
79
80

    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}}



do_test index6-1.10 {
  execsql {
    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
    PRAGMA integrity_check;
  }








|




>
|
>
>








61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

    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 {non-deterministic 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%';
  }
} {0 {}}
do_execsql_test index6-1.7 {
  DROP INDEX IF EXISTS bad1;
}

do_test index6-1.10 {
  execsql {
    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
    PRAGMA integrity_check;
  }
︙︙
Changes to test/index7.test.
︙︙
95
96
97
98
99
100
101
102
103
104
105
106











107


108
109
110
111
112
113
114

    CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1;
  }
} {1 {parameters prohibited in partial index WHERE clauses}}
do_test index7-1.5 {
  catchsql {
    CREATE INDEX bad1 ON t1(a,b) WHERE a!=random();
  }
} {1 {functions prohibited in partial index WHERE clauses}}
do_test index7-1.6 {
  catchsql {
    CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';
  }











} {1 {functions prohibited in partial index WHERE clauses}}



do_test index7-1.10 {
  execsql {
    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
    PRAGMA integrity_check;
  }








|




>
>
>
>
>
>
>
>
>
>
>
|
>
>








95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127

    CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1;
  }
} {1 {parameters prohibited in partial index WHERE clauses}}
do_test index7-1.5 {
  catchsql {
    CREATE INDEX bad1 ON t1(a,b) WHERE a!=random();
  }
} {1 {non-deterministic functions prohibited in partial index WHERE clauses}}
do_test index7-1.6 {
  catchsql {
    CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';
  }
} {0 {}}
do_execsql_test index7-1.7 {
  INSERT INTO t1(a,b,c)
     VALUES('abcde',1,101),('abdef',2,102),('xyz',3,103),('abcz',4,104);
  SELECT c FROM t1 WHERE a NOT LIKE 'abc%' AND a=7 ORDER BY +b;
} {7}
do_execsql_test index7-1.7eqp {
  EXPLAIN QUERY PLAN
  SELECT b FROM t1 WHERE a NOT LIKE 'abc%' AND a=7 ORDER BY +b;
} {/SEARCH TABLE t1 USING COVERING INDEX bad1 /}
do_execsql_test index7-1.8 {
  DELETE FROM t1 WHERE c>=101;
  DROP INDEX IF EXISTS bad1;
} {}

do_test index7-1.10 {
  execsql {
    ANALYZE;
    SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
    PRAGMA integrity_check;
  }
︙︙