Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Make the distinction between truly deterministic functions and date/time functions which only return the same answer for a single query. Only truly deterministic functions are allowed in indexes. Add new expression index test cases. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | index-expr |
Files: | files | file ages | folders |
SHA1: |
c77554b5c42327106a7b90334e9cc3c0 |
User & Date: | drh 2015-08-31 17:34:41.308 |
Context
2015-08-31
| ||
18:13 | Case should not be significant when comparing function names. (check-in: e2f1caf117 user: drh tags: index-expr) | |
17:34 | Make the distinction between truly deterministic functions and date/time functions which only return the same answer for a single query. Only truly deterministic functions are allowed in indexes. Add new expression index test cases. (check-in: c77554b5c4 user: drh tags: index-expr) | |
15:58 | Improved analysis and usage of indexed expressions in the query planner. (check-in: f889369638 user: drh tags: index-expr) | |
Changes
Changes to src/date.c.
︙ | ︙ | |||
1111 1112 1113 1114 1115 1116 1117 | ** This function registered all of the above C functions as SQL ** functions. This should be the only routine in this file with ** external linkage. */ void sqlite3RegisterDateTimeFunctions(void){ static SQLITE_WSD FuncDef aDateTimeFuncs[] = { #ifndef SQLITE_OMIT_DATETIME_FUNCS | | | | | | | | | | 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 | ** This function registered all of the above C functions as SQL ** functions. This should be the only routine in this file with ** external linkage. */ void sqlite3RegisterDateTimeFunctions(void){ static SQLITE_WSD FuncDef aDateTimeFuncs[] = { #ifndef SQLITE_OMIT_DATETIME_FUNCS DFUNCTION(julianday, -1, 0, 0, juliandayFunc ), DFUNCTION(date, -1, 0, 0, dateFunc ), DFUNCTION(time, -1, 0, 0, timeFunc ), DFUNCTION(datetime, -1, 0, 0, datetimeFunc ), DFUNCTION(strftime, -1, 0, 0, strftimeFunc ), DFUNCTION(current_time, 0, 0, 0, ctimeFunc ), DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc), DFUNCTION(current_date, 0, 0, 0, cdateFunc ), #else STR_FUNCTION(current_time, 0, "%H:%M:%S", 0, currentTimeFunc), STR_FUNCTION(current_date, 0, "%Y-%m-%d", 0, currentTimeFunc), STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc), #endif }; int i; |
︙ | ︙ |
Changes to src/resolve.c.
︙ | ︙ | |||
728 729 730 731 732 733 734 | pDef->zName); pNC->nErr++; } pExpr->op = TK_NULL; return WRC_Prune; } #endif | | > > > < > > > > | 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 | pDef->zName); pNC->nErr++; } pExpr->op = TK_NULL; return WRC_Prune; } #endif if( pDef->funcFlags & (SQLITE_FUNC_CONSTANT|SQLITE_FUNC_DATETIME) ){ /* For the purposes of the EP_ConstFunc flag, date and time ** functions are considered constant because the the time does ** not change for the duration of a query. */ ExprSetProperty(pExpr,EP_ConstFunc); } if( (pDef->funcFlags & SQLITE_FUNC_CONSTANT)==0 ){ /* DATETIME functions are considered non-deterministic because of ** the 'now' capability */ notValid(pParse, pNC, "non-deterministic functions", NC_IdxExpr, 0); } } if( is_agg && (pNC->ncFlags & NC_AllowAgg)==0 ){ sqlite3ErrorMsg(pParse, "misuse of aggregate function %.*s()", nId,zId); pNC->nErr++; is_agg = 0; |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1380 1381 1382 1383 1384 1385 1386 | }; /* ** Possible values for FuncDef.flags. Note that the _LENGTH and _TYPEOF ** values must correspond to OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG. There ** are assert() statements in the code to verify this. */ | | | | | | | | | | | | | > > > > > > > > | 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 | }; /* ** Possible values for FuncDef.flags. Note that the _LENGTH and _TYPEOF ** values must correspond to OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG. There ** are assert() statements in the code to verify this. */ #define SQLITE_FUNC_ENCMASK 0x0003 /* SQLITE_UTF8, SQLITE_UTF16BE or UTF16LE */ #define SQLITE_FUNC_LIKE 0x0004 /* Candidate for the LIKE optimization */ #define SQLITE_FUNC_CASE 0x0008 /* Case-sensitive LIKE-type function */ #define SQLITE_FUNC_EPHEM 0x0010 /* Ephemeral. Delete with VDBE */ #define SQLITE_FUNC_NEEDCOLL 0x0020 /* sqlite3GetFuncCollSeq() might be called*/ #define SQLITE_FUNC_LENGTH 0x0040 /* Built-in length() function */ #define SQLITE_FUNC_TYPEOF 0x0080 /* Built-in typeof() function */ #define SQLITE_FUNC_COUNT 0x0100 /* Built-in count(*) aggregate */ #define SQLITE_FUNC_COALESCE 0x0200 /* Built-in coalesce() or ifnull() */ #define SQLITE_FUNC_UNLIKELY 0x0400 /* Built-in unlikely() function */ #define SQLITE_FUNC_CONSTANT 0x0800 /* Constant inputs give a constant output */ #define SQLITE_FUNC_MINMAX 0x1000 /* True for min() and max() aggregates */ #define SQLITE_FUNC_DATETIME 0x2000 /* Function returns a date and/or time */ /* ** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are ** used to create the initializers for the FuncDef structures. ** ** FUNCTION(zName, nArg, iArg, bNC, xFunc) ** Used to create a scalar function definition of a function zName ** implemented by C function xFunc that accepts nArg arguments. The ** value passed as iArg is cast to a (void*) and made available ** as the user-data (sqlite3_user_data()) for the function. If ** argument bNC is true, then the SQLITE_FUNC_NEEDCOLL flag is set. ** ** VFUNCTION(zName, nArg, iArg, bNC, xFunc) ** Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag. ** ** DFUNCTION(zName, nArg, iArg, bNC, xFunc) ** Like FUNCTION except it omits the SQLITE_FUNC_CONSTANT flag and ** adds the SQLITE_FUNC_DATETIME flag. Used for date & time functions. ** ** AGGREGATE(zName, nArg, iArg, bNC, xStep, xFinal) ** Used to create an aggregate function definition implemented by ** the C functions xStep and xFinal. The first four parameters ** are interpreted in the same way as the first 4 parameters to ** FUNCTION(). ** ** LIKEFUNC(zName, nArg, pArg, flags) ** Used to create a scalar function definition of a function zName ** that accepts nArg arguments and is implemented by a call to C ** function likeFunc. Argument pArg is cast to a (void *) and made ** available as the function user-data (sqlite3_user_data()). The ** FuncDef.flags variable is set to the value passed as the flags ** parameter. */ #define FUNCTION(zName, nArg, iArg, bNC, xFunc) \ {nArg, SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0} #define VFUNCTION(zName, nArg, iArg, bNC, xFunc) \ {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0} #define DFUNCTION(zName, nArg, iArg, bNC, xFunc) \ {nArg, SQLITE_FUNC_DATETIME|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0} #define FUNCTION2(zName, nArg, iArg, bNC, xFunc, extraFlags) \ {nArg,SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags,\ SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0} #define STR_FUNCTION(zName, nArg, pArg, bNC, xFunc) \ {nArg, SQLITE_FUNC_CONSTANT|SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ pArg, 0, xFunc, 0, 0, #zName, 0, 0} #define LIKEFUNC(zName, nArg, arg, flags) \ |
︙ | ︙ | |||
2117 2118 2119 2120 2121 2122 2123 | #define EP_Skip 0x001000 /* COLLATE, AS, or UNLIKELY */ #define EP_Reduced 0x002000 /* Expr struct EXPR_REDUCEDSIZE bytes only */ #define EP_TokenOnly 0x004000 /* Expr struct EXPR_TOKENONLYSIZE bytes only */ #define EP_Static 0x008000 /* Held in memory not obtained from malloc() */ #define EP_MemToken 0x010000 /* Need to sqlite3DbFree() Expr.zToken */ #define EP_NoReduce 0x020000 /* Cannot EXPRDUP_REDUCE this Expr */ #define EP_Unlikely 0x040000 /* unlikely() or likelihood() function */ | | | 2125 2126 2127 2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 | #define EP_Skip 0x001000 /* COLLATE, AS, or UNLIKELY */ #define EP_Reduced 0x002000 /* Expr struct EXPR_REDUCEDSIZE bytes only */ #define EP_TokenOnly 0x004000 /* Expr struct EXPR_TOKENONLYSIZE bytes only */ #define EP_Static 0x008000 /* Held in memory not obtained from malloc() */ #define EP_MemToken 0x010000 /* Need to sqlite3DbFree() Expr.zToken */ #define EP_NoReduce 0x020000 /* Cannot EXPRDUP_REDUCE this Expr */ #define EP_Unlikely 0x040000 /* unlikely() or likelihood() function */ #define EP_ConstFunc 0x080000 /* A SQLITE_FUNC_CONSTANT or _DATETIME function */ #define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */ #define EP_Subquery 0x200000 /* Tree contains a TK_SELECT operator */ /* ** Combinations of two or more EP_* flags */ #define EP_Propagate (EP_Collate|EP_Subquery) /* Propagate these bits up tree */ |
︙ | ︙ |
Changes to test/indexexpr1.test.
︙ | ︙ | |||
14 15 16 17 18 19 20 | set testdir [file dirname $argv0] source $testdir/tester.tcl do_execsql_test indexexpr1-100 { CREATE TABLE t1(a,b,c); INSERT INTO t1(a,b,c) | | | | | | | | | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 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 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 | set testdir [file dirname $argv0] source $testdir/tester.tcl do_execsql_test indexexpr1-100 { CREATE TABLE t1(a,b,c); INSERT INTO t1(a,b,c) VALUES('In_the_beginning_was_the_Word',1,1), ('and_the_Word_was_with_God',1,2), ('and_the_Word_was_God',1,3), ('The_same_was_in_the_beginning_with_God',2,1), ('All_things_were_made_by_him',3,1), ('and_without_him_was_not_any_thing_made_that_was_made',3,2); CREATE INDEX t1a1 ON t1(substr(a,1,12)); } {} do_execsql_test indexexpr1-110 { SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c; } {1 2 | 1 3 |} do_execsql_test indexexpr1-110eqp { EXPLAIN QUERY PLAN SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c; } {/USING INDEX t1a1/} do_execsql_test indexexpr1-120 { SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c; } {1 2 | 1 3 |} do_execsql_test indexexpr1-120eqp { EXPLAIN QUERY PLAN SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c; } {/USING INDEX t1a1/} do_execsql_test indexexpr1-130 { CREATE INDEX t1ba ON t1(b,substr(a,2,3),c); SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c; } {2 3} do_execsql_test indexexpr1-130eqp { EXPLAIN QUERY PLAN SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c; } {/USING INDEX t1ba/} do_execsql_test indexexpr1-140 { SELECT rowid, substr(a,b,3), '|' FROM t1 ORDER BY 2; } {1 In_ | 2 and | 3 and | 6 d_w | 4 he_ | 5 l_t |} do_execsql_test indexexpr1-141 { CREATE INDEX t1abx ON t1(substr(a,b,3)); SELECT rowid FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +rowid; } {1 2 3} do_execsql_test indexexpr1-141eqp { EXPLAIN QUERY PLAN SELECT rowid FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +rowid; } {/USING INDEX t1abx/} do_execsql_test indexexpr1-142 { SELECT rowid FROM t1 WHERE +substr(a,b,3)<='and' ORDER BY +rowid; } {1 2 3} do_execsql_test indexexpr1-150 { SELECT rowid FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz') ORDER BY +rowid; } {2 3 5} do_execsql_test indexexpr1-150eqp { EXPLAIN QUERY PLAN SELECT rowid FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz') ORDER BY +rowid; } {/USING INDEX t1abx/} do_execsql_test indexexpr1-200 { DROP TABLE t1; CREATE TABLE t1(id ANY PRIMARY KEY, a,b,c) WITHOUT ROWID; INSERT INTO t1(id,a,b,c) VALUES(1,'In_the_beginning_was_the_Word',1,1), (2,'and_the_Word_was_with_God',1,2), (3,'and_the_Word_was_God',1,3), (4,'The_same_was_in_the_beginning_with_God',2,1), (5,'All_things_were_made_by_him',3,1), (6,'and_without_him_was_not_any_thing_made_that_was_made',3,2); CREATE INDEX t1a1 ON t1(substr(a,1,12)); } {} do_execsql_test indexexpr1-210 { SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c; } {1 2 | 1 3 |} do_execsql_test indexexpr1-210eqp { EXPLAIN QUERY PLAN SELECT b, c, '|' FROM t1 WHERE substr(a,1,12)=='and_the_Word' ORDER BY b, c; } {/USING INDEX t1a1/} do_execsql_test indexexpr1-220 { SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c; } {1 2 | 1 3 |} do_execsql_test indexexpr1-220eqp { EXPLAIN QUERY PLAN SELECT b, c, '|' FROM t1 WHERE 'and_the_Word'==substr(a,1,12) ORDER BY b, c; } {/USING INDEX t1a1/} do_execsql_test indexexpr1-230 { CREATE INDEX t1ba ON t1(b,substr(a,2,3),c); SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c; } {2 3} do_execsql_test indexexpr1-230eqp { EXPLAIN QUERY PLAN SELECT c FROM t1 WHERE b=1 AND substr(a,2,3)='nd_' ORDER BY c; } {/USING INDEX t1ba/} do_execsql_test indexexpr1-240 { SELECT id, substr(a,b,3), '|' FROM t1 ORDER BY 2; } {1 In_ | 2 and | 3 and | 6 d_w | 4 he_ | 5 l_t |} do_execsql_test indexexpr1-241 { CREATE INDEX t1abx ON t1(substr(a,b,3)); SELECT id FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +id; } {1 2 3} do_execsql_test indexexpr1-241eqp { EXPLAIN QUERY PLAN SELECT id FROM t1 WHERE substr(a,b,3)<='and' ORDER BY +id; } {/USING INDEX t1abx/} do_execsql_test indexexpr1-242 { SELECT id FROM t1 WHERE +substr(a,b,3)<='and' ORDER BY +id; } {1 2 3} do_execsql_test indexexpr1-250 { SELECT id FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz') ORDER BY +id; } {2 3 5} do_execsql_test indexexpr1-250eqp { EXPLAIN QUERY PLAN SELECT id FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz') ORDER BY +id; } {/USING INDEX t1abx/} do_catchsql_test indexexpr1-300 { CREATE TABLE t2(a,b,c); CREATE INDEX t2x1 ON t2(a,b+random()); } {1 {non-deterministic functions prohibited in index expressions}} do_catchsql_test indexexpr1-301 { CREATE INDEX t2x1 ON t2(a+julianday('now')); } {1 {non-deterministic functions prohibited in index expressions}} do_catchsql_test indexexpr1-310 { CREATE INDEX t2x2 ON t2(a,b+(SELECT 15)); } {1 {subqueries prohibited in index expressions}} finish_test |