Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Combine the Parse.ckBase and Parse.iSelfTab fields into just Parse.iSelfTab. This fixes a problem with date/time functions in check-constraints. Add some test cases for date/time functions in index expressions and check constraints. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | index-on-date-func |
| Files: | files | file ages | folders |
| SHA3-256: |
22eda0985ecd1f456c073e6ad735a841 |
| User & Date: | drh 2017-07-20 13:17:08 |
Context
|
2017-07-20
| ||
| 14:36 | New test cases for date/time functions in indexes on expressions, in the WHERE clause of partial indexes, and in CHECK constraints. Closed-Leaf check-in: b7f70c7f user: drh tags: index-on-date-func | |
| 13:17 | Combine the Parse.ckBase and Parse.iSelfTab fields into just Parse.iSelfTab. This fixes a problem with date/time functions in check-constraints. Add some test cases for date/time functions in index expressions and check constraints. check-in: 22eda098 user: drh tags: index-on-date-func | |
|
2017-07-19
| ||
| 19:48 | Allow indexes to be created on date/time functions as long as the 'now' date and the 'localtime' and 'utc' modifiers are not used. check-in: 0a5e1c04 user: drh tags: index-on-date-func | |
Changes
Changes to src/date.c.
382 382 DateTime *p 383 383 ){ 384 384 double r; 385 385 if( parseYyyyMmDd(zDate,p)==0 ){ 386 386 return 0; 387 387 }else if( parseHhMmSs(zDate, p)==0 ){ 388 388 return 0; 389 - }else if( sqlite3StrICmp(zDate,"now")==0 ){ 390 - sqlite3VdbePureFuncOnly(context); 389 + }else if( sqlite3StrICmp(zDate,"now")==0 && sqlite3NotPureFunc(context) ){ 391 390 return setDateTimeToCurrent(context, p); 392 391 }else if( sqlite3AtoF(zDate, &r, sqlite3Strlen30(zDate), SQLITE_UTF8) ){ 393 392 setRawDateNumber(p, r); 394 393 return 0; 395 394 } 396 395 return 1; 397 396 } ................................................................................ 666 665 #ifndef SQLITE_OMIT_LOCALTIME 667 666 case 'l': { 668 667 /* localtime 669 668 ** 670 669 ** Assuming the current time value is UTC (a.k.a. GMT), shift it to 671 670 ** show local time. 672 671 */ 673 - if( sqlite3_stricmp(z, "localtime")==0 ){ 674 - sqlite3VdbePureFuncOnly(pCtx); 672 + if( sqlite3_stricmp(z, "localtime")==0 && sqlite3NotPureFunc(pCtx) ){ 675 673 computeJD(p); 676 674 p->iJD += localtimeOffset(p, pCtx, &rc); 677 675 clearYMD_HMS_TZ(p); 678 676 } 679 677 break; 680 678 } 681 679 #endif ................................................................................ 693 691 p->iJD = (sqlite3_int64)r; 694 692 p->validJD = 1; 695 693 p->rawS = 0; 696 694 rc = 0; 697 695 } 698 696 } 699 697 #ifndef SQLITE_OMIT_LOCALTIME 700 - else if( sqlite3_stricmp(z, "utc")==0 ){ 701 - sqlite3VdbePureFuncOnly(pCtx); 698 + else if( sqlite3_stricmp(z, "utc")==0 && sqlite3NotPureFunc(pCtx) ){ 702 699 if( p->tzSet==0 ){ 703 700 sqlite3_int64 c1; 704 701 computeJD(p); 705 702 c1 = localtimeOffset(p, pCtx, &rc); 706 703 if( rc==SQLITE_OK ){ 707 704 p->iJD -= c1; 708 705 clearYMD_HMS_TZ(p);
Changes to src/expr.c.
3480 3480 return target; 3481 3481 } 3482 3482 /* Otherwise, fall thru into the TK_COLUMN case */ 3483 3483 } 3484 3484 case TK_COLUMN: { 3485 3485 int iTab = pExpr->iTable; 3486 3486 if( iTab<0 ){ 3487 - if( pParse->ckBase>0 ){ 3487 + if( pParse->iSelfTab<0 ){ 3488 3488 /* Generating CHECK constraints or inserting into partial index */ 3489 - return pExpr->iColumn + pParse->ckBase; 3489 + return pExpr->iColumn - pParse->iSelfTab; 3490 3490 }else{ 3491 3491 /* Coding an expression that is part of an index where column names 3492 3492 ** in the index refer to the table to which the index belongs */ 3493 3493 iTab = pParse->iSelfTab - 1; 3494 3494 } 3495 3495 } 3496 3496 return sqlite3ExprCodeGetColumn(pParse, pExpr->pTab,
Changes to src/insert.c.
1329 1329 } 1330 1330 1331 1331 /* Test all CHECK constraints 1332 1332 */ 1333 1333 #ifndef SQLITE_OMIT_CHECK 1334 1334 if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){ 1335 1335 ExprList *pCheck = pTab->pCheck; 1336 - pParse->ckBase = regNewData+1; 1336 + pParse->iSelfTab = -(regNewData+1); 1337 1337 onError = overrideError!=OE_Default ? overrideError : OE_Abort; 1338 1338 for(i=0; i<pCheck->nExpr; i++){ 1339 1339 int allOk; 1340 1340 Expr *pExpr = pCheck->a[i].pExpr; 1341 1341 if( aiChng && checkConstraintUnchanged(pExpr, aiChng, pkChng) ) continue; 1342 1342 allOk = sqlite3VdbeMakeLabel(v); 1343 1343 sqlite3ExprIfTrue(pParse, pExpr, allOk, SQLITE_JUMPIFNULL); ................................................................................ 1349 1349 if( onError==OE_Replace ) onError = OE_Abort; /* IMP: R-15569-63625 */ 1350 1350 sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_CHECK, 1351 1351 onError, zName, P4_TRANSIENT, 1352 1352 P5_ConstraintCheck); 1353 1353 } 1354 1354 sqlite3VdbeResolveLabel(v, allOk); 1355 1355 } 1356 + pParse->iSelfTab = 0; 1356 1357 } 1357 1358 #endif /* !defined(SQLITE_OMIT_CHECK) */ 1358 1359 1359 1360 /* If rowid is changing, make sure the new rowid does not previously 1360 1361 ** exist in the table. 1361 1362 */ 1362 1363 if( pkChng && pPk==0 ){ ................................................................................ 1493 1494 } 1494 1495 iThisCur = iIdxCur+ix; 1495 1496 addrUniqueOk = sqlite3VdbeMakeLabel(v); 1496 1497 1497 1498 /* Skip partial indices for which the WHERE clause is not true */ 1498 1499 if( pIdx->pPartIdxWhere ){ 1499 1500 sqlite3VdbeAddOp2(v, OP_Null, 0, aRegIdx[ix]); 1500 - pParse->ckBase = regNewData+1; 1501 + pParse->iSelfTab = -(regNewData+1); 1501 1502 sqlite3ExprIfFalseDup(pParse, pIdx->pPartIdxWhere, addrUniqueOk, 1502 1503 SQLITE_JUMPIFNULL); 1503 - pParse->ckBase = 0; 1504 + pParse->iSelfTab = 0; 1504 1505 } 1505 1506 1506 1507 /* Create a record for this index entry as it should appear after 1507 1508 ** the insert or update. Store that record in the aRegIdx[ix] register 1508 1509 */ 1509 1510 regIdx = aRegIdx[ix]+1; 1510 1511 for(i=0; i<pIdx->nColumn; i++){ 1511 1512 int iField = pIdx->aiColumn[i]; 1512 1513 int x; 1513 1514 if( iField==XN_EXPR ){ 1514 - pParse->ckBase = regNewData+1; 1515 + pParse->iSelfTab = -(regNewData+1); 1515 1516 sqlite3ExprCodeCopy(pParse, pIdx->aColExpr->a[i].pExpr, regIdx+i); 1516 - pParse->ckBase = 0; 1517 + pParse->iSelfTab = 0; 1517 1518 VdbeComment((v, "%s column %d", pIdx->zName, i)); 1518 1519 }else{ 1519 1520 if( iField==XN_ROWID || iField==pTab->iPKey ){ 1520 1521 x = regNewData; 1521 1522 }else{ 1522 1523 x = iField + regNewData + 1; 1523 1524 }
Changes to src/pragma.c.
1590 1590 if( pTab->pCheck && (db->flags & SQLITE_IgnoreChecks)==0 ){ 1591 1591 ExprList *pCheck = sqlite3ExprListDup(db, pTab->pCheck, 0); 1592 1592 if( db->mallocFailed==0 ){ 1593 1593 int addrCkFault = sqlite3VdbeMakeLabel(v); 1594 1594 int addrCkOk = sqlite3VdbeMakeLabel(v); 1595 1595 char *zErr; 1596 1596 int k; 1597 - pParse->iSelfTab = iDataCur+1; 1597 + pParse->iSelfTab = iDataCur + 1; 1598 1598 sqlite3ExprCachePush(pParse); 1599 1599 for(k=pCheck->nExpr-1; k>0; k--){ 1600 1600 sqlite3ExprIfFalse(pParse, pCheck->a[k].pExpr, addrCkFault, 0); 1601 1601 } 1602 1602 sqlite3ExprIfTrue(pParse, pCheck->a[0].pExpr, addrCkOk, 1603 1603 SQLITE_JUMPIFNULL); 1604 1604 sqlite3VdbeResolveLabel(v, addrCkFault);
Changes to src/shell.c.
8392 8392 find_home_dir(1); 8393 8393 #if !SQLITE_SHELL_IS_UTF8 8394 8394 for(i=0; i<argc; i++) sqlite3_free(argv[i]); 8395 8395 sqlite3_free(argv); 8396 8396 #endif 8397 8397 return rc; 8398 8398 } 8399 -
Changes to src/sqliteInt.h.
2959 2959 int nRangeReg; /* Size of the temporary register block */ 2960 2960 int iRangeReg; /* First register in temporary register block */ 2961 2961 int nErr; /* Number of errors seen */ 2962 2962 int nTab; /* Number of previously allocated VDBE cursors */ 2963 2963 int nMem; /* Number of memory cells used so far */ 2964 2964 int nOpAlloc; /* Number of slots allocated for Vdbe.aOp[] */ 2965 2965 int szOpAlloc; /* Bytes of memory space allocated for Vdbe.aOp[] */ 2966 - int ckBase; /* Base register of data during check constraints */ 2967 - int iSelfTab; /* Table of an index whose exprs are being coded */ 2966 + int iSelfTab; /* Table for associated with an index on expr, or negative 2967 + ** of the base register during check-constraint eval */ 2968 2968 int iCacheLevel; /* ColCache valid when aColCache[].iLevel<=iCacheLevel */ 2969 2969 int iCacheCnt; /* Counter used to generate aColCache[].lru values */ 2970 2970 int nLabel; /* Number of labels used */ 2971 2971 int *aLabel; /* Space to hold the labels */ 2972 2972 ExprList *pConstExpr;/* Constant expressions */ 2973 2973 Token constraintName;/* Name of the constraint currently being parsed */ 2974 2974 yDbMask writeMask; /* Start a write transaction on these databases */
Changes to src/vdbe.h.
249 249 typedef int (*RecordCompare)(int,const void*,UnpackedRecord*); 250 250 RecordCompare sqlite3VdbeFindCompare(UnpackedRecord*); 251 251 252 252 #ifndef SQLITE_OMIT_TRIGGER 253 253 void sqlite3VdbeLinkSubProgram(Vdbe *, SubProgram *); 254 254 #endif 255 255 256 -void sqlite3VdbePureFuncOnly(sqlite3_context*); 256 +int sqlite3NotPureFunc(sqlite3_context*); 257 257 258 258 /* Use SQLITE_ENABLE_COMMENTS to enable generation of extra comments on 259 259 ** each VDBE opcode. 260 260 ** 261 261 ** Use the SQLITE_ENABLE_MODULE_COMMENTS macro to see some extra no-op 262 262 ** comments in VDBE programs that show key decision points in the code 263 263 ** generator.
Changes to src/vdbeaux.c.
4588 4588 ** rather than OP_Function. 4589 4589 ** 4590 4590 ** OP_PureFunc means that the function must be deterministic, and should 4591 4591 ** throw an error if it is given inputs that would make it non-deterministic. 4592 4592 ** This routine is invoked by date/time functions that use non-deterministic 4593 4593 ** features such as 'now'. 4594 4594 */ 4595 -void sqlite3VdbePureFuncOnly(sqlite3_context *pCtx){ 4595 +int sqlite3NotPureFunc(sqlite3_context *pCtx){ 4596 4596 if( pCtx->pVdbe->aOp[pCtx->iOp].opcode==OP_PureFunc ){ 4597 4597 sqlite3_result_error(pCtx, 4598 - "non-deterministic functions prohibited in index expressions", -1); 4598 + "non-deterministic function in index expression or CHECK constraint", 4599 + -1); 4600 + return 0; 4599 4601 } 4602 + return 1; 4600 4603 } 4601 4604 4602 4605 #ifndef SQLITE_OMIT_VIRTUALTABLE 4603 4606 /* 4604 4607 ** Transfer error message text from an sqlite3_vtab.zErrMsg (text stored 4605 4608 ** in memory obtained from sqlite3_malloc) into a Vdbe.zErrMsg (text stored 4606 4609 ** in memory obtained from sqlite3DbMalloc).
Added test/date2.test.
1 +# 2017-07-20 2 +# 3 +# The author disclaims copyright to this source code. In place of 4 +# a legal notice, here is a blessing: 5 +# 6 +# May you do good and not evil. 7 +# May you find forgiveness for yourself and forgive others. 8 +# May you share freely, never taking more than you give. 9 +# 10 +#*********************************************************************** 11 +# This file implements regression tests for SQLite library. The 12 +# focus of this file is testing date and time functions used in 13 +# check constraints and index expressions. 14 +# 15 + 16 +set testdir [file dirname $argv0] 17 +source $testdir/tester.tcl 18 + 19 +# Skip this whole file if date and time functions are omitted 20 +# at compile-time 21 +# 22 +ifcapable {!datetime} { 23 + finish_test 24 + return 25 +} 26 + 27 +do_execsql_test date2-100 { 28 + CREATE TABLE t1(x, y, CHECK( date(x) BETWEEN '2017-07-01' AND '2017-07-31' )); 29 + INSERT INTO t1(x,y) VALUES('2017-07-20','one'); 30 +} {} 31 +do_catchsql_test date2-110 { 32 + INSERT INTO t1(x,y) VALUES('now','two'); 33 +} {1 {non-deterministic function in index expression or CHECK constraint}} 34 +do_execsql_test date2-120 { 35 + SELECT * FROM t1; 36 +} {2017-07-20 one} 37 +do_catchsql_test date2-130 { 38 + INSERT INTO t1(x,y) VALUES('2017-08-01','two'); 39 +} {1 {CHECK constraint failed: t1}} 40 + 41 +do_execsql_test date2-200 { 42 + CREATE TABLE t2(x,y); 43 + INSERT INTO t2(x,y) VALUES(1, '2017-07-20'), (2, 'xyzzy'); 44 + CREATE INDEX t2y ON t2(date(y)); 45 +} 46 +do_catchsql_test date2-210 { 47 + INSERT INTO t2(x,y) VALUES(3, 'now'); 48 +} {1 {non-deterministic function in index expression or CHECK constraint}} 49 +do_execsql_test date2-220 { 50 + SELECT x, y FROM t2 ORDER BY x; 51 +} {1 2017-07-20 2 xyzzy} 52 + 53 +finish_test
Changes to test/indexexpr1.test.
182 182 183 183 do_catchsql_test indexexpr1-300 { 184 184 CREATE TABLE t2(a,b,c); INSERT INTO t2 VALUES(1,2,3); 185 185 CREATE INDEX t2x1 ON t2(a,b+random()); 186 186 } {1 {non-deterministic functions prohibited in index expressions}} 187 187 do_catchsql_test indexexpr1-301 { 188 188 CREATE INDEX t2x1 ON t2(julianday('now',a)); 189 -} {1 {non-deterministic function}} 189 +} {1 {non-deterministic function in index expression or CHECK constraint}} 190 190 do_catchsql_test indexexpr1-310 { 191 191 CREATE INDEX t2x2 ON t2(a,b+(SELECT 15)); 192 192 } {1 {subqueries prohibited in index expressions}} 193 193 do_catchsql_test indexexpr1-320 { 194 194 CREATE TABLE e1(x,y,UNIQUE(y,substr(x,1,5))); 195 195 } {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}} 196 196 do_catchsql_test indexexpr1-330 {