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: |
c6e9b9421805c904b20900b711fa0e51 |
| 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
Changes to src/resolve.c.
619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 ... 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 ... 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 |
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 );
................................................................................
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;
................................................................................
** 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
|
< | | > |
619 620 621 622 623 624 625 626 627 628 629 630 631 632 ... 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 ... 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 |
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 );
................................................................................
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;
................................................................................
** 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;
}
|