Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Updates to requirements marks. No code changes. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
213020769f310aec1591d97756b53891 |
User & Date: | drh 2013-08-02 23:40:45.340 |
Context
2013-08-05
| ||
12:31 | Add a missing '#include "tcl.h"' to test_rtree.c. (check-in: 4b8b426f10 user: drh tags: trunk) | |
2013-08-03
| ||
20:24 | Begin adding experimental sqlite_stat4 table. This commit is buggy. (check-in: 2beea303a1 user: dan tags: sqlite_stat4) | |
2013-08-02
| ||
23:40 | Updates to requirements marks. No code changes. (check-in: 213020769f user: drh tags: trunk) | |
20:11 | Add NEVER() and ALWAYS() macros on some unreachable yet prudent branches. (check-in: c5c0a8ab6c user: drh tags: trunk) | |
Changes
Changes to src/tokenize.c.
︙ | ︙ | |||
119 120 121 122 123 124 125 | testcase( z[0]=='\r' ); for(i=1; sqlite3Isspace(z[i]); i++){} *tokenType = TK_SPACE; return i; } case '-': { if( z[1]=='-' ){ | < | 119 120 121 122 123 124 125 126 127 128 129 130 131 132 | testcase( z[0]=='\r' ); for(i=1; sqlite3Isspace(z[i]); i++){} *tokenType = TK_SPACE; return i; } case '-': { if( z[1]=='-' ){ for(i=2; (c=z[i])!=0 && c!='\n'; i++){} *tokenType = TK_SPACE; /* IMP: R-22934-25134 */ return i; } *tokenType = TK_MINUS; return 1; } |
︙ | ︙ | |||
152 153 154 155 156 157 158 | return 1; } case '/': { if( z[1]!='*' || z[2]==0 ){ *tokenType = TK_SLASH; return 1; } | < | 151 152 153 154 155 156 157 158 159 160 161 162 163 164 | return 1; } case '/': { if( z[1]!='*' || z[2]==0 ){ *tokenType = TK_SLASH; return 1; } for(i=3, c=z[2]; (c!='*' || z[i]!='/') && (c=z[i])!=0; i++){} if( c ) i++; *tokenType = TK_SPACE; /* IMP: R-22934-25134 */ return i; } case '%': { *tokenType = TK_REM; |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
654 655 656 657 658 659 660 | ** WhereTerms. All pointers to WhereTerms should be invalidated after ** calling this routine. Such pointers may be reinitialized by referencing ** the pWC->a[] array. */ static int whereClauseInsert(WhereClause *pWC, Expr *p, u8 wtFlags){ WhereTerm *pTerm; int idx; | | | 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 | ** WhereTerms. All pointers to WhereTerms should be invalidated after ** calling this routine. Such pointers may be reinitialized by referencing ** the pWC->a[] array. */ static int whereClauseInsert(WhereClause *pWC, Expr *p, u8 wtFlags){ WhereTerm *pTerm; int idx; testcase( wtFlags & TERM_VIRTUAL ); if( pWC->nTerm>=pWC->nSlot ){ WhereTerm *pOld = pWC->a; sqlite3 *db = pWC->pWInfo->pParse->db; pWC->a = sqlite3DbMallocRaw(db, sizeof(pWC->a[0])*pWC->nSlot*2 ); if( pWC->a==0 ){ if( wtFlags & TERM_DYNAMIC ){ sqlite3ExprDelete(db, p); |
︙ | ︙ | |||
799 800 801 802 803 804 805 | return mask; } /* ** Return TRUE if the given operator is one of the operators that is ** allowed for an indexable WHERE clause term. The allowed operators are ** "=", "<", ">", "<=", ">=", "IN", and "IS NULL" | < < < < < < < | 799 800 801 802 803 804 805 806 807 808 809 810 811 812 | return mask; } /* ** Return TRUE if the given operator is one of the operators that is ** allowed for an indexable WHERE clause term. The allowed operators are ** "=", "<", ">", "<=", ">=", "IN", and "IS NULL" */ static int allowedOp(int op){ assert( TK_GT>TK_EQ && TK_GT<TK_GE ); assert( TK_LT>TK_EQ && TK_LT<TK_GE ); assert( TK_LE>TK_EQ && TK_LE<TK_GE ); assert( TK_GE==TK_EQ+4 ); return op==TK_IN || (op>=TK_EQ && op<=TK_GE) || op==TK_ISNULL; |
︙ | ︙ | |||
1479 1480 1481 1482 1483 1484 1485 | } } } /* At this point, okToChngToIN is true if original pTerm satisfies ** case 1. In that case, construct a new virtual term that is ** pTerm converted into an IN operator. | < < | 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 | } } } /* At this point, okToChngToIN is true if original pTerm satisfies ** case 1. In that case, construct a new virtual term that is ** pTerm converted into an IN operator. */ if( okToChngToIN ){ Expr *pDup; /* A transient duplicate expression */ ExprList *pList = 0; /* The RHS of the IN operator */ Expr *pLeft = 0; /* The LHS of the IN operator */ Expr *pNew; /* The complete IN operator */ |
︙ | ︙ | |||
1722 1723 1724 1725 1726 1727 1728 | if( noCase ){ /* The point is to increment the last character before the first ** wildcard. But if we increment '@', that will push it into the ** alphabetic range where case conversions will mess up the ** inequality. To avoid this, make sure to also run the full ** LIKE on all candidate expressions by clearing the isComplete flag */ | | < < | 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 | if( noCase ){ /* The point is to increment the last character before the first ** wildcard. But if we increment '@', that will push it into the ** alphabetic range where case conversions will mess up the ** inequality. To avoid this, make sure to also run the full ** LIKE on all candidate expressions by clearing the isComplete flag */ if( c=='A'-1 ) isComplete = 0; c = sqlite3UpperToLower[c]; } *pC = c + 1; } sCollSeqName.z = noCase ? "NOCASE" : "BINARY"; sCollSeqName.n = 6; pNewExpr1 = sqlite3ExprDup(db, pLeft, 0); |
︙ | ︙ | |||
2814 2815 2816 2817 2818 2819 2820 | ** (2) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x AND t2.z='ok' ** (3) SELECT * FROM t1, t2 WHERE t1.a=t2.x AND t2.z='ok' ** ** The t2.z='ok' is disabled in the in (2) because it originates ** in the ON clause. The term is disabled in (3) because it is not part ** of a LEFT OUTER JOIN. In (1), the term is not disabled. ** | < < < | 2803 2804 2805 2806 2807 2808 2809 2810 2811 2812 2813 2814 2815 2816 | ** (2) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x AND t2.z='ok' ** (3) SELECT * FROM t1, t2 WHERE t1.a=t2.x AND t2.z='ok' ** ** The t2.z='ok' is disabled in the in (2) because it originates ** in the ON clause. The term is disabled in (3) because it is not part ** of a LEFT OUTER JOIN. In (1), the term is not disabled. ** ** Disabling a term causes that term to not be tested in the inner loop ** of the join. Disabling is an optimization. When terms are satisfied ** by indices, we disable them to prevent redundant tests in the inner ** loop. We would get the correct results if nothing were ever disabled, ** but joins might run a little slower. The trick is to disable as much ** as we can without disabling too much. If we disabled in (1), we'd get ** the wrong answer. See ticket #813. |
︙ | ︙ | |||
3046 3047 3048 3049 3050 3051 3052 | for(j=0; j<nEq; j++){ int r1; pTerm = pLoop->aLTerm[j]; assert( pTerm!=0 ); /* The following true for indices with redundant columns. ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */ testcase( (pTerm->wtFlags & TERM_CODED)!=0 ); | | | 3032 3033 3034 3035 3036 3037 3038 3039 3040 3041 3042 3043 3044 3045 3046 | for(j=0; j<nEq; j++){ int r1; pTerm = pLoop->aLTerm[j]; assert( pTerm!=0 ); /* The following true for indices with redundant columns. ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */ testcase( (pTerm->wtFlags & TERM_CODED)!=0 ); testcase( pTerm->wtFlags & TERM_VIRTUAL ); r1 = codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, regBase+j); if( r1!=regBase+j ){ if( nReg==1 ){ sqlite3ReleaseTempReg(pParse, regBase); regBase = r1; }else{ sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j); |
︙ | ︙ | |||
3357 3358 3359 3360 3361 3362 3363 | */ assert( pLoop->u.btree.nEq==1 ); iReleaseReg = sqlite3GetTempReg(pParse); pTerm = pLoop->aLTerm[0]; assert( pTerm!=0 ); assert( pTerm->pExpr!=0 ); assert( omitTable==0 ); | | | 3343 3344 3345 3346 3347 3348 3349 3350 3351 3352 3353 3354 3355 3356 3357 | */ assert( pLoop->u.btree.nEq==1 ); iReleaseReg = sqlite3GetTempReg(pParse); pTerm = pLoop->aLTerm[0]; assert( pTerm!=0 ); assert( pTerm->pExpr!=0 ); assert( omitTable==0 ); testcase( pTerm->wtFlags & TERM_VIRTUAL ); iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, bRev, iReleaseReg); addrNxt = pLevel->addrNxt; sqlite3VdbeAddOp2(v, OP_MustBeInt, iRowidReg, addrNxt); sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addrNxt, iRowidReg); sqlite3ExprCacheAffinityChange(pParse, iRowidReg, 1); sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg); VdbeComment((v, "pk")); |
︙ | ︙ | |||
3405 3406 3407 3408 3409 3410 3411 | /* TK_GE */ OP_SeekGe }; assert( TK_LE==TK_GT+1 ); /* Make sure the ordering.. */ assert( TK_LT==TK_GT+2 ); /* ... of the TK_xx values... */ assert( TK_GE==TK_GT+3 ); /* ... is correcct. */ assert( (pStart->wtFlags & TERM_VNULL)==0 ); | | | | 3391 3392 3393 3394 3395 3396 3397 3398 3399 3400 3401 3402 3403 3404 3405 3406 3407 3408 3409 3410 3411 3412 3413 3414 3415 3416 3417 3418 3419 3420 3421 3422 3423 3424 | /* TK_GE */ OP_SeekGe }; assert( TK_LE==TK_GT+1 ); /* Make sure the ordering.. */ assert( TK_LT==TK_GT+2 ); /* ... of the TK_xx values... */ assert( TK_GE==TK_GT+3 ); /* ... is correcct. */ assert( (pStart->wtFlags & TERM_VNULL)==0 ); testcase( pStart->wtFlags & TERM_VIRTUAL ); pX = pStart->pExpr; assert( pX!=0 ); testcase( pStart->leftCursor!=iCur ); /* transitive constraints */ r1 = sqlite3ExprCodeTemp(pParse, pX->pRight, &rTemp); sqlite3VdbeAddOp3(v, aMoveOp[pX->op-TK_GT], iCur, addrBrk, r1); VdbeComment((v, "pk")); sqlite3ExprCacheAffinityChange(pParse, r1, 1); sqlite3ReleaseTempReg(pParse, rTemp); disableTerm(pLevel, pStart); }else{ sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, addrBrk); } if( pEnd ){ Expr *pX; pX = pEnd->pExpr; assert( pX!=0 ); assert( (pEnd->wtFlags & TERM_VNULL)==0 ); testcase( pEnd->leftCursor!=iCur ); /* Transitive constraints */ testcase( pEnd->wtFlags & TERM_VIRTUAL ); memEndValue = ++pParse->nMem; sqlite3ExprCode(pParse, pX->pRight, memEndValue); if( pX->op==TK_LT || pX->op==TK_GT ){ testOp = bRev ? OP_Le : OP_Ge; }else{ testOp = bRev ? OP_Lt : OP_Gt; } |
︙ | ︙ | |||
3590 3591 3592 3593 3594 3595 3596 | zStartAff[nEq] = SQLITE_AFF_NONE; } if( sqlite3ExprNeedsNoAffinityChange(pRight, zStartAff[nEq]) ){ zStartAff[nEq] = SQLITE_AFF_NONE; } } nConstraint++; | | | 3576 3577 3578 3579 3580 3581 3582 3583 3584 3585 3586 3587 3588 3589 3590 | zStartAff[nEq] = SQLITE_AFF_NONE; } if( sqlite3ExprNeedsNoAffinityChange(pRight, zStartAff[nEq]) ){ zStartAff[nEq] = SQLITE_AFF_NONE; } } nConstraint++; testcase( pRangeStart->wtFlags & TERM_VIRTUAL ); }else if( isMinQuery ){ sqlite3VdbeAddOp2(v, OP_Null, 0, regBase+nEq); nConstraint++; startEq = 0; start_constraints = 1; } codeApplyAffinity(pParse, regBase, nConstraint, zStartAff); |
︙ | ︙ | |||
3632 3633 3634 3635 3636 3637 3638 | } if( sqlite3ExprNeedsNoAffinityChange(pRight, zEndAff[nEq]) ){ zEndAff[nEq] = SQLITE_AFF_NONE; } } codeApplyAffinity(pParse, regBase, nEq+1, zEndAff); nConstraint++; | | | 3618 3619 3620 3621 3622 3623 3624 3625 3626 3627 3628 3629 3630 3631 3632 | } if( sqlite3ExprNeedsNoAffinityChange(pRight, zEndAff[nEq]) ){ zEndAff[nEq] = SQLITE_AFF_NONE; } } codeApplyAffinity(pParse, regBase, nEq+1, zEndAff); nConstraint++; testcase( pRangeEnd->wtFlags & TERM_VIRTUAL ); } sqlite3DbFree(db, zStartAff); sqlite3DbFree(db, zEndAff); /* Top of the loop body */ pLevel->p2 = sqlite3VdbeCurrentAddr(v); |
︙ | ︙ | |||
3919 3920 3921 3922 3923 3924 3925 | pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev], iCur, addrBrk); pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP; } newNotReady = notReady & ~getMask(&pWInfo->sMaskSet, iCur); /* Insert code to test every subexpression that can be completely ** computed using the current set of tables. | < < < < | | 3905 3906 3907 3908 3909 3910 3911 3912 3913 3914 3915 3916 3917 3918 3919 3920 3921 3922 | pLevel->p2 = 1 + sqlite3VdbeAddOp2(v, aStart[bRev], iCur, addrBrk); pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP; } newNotReady = notReady & ~getMask(&pWInfo->sMaskSet, iCur); /* Insert code to test every subexpression that can be completely ** computed using the current set of tables. */ for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){ Expr *pE; testcase( pTerm->wtFlags & TERM_VIRTUAL ); testcase( pTerm->wtFlags & TERM_CODED ); if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue; if( (pTerm->prereqAll & newNotReady)!=0 ){ testcase( pWInfo->untestedTerms==0 && (pWInfo->wctrlFlags & WHERE_ONETABLE_ONLY)!=0 ); pWInfo->untestedTerms = 1; continue; |
︙ | ︙ | |||
3986 3987 3988 3989 3990 3991 3992 | */ if( pLevel->iLeftJoin ){ pLevel->addrFirst = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp2(v, OP_Integer, 1, pLevel->iLeftJoin); VdbeComment((v, "record LEFT JOIN hit")); sqlite3ExprCacheClear(pParse); for(pTerm=pWC->a, j=0; j<pWC->nTerm; j++, pTerm++){ | | | 3968 3969 3970 3971 3972 3973 3974 3975 3976 3977 3978 3979 3980 3981 3982 | */ if( pLevel->iLeftJoin ){ pLevel->addrFirst = sqlite3VdbeCurrentAddr(v); sqlite3VdbeAddOp2(v, OP_Integer, 1, pLevel->iLeftJoin); VdbeComment((v, "record LEFT JOIN hit")); sqlite3ExprCacheClear(pParse); for(pTerm=pWC->a, j=0; j<pWC->nTerm; j++, pTerm++){ testcase( pTerm->wtFlags & TERM_VIRTUAL ); testcase( pTerm->wtFlags & TERM_CODED ); if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue; if( (pTerm->prereqAll & newNotReady)!=0 ){ assert( pWInfo->untestedTerms ); continue; } assert( pTerm->pExpr ); |
︙ | ︙ | |||
5759 5760 5761 5762 5763 5764 5765 | /* Split the WHERE clause into separate subexpressions where each ** subexpression is separated by an AND operator. */ initMaskSet(pMaskSet); whereClauseInit(&pWInfo->sWC, pWInfo); sqlite3ExprCodeConstants(pParse, pWhere); | | | 5741 5742 5743 5744 5745 5746 5747 5748 5749 5750 5751 5752 5753 5754 5755 | /* Split the WHERE clause into separate subexpressions where each ** subexpression is separated by an AND operator. */ initMaskSet(pMaskSet); whereClauseInit(&pWInfo->sWC, pWInfo); sqlite3ExprCodeConstants(pParse, pWhere); whereSplit(&pWInfo->sWC, pWhere, TK_AND); sqlite3CodeVerifySchema(pParse, -1); /* Insert the cookie verifier Goto */ /* Special case: a WHERE clause that is constant. Evaluate the ** expression and either jump over all of the code or fall thru. */ if( pWhere && (nTabList==0 || sqlite3ExprIsConstantNotJoin(pWhere)) ){ sqlite3ExprIfFalse(pParse, pWhere, pWInfo->iBreak, SQLITE_JUMPIFNULL); |
︙ | ︙ |
Changes to test/e_createtable.test.
︙ | ︙ | |||
54 55 56 57 58 59 60 | db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name" ] } set res } | < < | | 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 | db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name" ] } set res } do_createtable_tests 0.1.1 -repair { drop_all_tables } { 1 "CREATE TABLE t1(c1 one)" {} 2 "CREATE TABLE t1(c1 one two)" {} 3 "CREATE TABLE t1(c1 one two three)" {} 4 "CREATE TABLE t1(c1 one two three four)" {} 5 "CREATE TABLE t1(c1 one two three four(14))" {} 6 "CREATE TABLE t1(c1 one two three four(14, 22))" {} 7 "CREATE TABLE t1(c1 var(+14, -22.3))" {} 8 "CREATE TABLE t1(c1 var(1.0e10))" {} } do_createtable_tests 0.1.2 -error { near "%s": syntax error } { 1 "CREATE TABLE t1(c1 one(number))" {number} } # syntax diagram column-constraint # do_createtable_tests 0.2.1 -repair { drop_all_tables execsql { CREATE TABLE t2(x PRIMARY KEY) } } { 1.1 "CREATE TABLE t1(c1 text PRIMARY KEY)" {} 1.2 "CREATE TABLE t1(c1 text PRIMARY KEY ASC)" {} |
︙ | ︙ | |||
122 123 124 125 126 127 128 | 8.2 { CREATE TABLE t1(c1 REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY ); } {} } | | | | | 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 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 | 8.2 { CREATE TABLE t1(c1 REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY ); } {} } # -- syntax diagram table-constraint # do_createtable_tests 0.3.1 -repair { drop_all_tables execsql { CREATE TABLE t2(x PRIMARY KEY) } } { 1.1 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))" {} 1.2 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))" {} 1.3 "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2) ON CONFLICT IGNORE)" {} 2.1 "CREATE TABLE t1(c1, c2, UNIQUE(c1))" {} 2.2 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2))" {} 2.3 "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)" {} 3.1 "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))" {} 4.1 "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)" {} } # -- syntax diagram column-def # do_createtable_tests 0.4.1 -repair { drop_all_tables } { 1 {CREATE TABLE t1( col1, col2 TEXT, col3 INTEGER UNIQUE, col4 VARCHAR(10, 10) PRIMARY KEY, "name with spaces" REFERENCES t1 ); } {} } # -- syntax diagram create-table-stmt # do_createtable_tests 0.5.1 -repair { drop_all_tables execsql { CREATE TABLE t2(a, b, c) } } { 1 "CREATE TABLE t1(a, b, c)" {} 2 "CREATE TEMP TABLE t1(a, b, c)" {} |
︙ | ︙ | |||
181 182 183 184 185 186 187 | 12 "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)" {} 13 "CREATE TABLE t1 AS SELECT * FROM t2" {} 14 "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2" {} 15 "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2" {} } | < | 179 180 181 182 183 184 185 186 187 188 189 190 191 192 | 12 "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)" {} 13 "CREATE TABLE t1 AS SELECT * FROM t2" {} 14 "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2" {} 15 "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2" {} } # # 1: Explicit parent-key columns. # 2: Implicit child-key columns. # # 1: MATCH FULL # 2: MATCH PARTIAL # 3: MATCH SIMPLE |
︙ | ︙ |
Changes to test/e_delete.test.
︙ | ︙ | |||
25 26 27 28 29 30 31 | } do_execsql_test e_delete-0.0 { CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a); } {} | | < | | 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | } do_execsql_test e_delete-0.0 { CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a); } {} # -- syntax diagram delete-stmt # -- syntax diagram qualified-table-name # do_delete_tests e_delete-0.1 { 1 "DELETE FROM t1" {} 2 "DELETE FROM t1 INDEXED BY i1" {} 3 "DELETE FROM t1 NOT INDEXED" {} 4 "DELETE FROM main.t1" {} 5 "DELETE FROM main.t1 INDEXED BY i1" {} |
︙ | ︙ | |||
288 289 290 291 292 293 294 | } # EVIDENCE-OF: R-40026-10531 If SQLite is compiled with the # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax # of the DELETE statement is extended by the addition of optional ORDER # BY and LIMIT clauses: # | | | 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 | } # EVIDENCE-OF: R-40026-10531 If SQLite is compiled with the # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option, then the syntax # of the DELETE statement is extended by the addition of optional ORDER # BY and LIMIT clauses: # # -- syntax diagram delete-stmt-limited # do_delete_tests e_delete-3.1 { 1 "DELETE FROM t1 LIMIT 5" {} 2 "DELETE FROM t1 LIMIT 5-1 OFFSET 2+2" {} 3 "DELETE FROM t1 LIMIT 2+2, 16/4" {} 4 "DELETE FROM t1 ORDER BY x LIMIT 5" {} 5 "DELETE FROM t1 ORDER BY x LIMIT 5-1 OFFSET 2+2" {} |
︙ | ︙ |
Changes to test/e_droptrigger.test.
︙ | ︙ | |||
65 66 67 68 69 70 71 | CREATE TRIGGER aux.tr1 BEFORE $event ON t3 BEGIN SELECT r('aux.tr1') ; END; CREATE TRIGGER aux.tr2 AFTER $event ON t3 BEGIN SELECT r('aux.tr2') ; END; CREATE TRIGGER aux.tr3 AFTER $event ON t3 BEGIN SELECT r('aux.tr3') ; END; " } | | | 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 | CREATE TRIGGER aux.tr1 BEFORE $event ON t3 BEGIN SELECT r('aux.tr1') ; END; CREATE TRIGGER aux.tr2 AFTER $event ON t3 BEGIN SELECT r('aux.tr2') ; END; CREATE TRIGGER aux.tr3 AFTER $event ON t3 BEGIN SELECT r('aux.tr3') ; END; " } # -- syntax diagram drop-trigger-stmt # do_droptrigger_tests 1.1 -repair { droptrigger_reopen_db } -tclquery { list_all_triggers } { 1 "DROP TRIGGER main.tr1" |
︙ | ︙ |
Changes to test/e_dropview.test.
︙ | ︙ | |||
66 67 68 69 70 71 72 | set res } proc do_dropview_tests {nm args} { uplevel do_select_tests $nm $args } | | | 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | set res } proc do_dropview_tests {nm args} { uplevel do_select_tests $nm $args } # -- syntax diagram drop-view-stmt # # All paths in the syntax diagram for DROP VIEW are tested by tests 1.*. # do_dropview_tests 1 -repair { dropview_reopen_db } -tclquery { list_all_views |
︙ | ︙ |
Changes to test/e_expr.test.
︙ | ︙ | |||
362 363 364 365 366 367 368 | string compare [reverse_str $zLeft] [reverse_str $zRight] } db collate reverse reverse_collate # EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix # operator that assigns a collating sequence to an expression. # | | | | | 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 | string compare [reverse_str $zLeft] [reverse_str $zRight] } db collate reverse reverse_collate # EVIDENCE-OF: R-59577-33471 The COLLATE operator is a unary postfix # operator that assigns a collating sequence to an expression. # # EVIDENCE-OF: R-36231-30731 The COLLATE operator has a higher # precedence (binds more tightly) than any binary operator and any unary # prefix operator except "~". # do_execsql_test e_expr-9.1 { SELECT 'abcd' < 'bbbb' COLLATE reverse } 0 do_execsql_test e_expr-9.2 { SELECT ('abcd' < 'bbbb') COLLATE reverse } 1 do_execsql_test e_expr-9.3 { SELECT 'abcd' <= 'bbbb' COLLATE reverse } 0 do_execsql_test e_expr-9.4 { SELECT ('abcd' <= 'bbbb') COLLATE reverse } 1 do_execsql_test e_expr-9.5 { SELECT 'abcd' > 'bbbb' COLLATE reverse } 1 |
︙ | ︙ | |||
627 628 629 630 631 632 633 | [sqlite3_column_type $stmt 3] } {NULL NULL NULL NULL} do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK #------------------------------------------------------------------------- # "Test" the syntax diagrams in lang_expr.html. # | | | | | 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 | [sqlite3_column_type $stmt 3] } {NULL NULL NULL NULL} do_test e_expr-11.7.1 { sqlite3_finalize $stmt } SQLITE_OK #------------------------------------------------------------------------- # "Test" the syntax diagrams in lang_expr.html. # # -- syntax diagram signed-number # do_execsql_test e_expr-12.1.1 { SELECT 0, +0, -0 } {0 0 0} do_execsql_test e_expr-12.1.2 { SELECT 1, +1, -1 } {1 1 -1} do_execsql_test e_expr-12.1.3 { SELECT 2, +2, -2 } {2 2 -2} do_execsql_test e_expr-12.1.4 { SELECT 1.4, +1.4, -1.4 } {1.4 1.4 -1.4} do_execsql_test e_expr-12.1.5 { SELECT 1.5e+5, +1.5e+5, -1.5e+5 } {150000.0 150000.0 -150000.0} do_execsql_test e_expr-12.1.6 { SELECT 0.0001, +0.0001, -0.0001 } {0.0001 0.0001 -0.0001} # -- syntax diagram literal-value # set sqlite_current_time 1 do_execsql_test e_expr-12.2.1 {SELECT 123} {123} do_execsql_test e_expr-12.2.2 {SELECT 123.4e05} {12340000.0} do_execsql_test e_expr-12.2.3 {SELECT 'abcde'} {abcde} do_execsql_test e_expr-12.2.4 {SELECT X'414243'} {ABC} do_execsql_test e_expr-12.2.5 {SELECT NULL} {{}} do_execsql_test e_expr-12.2.6 {SELECT CURRENT_TIME} {00:00:01} do_execsql_test e_expr-12.2.7 {SELECT CURRENT_DATE} {1970-01-01} do_execsql_test e_expr-12.2.8 {SELECT CURRENT_TIMESTAMP} {{1970-01-01 00:00:01}} set sqlite_current_time 0 # -- syntax diagram expr # forcedelete test.db2 execsql { ATTACH 'test.db2' AS dbname; CREATE TABLE dbname.tblname(cname); } |
︙ | ︙ | |||
812 813 814 815 816 817 818 | incr x do_test e_expr-12.3.$tn.$x { set rc [catch { execsql "SELECT $e FROM tblname" } msg] } {0} } } | | | 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 | incr x do_test e_expr-12.3.$tn.$x { set rc [catch { execsql "SELECT $e FROM tblname" } msg] } {0} } } # -- syntax diagram raise-function # foreach {tn raiseexpr} { 1 "RAISE(IGNORE)" 2 "RAISE(ROLLBACK, 'error message')" 3 "RAISE(ABORT, 'error message')" 4 "RAISE(FAIL, 'error message')" } { |
︙ | ︙ |
Changes to test/e_insert.test.
︙ | ︙ | |||
46 47 48 49 50 51 52 | CREATE TABLE a4(c UNIQUE, d); } {} proc do_insert_tests {args} { uplevel do_select_tests $args } | | | 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | CREATE TABLE a4(c UNIQUE, d); } {} proc do_insert_tests {args} { uplevel do_select_tests $args } # -- syntax diagram insert-stmt # do_insert_tests e_insert-0 { 1 "INSERT INTO a1 DEFAULT VALUES" {} 2 "INSERT INTO main.a1 DEFAULT VALUES" {} 3 "INSERT OR ROLLBACK INTO main.a1 DEFAULT VALUES" {} 4 "INSERT OR ROLLBACK INTO a1 DEFAULT VALUES" {} 5 "INSERT OR ABORT INTO main.a1 DEFAULT VALUES" {} |
︙ | ︙ |
Changes to test/e_reindex.test.
︙ | ︙ | |||
22 23 24 25 26 27 28 | do_execsql_test e_reindex-0.0 { CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a, b); CREATE INDEX i2 ON t1(b, a); } {} | | | 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | do_execsql_test e_reindex-0.0 { CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a, b); CREATE INDEX i2 ON t1(b, a); } {} # -- syntax diagram reindex-stmt # do_reindex_tests e_reindex-0.1 { 1 "REINDEX" {} 2 "REINDEX nocase" {} 3 "REINDEX binary" {} 4 "REINDEX t1" {} 5 "REINDEX main.t1" {} |
︙ | ︙ |
Changes to test/e_select.test.
︙ | ︙ | |||
79 80 81 82 83 84 85 | } } #------------------------------------------------------------------------- # The following tests check that all paths on the syntax diagrams on # the lang_select.html page may be taken. # | | | | 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 | } } #------------------------------------------------------------------------- # The following tests check that all paths on the syntax diagrams on # the lang_select.html page may be taken. # # -- syntax diagram join-constraint # do_join_test e_select-0.1.1 { SELECT count(*) FROM t1 %JOIN% t2 ON (t1.a=t2.a) } {3} do_join_test e_select-0.1.2 { SELECT count(*) FROM t1 %JOIN% t2 USING (a) } {3} do_join_test e_select-0.1.3 { SELECT count(*) FROM t1 %JOIN% t2 } {9} do_catchsql_test e_select-0.1.4 { SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a) } {1 {cannot have both ON and USING clauses in the same join}} do_catchsql_test e_select-0.1.5 { SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a) } {1 {near "ON": syntax error}} # -- syntax diagram select-core # # 0: SELECT ... # 1: SELECT DISTINCT ... # 2: SELECT ALL ... # # 0: No FROM clause # 1: Has FROM clause |
︙ | ︙ | |||
222 223 224 225 226 227 228 | 1 a 1 c } 2112.2 "SELECT ALL count(*), max(a) FROM t1 WHERE 0 GROUP BY b HAVING count(*)=2" { } } | | | | | 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 | 1 a 1 c } 2112.2 "SELECT ALL count(*), max(a) FROM t1 WHERE 0 GROUP BY b HAVING count(*)=2" { } } # -- syntax diagram result-column # do_select_tests e_select-0.3 { 1 "SELECT * FROM t1" {a one b two c three} 2 "SELECT t1.* FROM t1" {a one b two c three} 3 "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx} 4 "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx} 5 "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx} } # -- syntax diagram join-source # # -- syntax diagram join-op # do_select_tests e_select-0.4 { 1 "SELECT t1.rowid FROM t1" {1 2 3} 2 "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3} 3 "SELECT t1.rowid FROM t1,t2,t3" {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3} 4 "SELECT t1.rowid FROM t1" {1 2 3} |
︙ | ︙ | |||
259 260 261 262 263 264 265 | 12 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3} 13 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3} 14 "SELECT t1.rowid FROM t1 LEFT JOIN t3" {1 1 2 2 3 3} 15 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3} 16 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3} } | | | | | 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 | 12 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3} 13 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3} 14 "SELECT t1.rowid FROM t1 LEFT JOIN t3" {1 1 2 2 3 3} 15 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3} 16 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3} } # -- syntax diagram compound-operator # do_select_tests e_select-0.5 { 1 "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4} 2 "SELECT rowid FROM t1 UNION SELECT rowid+2 FROM t4" {1 2 3 4} 3 "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3} 4 "SELECT rowid FROM t1 EXCEPT SELECT rowid+2 FROM t4" {1 2} } # -- syntax diagram ordering-term # do_select_tests e_select-0.6 { 1 "SELECT b||a FROM t1 ORDER BY b||a" {onea threec twob} 2 "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob} 3 "SELECT b||a FROM t1 ORDER BY (b||a) ASC" {onea threec twob} 4 "SELECT b||a FROM t1 ORDER BY (b||a) DESC" {twob threec onea} } # -- syntax diagram select-stmt # do_select_tests e_select-0.7 { 1 "SELECT * FROM t1" {a one b two c three} 2 "SELECT * FROM t1 ORDER BY b" {a one c three b two} 3 "SELECT * FROM t1 ORDER BY b, a" {a one c three b two} 4 "SELECT * FROM t1 LIMIT 10" {a one b two c three} |
︙ | ︙ | |||
391 392 393 394 395 396 397 | # The tests are built on this assertion. Really, they test that the output # of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result # of calculating the cartesian product of the left and right-hand datasets. # # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER # JOIN", "JOIN" and "," join operators. # | | | | 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 | # The tests are built on this assertion. Really, they test that the output # of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result # of calculating the cartesian product of the left and right-hand datasets. # # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER # JOIN", "JOIN" and "," join operators. # # EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the # same result as the "INNER JOIN", "JOIN" and "," operators # # All tests are run 4 times, with the only difference in each run being # which of the 4 equivalent cartesian product join operators are used. # Since the output data is the same in all cases, we consider that this # qualifies as testing the two statements above. # do_execsql_test e_select-1.4.0 { |
︙ | ︙ | |||
1222 1223 1224 1225 1226 1227 1228 | 1 "SELECT ALL a FROM h1" {1 1 1 4 4 4} 2 "SELECT DISTINCT a FROM h1" {1 4} } # EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then # the entire set of result rows are returned by the SELECT. # | | | 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 | 1 "SELECT ALL a FROM h1" {1 1 1 4 4 4} 2 "SELECT DISTINCT a FROM h1" {1 4} } # EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then # the entire set of result rows are returned by the SELECT. # # EVIDENCE-OF: R-01256-01950 If neither ALL or DISTINCT are present, # then the behavior is as if ALL were specified. # # EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT, # then duplicate rows are removed from the set of result rows before it # is returned. # # The three testable statements above are tested by e_select-5.2.*, |
︙ | ︙ |
Changes to test/e_select2.test.
︙ | ︙ | |||
348 349 350 351 352 353 354 | # JOIN", "JOIN" or a comma (",") and there is no ON or USING clause, # then the result of the join is simply the cartesian product of the # left and right-hand datasets. # # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER # JOIN", "JOIN" and "," join operators. # | | | | 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 | # JOIN", "JOIN" or a comma (",") and there is no ON or USING clause, # then the result of the join is simply the cartesian product of the # left and right-hand datasets. # # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER # JOIN", "JOIN" and "," join operators. # # EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the # same result as the "INNER JOIN", "JOIN" and "," operators # test_join $tn.1.1 "t1, t2" {t1 t2} test_join $tn.1.2 "t1 INNER JOIN t2" {t1 t2} test_join $tn.1.3 "t1 CROSS JOIN t2" {t1 t2} test_join $tn.1.4 "t1 JOIN t2" {t1 t2} test_join $tn.1.5 "t2, t3" {t2 t3} test_join $tn.1.6 "t2 INNER JOIN t3" {t2 t3} |
︙ | ︙ |
Changes to test/e_update.test.
︙ | ︙ | |||
45 46 47 48 49 50 51 | CREATE TABLE aux.t5(a, b); } {} proc do_update_tests {args} { uplevel do_select_tests $args } | | | 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | CREATE TABLE aux.t5(a, b); } {} proc do_update_tests {args} { uplevel do_select_tests $args } # -- syntax diagram update-stmt # do_update_tests e_update-0 { 1 "UPDATE t1 SET a=10" {} 2 "UPDATE t1 SET a=10, b=5" {} 3 "UPDATE t1 SET a=10 WHERE b=5" {} 4 "UPDATE t1 SET b=5,a=10 WHERE 1" {} 5 "UPDATE main.t1 SET a=10" {} |
︙ | ︙ | |||
489 490 491 492 493 494 495 | } # EVIDENCE-OF: R-59581-44104 If SQLite is built with the # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax # of the UPDATE statement is extended with optional ORDER BY and LIMIT # clauses # | | | 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 | } # EVIDENCE-OF: R-59581-44104 If SQLite is built with the # SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option then the syntax # of the UPDATE statement is extended with optional ORDER BY and LIMIT # clauses # # -- syntax diagram update-stmt-limited # do_update_tests e_update-3.0 { 1 "UPDATE t1 SET a=b LIMIT 5" {} 2 "UPDATE t1 SET a=b LIMIT 5-1 OFFSET 2+2" {} 3 "UPDATE t1 SET a=b LIMIT 2+2, 16/4" {} 4 "UPDATE t1 SET a=b ORDER BY a LIMIT 5" {} 5 "UPDATE t1 SET a=b ORDER BY a LIMIT 5-1 OFFSET 2+2" {} |
︙ | ︙ |
Changes to test/e_uri.test.
︙ | ︙ | |||
355 356 357 358 359 360 361 | # EVIDENCE-OF: R-23027-03515 Setting it to "shared" is equivalent to # setting the SQLITE_OPEN_SHAREDCACHE bit in the flags argument passed # to sqlite3_open_v2(). # # EVIDENCE-OF: R-49793-28525 Setting the cache parameter to "private" is # equivalent to setting the SQLITE_OPEN_PRIVATECACHE bit. # | | | 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 | # EVIDENCE-OF: R-23027-03515 Setting it to "shared" is equivalent to # setting the SQLITE_OPEN_SHAREDCACHE bit in the flags argument passed # to sqlite3_open_v2(). # # EVIDENCE-OF: R-49793-28525 Setting the cache parameter to "private" is # equivalent to setting the SQLITE_OPEN_PRIVATECACHE bit. # # EVIDENCE-OF: R-31773-41793 If sqlite3_open_v2() is used and the # "cache" parameter is present in a URI filename, its value overrides # any behavior requested by setting SQLITE_OPEN_PRIVATECACHE or # SQLITE_OPEN_SHAREDCACHE flag. # set orig [sqlite3_enable_shared_cache] foreach {tn uri flags shared_default isshared} { 1.1 "file:test.db" "" 0 0 |
︙ | ︙ |
Changes to test/e_vacuum.test.
︙ | ︙ | |||
61 62 63 64 65 66 67 | set prevpageno $pageno } execsql { DROP TABLE temp.stat } set nFrag } | | | 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | set prevpageno $pageno } execsql { DROP TABLE temp.stat } set nFrag } # -- syntax diagram vacuum-stmt # do_execsql_test e_vacuum-0.1 { VACUUM } {} # EVIDENCE-OF: R-51469-36013 Unless SQLite is running in # "auto_vacuum=FULL" mode, when a large amount of data is deleted from # the database file it leaves behind empty space, or "free" database # pages. |
︙ | ︙ |
Changes to test/eqp.test.
︙ | ︙ | |||
366 367 368 369 370 371 372 | #------------------------------------------------------------------------- # This next block of tests verifies that the examples on the # lang_explain.html page are correct. # drop_all_tables | | | > > | | > | > | | | > | | | > | > | > | | > | > | | > | | > | | | > | > | > | | > | > | | | > | | | | | > | 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 | #------------------------------------------------------------------------- # This next block of tests verifies that the examples on the # lang_explain.html page are correct. # drop_all_tables # EVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b # FROM t1 WHERE a=1; # 0|0|0|SCAN TABLE t1 # do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) } det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" { 0 0 0 {SCAN TABLE t1} } # EVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a); # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; # 0|0|0|SEARCH TABLE t1 USING INDEX i1 # do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) } det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" { 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} } # EVIDENCE-OF: R-21179-11011 sqlite> CREATE INDEX i2 ON t1(a, b); # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) # do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) } det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" { 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} } # EVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN # SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) # 0|1|1|SCAN TABLE t2 # do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)} det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} 0 1 1 {SCAN TABLE t2} } # EVIDENCE-OF: R-33626-61085 sqlite> EXPLAIN QUERY PLAN # SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; # 0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) # 0|1|0|SCAN TABLE t2 # det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" { 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} 0 1 0 {SCAN TABLE t2} } # EVIDENCE-OF: R-04002-25654 sqlite> CREATE INDEX i3 ON t1(b); # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2; # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) # 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) # do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)} det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" { 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} } # EVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN # SELECT c, d FROM t2 ORDER BY c; # 0|0|0|SCAN TABLE t2 # 0|0|0|USE TEMP B-TREE FOR ORDER BY # det 5.7 "SELECT c, d FROM t2 ORDER BY c" { 0 0 0 {SCAN TABLE t2} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } # EVIDENCE-OF: R-58157-12355 sqlite> CREATE INDEX i4 ON t2(c); # sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; # 0|0|0|SCAN TABLE t2 USING INDEX i4 # do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)} det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" { 0 0 0 {SCAN TABLE t2 USING INDEX i4} } # EVIDENCE-OF: R-13931-10421 sqlite> EXPLAIN QUERY PLAN SELECT # (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2; # 0|0|0|SCAN TABLE t2 # 0|0|0|EXECUTE SCALAR SUBQUERY 1 # 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) # 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 # 2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) # det 5.9 { SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2 } { 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} } # EVIDENCE-OF: R-50892-45943 sqlite> EXPLAIN QUERY PLAN # SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x; # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 # 0|0|0|SCAN SUBQUERY 1 # 0|0|0|USE TEMP B-TREE FOR GROUP BY # det 5.10 { SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x } { 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} 0 0 0 {SCAN SUBQUERY 1} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} } # EVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN # SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1; # 0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?) # 0|1|1|SCAN TABLE t1 # det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" { 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)} 0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2} } # EVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN # SELECT a FROM t1 UNION SELECT c FROM t2; # 1|0|0|SCAN TABLE t1 # 2|0|0|SCAN TABLE t2 # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) # det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" { 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} } # EVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN # SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1; # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 # 2|0|0|SCAN TABLE t2 2|0|0|USE TEMP B-TREE FOR ORDER BY # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) # det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" { 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} } |
︙ | ︙ |