Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Update this branch with latest trunk changes. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | server-edition |
Files: | files | file ages | folders |
SHA3-256: |
83b1e163ef6ac01d252268415f26a2a9 |
User & Date: | dan 2017-05-01 20:07:59.204 |
Context
2017-05-06
| ||
16:04 | Update this branch with latest trunk changes. (check-in: ed6bad67f5 user: dan tags: server-edition) | |
2017-05-01
| ||
20:07 | Update this branch with latest trunk changes. (check-in: 83b1e163ef user: dan tags: server-edition) | |
18:24 | Enhance "PRAGMA integrity_check" to detect duplicate rowids within a leaf page. (check-in: adcad37b00 user: dan tags: trunk) | |
2017-04-29
| ||
15:48 | Avoid reading the file-size before page 1 is read-locked. (check-in: f6b025bf63 user: dan tags: server-edition) | |
Changes
Changes to src/btree.c.
︙ | ︙ | |||
8444 8445 8446 8447 8448 8449 8450 8451 8452 8453 8454 8455 8456 8457 | return SQLITE_OK; } dropCell(pPage, idx, info.nSize, &rc); if( rc ) goto end_insert; }else if( loc<0 && pPage->nCell>0 ){ assert( pPage->leaf ); idx = ++pCur->ix; }else{ assert( pPage->leaf ); } insertCell(pPage, idx, newCell, szNew, 0, 0, &rc); assert( pPage->nOverflow==0 || rc==SQLITE_OK ); assert( rc!=SQLITE_OK || pPage->nCell>0 || pPage->nOverflow>0 ); | > | 8444 8445 8446 8447 8448 8449 8450 8451 8452 8453 8454 8455 8456 8457 8458 | return SQLITE_OK; } dropCell(pPage, idx, info.nSize, &rc); if( rc ) goto end_insert; }else if( loc<0 && pPage->nCell>0 ){ assert( pPage->leaf ); idx = ++pCur->ix; pCur->curFlags &= ~BTCF_ValidNKey; }else{ assert( pPage->leaf ); } insertCell(pPage, idx, newCell, szNew, 0, 0, &rc); assert( pPage->nOverflow==0 || rc==SQLITE_OK ); assert( rc!=SQLITE_OK || pPage->nCell>0 || pPage->nOverflow>0 ); |
︙ | ︙ | |||
9568 9569 9570 9571 9572 9573 9574 9575 9576 9577 9578 9579 9580 9581 | /* Check for integer primary key out of range */ if( pPage->intKey ){ if( keyCanBeEqual ? (info.nKey > maxKey) : (info.nKey >= maxKey) ){ checkAppendMsg(pCheck, "Rowid %lld out of order", info.nKey); } maxKey = info.nKey; } /* Check the content overflow list */ if( info.nPayload>info.nLocal ){ int nPage; /* Number of pages on the overflow chain */ Pgno pgnoOvfl; /* First page of the overflow chain */ assert( pc + info.nSize - 4 <= usableSize ); | > | 9569 9570 9571 9572 9573 9574 9575 9576 9577 9578 9579 9580 9581 9582 9583 | /* Check for integer primary key out of range */ if( pPage->intKey ){ if( keyCanBeEqual ? (info.nKey > maxKey) : (info.nKey >= maxKey) ){ checkAppendMsg(pCheck, "Rowid %lld out of order", info.nKey); } maxKey = info.nKey; keyCanBeEqual = 0; /* Only the first key on the page may ==maxKey */ } /* Check the content overflow list */ if( info.nPayload>info.nLocal ){ int nPage; /* Number of pages on the overflow chain */ Pgno pgnoOvfl; /* First page of the overflow chain */ assert( pc + info.nSize - 4 <= usableSize ); |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
5031 5032 5033 5034 5035 5036 5037 5038 5039 5040 5041 5042 5043 5044 | ** once, if the subquery is part of the WHERE clause in a LEFT JOIN, ** for example. In that case, do not regenerate the code to manifest ** a view or the co-routine to implement a view. The first instance ** is sufficient, though the subroutine to manifest the view does need ** to be invoked again. */ if( pItem->addrFillSub ){ if( pItem->fg.viaCoroutine==0 ){ sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub); } continue; } /* Increment Parse.nHeight by the height of the largest expression ** tree referred to by this, the parent select. The child select | > > > > | 5031 5032 5033 5034 5035 5036 5037 5038 5039 5040 5041 5042 5043 5044 5045 5046 5047 5048 | ** once, if the subquery is part of the WHERE clause in a LEFT JOIN, ** for example. In that case, do not regenerate the code to manifest ** a view or the co-routine to implement a view. The first instance ** is sufficient, though the subroutine to manifest the view does need ** to be invoked again. */ if( pItem->addrFillSub ){ if( pItem->fg.viaCoroutine==0 ){ /* The subroutine that manifests the view might be a one-time routine, ** or it might need to be rerun on each iteration because it ** encodes a correlated subquery. */ testcase( sqlite3VdbeGetOp(v, pItem->addrFillSub)->opcode==OP_Once ); sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub); } continue; } /* Increment Parse.nHeight by the height of the largest expression ** tree referred to by this, the parent select. The child select |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
760 761 762 763 764 765 766 | ** to the current line should be indented for EXPLAIN output. */ case OP_Goto: { /* jump */ jump_to_p2_and_check_for_interrupt: pOp = &aOp[pOp->p2 - 1]; /* Opcodes that are used as the bottom of a loop (OP_Next, OP_Prev, | | | 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 | ** to the current line should be indented for EXPLAIN output. */ case OP_Goto: { /* jump */ jump_to_p2_and_check_for_interrupt: pOp = &aOp[pOp->p2 - 1]; /* Opcodes that are used as the bottom of a loop (OP_Next, OP_Prev, ** OP_VNext, or OP_SorterNext) all jump here upon ** completion. Check to see if sqlite3_interrupt() has been called ** or if the progress callback needs to be invoked. ** ** This code uses unstructured "goto" statements and does not look clean. ** But that is not due to sloppy coding habits. The code is written this ** way for performance, to avoid having to run the interrupt and progress ** checks on every opcode. This helps sqlite3_step() to run about 1.5% |
︙ | ︙ | |||
1563 1564 1565 1566 1567 1568 1569 | arithmetic_result_is_null: sqlite3VdbeMemSetNull(pOut); break; } /* Opcode: CollSeq P1 * * P4 ** | | | 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 | arithmetic_result_is_null: sqlite3VdbeMemSetNull(pOut); break; } /* Opcode: CollSeq P1 * * P4 ** ** P4 is a pointer to a CollSeq object. If the next call to a user function ** or aggregate calls sqlite3GetFuncCollSeq(), this collation sequence will ** be returned. This is used by the built-in min(), max() and nullif() ** functions. ** ** If P1 is not zero, then it is a register that a subsequent min() or ** max() aggregate will set to 1 if the current row is not the minimum or ** maximum. The P1 register is initialized to 0 by this instruction. |
︙ | ︙ | |||
1844 1845 1846 1847 1848 1849 1850 | #ifndef SQLITE_OMIT_CAST /* Opcode: Cast P1 P2 * * * ** Synopsis: affinity(r[P1]) ** ** Force the value in register P1 to be the type defined by P2. ** ** <ul> | | | | | | | 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 | #ifndef SQLITE_OMIT_CAST /* Opcode: Cast P1 P2 * * * ** Synopsis: affinity(r[P1]) ** ** Force the value in register P1 to be the type defined by P2. ** ** <ul> ** <li> P2=='A' → BLOB ** <li> P2=='B' → TEXT ** <li> P2=='C' → NUMERIC ** <li> P2=='D' → INTEGER ** <li> P2=='E' → REAL ** </ul> ** ** A NULL value is not changed by this routine. It remains NULL. */ case OP_Cast: { /* in1 */ assert( pOp->p2>=SQLITE_AFF_BLOB && pOp->p2<=SQLITE_AFF_REAL ); testcase( pOp->p2==SQLITE_AFF_TEXT ); |
︙ | ︙ | |||
2719 2720 2721 2722 2723 2724 2725 | } /* Opcode: Affinity P1 P2 * P4 * ** Synopsis: affinity(r[P1@P2]) ** ** Apply affinities to a range of P2 registers starting with P1. ** | | | | 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 | } /* Opcode: Affinity P1 P2 * P4 * ** Synopsis: affinity(r[P1@P2]) ** ** Apply affinities to a range of P2 registers starting with P1. ** ** P4 is a string that is P2 characters long. The N-th character of the ** string indicates the column affinity that should be used for the N-th ** memory cell in the range. */ case OP_Affinity: { const char *zAffinity; /* The affinity to be applied */ zAffinity = pOp->p4.z; assert( zAffinity!=0 ); |
︙ | ︙ | |||
2747 2748 2749 2750 2751 2752 2753 | /* Opcode: MakeRecord P1 P2 P3 P4 * ** Synopsis: r[P3]=mkrec(r[P1@P2]) ** ** Convert P2 registers beginning with P1 into the [record format] ** use as a data record in a database table or as a key ** in an index. The OP_Column opcode can decode the record later. ** | | | | 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 2759 2760 2761 2762 | /* Opcode: MakeRecord P1 P2 P3 P4 * ** Synopsis: r[P3]=mkrec(r[P1@P2]) ** ** Convert P2 registers beginning with P1 into the [record format] ** use as a data record in a database table or as a key ** in an index. The OP_Column opcode can decode the record later. ** ** P4 may be a string that is P2 characters long. The N-th character of the ** string indicates the column affinity that should be used for the N-th ** field of the index key. ** ** The mapping from character to affinity is given by the SQLITE_AFF_ ** macros defined in sqliteInt.h. ** ** If P4 is NULL then all index fields have the affinity BLOB. */ |
︙ | ︙ | |||
5744 5745 5746 5747 5748 5749 5750 | break; } #endif /* SQLITE_OMIT_INTEGRITY_CHECK */ /* Opcode: RowSetAdd P1 P2 * * * ** Synopsis: rowset(P1)=r[P2] ** | | | > | | 5744 5745 5746 5747 5748 5749 5750 5751 5752 5753 5754 5755 5756 5757 5758 5759 5760 5761 5762 5763 5764 5765 5766 5767 5768 5769 5770 5771 5772 5773 5774 5775 5776 5777 5778 5779 5780 | break; } #endif /* SQLITE_OMIT_INTEGRITY_CHECK */ /* Opcode: RowSetAdd P1 P2 * * * ** Synopsis: rowset(P1)=r[P2] ** ** Insert the integer value held by register P2 into a RowSet object ** held in register P1. ** ** An assertion fails if P2 is not an integer. */ case OP_RowSetAdd: { /* in1, in2 */ pIn1 = &aMem[pOp->p1]; pIn2 = &aMem[pOp->p2]; assert( (pIn2->flags & MEM_Int)!=0 ); if( (pIn1->flags & MEM_RowSet)==0 ){ sqlite3VdbeMemSetRowSet(pIn1); if( (pIn1->flags & MEM_RowSet)==0 ) goto no_mem; } sqlite3RowSetInsert(pIn1->u.pRowSet, pIn2->u.i); break; } /* Opcode: RowSetRead P1 P2 P3 * * ** Synopsis: r[P3]=rowset(P1) ** ** Extract the smallest value from the RowSet object in P1 ** and put that value into register P3. ** Or, if RowSet object P1 is initially empty, leave P3 ** unchanged and jump to instruction P2. */ case OP_RowSetRead: { /* jump, in1, out3 */ i64 val; pIn1 = &aMem[pOp->p1]; if( (pIn1->flags & MEM_RowSet)==0 |
︙ | ︙ | |||
5796 5797 5798 5799 5800 5801 5802 | ** ** Register P3 is assumed to hold a 64-bit integer value. If register P1 ** contains a RowSet object and that RowSet object contains ** the value held in P3, jump to register P2. Otherwise, insert the ** integer in P3 into the RowSet and continue on to the ** next opcode. ** | | | | | < | | | 5797 5798 5799 5800 5801 5802 5803 5804 5805 5806 5807 5808 5809 5810 5811 5812 5813 5814 5815 5816 5817 5818 | ** ** Register P3 is assumed to hold a 64-bit integer value. If register P1 ** contains a RowSet object and that RowSet object contains ** the value held in P3, jump to register P2. Otherwise, insert the ** integer in P3 into the RowSet and continue on to the ** next opcode. ** ** The RowSet object is optimized for the case where sets of integers ** are inserted in distinct phases, which each set contains no duplicates. ** Each set is identified by a unique P4 value. The first set ** must have P4==0, the final set must have P4==-1, and for all other sets ** must have P4>0. ** ** This allows optimizations: (a) when P4==0 there is no need to test ** the RowSet object for P3, as it is guaranteed not to contain it, ** (b) when P4==-1 there is no need to insert the value, as it will ** never be tested for, and (c) when a value that is part of set X is ** inserted, there is no need to search to see if the same value was ** previously inserted as part of set X (only if it was previously ** inserted as part of some other set). */ case OP_RowSetTest: { /* jump, in1, in3 */ |
︙ | ︙ |
Changes to src/wherecode.c.
︙ | ︙ | |||
1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 | Vdbe *v; /* The prepared stmt under constructions */ struct SrcList_item *pTabItem; /* FROM clause term being coded */ int addrBrk; /* Jump here to break out of the loop */ int addrHalt; /* addrBrk for the outermost loop */ int addrCont; /* Jump here to continue with next cycle */ int iRowidReg = 0; /* Rowid is stored in this register, if not zero */ int iReleaseReg = 0; /* Temp register to free before returning */ pParse = pWInfo->pParse; v = pParse->pVdbe; pWC = &pWInfo->sWC; db = pParse->db; pLevel = &pWInfo->a[iLevel]; pLoop = pLevel->pWLoop; | > > | 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 | Vdbe *v; /* The prepared stmt under constructions */ struct SrcList_item *pTabItem; /* FROM clause term being coded */ int addrBrk; /* Jump here to break out of the loop */ int addrHalt; /* addrBrk for the outermost loop */ int addrCont; /* Jump here to continue with next cycle */ int iRowidReg = 0; /* Rowid is stored in this register, if not zero */ int iReleaseReg = 0; /* Temp register to free before returning */ Index *pIdx = 0; /* Index used by loop (if any) */ int loopAgain; /* True if constraint generator loop should repeat */ pParse = pWInfo->pParse; v = pParse->pVdbe; pWC = &pWInfo->sWC; db = pParse->db; pLevel = &pWInfo->a[iLevel]; pLoop = pLevel->pWLoop; |
︙ | ︙ | |||
1450 1451 1452 1453 1454 1455 1456 | int regBase; /* Base register holding constraint values */ WhereTerm *pRangeStart = 0; /* Inequality constraint at range start */ WhereTerm *pRangeEnd = 0; /* Inequality constraint at range end */ int startEq; /* True if range start uses ==, >= or <= */ int endEq; /* True if range end uses ==, >= or <= */ int start_constraints; /* Start of range is constrained */ int nConstraint; /* Number of constraint terms */ | < | 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 | int regBase; /* Base register holding constraint values */ WhereTerm *pRangeStart = 0; /* Inequality constraint at range start */ WhereTerm *pRangeEnd = 0; /* Inequality constraint at range end */ int startEq; /* True if range start uses ==, >= or <= */ int endEq; /* True if range end uses ==, >= or <= */ int start_constraints; /* Start of range is constrained */ int nConstraint; /* Number of constraint terms */ int iIdxCur; /* The VDBE cursor for the index */ int nExtraReg = 0; /* Number of extra registers needed */ int op; /* Instruction opcode */ char *zStartAff; /* Affinity for start of range constraint */ char *zEndAff = 0; /* Affinity for end of range constraint */ u8 bSeekPastNull = 0; /* True to seek past initial nulls */ u8 bStopAtNull = 0; /* Add condition to terminate at NULLs */ |
︙ | ︙ | |||
1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 | pLevel->p1 = iIdxCur; pLevel->p3 = (pLoop->wsFlags&WHERE_UNQ_WANTED)!=0 ? 1:0; if( (pLoop->wsFlags & WHERE_CONSTRAINT)==0 ){ pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP; }else{ assert( pLevel->p5==0 ); } }else #ifndef SQLITE_OMIT_OR_OPTIMIZATION if( pLoop->wsFlags & WHERE_MULTI_OR ){ /* Case 5: Two or more separately indexed terms connected by OR ** ** Example: | > | 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 | pLevel->p1 = iIdxCur; pLevel->p3 = (pLoop->wsFlags&WHERE_UNQ_WANTED)!=0 ? 1:0; if( (pLoop->wsFlags & WHERE_CONSTRAINT)==0 ){ pLevel->p5 = SQLITE_STMTSTATUS_FULLSCAN_STEP; }else{ assert( pLevel->p5==0 ); } if( omitTable ) pIdx = 0; }else #ifndef SQLITE_OMIT_OR_OPTIMIZATION if( pLoop->wsFlags & WHERE_MULTI_OR ){ /* Case 5: Two or more separately indexed terms connected by OR ** ** Example: |
︙ | ︙ | |||
2018 2019 2020 2021 2022 2023 2024 2025 | #ifdef SQLITE_ENABLE_STMT_SCANSTATUS pLevel->addrVisit = sqlite3VdbeCurrentAddr(v); #endif /* Insert code to test every subexpression that can be completely ** computed using the current set of tables. */ | > > > > > > > | | | | | | | | | | | | | | | | | > > > > | | | | | | | | | | | | | | | | > > | 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052 2053 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 | #ifdef SQLITE_ENABLE_STMT_SCANSTATUS pLevel->addrVisit = sqlite3VdbeCurrentAddr(v); #endif /* Insert code to test every subexpression that can be completely ** computed using the current set of tables. ** ** This loop may run either once (pIdx==0) or twice (pIdx!=0). If ** it is run twice, then the first iteration codes those sub-expressions ** that can be computed using columns from pIdx only (without seeking ** the main table cursor). */ do{ loopAgain = 0; for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){ Expr *pE; int skipLikeAddr = 0; testcase( pTerm->wtFlags & TERM_VIRTUAL ); testcase( pTerm->wtFlags & TERM_CODED ); if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue; if( (pTerm->prereqAll & pLevel->notReady)!=0 ){ testcase( pWInfo->untestedTerms==0 && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)!=0 ); pWInfo->untestedTerms = 1; continue; } pE = pTerm->pExpr; assert( pE!=0 ); if( pLevel->iLeftJoin && !ExprHasProperty(pE, EP_FromJoin) ){ continue; } if( pIdx && !sqlite3ExprCoveredByIndex(pE, pLevel->iTabCur, pIdx) ){ loopAgain = 1; continue; } if( pTerm->wtFlags & TERM_LIKECOND ){ /* If the TERM_LIKECOND flag is set, that means that the range search ** is sufficient to guarantee that the LIKE operator is true, so we ** can skip the call to the like(A,B) function. But this only works ** for strings. So do not skip the call to the function on the pass ** that compares BLOBs. */ #ifdef SQLITE_LIKE_DOESNT_MATCH_BLOBS continue; #else u32 x = pLevel->iLikeRepCntr; assert( x>0 ); skipLikeAddr = sqlite3VdbeAddOp1(v, (x&1)?OP_IfNot:OP_If, (int)(x>>1)); VdbeCoverage(v); #endif } sqlite3ExprIfFalse(pParse, pE, addrCont, SQLITE_JUMPIFNULL); if( skipLikeAddr ) sqlite3VdbeJumpHere(v, skipLikeAddr); pTerm->wtFlags |= TERM_CODED; } pIdx = 0; }while( loopAgain ); /* Insert code to test for implied constraints based on transitivity ** of the "==" operator. ** ** Example: If the WHERE clause contains "t1.a=t2.b" and "t2.b=123" ** and we are coding the t1 loop and the t2 loop has not yet coded, ** then we cannot use the "t1.a=t2.b" constraint, but we can code |
︙ | ︙ |
Changes to test/conflict3.test.
︙ | ︙ | |||
15 16 17 18 19 20 21 22 23 24 25 26 27 | # # This file focuses on making sure that combinations of REPLACE, # IGNORE, and FAIL conflict resolution play well together. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !conflict { finish_test return } | > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > > > > > > > > > > > > > | 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 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 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 249 250 251 252 253 254 255 256 257 258 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 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 | # # This file focuses on making sure that combinations of REPLACE, # IGNORE, and FAIL conflict resolution play well together. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix conflict3 ifcapable !conflict { finish_test return } do_execsql_test 1.1 { CREATE TABLE t1( a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 1.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 1.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 1.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Replete the tests above, but this time on a table non-INTEGER primary key. # do_execsql_test 2.1 { DROP TABLE t1; CREATE TABLE t1( a INT PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 2.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 2.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 2.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Replete again on a WITHOUT ROWID table. # do_execsql_test 3.1 { DROP TABLE t1; CREATE TABLE t1( a INT PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 3.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 3.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 3.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Arrange the table rows in a different order and repeat. # do_execsql_test 4.1 { DROP TABLE t1; CREATE TABLE t1( b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL, a INT PRIMARY KEY ON CONFLICT REPLACE ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 4.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 4.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 4.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Arrange the table rows in a different order and repeat. # do_execsql_test 5.1 { DROP TABLE t1; CREATE TABLE t1( b UNIQUE ON CONFLICT IGNORE, a INT PRIMARY KEY ON CONFLICT REPLACE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 5.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 5.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 5.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Arrange the table rows in a different order and repeat. # do_execsql_test 6.1 { DROP TABLE t1; CREATE TABLE t1( c UNIQUE ON CONFLICT FAIL, a INT PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 6.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 6.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 6.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test 7.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b INTEGER PRIMARY KEY ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 7.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 7.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 7.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test 8.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b INT PRIMARY KEY ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 8.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 8.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 8.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test 9.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b INT PRIMARY KEY ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 9.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 9.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 9.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test 10.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c INTEGER PRIMARY KEY ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 10.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 10.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 10.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test 11.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c PRIMARY KEY ON CONFLICT FAIL ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test 11.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test 11.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test 11.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Check that ticket [f68dc596c4] has been fixed. # do_execsql_test 12.1 { CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT); INSERT INTO t2 VALUES(111, '111'); } do_execsql_test 12.2 { REPLACE INTO t2 VALUES(NULL, '112'), (111, '111B'); } do_execsql_test 12.3 { SELECT * FROM t2; } {111 111B 112 112} finish_test |
Added test/pushdown.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 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 | # 2017 April 29 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix pushdown do_execsql_test 1.0 { CREATE TABLE t1(a, b, c); INSERT INTO t1 VALUES(1, 'b1', 'c1'); INSERT INTO t1 VALUES(2, 'b2', 'c2'); INSERT INTO t1 VALUES(3, 'b3', 'c3'); INSERT INTO t1 VALUES(4, 'b4', 'c4'); CREATE INDEX i1 ON t1(a, c); } proc f {val} { lappend ::L $val return 0 } db func f f do_test 1.1 { set L [list] execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) } set L } {c2} do_test 1.2 { set L [list] execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) } set L } {c3} do_execsql_test 1.3 { DROP INDEX i1; CREATE INDEX i1 ON t1(a, b); } do_test 1.4 { set L [list] execsql { SELECT * FROM t1 WHERE a=2 AND f(b) AND f(c) } set L } {b2} do_test 1.5 { set L [list] execsql { SELECT * FROM t1 WHERE a=3 AND f(c) AND f(b) } set L } {b3} finish_test |