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: |
c6e9b9421805c904b20900b711fa0e51 |
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
Changes to src/resolve.c.
︙ | ︙ | |||
619 620 621 622 623 624 625 | const char *zColumn; const char *zTable; const char *zDb; Expr *pRight; /* if( pSrcList==0 ) break; */ notValid(pParse, pNC, "the \".\" operator", NC_IdxExpr); | < | 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 | 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) ); | | | 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 | ** 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. */ | | > | 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 | 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(); } | | > | > > | 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 | 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(); } | | > > > > > > > > > > > | > > | 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; } |
︙ | ︙ |