Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Merge updates from trunk. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | toTypeFuncs |
| Files: | files | file ages | folders |
| SHA1: |
8922be1a3e0269552e12b87fe1e5141c |
| User & Date: | mistachkin 2013-09-06 22:27:55 |
Context
|
2013-09-24
| ||
| 19:07 | Merge updates from trunk. (check-in: 435ce3b3 user: mistachkin tags: toTypeFuncs) | |
|
2013-09-06
| ||
| 22:27 | Merge updates from trunk. (check-in: 8922be1a user: mistachkin tags: toTypeFuncs) | |
| 21:41 | Add the ability to generate assembly listing files using the MSVC makefile. (check-in: 6caa2cd1 user: mistachkin tags: toTypeFuncs) | |
| 13:10 | Combine the FuncDef.iPrefEnc and FuncDef.flags fields into a single new FuncDef.funcFlags field. (check-in: 97b10e66 user: drh tags: trunk) | |
Changes
Changes to src/analyze.c.
367 367 #endif 368 368 369 369 /* Return a pointer to the allocated object to the caller */ 370 370 sqlite3_result_blob(context, p, sizeof(p), sqlite3_free); 371 371 } 372 372 static const FuncDef statInitFuncdef = { 373 373 1+IsStat34, /* nArg */ 374 - SQLITE_UTF8, /* iPrefEnc */ 375 - 0, /* flags */ 374 + SQLITE_UTF8, /* funcFlags */ 376 375 0, /* pUserData */ 377 376 0, /* pNext */ 378 377 statInit, /* xFunc */ 379 378 0, /* xStep */ 380 379 0, /* xFinalize */ 381 380 "stat_init", /* zName */ 382 381 0, /* pHash */ 383 382 0 /* pDestructor */ 384 383 }; 384 + 385 +#ifdef SQLITE_ENABLE_STAT4 386 +/* 387 +** pNew and pOld are both candidate non-periodic samples selected for 388 +** the same column (pNew->iCol==pOld->iCol). Ignoring this column and 389 +** considering only any trailing columns and the sample hash value, this 390 +** function returns true if sample pNew is to be preferred over pOld. 391 +** In other words, if we assume that the cardinalities of the selected 392 +** column for pNew and pOld are equal, is pNew to be preferred over pOld. 393 +** 394 +** This function assumes that for each argument sample, the contents of 395 +** the anEq[] array from pSample->anEq[pSample->iCol+1] onwards are valid. 396 +*/ 397 +static int sampleIsBetterPost( 398 + Stat4Accum *pAccum, 399 + Stat4Sample *pNew, 400 + Stat4Sample *pOld 401 +){ 402 + int nCol = pAccum->nCol; 403 + int i; 404 + assert( pNew->iCol==pOld->iCol ); 405 + for(i=pNew->iCol+1; i<nCol; i++){ 406 + if( pNew->anEq[i]>pOld->anEq[i] ) return 1; 407 + if( pNew->anEq[i]<pOld->anEq[i] ) return 0; 408 + } 409 + if( pNew->iHash>pOld->iHash ) return 1; 410 + return 0; 411 +} 412 +#endif 385 413 386 414 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 387 415 /* 388 416 ** Return true if pNew is to be preferred over pOld. 417 +** 418 +** This function assumes that for each argument sample, the contents of 419 +** the anEq[] array from pSample->anEq[pSample->iCol] onwards are valid. 389 420 */ 390 -static int sampleIsBetter(Stat4Sample *pNew, Stat4Sample *pOld){ 421 +static int sampleIsBetter( 422 + Stat4Accum *pAccum, 423 + Stat4Sample *pNew, 424 + Stat4Sample *pOld 425 +){ 391 426 tRowcnt nEqNew = pNew->anEq[pNew->iCol]; 392 427 tRowcnt nEqOld = pOld->anEq[pOld->iCol]; 393 428 394 429 assert( pOld->isPSample==0 && pNew->isPSample==0 ); 395 430 assert( IsStat4 || (pNew->iCol==0 && pOld->iCol==0) ); 396 431 397 - if( (nEqNew>nEqOld) 398 - || (nEqNew==nEqOld && pNew->iCol<pOld->iCol) 399 - || (nEqNew==nEqOld && pNew->iCol==pOld->iCol && pNew->iHash>pOld->iHash) 400 - ){ 401 - return 1; 432 + if( (nEqNew>nEqOld) ) return 1; 433 +#ifdef SQLITE_ENABLE_STAT4 434 + if( nEqNew==nEqOld ){ 435 + if( pNew->iCol<pOld->iCol ) return 1; 436 + return (pNew->iCol==pOld->iCol && sampleIsBetterPost(pAccum, pNew, pOld)); 402 437 } 403 438 return 0; 439 +#else 440 + return (nEqNew==nEqOld && pNew->iHash>pOld->iHash); 441 +#endif 404 442 } 405 443 406 444 /* 407 445 ** Copy the contents of object (*pFrom) into (*pTo). 408 446 */ 409 447 void sampleCopy(Stat4Accum *p, Stat4Sample *pTo, Stat4Sample *pFrom){ 410 448 pTo->iRowid = pFrom->iRowid; ................................................................................ 419 457 /* 420 458 ** Copy the contents of sample *pNew into the p->a[] array. If necessary, 421 459 ** remove the least desirable sample from p->a[] to make room. 422 460 */ 423 461 static void sampleInsert(Stat4Accum *p, Stat4Sample *pNew, int nEqZero){ 424 462 Stat4Sample *pSample; 425 463 int i; 426 - i64 iSeq; 427 - int iPos; 428 464 429 465 assert( IsStat4 || nEqZero==0 ); 430 466 467 +#ifdef SQLITE_ENABLE_STAT4 431 468 if( pNew->isPSample==0 ){ 432 469 Stat4Sample *pUpgrade = 0; 433 470 assert( pNew->anEq[pNew->iCol]>0 ); 434 471 435 472 /* This sample is being added because the prefix that ends in column 436 473 ** iCol occurs many times in the table. However, if we have already 437 474 ** added a sample that shares this prefix, there is no need to add 438 475 ** this one. Instead, upgrade the priority of the highest priority 439 476 ** existing sample that shares this prefix. */ 440 477 for(i=p->nSample-1; i>=0; i--){ 441 478 Stat4Sample *pOld = &p->a[i]; 442 479 if( pOld->anEq[pNew->iCol]==0 ){ 443 480 if( pOld->isPSample ) return; 444 - assert( sampleIsBetter(pNew, pOld) ); 445 - if( pUpgrade==0 || sampleIsBetter(pOld, pUpgrade) ){ 481 + assert( pOld->iCol>pNew->iCol ); 482 + assert( sampleIsBetter(p, pNew, pOld) ); 483 + if( pUpgrade==0 || sampleIsBetter(p, pOld, pUpgrade) ){ 446 484 pUpgrade = pOld; 447 485 } 448 486 } 449 487 } 450 488 if( pUpgrade ){ 451 489 pUpgrade->iCol = pNew->iCol; 452 490 pUpgrade->anEq[pUpgrade->iCol] = pNew->anEq[pUpgrade->iCol]; 453 491 goto find_new_min; 454 492 } 455 493 } 494 +#endif 456 495 457 496 /* If necessary, remove sample iMin to make room for the new sample. */ 458 497 if( p->nSample>=p->mxSample ){ 459 498 Stat4Sample *pMin = &p->a[p->iMin]; 460 499 tRowcnt *anEq = pMin->anEq; 461 500 tRowcnt *anLt = pMin->anLt; 462 501 tRowcnt *anDLt = pMin->anDLt; ................................................................................ 464 503 pSample = &p->a[p->nSample-1]; 465 504 pSample->anEq = anEq; 466 505 pSample->anDLt = anDLt; 467 506 pSample->anLt = anLt; 468 507 p->nSample = p->mxSample-1; 469 508 } 470 509 471 - /* Figure out where in the a[] array the new sample should be inserted. */ 472 - iSeq = pNew->anLt[p->nCol-1]; 473 - for(iPos=p->nSample; iPos>0; iPos--){ 474 - if( iSeq>p->a[iPos-1].anLt[p->nCol-1] ) break; 475 - } 510 + /* The "rows less-than" for the rowid column must be greater than that 511 + ** for the last sample in the p->a[] array. Otherwise, the samples would 512 + ** be out of order. */ 513 +#ifdef SQLITE_ENABLE_STAT4 514 + assert( p->nSample==0 515 + || pNew->anLt[p->nCol-1] > p->a[p->nSample-1].anLt[p->nCol-1] ); 516 +#endif 476 517 477 518 /* Insert the new sample */ 478 - pSample = &p->a[iPos]; 479 - if( iPos!=p->nSample ){ 480 - Stat4Sample *pEnd = &p->a[p->nSample]; 481 - tRowcnt *anEq = pEnd->anEq; 482 - tRowcnt *anLt = pEnd->anLt; 483 - tRowcnt *anDLt = pEnd->anDLt; 484 - memmove(&p->a[iPos], &p->a[iPos+1], (p->nSample-iPos)*sizeof(p->a[0])); 485 - pSample->anEq = anEq; 486 - pSample->anDLt = anDLt; 487 - pSample->anLt = anLt; 488 - } 489 - p->nSample++; 519 + pSample = &p->a[p->nSample]; 490 520 sampleCopy(p, pSample, pNew); 521 + p->nSample++; 491 522 492 523 /* Zero the first nEqZero entries in the anEq[] array. */ 493 524 memset(pSample->anEq, 0, sizeof(tRowcnt)*nEqZero); 494 525 526 +#ifdef SQLITE_ENABLE_STAT4 495 527 find_new_min: 528 +#endif 496 529 if( p->nSample>=p->mxSample ){ 497 530 int iMin = -1; 498 531 for(i=0; i<p->mxSample; i++){ 499 532 if( p->a[i].isPSample ) continue; 500 - if( iMin<0 || sampleIsBetter(&p->a[iMin], &p->a[i]) ){ 533 + if( iMin<0 || sampleIsBetter(p, &p->a[iMin], &p->a[i]) ){ 501 534 iMin = i; 502 535 } 503 536 } 504 537 assert( iMin>=0 ); 505 538 p->iMin = iMin; 506 539 } 507 540 } ................................................................................ 517 550 #ifdef SQLITE_ENABLE_STAT4 518 551 int i; 519 552 520 553 /* Check if any samples from the aBest[] array should be pushed 521 554 ** into IndexSample.a[] at this point. */ 522 555 for(i=(p->nCol-2); i>=iChng; i--){ 523 556 Stat4Sample *pBest = &p->aBest[i]; 524 - if( p->nSample<p->mxSample 525 - || sampleIsBetter(pBest, &p->a[p->iMin]) 526 - ){ 557 + pBest->anEq[i] = p->current.anEq[i]; 558 + if( p->nSample<p->mxSample || sampleIsBetter(p, pBest, &p->a[p->iMin]) ){ 527 559 sampleInsert(p, pBest, i); 528 560 } 529 561 } 530 562 531 563 /* Update the anEq[] fields of any samples already collected. */ 532 564 for(i=p->nSample-1; i>=0; i--){ 533 565 int j; ................................................................................ 546 578 if( (nLt/p->nPSample)!=(nLt+nEq)/p->nPSample ){ 547 579 p->current.isPSample = 1; 548 580 sampleInsert(p, &p->current, 0); 549 581 p->current.isPSample = 0; 550 582 }else 551 583 552 584 /* Or if it is a non-periodic sample. Add it in this case too. */ 553 - if( p->nSample<p->mxSample || sampleIsBetter(&p->current, &p->a[p->iMin]) ){ 585 + if( p->nSample<p->mxSample 586 + || sampleIsBetter(p, &p->current, &p->a[p->iMin]) 587 + ){ 554 588 sampleInsert(p, &p->current, 0); 555 589 } 556 590 } 557 591 #endif 558 592 } 559 593 560 594 /* ................................................................................ 580 614 Stat4Accum *p = (Stat4Accum*)sqlite3_value_blob(argv[0]); 581 615 int iChng = sqlite3_value_int(argv[1]); 582 616 583 617 assert( p->nCol>1 ); /* Includes rowid field */ 584 618 assert( iChng<p->nCol ); 585 619 586 620 if( p->nRow==0 ){ 587 - /* anEq[0] is only zero for the very first call to this function. Do 588 - ** appropriate initialization */ 621 + /* This is the first call to this function. Do initialization. */ 589 622 for(i=0; i<p->nCol; i++) p->current.anEq[i] = 1; 590 623 }else{ 591 624 /* Second and subsequent calls get processed here */ 592 625 samplePushPrevious(p, iChng); 593 626 594 627 /* Update anDLt[], anLt[] and anEq[] to reflect the values that apply 595 628 ** to the current row of the index. */ ................................................................................ 621 654 sampleInsert(p, &p->current, p->nCol-1); 622 655 p->current.isPSample = 0; 623 656 } 624 657 625 658 /* Update the aBest[] array. */ 626 659 for(i=0; i<(p->nCol-1); i++){ 627 660 p->current.iCol = i; 628 - if( i>=iChng || sampleIsBetter(&p->current, &p->aBest[i]) ){ 661 + if( i>=iChng || sampleIsBetterPost(p, &p->current, &p->aBest[i]) ){ 629 662 sampleCopy(p, &p->aBest[i], &p->current); 630 663 } 631 664 } 632 665 } 633 666 #endif 634 667 } 635 668 static const FuncDef statPushFuncdef = { 636 669 2+IsStat34, /* nArg */ 637 - SQLITE_UTF8, /* iPrefEnc */ 638 - 0, /* flags */ 670 + SQLITE_UTF8, /* funcFlags */ 639 671 0, /* pUserData */ 640 672 0, /* pNext */ 641 673 statPush, /* xFunc */ 642 674 0, /* xStep */ 643 675 0, /* xFinalize */ 644 676 "stat_push", /* zName */ 645 677 0, /* pHash */ ................................................................................ 766 798 } 767 799 } 768 800 } 769 801 #endif /* SQLITE_ENABLE_STAT3_OR_STAT4 */ 770 802 } 771 803 static const FuncDef statGetFuncdef = { 772 804 1+IsStat34, /* nArg */ 773 - SQLITE_UTF8, /* iPrefEnc */ 774 - 0, /* flags */ 805 + SQLITE_UTF8, /* funcFlags */ 775 806 0, /* pUserData */ 776 807 0, /* pNext */ 777 808 statGet, /* xFunc */ 778 809 0, /* xStep */ 779 810 0, /* xFinalize */ 780 811 "stat_get", /* zName */ 781 812 0, /* pHash */
Changes to src/attach.c.
375 375 ** Called by the parser to compile a DETACH statement. 376 376 ** 377 377 ** DETACH pDbname 378 378 */ 379 379 void sqlite3Detach(Parse *pParse, Expr *pDbname){ 380 380 static const FuncDef detach_func = { 381 381 1, /* nArg */ 382 - SQLITE_UTF8, /* iPrefEnc */ 383 - 0, /* flags */ 382 + SQLITE_UTF8, /* funcFlags */ 384 383 0, /* pUserData */ 385 384 0, /* pNext */ 386 385 detachFunc, /* xFunc */ 387 386 0, /* xStep */ 388 387 0, /* xFinalize */ 389 388 "sqlite_detach", /* zName */ 390 389 0, /* pHash */ ................................................................................ 397 396 ** Called by the parser to compile an ATTACH statement. 398 397 ** 399 398 ** ATTACH p AS pDbname KEY pKey 400 399 */ 401 400 void sqlite3Attach(Parse *pParse, Expr *p, Expr *pDbname, Expr *pKey){ 402 401 static const FuncDef attach_func = { 403 402 3, /* nArg */ 404 - SQLITE_UTF8, /* iPrefEnc */ 405 - 0, /* flags */ 403 + SQLITE_UTF8, /* funcFlags */ 406 404 0, /* pUserData */ 407 405 0, /* pNext */ 408 406 attachFunc, /* xFunc */ 409 407 0, /* xStep */ 410 408 0, /* xFinalize */ 411 409 "sqlite_attach", /* zName */ 412 410 0, /* pHash */
Changes to src/callback.c.
266 266 if( p->nArg==nArg ){ 267 267 match = 4; 268 268 }else{ 269 269 match = 1; 270 270 } 271 271 272 272 /* Bonus points if the text encoding matches */ 273 - if( enc==p->iPrefEnc ){ 273 + if( enc==(p->funcFlags & SQLITE_FUNC_ENCMASK) ){ 274 274 match += 2; /* Exact encoding match */ 275 - }else if( (enc & p->iPrefEnc & 2)!=0 ){ 275 + }else if( (enc & p->funcFlags & 2)!=0 ){ 276 276 match += 1; /* Both are UTF16, but with different byte orders */ 277 277 } 278 278 279 279 return match; 280 280 } 281 281 282 282 /* ................................................................................ 402 402 ** exact match for the name, number of arguments and encoding, then add a 403 403 ** new entry to the hash table and return it. 404 404 */ 405 405 if( createFlag && bestScore<FUNC_PERFECT_MATCH && 406 406 (pBest = sqlite3DbMallocZero(db, sizeof(*pBest)+nName+1))!=0 ){ 407 407 pBest->zName = (char *)&pBest[1]; 408 408 pBest->nArg = (u16)nArg; 409 - pBest->iPrefEnc = enc; 409 + pBest->funcFlags = enc; 410 410 memcpy(pBest->zName, zName, nName); 411 411 pBest->zName[nName] = 0; 412 412 sqlite3FuncDefInsert(&db->aFunc, pBest); 413 413 } 414 414 415 415 if( pBest && (pBest->xStep || pBest->xFunc || createFlag) ){ 416 416 return pBest;
Changes to src/delete.c.
532 532 ** being deleted. Do not attempt to delete the row a second time, and 533 533 ** do not fire AFTER triggers. */ 534 534 sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid); 535 535 536 536 /* Do FK processing. This call checks that any FK constraints that 537 537 ** refer to this table (i.e. constraints attached to other tables) 538 538 ** are not violated by deleting this row. */ 539 - sqlite3FkCheck(pParse, pTab, iOld, 0); 539 + sqlite3FkCheck(pParse, pTab, iOld, 0, 0, 0); 540 540 } 541 541 542 542 /* Delete the index and table entries. Skip this step if pTab is really 543 543 ** a view (in which case the only effect of the DELETE statement is to 544 544 ** fire the INSTEAD OF triggers). */ 545 545 if( pTab->pSelect==0 ){ 546 546 sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, 0); ................................................................................ 549 549 sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_TRANSIENT); 550 550 } 551 551 } 552 552 553 553 /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to 554 554 ** handle rows (possibly in other tables) that refer via a foreign key 555 555 ** to the row just deleted. */ 556 - sqlite3FkActions(pParse, pTab, 0, iOld); 556 + sqlite3FkActions(pParse, pTab, 0, iOld, 0, 0); 557 557 558 558 /* Invoke AFTER DELETE trigger programs. */ 559 559 sqlite3CodeRowTrigger(pParse, pTrigger, 560 560 TK_DELETE, 0, TRIGGER_AFTER, pTab, iOld, onconf, iLabel 561 561 ); 562 562 563 563 /* Jump here if the row had already been deleted before any BEFORE
Changes to src/expr.c.
2630 2630 break; 2631 2631 } 2632 2632 2633 2633 /* Attempt a direct implementation of the built-in COALESCE() and 2634 2634 ** IFNULL() functions. This avoids unnecessary evalation of 2635 2635 ** arguments past the first non-NULL argument. 2636 2636 */ 2637 - if( pDef->flags & SQLITE_FUNC_COALESCE ){ 2637 + if( pDef->funcFlags & SQLITE_FUNC_COALESCE ){ 2638 2638 int endCoalesce = sqlite3VdbeMakeLabel(v); 2639 2639 assert( nFarg>=2 ); 2640 2640 sqlite3ExprCode(pParse, pFarg->a[0].pExpr, target); 2641 2641 for(i=1; i<nFarg; i++){ 2642 2642 sqlite3VdbeAddOp2(v, OP_NotNull, target, endCoalesce); 2643 2643 sqlite3ExprCacheRemove(pParse, target, 1); 2644 2644 sqlite3ExprCachePush(pParse); ................................................................................ 2654 2654 r1 = sqlite3GetTempRange(pParse, nFarg); 2655 2655 2656 2656 /* For length() and typeof() functions with a column argument, 2657 2657 ** set the P5 parameter to the OP_Column opcode to OPFLAG_LENGTHARG 2658 2658 ** or OPFLAG_TYPEOFARG respectively, to avoid unnecessary data 2659 2659 ** loading. 2660 2660 */ 2661 - if( (pDef->flags & (SQLITE_FUNC_LENGTH|SQLITE_FUNC_TYPEOF))!=0 ){ 2661 + if( (pDef->funcFlags & (SQLITE_FUNC_LENGTH|SQLITE_FUNC_TYPEOF))!=0 ){ 2662 2662 u8 exprOp; 2663 2663 assert( nFarg==1 ); 2664 2664 assert( pFarg->a[0].pExpr!=0 ); 2665 2665 exprOp = pFarg->a[0].pExpr->op; 2666 2666 if( exprOp==TK_COLUMN || exprOp==TK_AGG_COLUMN ){ 2667 2667 assert( SQLITE_FUNC_LENGTH==OPFLAG_LENGTHARG ); 2668 2668 assert( SQLITE_FUNC_TYPEOF==OPFLAG_TYPEOFARG ); 2669 - testcase( pDef->flags==SQLITE_FUNC_LENGTH ); 2670 - pFarg->a[0].pExpr->op2 = pDef->flags; 2669 + testcase( (pDef->funcFlags&~SQLITE_FUNC_ENCMASK) 2670 + ==SQLITE_FUNC_LENGTH ); 2671 + pFarg->a[0].pExpr->op2 = pDef->funcFlags&~SQLITE_FUNC_ENCMASK; 2671 2672 } 2672 2673 } 2673 2674 2674 2675 sqlite3ExprCachePush(pParse); /* Ticket 2ea2425d34be */ 2675 2676 sqlite3ExprCodeExprList(pParse, pFarg, r1, 1); 2676 2677 sqlite3ExprCachePop(pParse, 1); /* Ticket 2ea2425d34be */ 2677 2678 }else{ ................................................................................ 2696 2697 pDef = sqlite3VtabOverloadFunction(db, pDef, nFarg, pFarg->a[0].pExpr); 2697 2698 } 2698 2699 #endif 2699 2700 for(i=0; i<nFarg; i++){ 2700 2701 if( i<32 && sqlite3ExprIsConstant(pFarg->a[i].pExpr) ){ 2701 2702 constMask |= (1<<i); 2702 2703 } 2703 - if( (pDef->flags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){ 2704 + if( (pDef->funcFlags & SQLITE_FUNC_NEEDCOLL)!=0 && !pColl ){ 2704 2705 pColl = sqlite3ExprCollSeq(pParse, pFarg->a[i].pExpr); 2705 2706 } 2706 2707 } 2707 - if( pDef->flags & SQLITE_FUNC_NEEDCOLL ){ 2708 + if( pDef->funcFlags & SQLITE_FUNC_NEEDCOLL ){ 2708 2709 if( !pColl ) pColl = db->pDfltColl; 2709 2710 sqlite3VdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)pColl, P4_COLLSEQ); 2710 2711 } 2711 2712 sqlite3VdbeAddOp4(v, OP_Function, constMask, r1, target, 2712 2713 (char*)pDef, P4_FUNCDEF); 2713 2714 sqlite3VdbeChangeP5(v, (u8)nFarg); 2714 2715 if( nFarg ){
Changes to src/fkey.c.
678 678 679 679 if( iSkip ){ 680 680 sqlite3VdbeResolveLabel(v, iSkip); 681 681 } 682 682 } 683 683 } 684 684 685 + 686 +/* 687 +** The second argument points to an FKey object representing a foreign key 688 +** for which pTab is the child table. An UPDATE statement against pTab 689 +** is currently being processed. For each column of the table that is 690 +** actually updated, the corresponding element in the aChange[] array 691 +** is zero or greater (if a column is unmodified the corresponding element 692 +** is set to -1). If the rowid column is modified by the UPDATE statement 693 +** the bChngRowid argument is non-zero. 694 +** 695 +** This function returns true if any of the columns that are part of the 696 +** child key for FK constraint *p are modified. 697 +*/ 698 +static int fkChildIsModified( 699 + Table *pTab, /* Table being updated */ 700 + FKey *p, /* Foreign key for which pTab is the child */ 701 + int *aChange, /* Array indicating modified columns */ 702 + int bChngRowid /* True if rowid is modified by this update */ 703 +){ 704 + int i; 705 + for(i=0; i<p->nCol; i++){ 706 + int iChildKey = p->aCol[i].iFrom; 707 + if( aChange[iChildKey]>=0 ) return 1; 708 + if( iChildKey==pTab->iPKey && bChngRowid ) return 1; 709 + } 710 + return 0; 711 +} 712 + 713 +/* 714 +** The second argument points to an FKey object representing a foreign key 715 +** for which pTab is the parent table. An UPDATE statement against pTab 716 +** is currently being processed. For each column of the table that is 717 +** actually updated, the corresponding element in the aChange[] array 718 +** is zero or greater (if a column is unmodified the corresponding element 719 +** is set to -1). If the rowid column is modified by the UPDATE statement 720 +** the bChngRowid argument is non-zero. 721 +** 722 +** This function returns true if any of the columns that are part of the 723 +** parent key for FK constraint *p are modified. 724 +*/ 725 +static int fkParentIsModified( 726 + Table *pTab, 727 + FKey *p, 728 + int *aChange, 729 + int bChngRowid 730 +){ 731 + int i; 732 + for(i=0; i<p->nCol; i++){ 733 + char *zKey = p->aCol[i].zCol; 734 + int iKey; 735 + for(iKey=0; iKey<pTab->nCol; iKey++){ 736 + if( aChange[iKey]>=0 || (iKey==pTab->iPKey && bChngRowid) ){ 737 + Column *pCol = &pTab->aCol[iKey]; 738 + if( zKey ){ 739 + if( 0==sqlite3StrICmp(pCol->zName, zKey) ) return 1; 740 + }else if( pCol->colFlags & COLFLAG_PRIMKEY ){ 741 + return 1; 742 + } 743 + } 744 + } 745 + } 746 + return 0; 747 +} 748 + 685 749 /* 686 750 ** This function is called when inserting, deleting or updating a row of 687 751 ** table pTab to generate VDBE code to perform foreign key constraint 688 752 ** processing for the operation. 689 753 ** 690 754 ** For a DELETE operation, parameter regOld is passed the index of the 691 755 ** first register in an array of (pTab->nCol+1) registers containing the ................................................................................ 702 766 ** described for DELETE. Then again after the original record is deleted 703 767 ** but before the new record is inserted using the INSERT convention. 704 768 */ 705 769 void sqlite3FkCheck( 706 770 Parse *pParse, /* Parse context */ 707 771 Table *pTab, /* Row is being deleted from this table */ 708 772 int regOld, /* Previous row data is stored here */ 709 - int regNew /* New row data is stored here */ 773 + int regNew, /* New row data is stored here */ 774 + int *aChange, /* Array indicating UPDATEd columns (or 0) */ 775 + int bChngRowid /* True if rowid is UPDATEd */ 710 776 ){ 711 777 sqlite3 *db = pParse->db; /* Database handle */ 712 778 FKey *pFKey; /* Used to iterate through FKs */ 713 779 int iDb; /* Index of database containing pTab */ 714 780 const char *zDb; /* Name of database containing pTab */ 715 781 int isIgnoreErrors = pParse->disableTriggers; 716 782 ................................................................................ 729 795 Table *pTo; /* Parent table of foreign key pFKey */ 730 796 Index *pIdx = 0; /* Index on key columns in pTo */ 731 797 int *aiFree = 0; 732 798 int *aiCol; 733 799 int iCol; 734 800 int i; 735 801 int isIgnore = 0; 802 + 803 + if( aChange 804 + && sqlite3_stricmp(pTab->zName, pFKey->zTo)!=0 805 + && fkChildIsModified(pTab, pFKey, aChange, bChngRowid)==0 806 + ){ 807 + continue; 808 + } 736 809 737 810 /* Find the parent table of this foreign key. Also find a unique index 738 811 ** on the parent key columns in the parent table. If either of these 739 812 ** schema items cannot be located, set an error in pParse and return 740 813 ** early. */ 741 814 if( pParse->disableTriggers ){ 742 815 pTo = sqlite3FindTable(db, pFKey->zTo, zDb); ................................................................................ 811 884 } 812 885 813 886 /* Loop through all the foreign key constraints that refer to this table */ 814 887 for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ 815 888 Index *pIdx = 0; /* Foreign key index for pFKey */ 816 889 SrcList *pSrc; 817 890 int *aiCol = 0; 891 + 892 + if( aChange && fkParentIsModified(pTab, pFKey, aChange, bChngRowid)==0 ){ 893 + continue; 894 + } 818 895 819 896 if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferFKs) 820 897 && !pParse->pToplevel && !pParse->isMultiWrite 821 898 ){ 822 899 assert( regOld==0 && regNew!=0 ); 823 900 /* Inserting a single row into a parent table cannot cause an immediate 824 901 ** foreign key violation. So do nothing in this case. */ ................................................................................ 884 961 if( pIdx ){ 885 962 for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]); 886 963 } 887 964 } 888 965 } 889 966 return mask; 890 967 } 968 + 891 969 892 970 /* 893 971 ** This function is called before generating code to update or delete a 894 972 ** row contained in table pTab. If the operation is a DELETE, then 895 973 ** parameter aChange is passed a NULL value. For an UPDATE, aChange points 896 974 ** to an array of size N, where N is the number of columns in table pTab. 897 975 ** If the i'th column is not modified by the UPDATE, then the corresponding ................................................................................ 914 992 /* A DELETE operation. Foreign key processing is required if the 915 993 ** table in question is either the child or parent table for any 916 994 ** foreign key constraint. */ 917 995 return (sqlite3FkReferences(pTab) || pTab->pFKey); 918 996 }else{ 919 997 /* This is an UPDATE. Foreign key processing is only required if the 920 998 ** operation modifies one or more child or parent key columns. */ 921 - int i; 922 999 FKey *p; 923 1000 924 1001 /* Check if any child key columns are being modified. */ 925 1002 for(p=pTab->pFKey; p; p=p->pNextFrom){ 926 - for(i=0; i<p->nCol; i++){ 927 - int iChildKey = p->aCol[i].iFrom; 928 - if( aChange[iChildKey]>=0 ) return 1; 929 - if( iChildKey==pTab->iPKey && chngRowid ) return 1; 930 - } 1003 + if( fkChildIsModified(pTab, p, aChange, chngRowid) ) return 1; 931 1004 } 932 1005 933 1006 /* Check if any parent key columns are being modified. */ 934 1007 for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){ 935 - for(i=0; i<p->nCol; i++){ 936 - char *zKey = p->aCol[i].zCol; 937 - int iKey; 938 - for(iKey=0; iKey<pTab->nCol; iKey++){ 939 - Column *pCol = &pTab->aCol[iKey]; 940 - if( (zKey ? !sqlite3StrICmp(pCol->zName, zKey) 941 - : (pCol->colFlags & COLFLAG_PRIMKEY)!=0) ){ 942 - if( aChange[iKey]>=0 ) return 1; 943 - if( iKey==pTab->iPKey && chngRowid ) return 1; 944 - } 945 - } 946 - } 1008 + if( fkParentIsModified(pTab, p, aChange, chngRowid) ) return 1; 947 1009 } 948 1010 } 949 1011 } 950 1012 return 0; 951 1013 } 952 1014 953 1015 /* ................................................................................ 1165 1227 ** This function is called when deleting or updating a row to implement 1166 1228 ** any required CASCADE, SET NULL or SET DEFAULT actions. 1167 1229 */ 1168 1230 void sqlite3FkActions( 1169 1231 Parse *pParse, /* Parse context */ 1170 1232 Table *pTab, /* Table being updated or deleted from */ 1171 1233 ExprList *pChanges, /* Change-list for UPDATE, NULL for DELETE */ 1172 - int regOld /* Address of array containing old row */ 1234 + int regOld, /* Address of array containing old row */ 1235 + int *aChange, /* Array indicating UPDATEd columns (or 0) */ 1236 + int bChngRowid /* True if rowid is UPDATEd */ 1173 1237 ){ 1174 1238 /* If foreign-key support is enabled, iterate through all FKs that 1175 1239 ** refer to table pTab. If there is an action associated with the FK 1176 1240 ** for this operation (either update or delete), invoke the associated 1177 1241 ** trigger sub-program. */ 1178 1242 if( pParse->db->flags&SQLITE_ForeignKeys ){ 1179 1243 FKey *pFKey; /* Iterator variable */ 1180 1244 for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ 1181 - Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges); 1182 - if( pAction ){ 1183 - sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0); 1245 + if( aChange==0 || fkParentIsModified(pTab, pFKey, aChange, bChngRowid) ){ 1246 + Trigger *pAct = fkActionTrigger(pParse, pTab, pFKey, pChanges); 1247 + if( pAct ){ 1248 + sqlite3CodeRowTriggerDirect(pParse, pAct, pTab, regOld, OE_Abort, 0); 1249 + } 1184 1250 } 1185 1251 } 1186 1252 } 1187 1253 } 1188 1254 1189 1255 #endif /* ifndef SQLITE_OMIT_TRIGGER */ 1190 1256
Changes to src/func.c.
1689 1689 ** Set the LIKEOPT flag on the 2-argument function with the given name. 1690 1690 */ 1691 1691 static void setLikeOptFlag(sqlite3 *db, const char *zName, u8 flagVal){ 1692 1692 FuncDef *pDef; 1693 1693 pDef = sqlite3FindFunction(db, zName, sqlite3Strlen30(zName), 1694 1694 2, SQLITE_UTF8, 0); 1695 1695 if( ALWAYS(pDef) ){ 1696 - pDef->flags = flagVal; 1696 + pDef->funcFlags |= flagVal; 1697 1697 } 1698 1698 } 1699 1699 1700 1700 /* 1701 1701 ** Register the built-in LIKE and GLOB functions. The caseSensitive 1702 1702 ** parameter determines whether or not the LIKE operator is case 1703 1703 ** sensitive. GLOB is always case sensitive. ................................................................................ 1733 1733 ){ 1734 1734 return 0; 1735 1735 } 1736 1736 assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); 1737 1737 pDef = sqlite3FindFunction(db, pExpr->u.zToken, 1738 1738 sqlite3Strlen30(pExpr->u.zToken), 1739 1739 2, SQLITE_UTF8, 0); 1740 - if( NEVER(pDef==0) || (pDef->flags & SQLITE_FUNC_LIKE)==0 ){ 1740 + if( NEVER(pDef==0) || (pDef->funcFlags & SQLITE_FUNC_LIKE)==0 ){ 1741 1741 return 0; 1742 1742 } 1743 1743 1744 1744 /* The memcpy() statement assumes that the wildcard characters are 1745 1745 ** the first three statements in the compareInfo structure. The 1746 1746 ** asserts() that follow verify that assumption 1747 1747 */ 1748 1748 memcpy(aWc, pDef->pUserData, 3); 1749 1749 assert( (char*)&likeInfoAlt == (char*)&likeInfoAlt.matchAll ); 1750 1750 assert( &((char*)&likeInfoAlt)[1] == (char*)&likeInfoAlt.matchOne ); 1751 1751 assert( &((char*)&likeInfoAlt)[2] == (char*)&likeInfoAlt.matchSet ); 1752 - *pIsNocase = (pDef->flags & SQLITE_FUNC_CASE)==0; 1752 + *pIsNocase = (pDef->funcFlags & SQLITE_FUNC_CASE)==0; 1753 1753 return 1; 1754 1754 } 1755 1755 1756 1756 /* 1757 1757 ** All all of the FuncDef structures in the aBuiltinFunc[] array above 1758 1758 ** to the global function hash table. This occurs at start-time (as 1759 1759 ** a consequence of calling sqlite3_initialize()). ................................................................................ 1826 1826 FUNCTION(load_extension, 1, 0, 0, loadExt ), 1827 1827 FUNCTION(load_extension, 2, 0, 0, loadExt ), 1828 1828 #endif 1829 1829 AGGREGATE(sum, 1, 0, 0, sumStep, sumFinalize ), 1830 1830 AGGREGATE(total, 1, 0, 0, sumStep, totalFinalize ), 1831 1831 AGGREGATE(avg, 1, 0, 0, sumStep, avgFinalize ), 1832 1832 /* AGGREGATE(count, 0, 0, 0, countStep, countFinalize ), */ 1833 - {0,SQLITE_UTF8,SQLITE_FUNC_COUNT,0,0,0,countStep,countFinalize,"count",0,0}, 1833 + {0,SQLITE_UTF8|SQLITE_FUNC_COUNT,0,0,0,countStep,countFinalize,"count",0,0}, 1834 1834 AGGREGATE(count, 1, 0, 0, countStep, countFinalize ), 1835 1835 AGGREGATE(group_concat, 1, 0, 0, groupConcatStep, groupConcatFinalize), 1836 1836 AGGREGATE(group_concat, 2, 0, 0, groupConcatStep, groupConcatFinalize), 1837 1837 1838 1838 LIKEFUNC(glob, 2, &globInfo, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE), 1839 1839 #ifdef SQLITE_CASE_SENSITIVE_LIKE 1840 1840 LIKEFUNC(like, 2, &likeInfoAlt, SQLITE_FUNC_LIKE|SQLITE_FUNC_CASE),
Changes to src/insert.c.
1027 1027 }else 1028 1028 #endif 1029 1029 { 1030 1030 int isReplace; /* Set to true if constraints may cause a replace */ 1031 1031 sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx, 1032 1032 keyColumn>=0, 0, onError, endOfLoop, &isReplace 1033 1033 ); 1034 - sqlite3FkCheck(pParse, pTab, 0, regIns); 1034 + sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0); 1035 1035 sqlite3CompleteInsertion( 1036 1036 pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0 1037 1037 ); 1038 1038 } 1039 1039 } 1040 1040 1041 1041 /* Update the count of rows that are inserted
Changes to src/main.c.
1402 1402 1403 1403 /* Check if an existing function is being overridden or deleted. If so, 1404 1404 ** and there are active VMs, then return SQLITE_BUSY. If a function 1405 1405 ** is being overridden/deleted but there are no active VMs, allow the 1406 1406 ** operation to continue but invalidate all precompiled statements. 1407 1407 */ 1408 1408 p = sqlite3FindFunction(db, zFunctionName, nName, nArg, (u8)enc, 0); 1409 - if( p && p->iPrefEnc==enc && p->nArg==nArg ){ 1409 + if( p && (p->funcFlags & SQLITE_FUNC_ENCMASK)==enc && p->nArg==nArg ){ 1410 1410 if( db->nVdbeActive ){ 1411 1411 sqlite3Error(db, SQLITE_BUSY, 1412 1412 "unable to delete/modify user-function due to active statements"); 1413 1413 assert( !db->mallocFailed ); 1414 1414 return SQLITE_BUSY; 1415 1415 }else{ 1416 1416 sqlite3ExpirePreparedStatements(db); ................................................................................ 1427 1427 ** being replaced invoke the destructor function here. */ 1428 1428 functionDestroy(db, p); 1429 1429 1430 1430 if( pDestructor ){ 1431 1431 pDestructor->nRef++; 1432 1432 } 1433 1433 p->pDestructor = pDestructor; 1434 - p->flags = 0; 1434 + p->funcFlags &= SQLITE_FUNC_ENCMASK; 1435 1435 p->xFunc = xFunc; 1436 1436 p->xStep = xStep; 1437 1437 p->xFinalize = xFinal; 1438 1438 p->pUserData = pUserData; 1439 1439 p->nArg = (u16)nArg; 1440 1440 return SQLITE_OK; 1441 1441 }
Changes to src/select.c.
3224 3224 pTab = p->pSrc->a[0].pTab; 3225 3225 pExpr = p->pEList->a[0].pExpr; 3226 3226 assert( pTab && !pTab->pSelect && pExpr ); 3227 3227 3228 3228 if( IsVirtual(pTab) ) return 0; 3229 3229 if( pExpr->op!=TK_AGG_FUNCTION ) return 0; 3230 3230 if( NEVER(pAggInfo->nFunc==0) ) return 0; 3231 - if( (pAggInfo->aFunc[0].pFunc->flags&SQLITE_FUNC_COUNT)==0 ) return 0; 3231 + if( (pAggInfo->aFunc[0].pFunc->funcFlags&SQLITE_FUNC_COUNT)==0 ) return 0; 3232 3232 if( pExpr->flags&EP_Distinct ) return 0; 3233 3233 3234 3234 return pTab; 3235 3235 } 3236 3236 3237 3237 /* 3238 3238 ** If the source-list item passed as an argument was augmented with an ................................................................................ 3821 3821 regAgg = 0; 3822 3822 } 3823 3823 if( pF->iDistinct>=0 ){ 3824 3824 addrNext = sqlite3VdbeMakeLabel(v); 3825 3825 assert( nArg==1 ); 3826 3826 codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg); 3827 3827 } 3828 - if( pF->pFunc->flags & SQLITE_FUNC_NEEDCOLL ){ 3828 + if( pF->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ 3829 3829 CollSeq *pColl = 0; 3830 3830 struct ExprList_item *pItem; 3831 3831 int j; 3832 3832 assert( pList!=0 ); /* pList!=0 if pF->pFunc has NEEDCOLL */ 3833 3833 for(j=0, pItem=pList->a; !pColl && j<nArg; j++, pItem++){ 3834 3834 pColl = sqlite3ExprCollSeq(pParse, pItem->pExpr); 3835 3835 }
Changes to src/shell.c.
1190 1190 void *pData = sqlite3_malloc(3*nCol*sizeof(const char*) + 1); 1191 1191 if( !pData ){ 1192 1192 rc = SQLITE_NOMEM; 1193 1193 }else{ 1194 1194 char **azCols = (char **)pData; /* Names of result columns */ 1195 1195 char **azVals = &azCols[nCol]; /* Results */ 1196 1196 int *aiTypes = (int *)&azVals[nCol]; /* Result types */ 1197 - int i; 1197 + int i, x; 1198 1198 assert(sizeof(int) <= sizeof(char *)); 1199 1199 /* save off ptrs to column names */ 1200 1200 for(i=0; i<nCol; i++){ 1201 1201 azCols[i] = (char *)sqlite3_column_name(pStmt, i); 1202 1202 } 1203 1203 do{ 1204 1204 /* extract the data and data types */ 1205 1205 for(i=0; i<nCol; i++){ 1206 - azVals[i] = (char *)sqlite3_column_text(pStmt, i); 1207 - aiTypes[i] = sqlite3_column_type(pStmt, i); 1206 + aiTypes[i] = x = sqlite3_column_type(pStmt, i); 1207 + if( x==SQLITE_BLOB && pArg->mode==MODE_Insert ){ 1208 + azVals[i] = ""; 1209 + }else{ 1210 + azVals[i] = (char*)sqlite3_column_text(pStmt, i); 1211 + } 1208 1212 if( !azVals[i] && (aiTypes[i]!=SQLITE_NULL) ){ 1209 1213 rc = SQLITE_NOMEM; 1210 1214 break; /* from for */ 1211 1215 } 1212 1216 } /* end for */ 1213 1217 1214 1218 /* if data and types extracted successfully... */
Changes to src/sqliteInt.h.
1061 1061 ** Each SQL function is defined by an instance of the following 1062 1062 ** structure. A pointer to this structure is stored in the sqlite.aFunc 1063 1063 ** hash table. When multiple functions have the same name, the hash table 1064 1064 ** points to a linked list of these structures. 1065 1065 */ 1066 1066 struct FuncDef { 1067 1067 i16 nArg; /* Number of arguments. -1 means unlimited */ 1068 - u8 iPrefEnc; /* Preferred text encoding (SQLITE_UTF8, 16LE, 16BE) */ 1069 - u8 flags; /* Some combination of SQLITE_FUNC_* */ 1068 + u16 funcFlags; /* Some combination of SQLITE_FUNC_* */ 1070 1069 void *pUserData; /* User data parameter */ 1071 1070 FuncDef *pNext; /* Next function with same name */ 1072 1071 void (*xFunc)(sqlite3_context*,int,sqlite3_value**); /* Regular function */ 1073 1072 void (*xStep)(sqlite3_context*,int,sqlite3_value**); /* Aggregate step */ 1074 1073 void (*xFinalize)(sqlite3_context*); /* Aggregate finalizer */ 1075 1074 char *zName; /* SQL name of the function. */ 1076 1075 FuncDef *pHash; /* Next with a different name but the same hash */ ................................................................................ 1098 1097 }; 1099 1098 1100 1099 /* 1101 1100 ** Possible values for FuncDef.flags. Note that the _LENGTH and _TYPEOF 1102 1101 ** values must correspond to OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG. There 1103 1102 ** are assert() statements in the code to verify this. 1104 1103 */ 1105 -#define SQLITE_FUNC_LIKE 0x01 /* Candidate for the LIKE optimization */ 1106 -#define SQLITE_FUNC_CASE 0x02 /* Case-sensitive LIKE-type function */ 1107 -#define SQLITE_FUNC_EPHEM 0x04 /* Ephemeral. Delete with VDBE */ 1108 -#define SQLITE_FUNC_NEEDCOLL 0x08 /* sqlite3GetFuncCollSeq() might be called */ 1109 -#define SQLITE_FUNC_COUNT 0x10 /* Built-in count(*) aggregate */ 1110 -#define SQLITE_FUNC_COALESCE 0x20 /* Built-in coalesce() or ifnull() function */ 1111 -#define SQLITE_FUNC_LENGTH 0x40 /* Built-in length() function */ 1112 -#define SQLITE_FUNC_TYPEOF 0x80 /* Built-in typeof() function */ 1104 +#define SQLITE_FUNC_ENCMASK 0x003 /* SQLITE_UTF8, SQLITE_UTF16BE or UTF16LE */ 1105 +#define SQLITE_FUNC_LIKE 0x004 /* Candidate for the LIKE optimization */ 1106 +#define SQLITE_FUNC_CASE 0x008 /* Case-sensitive LIKE-type function */ 1107 +#define SQLITE_FUNC_EPHEM 0x010 /* Ephemeral. Delete with VDBE */ 1108 +#define SQLITE_FUNC_NEEDCOLL 0x020 /* sqlite3GetFuncCollSeq() might be called */ 1109 +#define SQLITE_FUNC_LENGTH 0x040 /* Built-in length() function */ 1110 +#define SQLITE_FUNC_TYPEOF 0x080 /* Built-in typeof() function */ 1111 +#define SQLITE_FUNC_COUNT 0x100 /* Built-in count(*) aggregate */ 1112 +#define SQLITE_FUNC_COALESCE 0x200 /* Built-in coalesce() or ifnull() */ 1113 +#define SQLITE_FUNC_UNLIKELY 0x400 /* Built-in unlikely() function */ 1113 1114 1114 1115 /* 1115 1116 ** The following three macros, FUNCTION(), LIKEFUNC() and AGGREGATE() are 1116 1117 ** used to create the initializers for the FuncDef structures. 1117 1118 ** 1118 1119 ** FUNCTION(zName, nArg, iArg, bNC, xFunc) 1119 1120 ** Used to create a scalar function definition of a function zName ................................................................................ 1133 1134 ** that accepts nArg arguments and is implemented by a call to C 1134 1135 ** function likeFunc. Argument pArg is cast to a (void *) and made 1135 1136 ** available as the function user-data (sqlite3_user_data()). The 1136 1137 ** FuncDef.flags variable is set to the value passed as the flags 1137 1138 ** parameter. 1138 1139 */ 1139 1140 #define FUNCTION(zName, nArg, iArg, bNC, xFunc) \ 1140 - {nArg, SQLITE_UTF8, (bNC*SQLITE_FUNC_NEEDCOLL), \ 1141 + {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ 1141 1142 SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0} 1142 1143 #define FUNCTION2(zName, nArg, iArg, bNC, xFunc, extraFlags) \ 1143 - {nArg, SQLITE_UTF8, (bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags, \ 1144 + {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL)|extraFlags, \ 1144 1145 SQLITE_INT_TO_PTR(iArg), 0, xFunc, 0, 0, #zName, 0, 0} 1145 1146 #define STR_FUNCTION(zName, nArg, pArg, bNC, xFunc) \ 1146 - {nArg, SQLITE_UTF8, bNC*SQLITE_FUNC_NEEDCOLL, \ 1147 + {nArg, SQLITE_UTF8|(bNC*SQLITE_FUNC_NEEDCOLL), \ 1147 1148 pArg, 0, xFunc, 0, 0, #zName, 0, 0} 1148 1149 #define LIKEFUNC(zName, nArg, arg, flags) \ 1149 - {nArg, SQLITE_UTF8, flags, (void *)arg, 0, likeFunc, 0, 0, #zName, 0, 0} 1150 + {nArg, SQLITE_UTF8|flags, (void *)arg, 0, likeFunc, 0, 0, #zName, 0, 0} 1150 1151 #define AGGREGATE(zName, nArg, arg, nc, xStep, xFinal) \ 1151 - {nArg, SQLITE_UTF8, nc*SQLITE_FUNC_NEEDCOLL, \ 1152 + {nArg, SQLITE_UTF8|(nc*SQLITE_FUNC_NEEDCOLL), \ 1152 1153 SQLITE_INT_TO_PTR(arg), 0, 0, xStep,xFinal,#zName,0,0} 1153 1154 1154 1155 /* 1155 1156 ** All current savepoints are stored in a linked list starting at 1156 1157 ** sqlite3.pSavepoint. The first element in the list is the most recently 1157 1158 ** opened savepoint. Savepoints are added to the list by the vdbe 1158 1159 ** OP_Savepoint instruction. ................................................................................ 3205 3206 ** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign 3206 3207 ** key functionality is available. If OMIT_TRIGGER is defined but 3207 3208 ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In 3208 3209 ** this case foreign keys are parsed, but no other functionality is 3209 3210 ** provided (enforcement of FK constraints requires the triggers sub-system). 3210 3211 */ 3211 3212 #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) 3212 - void sqlite3FkCheck(Parse*, Table*, int, int); 3213 + void sqlite3FkCheck(Parse*, Table*, int, int, int*, int); 3213 3214 void sqlite3FkDropTable(Parse*, SrcList *, Table*); 3214 - void sqlite3FkActions(Parse*, Table*, ExprList*, int); 3215 + void sqlite3FkActions(Parse*, Table*, ExprList*, int, int*, int); 3215 3216 int sqlite3FkRequired(Parse*, Table*, int*, int); 3216 3217 u32 sqlite3FkOldmask(Parse*, Table*); 3217 3218 FKey *sqlite3FkReferences(Table *); 3218 3219 #else 3219 - #define sqlite3FkActions(a,b,c,d) 3220 + #define sqlite3FkActions(a,b,c,d,e,f) 3220 3221 #define sqlite3FkCheck(a,b,c,d) 3221 3222 #define sqlite3FkDropTable(a,b,c) 3222 - #define sqlite3FkOldmask(a,b) 0 3223 - #define sqlite3FkRequired(a,b,c,d) 0 3223 + #define sqlite3FkOldmask(a,b) 0 3224 + #define sqlite3FkRequired(a,b,c,d,e,f) 0 3224 3225 #endif 3225 3226 #ifndef SQLITE_OMIT_FOREIGN_KEY 3226 3227 void sqlite3FkDelete(sqlite3 *, Table*); 3227 3228 int sqlite3FkLocateIndex(Parse*,Table*,FKey*,Index**,int**); 3228 3229 #else 3229 3230 #define sqlite3FkDelete(a,b) 3230 3231 #define sqlite3FkLocateIndex(a,b,c,d,e)
Changes to src/update.c.
484 484 485 485 /* Do constraint checks. */ 486 486 sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid, 487 487 aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0); 488 488 489 489 /* Do FK constraint checks. */ 490 490 if( hasFK ){ 491 - sqlite3FkCheck(pParse, pTab, regOldRowid, 0); 491 + sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngRowid); 492 492 } 493 493 494 494 /* Delete the index entries associated with the current record. */ 495 495 j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid); 496 496 sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx); 497 497 498 498 /* If changing the record number, delete the old record. */ 499 499 if( hasFK || chngRowid ){ 500 500 sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0); 501 501 } 502 502 sqlite3VdbeJumpHere(v, j1); 503 503 504 504 if( hasFK ){ 505 - sqlite3FkCheck(pParse, pTab, 0, regNewRowid); 505 + sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngRowid); 506 506 } 507 507 508 508 /* Insert the new index entries and the new record. */ 509 509 sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0); 510 510 511 511 /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to 512 512 ** handle rows (possibly in other tables) that refer via a foreign key 513 513 ** to the row just updated. */ 514 514 if( hasFK ){ 515 - sqlite3FkActions(pParse, pTab, pChanges, regOldRowid); 515 + sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngRowid); 516 516 } 517 517 } 518 518 519 519 /* Increment the row counter 520 520 */ 521 521 if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){ 522 522 sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);
Changes to src/vdbe.c.
1433 1433 ** the pointer to ctx.s so in case the user-function can use 1434 1434 ** the already allocated buffer instead of allocating a new one. 1435 1435 */ 1436 1436 sqlite3VdbeMemMove(&ctx.s, pOut); 1437 1437 MemSetTypeFlag(&ctx.s, MEM_Null); 1438 1438 1439 1439 ctx.fErrorOrAux = 0; 1440 - if( ctx.pFunc->flags & SQLITE_FUNC_NEEDCOLL ){ 1440 + if( ctx.pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ 1441 1441 assert( pOp>aOp ); 1442 1442 assert( pOp[-1].p4type==P4_COLLSEQ ); 1443 1443 assert( pOp[-1].opcode==OP_CollSeq ); 1444 1444 ctx.pColl = pOp[-1].p4.pColl; 1445 1445 } 1446 1446 db->lastRowid = lastRowid; 1447 1447 (*ctx.pFunc->xFunc)(&ctx, n, apVal); /* IMP: R-24505-23230 */ ................................................................................ 5433 5433 ctx.s.z = 0; 5434 5434 ctx.s.zMalloc = 0; 5435 5435 ctx.s.xDel = 0; 5436 5436 ctx.s.db = db; 5437 5437 ctx.isError = 0; 5438 5438 ctx.pColl = 0; 5439 5439 ctx.skipFlag = 0; 5440 - if( ctx.pFunc->flags & SQLITE_FUNC_NEEDCOLL ){ 5440 + if( ctx.pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ 5441 5441 assert( pOp>p->aOp ); 5442 5442 assert( pOp[-1].p4type==P4_COLLSEQ ); 5443 5443 assert( pOp[-1].opcode==OP_CollSeq ); 5444 5444 ctx.pColl = pOp[-1].p4.pColl; 5445 5445 } 5446 5446 (ctx.pFunc->xStep)(&ctx, n, apVal); /* IMP: R-24505-23230 */ 5447 5447 if( ctx.isError ){
Changes to src/vdbeaux.c.
600 600 601 601 602 602 /* 603 603 ** If the input FuncDef structure is ephemeral, then free it. If 604 604 ** the FuncDef is not ephermal, then do nothing. 605 605 */ 606 606 static void freeEphemeralFunction(sqlite3 *db, FuncDef *pDef){ 607 - if( ALWAYS(pDef) && (pDef->flags & SQLITE_FUNC_EPHEM)!=0 ){ 607 + if( ALWAYS(pDef) && (pDef->funcFlags & SQLITE_FUNC_EPHEM)!=0 ){ 608 608 sqlite3DbFree(db, pDef); 609 609 } 610 610 } 611 611 612 612 static void vdbeFreeOpArray(sqlite3 *, Op *, int); 613 613 614 614 /*
Changes to src/vdbemem.c.
1300 1300 Expr *pExpr, /* The expression to extract a value from */ 1301 1301 u8 affinity, /* Affinity to use */ 1302 1302 int iVal, /* Array element to populate */ 1303 1303 int *pbOk /* OUT: True if value was extracted */ 1304 1304 ){ 1305 1305 int rc = SQLITE_OK; 1306 1306 sqlite3_value *pVal = 0; 1307 + sqlite3 *db = pParse->db; 1308 + 1307 1309 1308 1310 struct ValueNewStat4Ctx alloc; 1309 1311 alloc.pParse = pParse; 1310 1312 alloc.pIdx = pIdx; 1311 1313 alloc.ppRec = ppRec; 1312 1314 alloc.iVal = iVal; 1313 1315 1314 1316 /* Skip over any TK_COLLATE nodes */ 1315 1317 pExpr = sqlite3ExprSkipCollate(pExpr); 1316 1318 1317 1319 if( !pExpr ){ 1318 - pVal = valueNew(pParse->db, &alloc); 1320 + pVal = valueNew(db, &alloc); 1319 1321 if( pVal ){ 1320 1322 sqlite3VdbeMemSetNull((Mem*)pVal); 1321 1323 *pbOk = 1; 1322 1324 } 1323 1325 }else if( pExpr->op==TK_VARIABLE 1324 1326 || (pExpr->op==TK_REGISTER && pExpr->op2==TK_VARIABLE) 1325 1327 ){ 1326 1328 Vdbe *v; 1327 1329 int iBindVar = pExpr->iColumn; 1328 1330 sqlite3VdbeSetVarmask(pParse->pVdbe, iBindVar); 1329 1331 if( (v = pParse->pReprepare)!=0 ){ 1330 - pVal = valueNew(pParse->db, &alloc); 1332 + pVal = valueNew(db, &alloc); 1331 1333 if( pVal ){ 1332 1334 rc = sqlite3VdbeMemCopy((Mem*)pVal, &v->aVar[iBindVar-1]); 1333 1335 if( rc==SQLITE_OK ){ 1334 - sqlite3ValueApplyAffinity(pVal, affinity, SQLITE_UTF8); 1336 + sqlite3ValueApplyAffinity(pVal, affinity, ENC(db)); 1335 1337 } 1336 1338 pVal->db = pParse->db; 1337 1339 *pbOk = 1; 1338 1340 sqlite3VdbeMemStoreType((Mem*)pVal); 1339 1341 } 1340 1342 }else{ 1341 1343 *pbOk = 0; 1342 1344 } 1343 1345 }else{ 1344 - sqlite3 *db = pParse->db; 1345 1346 rc = valueFromExpr(db, pExpr, ENC(db), affinity, &pVal, &alloc); 1346 1347 *pbOk = (pVal!=0); 1347 1348 } 1348 1349 1349 - assert( pVal==0 || pVal->db==pParse->db ); 1350 + assert( pVal==0 || pVal->db==db ); 1350 1351 return rc; 1351 1352 } 1352 1353 1353 1354 /* 1354 1355 ** Unless it is NULL, the argument must be an UnpackedRecord object returned 1355 1356 ** by an earlier call to sqlite3Stat4ProbeSetValue(). This call deletes 1356 1357 ** the object.
Changes to src/vtab.c.
1009 1009 return pDef; 1010 1010 } 1011 1011 *pNew = *pDef; 1012 1012 pNew->zName = (char *)&pNew[1]; 1013 1013 memcpy(pNew->zName, pDef->zName, sqlite3Strlen30(pDef->zName)+1); 1014 1014 pNew->xFunc = xFunc; 1015 1015 pNew->pUserData = pArg; 1016 - pNew->flags |= SQLITE_FUNC_EPHEM; 1016 + pNew->funcFlags |= SQLITE_FUNC_EPHEM; 1017 1017 return pNew; 1018 1018 } 1019 1019 1020 1020 /* 1021 1021 ** Make sure virtual table pTab is contained in the pParse->apVirtualLock[] 1022 1022 ** array so that an OP_VBegin will get generated for it. Add pTab to the 1023 1023 ** array if it is missing. If pTab is already in the array, this routine
Changes to src/where.c.
2417 2417 Parse *pParse, /* Database connection */ 2418 2418 Index *pIdx, /* Index to consider domain of */ 2419 2419 UnpackedRecord *pRec, /* Vector of values to consider */ 2420 2420 int roundUp, /* Round up if true. Round down if false */ 2421 2421 tRowcnt *aStat /* OUT: stats written here */ 2422 2422 ){ 2423 2423 IndexSample *aSample = pIdx->aSample; 2424 - int iCol = pRec->nField-1; /* Index of required stats in anEq[] etc. */ 2424 + int iCol; /* Index of required stats in anEq[] etc. */ 2425 2425 int iMin = 0; /* Smallest sample not yet tested */ 2426 2426 int i = pIdx->nSample; /* Smallest sample larger than or equal to pRec */ 2427 2427 int iTest; /* Next sample to test */ 2428 2428 int res; /* Result of comparison operation */ 2429 2429 2430 + assert( pRec!=0 || pParse->db->mallocFailed ); 2431 + if( pRec==0 ) return; 2432 + iCol = pRec->nField - 1; 2430 2433 assert( pIdx->nSample>0 ); 2431 2434 assert( pRec->nField>0 && iCol<pIdx->nSampleCol ); 2432 2435 do{ 2433 2436 iTest = (iMin+i)/2; 2434 2437 res = sqlite3VdbeRecordCompare(aSample[iTest].n, aSample[iTest].p, pRec); 2435 2438 if( res<0 ){ 2436 2439 iMin = iTest+1; ................................................................................ 2542 2545 int rc = SQLITE_OK; 2543 2546 int nOut = (int)*pnOut; 2544 2547 2545 2548 #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 2546 2549 Index *p = pBuilder->pNew->u.btree.pIndex; 2547 2550 int nEq = pBuilder->pNew->u.btree.nEq; 2548 2551 2549 - if( nEq==pBuilder->nRecValid 2552 + if( p->nSample>0 2553 + && nEq==pBuilder->nRecValid 2550 2554 && nEq<p->nSampleCol 2551 - && p->nSample 2552 2555 && OptimizationEnabled(pParse->db, SQLITE_Stat3) 2553 2556 ){ 2554 2557 UnpackedRecord *pRec = pBuilder->pRec; 2555 2558 tRowcnt a[2]; 2556 - u8 aff = p->pTable->aCol[p->aiColumn[0]].affinity; 2559 + u8 aff; 2557 2560 2558 2561 /* Variable iLower will be set to the estimate of the number of rows in 2559 2562 ** the index that are less than the lower bound of the range query. The 2560 2563 ** lower bound being the concatenation of $P and $L, where $P is the 2561 2564 ** key-prefix formed by the nEq values matched against the nEq left-most 2562 2565 ** columns of the index, and $L is the value in pLower. 2563 2566 ** ................................................................................ 2571 2574 ** less than the upper bound of the range query. Where the upper bound 2572 2575 ** is either ($P) or ($P:$U). Again, even if $U is available, both values 2573 2576 ** of iUpper are requested of whereKeyStats() and the smaller used. 2574 2577 */ 2575 2578 tRowcnt iLower; 2576 2579 tRowcnt iUpper; 2577 2580 2581 + if( nEq==p->nColumn ){ 2582 + aff = SQLITE_AFF_INTEGER; 2583 + }else{ 2584 + aff = p->pTable->aCol[p->aiColumn[nEq]].affinity; 2585 + } 2578 2586 /* Determine iLower and iUpper using ($P) only. */ 2579 2587 if( nEq==0 ){ 2580 2588 iLower = 0; 2581 2589 iUpper = p->aiRowEst[0]; 2582 2590 }else{ 2583 2591 /* Note: this call could be optimized away - since the same values must 2584 2592 ** have been requested when testing key $P in whereEqualScanEst(). */ ................................................................................ 4056 4064 return SQLITE_OK; 4057 4065 } 4058 4066 4059 4067 /* 4060 4068 ** Transfer content from the second pLoop into the first. 4061 4069 */ 4062 4070 static int whereLoopXfer(sqlite3 *db, WhereLoop *pTo, WhereLoop *pFrom){ 4063 - if( whereLoopResize(db, pTo, pFrom->nLTerm) ) return SQLITE_NOMEM; 4064 4071 whereLoopClearUnion(db, pTo); 4072 + if( whereLoopResize(db, pTo, pFrom->nLTerm) ){ 4073 + memset(&pTo->u, 0, sizeof(pTo->u)); 4074 + return SQLITE_NOMEM; 4075 + } 4065 4076 memcpy(pTo, pFrom, WHERE_LOOP_XFER_SZ); 4066 4077 memcpy(pTo->aLTerm, pFrom->aLTerm, pTo->nLTerm*sizeof(pTo->aLTerm[0])); 4067 4078 if( pFrom->wsFlags & WHERE_VIRTUALTABLE ){ 4068 4079 pFrom->u.vtab.needFree = 0; 4069 4080 }else if( (pFrom->wsFlags & WHERE_AUTO_INDEX)!=0 ){ 4070 4081 pFrom->u.btree.pIndex = 0; 4071 4082 } ................................................................................ 4171 4182 && p->rRun<=pTemplate->rRun 4172 4183 && p->nOut<=pTemplate->nOut 4173 4184 ){ 4174 4185 /* This branch taken when p is equal or better than pTemplate in 4175 4186 ** all of (1) dependencies (2) setup-cost, (3) run-cost, and 4176 4187 ** (4) number of output rows. */ 4177 4188 assert( p->rSetup==pTemplate->rSetup ); 4178 - if( p->nLTerm<pTemplate->nLTerm 4189 + if( p->prereq==pTemplate->prereq 4190 + && p->nLTerm<pTemplate->nLTerm 4179 4191 && (p->wsFlags & WHERE_INDEXED)!=0 4180 4192 && (pTemplate->wsFlags & WHERE_INDEXED)!=0 4181 4193 && p->u.btree.pIndex==pTemplate->u.btree.pIndex 4182 - && p->prereq==pTemplate->prereq 4183 4194 ){ 4184 4195 /* Overwrite an existing WhereLoop with an similar one that uses 4185 4196 ** more terms of the index */ 4186 4197 pNext = p->pNextLoop; 4187 4198 break; 4188 4199 }else{ 4189 4200 /* pTemplate is not helpful. ................................................................................ 5900 5911 #endif 5901 5912 /* Attempt to omit tables from the join that do not effect the result */ 5902 5913 if( pWInfo->nLevel>=2 5903 5914 && pResultSet!=0 5904 5915 && OptimizationEnabled(db, SQLITE_OmitNoopJoin) 5905 5916 ){ 5906 5917 Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet); 5907 - if( pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, pOrderBy); 5918 + if( sWLB.pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, sWLB.pOrderBy); 5908 5919 while( pWInfo->nLevel>=2 ){ 5909 5920 WhereTerm *pTerm, *pEnd; 5910 5921 pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop; 5911 5922 if( (pWInfo->pTabList->a[pLoop->iTab].jointype & JT_LEFT)==0 ) break; 5912 5923 if( (wctrlFlags & WHERE_WANT_DISTINCT)==0 5913 5924 && (pLoop->wsFlags & WHERE_ONEROW)==0 5914 5925 ){
Changes to test/analyze9.test.
240 240 neq, 241 241 lrange(nlt, 0, 2), 242 242 lrange(ndlt, 0, 2), 243 243 lrange(test_decode(sample), 0, 1) 244 244 FROM sqlite_stat4 245 245 ORDER BY rowid DESC LIMIT 2; 246 246 } { 247 - {2 1 1 1} {295 296 296} {120 122 125} {201 4} 247 + {2 1 1 1} {295 295 295} {120 121 124} {201 3} 248 248 {5 3 1 1} {290 290 292} {119 119 121} {200 1} 249 249 } 250 250 251 251 do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120 252 252 do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119 253 253 254 254 # Check that the perioidic samples are present. ................................................................................ 552 552 set sql { 553 553 SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3 554 554 } 555 555 do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/} 556 556 } 557 557 } 558 558 559 -finish_test 559 +#------------------------------------------------------------------------- 560 +# Check that affinities are taken into account when using stat4 data to 561 +# estimate the number of rows scanned by a rowid constraint. 562 +# 563 +drop_all_tables 564 +do_test 13.1 { 565 + execsql { 566 + CREATE TABLE t1(a, b, c); 567 + CREATE INDEX i1 ON t1(a); 568 + CREATE INDEX i2 ON t1(b, c); 569 + } 570 + for {set i 0} {$i<100} {incr i} { 571 + if {$i %2} {set a abc} else {set a def} 572 + execsql { INSERT INTO t1(rowid, a, b, c) VALUES($i, $a, $i, $i) } 573 + } 574 + execsql ANALYZE 575 +} {} 576 +do_eqp_test 13.2.1 { 577 + SELECT * FROM t1 WHERE a='abc' AND rowid<15 AND b<20 578 +} {/SEARCH TABLE t1 USING INDEX i1/} 579 +do_eqp_test 13.2.2 { 580 + SELECT * FROM t1 WHERE a='abc' AND rowid<'15' AND b<20 581 +} {/SEARCH TABLE t1 USING INDEX i1/} 582 +do_eqp_test 13.3.1 { 583 + SELECT * FROM t1 WHERE a='abc' AND rowid<100 AND b<20 584 +} {/SEARCH TABLE t1 USING INDEX i2/} 585 +do_eqp_test 13.3.2 { 586 + SELECT * FROM t1 WHERE a='abc' AND rowid<'100' AND b<20 587 +} {/SEARCH TABLE t1 USING INDEX i2/} 588 + 589 +#------------------------------------------------------------------------- 590 +# Check also that affinities are taken into account when using stat4 data 591 +# to estimate the number of rows scanned by any other constraint on a 592 +# column other than the leftmost. 593 +# 594 +drop_all_tables 595 +do_test 14.1 { 596 + execsql { CREATE TABLE t1(a, b INTEGER, c) } 597 + for {set i 0} {$i<100} {incr i} { 598 + set c [expr $i % 3] 599 + execsql { INSERT INTO t1 VALUES('ott', $i, $c) } 600 + } 601 + execsql { 602 + CREATE INDEX i1 ON t1(a, b); 603 + CREATE INDEX i2 ON t1(c); 604 + ANALYZE; 605 + } 606 +} {} 607 +do_eqp_test 13.2.1 { 608 + SELECT * FROM t1 WHERE a='ott' AND b<10 AND c=1 609 +} {/SEARCH TABLE t1 USING INDEX i1/} 610 +do_eqp_test 13.2.2 { 611 + SELECT * FROM t1 WHERE a='ott' AND b<'10' AND c=1 612 +} {/SEARCH TABLE t1 USING INDEX i1/} 613 + 614 +#------------------------------------------------------------------------- 615 +# By default, 16 non-periodic samples are collected for the stat4 table. 616 +# The following tests attempt to verify that the most common keys are 617 +# being collected. 618 +# 619 +proc check_stat4 {tn} { 620 + db eval ANALYZE 621 + db eval {SELECT a, b, c, d FROM t1} { 622 + incr k($a) 623 + incr k([list $a $b]) 624 + incr k([list $a $b $c]) 625 + if { [info exists k([list $a $b $c $d])]==0 } { incr nRow } 626 + incr k([list $a $b $c $d]) 627 + } 628 + 629 + set L [list] 630 + foreach key [array names k] { 631 + lappend L [list $k($key) $key] 632 + } 633 + 634 + set nSample $nRow 635 + if {$nSample>16} {set nSample 16} 636 + 637 + set nThreshold [lindex [lsort -decr -integer -index 0 $L] [expr $nSample-1] 0] 638 + foreach key [array names k] { 639 + if {$k($key)>$nThreshold} { 640 + set expect($key) 1 641 + } 642 + if {$k($key)==$nThreshold} { 643 + set possible($key) 1 644 + } 645 + } 646 + 647 + 648 + set nPossible [expr $nSample - [llength [array names expect]]] 649 + 650 + #puts "EXPECT: [array names expect]" 651 + #puts "POSSIBLE($nPossible/[array size possible]): [array names possible]" 652 + #puts "HAVE: [db eval {SELECT test_decode(sample) FROM sqlite_stat4 WHERE idx='i1'}]" 653 + 654 + db eval {SELECT test_decode(sample) AS s FROM sqlite_stat4 WHERE idx='i1'} { 655 + set seen 0 656 + for {set i 0} {$i<4} {incr i} { 657 + unset -nocomplain expect([lrange $s 0 $i]) 658 + if {[info exists possible([lrange $s 0 $i])]} { 659 + set seen 1 660 + unset -nocomplain possible([lrange $s 0 $i]) 661 + } 662 + } 663 + if {$seen} {incr nPossible -1} 664 + } 665 + if {$nPossible<0} {set nPossible 0} 666 + 667 + set res [list [llength [array names expect]] $nPossible] 668 + uplevel [list do_test $tn [list set {} $res] {0 0}] 669 +} 670 + 671 +drop_all_tables 672 +do_test 14.1.1 { 673 + execsql { 674 + CREATE TABLE t1(a,b,c,d); 675 + CREATE INDEX i1 ON t1(a,b,c,d); 676 + } 677 + for {set i 0} {$i < 160} {incr i} { 678 + execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } 679 + if {($i % 10)==0} { execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } } 680 + } 681 +} {} 682 +check_stat4 14.1.2 683 + 684 +do_test 14.2.1 { 685 + execsql { DELETE FROM t1 } 686 + for {set i 0} {$i < 1600} {incr i} { 687 + execsql { INSERT INTO t1 VALUES($i/10,$i/17,$i/27,$i/37) } 688 + } 689 +} {} 690 +check_stat4 14.2.2 691 + 692 +do_test 14.3.1 { 693 + for {set i 0} {$i < 10} {incr i} { 694 + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 695 + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 696 + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 697 + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 698 + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 699 + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 700 + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 701 + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 702 + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 703 + execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 704 + } 705 +} {} 706 +check_stat4 14.3.2 707 + 708 +do_test 14.4.1 { 709 + execsql {DELETE FROM t1} 710 + for {set i 1} {$i < 160} {incr i} { 711 + set b [expr $i % 10] 712 + if {$b==0 || $b==2} {set b 1} 713 + execsql { INSERT INTO t1 VALUES($i/10,$b,$i,$i) } 714 + } 715 +} {} 716 +check_stat4 14.4.2 717 +db func lrange lrange 718 +db func lindex lindex 719 +do_execsql_test 14.4.3 { 720 + SELECT lrange(test_decode(sample), 0, 1) AS s FROM sqlite_stat4 721 + WHERE lindex(s, 1)=='1' ORDER BY rowid 722 +} { 723 + {0 1} {1 1} {2 1} {3 1} 724 + {4 1} {5 1} {6 1} {7 1} 725 + {8 1} {9 1} {10 1} {11 1} 726 + {12 1} {13 1} {14 1} {15 1} 727 +} 728 + 729 +#------------------------------------------------------------------------- 730 +# Test that nothing untoward happens if the stat4 table contains entries 731 +# for indexes that do not exist. Or NULL values in the idx column. 732 +# Or NULL values in any of the other columns. 733 +# 734 +drop_all_tables 735 +do_execsql_test 15.1 { 736 + CREATE TABLE x1(a, b, UNIQUE(a, b)); 737 + INSERT INTO x1 VALUES(1, 2); 738 + INSERT INTO x1 VALUES(3, 4); 739 + INSERT INTO x1 VALUES(5, 6); 740 + ANALYZE; 741 + INSERT INTO sqlite_stat4 VALUES(NULL, NULL, NULL, NULL, NULL, NULL); 742 +} 743 +db close 744 +sqlite3 db test.db 745 +do_execsql_test 15.2 { SELECT * FROM x1 } {1 2 3 4 5 6} 746 + 747 +do_execsql_test 15.3 { 748 + INSERT INTO sqlite_stat4 VALUES(42, 42, 42, 42, 42, 42); 749 +} 750 +db close 751 +sqlite3 db test.db 752 +do_execsql_test 15.4 { SELECT * FROM x1 } {1 2 3 4 5 6} 753 + 754 +do_execsql_test 15.5 { 755 + UPDATE sqlite_stat1 SET stat = NULL; 756 +} 757 +db close 758 +sqlite3 db test.db 759 +do_execsql_test 15.6 { SELECT * FROM x1 } {1 2 3 4 5 6} 760 + 761 +do_execsql_test 15.7 { 762 + ANALYZE; 763 + UPDATE sqlite_stat1 SET tbl = 'no such tbl'; 764 +} 765 +db close 766 +sqlite3 db test.db 767 +do_execsql_test 15.8 { SELECT * FROM x1 } {1 2 3 4 5 6} 768 + 769 +do_execsql_test 15.9 { 770 + ANALYZE; 771 + UPDATE sqlite_stat4 SET neq = NULL, nlt=NULL, ndlt=NULL; 772 +} 773 +db close 774 +sqlite3 db test.db 775 +do_execsql_test 15.10 { SELECT * FROM x1 } {1 2 3 4 5 6} 776 + 777 +# This is just for coverage.... 778 +do_execsql_test 15.11 { 779 + ANALYZE; 780 + UPDATE sqlite_stat1 SET stat = stat || ' unordered'; 781 +} 782 +db close 783 +sqlite3 db test.db 784 +do_execsql_test 15.12 { SELECT * FROM x1 } {1 2 3 4 5 6} 785 + 786 +#------------------------------------------------------------------------- 787 +# Test that allocations used for sqlite_stat4 samples are included in 788 +# the quantity returned by SQLITE_DBSTATUS_SCHEMA_USED. 789 +# 790 +set one [string repeat x 1000] 791 +set two [string repeat x 2000] 792 +do_test 16.1 { 793 + reset_db 794 + execsql { 795 + CREATE TABLE t1(a, UNIQUE(a)); 796 + INSERT INTO t1 VALUES($one); 797 + ANALYZE; 798 + } 799 + set nByte [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] 800 + 801 + reset_db 802 + execsql { 803 + CREATE TABLE t1(a, UNIQUE(a)); 804 + INSERT INTO t1 VALUES($two); 805 + ANALYZE; 806 + } 807 + set nByte2 [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] 808 + 809 + expr {$nByte2 > $nByte+950 && $nByte2 < $nByte+1050} 810 +} {1} 811 + 812 +#------------------------------------------------------------------------- 813 +# Test that stat4 data may be used with partial indexes. 814 +# 815 +do_test 17.1 { 816 + reset_db 817 + execsql { 818 + CREATE TABLE t1(a, b, c, d); 819 + CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL; 820 + INSERT INTO t1 VALUES(-1, -1, -1, NULL); 821 + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 822 + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 823 + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 824 + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 825 + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 826 + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 827 + } 828 + 829 + for {set i 0} {$i < 32} {incr i} { 830 + if {$i<8} {set b 0} else { set b $i } 831 + execsql { INSERT INTO t1 VALUES($i%2, $b, $i/2, 'abc') } 832 + } 833 + execsql {ANALYZE main.t1} 834 +} {} 835 + 836 +do_catchsql_test 17.1.2 { 837 + ANALYZE temp.t1; 838 +} {1 {no such table: temp.t1}} 839 + 840 +do_eqp_test 17.2 { 841 + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10; 842 +} {/USING INDEX i1/} 843 +do_eqp_test 17.3 { 844 + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; 845 +} {/USING INDEX i1/} 846 + 847 +do_execsql_test 17.4 { 848 + CREATE INDEX i2 ON t1(c); 849 + ANALYZE main.i2; 850 +} 851 +do_eqp_test 17.5 { 852 + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10; 853 +} {/USING INDEX i1/} 854 +do_eqp_test 17.6 { 855 + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; 856 +} {/USING INDEX i2/} 857 + 858 +#------------------------------------------------------------------------- 859 +# 860 +do_test 18.1 { 861 + reset_db 862 + execsql { 863 + CREATE TABLE t1(a, b); 864 + CREATE INDEX i1 ON t1(a, b); 865 + } 866 + for {set i 0} {$i < 9} {incr i} { 867 + execsql { 868 + INSERT INTO t1 VALUES($i, 0); 869 + INSERT INTO t1 VALUES($i, 0); 870 + INSERT INTO t1 VALUES($i, 0); 871 + INSERT INTO t1 VALUES($i, 0); 872 + INSERT INTO t1 VALUES($i, 0); 873 + INSERT INTO t1 VALUES($i, 0); 874 + INSERT INTO t1 VALUES($i, 0); 875 + INSERT INTO t1 VALUES($i, 0); 876 + INSERT INTO t1 VALUES($i, 0); 877 + INSERT INTO t1 VALUES($i, 0); 878 + INSERT INTO t1 VALUES($i, 0); 879 + INSERT INTO t1 VALUES($i, 0); 880 + INSERT INTO t1 VALUES($i, 0); 881 + INSERT INTO t1 VALUES($i, 0); 882 + INSERT INTO t1 VALUES($i, 0); 883 + } 884 + } 885 + execsql ANALYZE 886 + execsql { SELECT count(*) FROM sqlite_stat4 } 887 +} {9} 888 + 889 +#------------------------------------------------------------------------- 890 +# For coverage. 891 +# 892 +ifcapable view { 893 + do_test 19.1 { 894 + reset_db 895 + execsql { 896 + CREATE TABLE t1(x, y); 897 + CREATE INDEX i1 ON t1(x, y); 898 + CREATE VIEW v1 AS SELECT * FROM t1; 899 + ANALYZE; 900 + } 901 + } {} 902 +} 903 +ifcapable auth { 904 + proc authproc {op args} { 905 + if {$op == "SQLITE_ANALYZE"} { return "SQLITE_DENY" } 906 + return "SQLITE_OK" 907 + } 908 + do_test 19.2 { 909 + reset_db 910 + db auth authproc 911 + execsql { 912 + CREATE TABLE t1(x, y); 913 + CREATE VIEW v1 AS SELECT * FROM t1; 914 + } 915 + catchsql ANALYZE 916 + } {1 {not authorized}} 917 +} 918 + 919 +#------------------------------------------------------------------------- 920 +# 921 +reset_db 922 +proc r {args} { expr rand() } 923 +db func r r 924 +db func lrange lrange 925 +do_test 20.1 { 926 + execsql { 927 + CREATE TABLE t1(a,b,c,d); 928 + CREATE INDEX i1 ON t1(a,b,c,d); 929 + } 930 + for {set i 0} {$i < 16} {incr i} { 931 + execsql { 932 + INSERT INTO t1 VALUES($i, r(), r(), r()); 933 + INSERT INTO t1 VALUES($i, $i, r(), r()); 934 + INSERT INTO t1 VALUES($i, $i, $i, r()); 935 + INSERT INTO t1 VALUES($i, $i, $i, $i); 936 + INSERT INTO t1 VALUES($i, $i, $i, $i); 937 + INSERT INTO t1 VALUES($i, $i, $i, r()); 938 + INSERT INTO t1 VALUES($i, $i, r(), r()); 939 + INSERT INTO t1 VALUES($i, r(), r(), r()); 940 + } 941 + } 942 +} {} 943 +do_execsql_test 20.2 { ANALYZE } 944 +for {set i 0} {$i<16} {incr i} { 945 + set val "$i $i $i $i" 946 + do_execsql_test 20.3.$i { 947 + SELECT count(*) FROM sqlite_stat4 948 + WHERE lrange(test_decode(sample), 0, 3)=$val 949 + } {1} 950 +} 560 951 952 +finish_test 561 953
Added test/analyzeB.test.
1 +# 2013 August 3 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 +# 12 +# This file contains automated tests used to verify that the sqlite_stat3 13 +# functionality is working. The tests in this file are based on a subset 14 +# of the sqlite_stat4 tests in analyze9.test. 15 +# 16 + 17 +set testdir [file dirname $argv0] 18 +source $testdir/tester.tcl 19 +set testprefix analyzeB 20 + 21 +ifcapable !stat3 { 22 + finish_test 23 + return 24 +} 25 + 26 +do_execsql_test 1.0 { 27 + CREATE TABLE t1(a TEXT, b TEXT); 28 + INSERT INTO t1 VALUES('(0)', '(0)'); 29 + INSERT INTO t1 VALUES('(1)', '(1)'); 30 + INSERT INTO t1 VALUES('(2)', '(2)'); 31 + INSERT INTO t1 VALUES('(3)', '(3)'); 32 + INSERT INTO t1 VALUES('(4)', '(4)'); 33 + CREATE INDEX i1 ON t1(a, b); 34 +} {} 35 + 36 + 37 +do_execsql_test 1.1 { 38 + ANALYZE; 39 +} {} 40 + 41 +do_execsql_test 1.2 { 42 + SELECT tbl,idx,nEq,nLt,nDLt,quote(sample) FROM sqlite_stat3; 43 +} { 44 + t1 i1 1 0 0 '(0)' 45 + t1 i1 1 1 1 '(1)' 46 + t1 i1 1 2 2 '(2)' 47 + t1 i1 1 3 3 '(3)' 48 + t1 i1 1 4 4 '(4)' 49 +} 50 + 51 +if {[permutation] != "utf16"} { 52 + do_execsql_test 1.3 { 53 + SELECT tbl,idx,nEq,nLt,nDLt,quote(sample) FROM sqlite_stat3; 54 + } { 55 + t1 i1 1 0 0 '(0)' 56 + t1 i1 1 1 1 '(1)' 57 + t1 i1 1 2 2 '(2)' 58 + t1 i1 1 3 3 '(3)' 59 + t1 i1 1 4 4 '(4)' 60 + } 61 +} 62 + 63 + 64 +#------------------------------------------------------------------------- 65 +# This is really just to test SQL user function "test_decode". 66 +# 67 +reset_db 68 +do_execsql_test 2.1 { 69 + CREATE TABLE t1(a, b, c); 70 + INSERT INTO t1(a) VALUES('some text'); 71 + INSERT INTO t1(a) VALUES(14); 72 + INSERT INTO t1(a) VALUES(NULL); 73 + INSERT INTO t1(a) VALUES(22.0); 74 + INSERT INTO t1(a) VALUES(x'656667'); 75 + CREATE INDEX i1 ON t1(a, b, c); 76 + ANALYZE; 77 + SELECT quote(sample) FROM sqlite_stat3; 78 +} { 79 + NULL 14 22.0 {'some text'} X'656667' 80 +} 81 + 82 +#------------------------------------------------------------------------- 83 +# 84 +reset_db 85 +do_execsql_test 3.1 { 86 + CREATE TABLE t2(a, b); 87 + CREATE INDEX i2 ON t2(a, b); 88 + BEGIN; 89 +} 90 + 91 +do_test 3.2 { 92 + for {set i 0} {$i < 1000} {incr i} { 93 + set a [expr $i / 10] 94 + set b [expr int(rand() * 15.0)] 95 + execsql { INSERT INTO t2 VALUES($a, $b) } 96 + } 97 + execsql COMMIT 98 +} {} 99 + 100 +db func lindex lindex 101 + 102 +# Each value of "a" occurs exactly 10 times in the table. 103 +# 104 +do_execsql_test 3.3.1 { 105 + SELECT count(*) FROM t2 GROUP BY a; 106 +} [lrange [string repeat "10 " 100] 0 99] 107 + 108 +# The first element in the "nEq" list of all samples should therefore be 10. 109 +# 110 +do_execsql_test 3.3.2 { 111 + ANALYZE; 112 + SELECT nEq FROM sqlite_stat3; 113 +} [lrange [string repeat "10 " 100] 0 23] 114 + 115 +#------------------------------------------------------------------------- 116 +# 117 +do_execsql_test 3.4 { 118 + DROP TABLE IF EXISTS t1; 119 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 120 + INSERT INTO t1 VALUES(1, 1, 'one-a'); 121 + INSERT INTO t1 VALUES(11, 1, 'one-b'); 122 + INSERT INTO t1 VALUES(21, 1, 'one-c'); 123 + INSERT INTO t1 VALUES(31, 1, 'one-d'); 124 + INSERT INTO t1 VALUES(41, 1, 'one-e'); 125 + INSERT INTO t1 VALUES(51, 1, 'one-f'); 126 + INSERT INTO t1 VALUES(61, 1, 'one-g'); 127 + INSERT INTO t1 VALUES(71, 1, 'one-h'); 128 + INSERT INTO t1 VALUES(81, 1, 'one-i'); 129 + INSERT INTO t1 VALUES(91, 1, 'one-j'); 130 + INSERT INTO t1 SELECT a+1,2,'two' || substr(c,4) FROM t1; 131 + INSERT INTO t1 SELECT a+2,3,'three'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; 132 + INSERT INTO t1 SELECT a+3,4,'four'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; 133 + INSERT INTO t1 SELECT a+4,5,'five'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; 134 + INSERT INTO t1 SELECT a+5,6,'six'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; 135 + CREATE INDEX t1b ON t1(b); 136 + ANALYZE; 137 + SELECT c FROM t1 WHERE b=3 AND a BETWEEN 30 AND 60; 138 +} {three-d three-e three-f} 139 + 140 + 141 +#------------------------------------------------------------------------- 142 +# These tests verify that the sample selection for stat3 appears to be 143 +# working as designed. 144 +# 145 + 146 +reset_db 147 +db func lindex lindex 148 +db func lrange lrange 149 + 150 +do_execsql_test 4.0 { 151 + DROP TABLE IF EXISTS t1; 152 + CREATE TABLE t1(a, b, c); 153 + CREATE INDEX i1 ON t1(c, b, a); 154 +} 155 + 156 + 157 +proc insert_filler_rows_n {iStart args} { 158 + set A(-ncopy) 1 159 + set A(-nval) 1 160 + 161 + foreach {k v} $args { 162 + if {[info exists A($k)]==0} { error "no such option: $k" } 163 + set A($k) $v 164 + } 165 + if {[llength $args] % 2} { 166 + error "option requires an argument: [lindex $args end]" 167 + } 168 + 169 + for {set i 0} {$i < $A(-nval)} {incr i} { 170 + set iVal [expr $iStart+$i] 171 + for {set j 0} {$j < $A(-ncopy)} {incr j} { 172 + execsql { INSERT INTO t1 VALUES($iVal, $iVal, $iVal) } 173 + } 174 + } 175 +} 176 + 177 +do_test 4.1 { 178 + execsql { BEGIN } 179 + insert_filler_rows_n 0 -ncopy 10 -nval 19 180 + insert_filler_rows_n 20 -ncopy 1 -nval 100 181 + 182 + execsql { 183 + INSERT INTO t1(c, b, a) VALUES(200, 1, 'a'); 184 + INSERT INTO t1(c, b, a) VALUES(200, 1, 'b'); 185 + INSERT INTO t1(c, b, a) VALUES(200, 1, 'c'); 186 + 187 + INSERT INTO t1(c, b, a) VALUES(200, 2, 'e'); 188 + INSERT INTO t1(c, b, a) VALUES(200, 2, 'f'); 189 + 190 + INSERT INTO t1(c, b, a) VALUES(201, 3, 'g'); 191 + INSERT INTO t1(c, b, a) VALUES(201, 4, 'h'); 192 + 193 + ANALYZE; 194 + SELECT count(*) FROM sqlite_stat3; 195 + SELECT count(*) FROM t1; 196 + } 197 +} {24 297} 198 + 199 +do_execsql_test 4.2 { 200 + SELECT neq, nlt, ndlt, sample FROM sqlite_stat3 ORDER BY rowid LIMIT 16; 201 +} { 202 + 10 0 0 0 203 + 10 10 1 1 204 + 10 20 2 2 205 + 10 30 3 3 206 + 10 40 4 4 207 + 10 50 5 5 208 + 10 60 6 6 209 + 10 70 7 7 210 + 10 80 8 8 211 + 10 90 9 9 212 + 10 100 10 10 213 + 10 110 11 11 214 + 10 120 12 12 215 + 10 130 13 13 216 + 10 140 14 14 217 + 10 150 15 15 218 +} 219 + 220 +do_execsql_test 4.3 { 221 + SELECT neq, nlt, ndlt, sample FROM sqlite_stat3 222 + ORDER BY rowid DESC LIMIT 2; 223 +} { 224 + 2 295 120 201 225 + 5 290 119 200 226 +} 227 + 228 +do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120 229 +do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119 230 + 231 +reset_db 232 +do_test 4.7 { 233 + execsql { 234 + BEGIN; 235 + CREATE TABLE t1(o,t INTEGER PRIMARY KEY); 236 + CREATE INDEX i1 ON t1(o); 237 + } 238 + for {set i 0} {$i<10000} {incr i [expr (($i<1000)?1:10)]} { 239 + execsql { INSERT INTO t1 VALUES('x', $i) } 240 + } 241 + execsql { 242 + COMMIT; 243 + ANALYZE; 244 + SELECT count(*) FROM sqlite_stat3; 245 + } 246 +} {1} 247 +do_execsql_test 4.8 { 248 + SELECT sample FROM sqlite_stat3; 249 +} {x} 250 + 251 + 252 +#------------------------------------------------------------------------- 253 +# The following would cause a crash at one point. 254 +# 255 +reset_db 256 +do_execsql_test 5.1 { 257 + PRAGMA encoding = 'utf-16'; 258 + CREATE TABLE t0(v); 259 + ANALYZE; 260 +} 261 + 262 +#------------------------------------------------------------------------- 263 +# This was also crashing (corrupt sqlite_stat3 table). 264 +# 265 +reset_db 266 +do_execsql_test 6.1 { 267 + CREATE TABLE t1(a, b); 268 + CREATE INDEX i1 ON t1(a); 269 + CREATE INDEX i2 ON t1(b); 270 + INSERT INTO t1 VALUES(1, 1); 271 + INSERT INTO t1 VALUES(2, 2); 272 + INSERT INTO t1 VALUES(3, 3); 273 + INSERT INTO t1 VALUES(4, 4); 274 + INSERT INTO t1 VALUES(5, 5); 275 + ANALYZE; 276 + PRAGMA writable_schema = 1; 277 + CREATE TEMP TABLE x1 AS 278 + SELECT tbl,idx,neq,nlt,ndlt,sample FROM sqlite_stat3 279 + ORDER BY (rowid%5), rowid; 280 + DELETE FROM sqlite_stat3; 281 + INSERT INTO sqlite_stat3 SELECT * FROM x1; 282 + PRAGMA writable_schema = 0; 283 + ANALYZE sqlite_master; 284 +} 285 +do_execsql_test 6.2 { 286 + SELECT * FROM t1 WHERE a = 'abc'; 287 +} 288 + 289 +#------------------------------------------------------------------------- 290 +# The following tests experiment with adding corrupted records to the 291 +# 'sample' column of the sqlite_stat3 table. 292 +# 293 +reset_db 294 +sqlite3_db_config_lookaside db 0 0 0 295 + 296 +do_execsql_test 7.1 { 297 + CREATE TABLE t1(a, b); 298 + CREATE INDEX i1 ON t1(a, b); 299 + INSERT INTO t1 VALUES(1, 1); 300 + INSERT INTO t1 VALUES(2, 2); 301 + INSERT INTO t1 VALUES(3, 3); 302 + INSERT INTO t1 VALUES(4, 4); 303 + INSERT INTO t1 VALUES(5, 5); 304 + ANALYZE; 305 + UPDATE sqlite_stat3 SET sample = X'' WHERE rowid = 1; 306 + ANALYZE sqlite_master; 307 +} 308 + 309 +do_execsql_test 7.2 { 310 + UPDATE sqlite_stat3 SET sample = X'FFFF'; 311 + ANALYZE sqlite_master; 312 + SELECT * FROM t1 WHERE a = 1; 313 +} {1 1} 314 + 315 +do_execsql_test 7.3 { 316 + ANALYZE; 317 + UPDATE sqlite_stat3 SET neq = '0 0 0'; 318 + ANALYZE sqlite_master; 319 + SELECT * FROM t1 WHERE a = 1; 320 +} {1 1} 321 + 322 +do_execsql_test 7.4 { 323 + ANALYZE; 324 + UPDATE sqlite_stat3 SET ndlt = '0 0 0'; 325 + ANALYZE sqlite_master; 326 + SELECT * FROM t1 WHERE a = 3; 327 +} {3 3} 328 + 329 +do_execsql_test 7.5 { 330 + ANALYZE; 331 + UPDATE sqlite_stat3 SET nlt = '0 0 0'; 332 + ANALYZE sqlite_master; 333 + SELECT * FROM t1 WHERE a = 5; 334 +} {5 5} 335 + 336 +#------------------------------------------------------------------------- 337 +# 338 +reset_db 339 +do_execsql_test 8.1 { 340 + CREATE TABLE t1(x TEXT); 341 + CREATE INDEX i1 ON t1(x); 342 + INSERT INTO t1 VALUES('1'); 343 + INSERT INTO t1 VALUES('2'); 344 + INSERT INTO t1 VALUES('3'); 345 + INSERT INTO t1 VALUES('4'); 346 + ANALYZE; 347 +} 348 +do_execsql_test 8.2 { 349 + SELECT * FROM t1 WHERE x = 3; 350 +} {3} 351 + 352 +#------------------------------------------------------------------------- 353 +# 354 +reset_db 355 +do_execsql_test 9.1 { 356 + CREATE TABLE t1(a, b, c, d, e); 357 + CREATE INDEX i1 ON t1(a, b, c, d); 358 + CREATE INDEX i2 ON t1(e); 359 +} 360 +do_test 9.2 { 361 + execsql BEGIN; 362 + for {set i 0} {$i < 100} {incr i} { 363 + execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])" 364 + } 365 + for {set i 0} {$i < 20} {incr i} { 366 + execsql "INSERT INTO t1 VALUES('x', 'y', 'z', 101, $i)" 367 + } 368 + for {set i 102} {$i < 200} {incr i} { 369 + execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])" 370 + } 371 + execsql COMMIT 372 + execsql ANALYZE 373 +} {} 374 + 375 +do_eqp_test 9.3.1 { 376 + SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=101 AND e=5; 377 +} {/t1 USING INDEX i1/} 378 +do_eqp_test 9.3.2 { 379 + SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=99 AND e=5; 380 +} {/t1 USING INDEX i1/} 381 + 382 +set value_d [expr 101] 383 +do_eqp_test 9.4.1 { 384 + SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5 385 +} {/t1 USING INDEX i1/} 386 +set value_d [expr 99] 387 +do_eqp_test 9.4.2 { 388 + SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5 389 +} {/t1 USING INDEX i1/} 390 + 391 +#------------------------------------------------------------------------- 392 +# Check that the planner takes stat3 data into account when considering 393 +# "IS NULL" and "IS NOT NULL" constraints. 394 +# 395 +do_execsql_test 10.1.1 { 396 + DROP TABLE IF EXISTS t3; 397 + CREATE TABLE t3(a, b); 398 + CREATE INDEX t3a ON t3(a); 399 + CREATE INDEX t3b ON t3(b); 400 +} 401 +do_test 10.1.2 { 402 + for {set i 1} {$i < 100} {incr i} { 403 + if {$i>90} { set a $i } else { set a NULL } 404 + set b [expr $i % 5] 405 + execsql "INSERT INTO t3 VALUES($a, $b)" 406 + } 407 + execsql ANALYZE 408 +} {} 409 +do_eqp_test 10.1.3 { 410 + SELECT * FROM t3 WHERE a IS NULL AND b = 2 411 +} {/t3 USING INDEX t3b/} 412 +do_eqp_test 10.1.4 { 413 + SELECT * FROM t3 WHERE a IS NOT NULL AND b = 2 414 +} {/t3 USING INDEX t3a/} 415 + 416 +#------------------------------------------------------------------------- 417 +# Check that stat3 data is used correctly with non-default collation 418 +# sequences. 419 +# 420 +foreach {tn schema} { 421 + 1 { 422 + CREATE TABLE t4(a COLLATE nocase, b); 423 + CREATE INDEX t4a ON t4(a); 424 + CREATE INDEX t4b ON t4(b); 425 + } 426 + 2 { 427 + CREATE TABLE t4(a, b); 428 + CREATE INDEX t4a ON t4(a COLLATE nocase); 429 + CREATE INDEX t4b ON t4(b); 430 + } 431 +} { 432 + drop_all_tables 433 + do_test 11.$tn.1 { execsql $schema } {} 434 + 435 + do_test 11.$tn.2 { 436 + for {set i 0} {$i < 100} {incr i} { 437 + if { ($i % 10)==0 } { set a ABC } else { set a DEF } 438 + set b [expr $i % 5] 439 + execsql { INSERT INTO t4 VALUES($a, $b) } 440 + } 441 + execsql ANALYZE 442 + } {} 443 + 444 + do_eqp_test 11.$tn.3 { 445 + SELECT * FROM t4 WHERE a = 'def' AND b = 3; 446 + } {/t4 USING INDEX t4b/} 447 + 448 + if {$tn==1} { 449 + set sql "SELECT * FROM t4 WHERE a = 'abc' AND b = 3;" 450 + do_eqp_test 11.$tn.4 $sql {/t4 USING INDEX t4a/} 451 + } else { 452 + 453 + set sql "SELECT * FROM t4 WHERE a = 'abc' COLLATE nocase AND b = 3;" 454 + do_eqp_test 11.$tn.5 $sql {/t4 USING INDEX t4a/} 455 + 456 + set sql "SELECT * FROM t4 WHERE a COLLATE nocase = 'abc' AND b = 3;" 457 + do_eqp_test 11.$tn.6 $sql {/t4 USING INDEX t4a/} 458 + } 459 +} 460 + 461 +#------------------------------------------------------------------------- 462 +# Test that nothing untoward happens if the stat3 table contains entries 463 +# for indexes that do not exist. Or NULL values in the idx column. 464 +# Or NULL values in any of the other columns. 465 +# 466 +drop_all_tables 467 +do_execsql_test 15.1 { 468 + CREATE TABLE x1(a, b, UNIQUE(a, b)); 469 + INSERT INTO x1 VALUES(1, 2); 470 + INSERT INTO x1 VALUES(3, 4); 471 + INSERT INTO x1 VALUES(5, 6); 472 + ANALYZE; 473 + INSERT INTO sqlite_stat3 VALUES(NULL, NULL, NULL, NULL, NULL, NULL); 474 +} 475 +db close 476 +sqlite3 db test.db 477 +do_execsql_test 15.2 { SELECT * FROM x1 } {1 2 3 4 5 6} 478 + 479 +do_execsql_test 15.3 { 480 + INSERT INTO sqlite_stat3 VALUES(42, 42, 42, 42, 42, 42); 481 +} 482 +db close 483 +sqlite3 db test.db 484 +do_execsql_test 15.4 { SELECT * FROM x1 } {1 2 3 4 5 6} 485 + 486 +do_execsql_test 15.5 { 487 + UPDATE sqlite_stat1 SET stat = NULL; 488 +} 489 +db close 490 +sqlite3 db test.db 491 +do_execsql_test 15.6 { SELECT * FROM x1 } {1 2 3 4 5 6} 492 + 493 +do_execsql_test 15.7 { 494 + ANALYZE; 495 + UPDATE sqlite_stat1 SET tbl = 'no such tbl'; 496 +} 497 +db close 498 +sqlite3 db test.db 499 +do_execsql_test 15.8 { SELECT * FROM x1 } {1 2 3 4 5 6} 500 + 501 +do_execsql_test 15.9 { 502 + ANALYZE; 503 + UPDATE sqlite_stat3 SET neq = NULL, nlt=NULL, ndlt=NULL; 504 +} 505 +db close 506 +sqlite3 db test.db 507 +do_execsql_test 15.10 { SELECT * FROM x1 } {1 2 3 4 5 6} 508 + 509 +# This is just for coverage.... 510 +do_execsql_test 15.11 { 511 + ANALYZE; 512 + UPDATE sqlite_stat1 SET stat = stat || ' unordered'; 513 +} 514 +db close 515 +sqlite3 db test.db 516 +do_execsql_test 15.12 { SELECT * FROM x1 } {1 2 3 4 5 6} 517 + 518 +#------------------------------------------------------------------------- 519 +# Test that allocations used for sqlite_stat3 samples are included in 520 +# the quantity returned by SQLITE_DBSTATUS_SCHEMA_USED. 521 +# 522 +set one [string repeat x 1000] 523 +set two [string repeat x 2000] 524 +do_test 16.1 { 525 + reset_db 526 + execsql { 527 + CREATE TABLE t1(a, UNIQUE(a)); 528 + INSERT INTO t1 VALUES($one); 529 + ANALYZE; 530 + } 531 + set nByte [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] 532 + 533 + reset_db 534 + execsql { 535 + CREATE TABLE t1(a, UNIQUE(a)); 536 + INSERT INTO t1 VALUES($two); 537 + ANALYZE; 538 + } 539 + set nByte2 [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] 540 + 541 + expr {$nByte2 > $nByte+950 && $nByte2 < $nByte+1050} 542 +} {1} 543 + 544 +#------------------------------------------------------------------------- 545 +# Test that stat3 data may be used with partial indexes. 546 +# 547 +do_test 17.1 { 548 + reset_db 549 + execsql { 550 + CREATE TABLE t1(a, b, c, d); 551 + CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL; 552 + INSERT INTO t1 VALUES(-1, -1, -1, NULL); 553 + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 554 + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 555 + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 556 + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 557 + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 558 + INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 559 + } 560 + 561 + for {set i 0} {$i < 32} {incr i} { 562 + execsql { INSERT INTO t1 VALUES($i%2, $b, $i/2, 'abc') } 563 + } 564 + execsql {ANALYZE main.t1} 565 +} {} 566 + 567 +do_catchsql_test 17.1.2 { 568 + ANALYZE temp.t1; 569 +} {1 {no such table: temp.t1}} 570 + 571 +do_eqp_test 17.2 { 572 + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0; 573 +} {/USING INDEX i1/} 574 +do_eqp_test 17.3 { 575 + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0; 576 +} {/USING INDEX i1/} 577 + 578 +do_execsql_test 17.4 { 579 + CREATE INDEX i2 ON t1(c) WHERE d IS NOT NULL; 580 + ANALYZE main.i2; 581 +} 582 +do_eqp_test 17.5 { 583 + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0; 584 +} {/USING INDEX i1/} 585 +do_eqp_test 17.6 { 586 + SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; 587 +} {/USING INDEX i2/} 588 + 589 +#------------------------------------------------------------------------- 590 +# 591 +do_test 18.1 { 592 + reset_db 593 + execsql { 594 + CREATE TABLE t1(a, b); 595 + CREATE INDEX i1 ON t1(a, b); 596 + } 597 + for {set i 0} {$i < 9} {incr i} { 598 + execsql { 599 + INSERT INTO t1 VALUES($i, 0); 600 + INSERT INTO t1 VALUES($i, 0); 601 + INSERT INTO t1 VALUES($i, 0); 602 + INSERT INTO t1 VALUES($i, 0); 603 + INSERT INTO t1 VALUES($i, 0); 604 + INSERT INTO t1 VALUES($i, 0); 605 + INSERT INTO t1 VALUES($i, 0); 606 + INSERT INTO t1 VALUES($i, 0); 607 + INSERT INTO t1 VALUES($i, 0); 608 + INSERT INTO t1 VALUES($i, 0); 609 + INSERT INTO t1 VALUES($i, 0); 610 + INSERT INTO t1 VALUES($i, 0); 611 + INSERT INTO t1 VALUES($i, 0); 612 + INSERT INTO t1 VALUES($i, 0); 613 + INSERT INTO t1 VALUES($i, 0); 614 + } 615 + } 616 + execsql ANALYZE 617 + execsql { SELECT count(*) FROM sqlite_stat3 } 618 +} {9} 619 + 620 +#------------------------------------------------------------------------- 621 +# For coverage. 622 +# 623 +ifcapable view { 624 + do_test 19.1 { 625 + reset_db 626 + execsql { 627 + CREATE TABLE t1(x, y); 628 + CREATE INDEX i1 ON t1(x, y); 629 + CREATE VIEW v1 AS SELECT * FROM t1; 630 + ANALYZE; 631 + } 632 + } {} 633 +} 634 +ifcapable auth { 635 + proc authproc {op args} { 636 + if {$op == "SQLITE_ANALYZE"} { return "SQLITE_DENY" } 637 + return "SQLITE_OK" 638 + } 639 + do_test 19.2 { 640 + reset_db 641 + db auth authproc 642 + execsql { 643 + CREATE TABLE t1(x, y); 644 + CREATE VIEW v1 AS SELECT * FROM t1; 645 + } 646 + catchsql ANALYZE 647 + } {1 {not authorized}} 648 +} 649 + 650 +#------------------------------------------------------------------------- 651 +# 652 +reset_db 653 +proc r {args} { expr rand() } 654 +db func r r 655 +db func lrange lrange 656 +do_test 20.1 { 657 + execsql { 658 + CREATE TABLE t1(a,b,c,d); 659 + CREATE INDEX i1 ON t1(a,b,c,d); 660 + } 661 + for {set i 0} {$i < 16} {incr i} { 662 + execsql { 663 + INSERT INTO t1 VALUES($i, r(), r(), r()); 664 + INSERT INTO t1 VALUES($i, $i, r(), r()); 665 + INSERT INTO t1 VALUES($i, $i, $i, r()); 666 + INSERT INTO t1 VALUES($i, $i, $i, $i); 667 + INSERT INTO t1 VALUES($i, $i, $i, $i); 668 + INSERT INTO t1 VALUES($i, $i, $i, r()); 669 + INSERT INTO t1 VALUES($i, $i, r(), r()); 670 + INSERT INTO t1 VALUES($i, r(), r(), r()); 671 + } 672 + } 673 +} {} 674 +do_execsql_test 20.2 { ANALYZE } 675 +for {set i 0} {$i<16} {incr i} { 676 + set val $i 677 + do_execsql_test 20.3.$i { 678 + SELECT count(*) FROM sqlite_stat3 WHERE sample=$val 679 + } {1} 680 +} 681 + 682 +finish_test 683 +
Added test/fkey7.test.
1 +# 2001 September 15 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. 12 +# 13 +# This file implements tests for foreign keys. 14 +# 15 + 16 +set testdir [file dirname $argv0] 17 +source $testdir/tester.tcl 18 +set testprefix fkey7 19 + 20 +ifcapable {!foreignkey} { 21 + finish_test 22 + return 23 +} 24 + 25 +do_execsql_test 1.1 { 26 + PRAGMA foreign_keys = 1; 27 + 28 + CREATE TABLE s1(a PRIMARY KEY, b); 29 + CREATE TABLE par(a, b REFERENCES s1, c UNIQUE, PRIMARY KEY(a)); 30 + 31 + CREATE TABLE c1(a, b REFERENCES par); 32 + CREATE TABLE c2(a, b REFERENCES par); 33 + CREATE TABLE c3(a, b REFERENCES par(c)); 34 +} 35 + 36 +proc auth {op tbl args} { 37 + if {$op == "SQLITE_READ"} { set ::tbls($tbl) 1 } 38 + return "SQLITE_OK" 39 +} 40 +db auth auth 41 +db cache size 0 42 +proc do_tblsread_test {tn sql tbllist} { 43 + array unset ::tbls 44 + uplevel [list execsql $sql] 45 + uplevel [list do_test $tn {lsort [array names ::tbls]} $tbllist] 46 +} 47 + 48 +do_tblsread_test 1.2 { UPDATE par SET b=? WHERE a=? } {par s1} 49 +do_tblsread_test 1.3 { UPDATE par SET a=? WHERE b=? } {c1 c2 par} 50 +do_tblsread_test 1.4 { UPDATE par SET c=? WHERE b=? } {c3 par} 51 +do_tblsread_test 1.5 { UPDATE par SET a=?,b=?,c=? WHERE b=? } {c1 c2 c3 par s1} 52 + 53 + 54 +finish_test
Changes to test/mallocA.test.
92 92 } -test { 93 93 faultsim_test_result [list 0 2] 94 94 } 95 95 do_faultsim_test 6.2 -faults oom* -body { 96 96 execsql { SELECT rowid FROM t1 WHERE a='abc' AND b<'y' } 97 97 } -test { 98 98 faultsim_test_result [list 0 {1 2}] 99 +} 100 +ifcapable stat3 { 101 + do_test 6.3-prep { 102 + execsql { 103 + PRAGMA writable_schema = 1; 104 + CREATE TABLE sqlite_stat4 AS 105 + SELECT tbl, idx, neq, nlt, ndlt, sqlite_record(sample) AS sample 106 + FROM sqlite_stat3; 107 + } 108 + } {} 109 + do_faultsim_test 6.3 -faults oom* -body { 110 + execsql { 111 + ANALYZE sqlite_master; 112 + SELECT rowid FROM t1 WHERE a='abc' AND b<'y'; 113 + } 114 + } -test { 115 + faultsim_test_result [list 0 {1 2}] 116 + } 99 117 } 100 118 101 119 # Ensure that no file descriptors were leaked. 102 120 do_test malloc-99.X { 103 121 catch {db close} 104 122 set sqlite_open_file_count 105 123 } {0} 106 124 107 125 forcedelete test.db.bu 108 126 finish_test
Changes to test/permutations.test.
307 307 test_suite "coverage-pager" -description { 308 308 Coverage tests for file pager.c. 309 309 } -files { 310 310 pager1.test pager2.test pagerfault.test pagerfault2.test 311 311 walfault.test walbak.test journal2.test tkt-9d68c883.test 312 312 } 313 313 314 +test_suite "coverage-analyze" -description { 315 + Coverage tests for file analyze.c. 316 +} -files { 317 + analyze3.test analyze4.test analyze5.test analyze6.test 318 + analyze7.test analyze8.test analyze9.test analyzeA.test 319 + analyze.test analyzeB.test mallocA.test 320 +} 321 + 314 322 315 323 lappend ::testsuitelist xxx 316 324 #------------------------------------------------------------------------- 317 325 # Define the permutation test suites: 318 326 # 319 327 320 328 # Run some tests using pre-allocated page and scratch blocks. ................................................................................ 497 505 test_suite "utf16" -description { 498 506 Run tests using UTF-16 databases 499 507 } -presql { 500 508 pragma encoding = 'UTF-16' 501 509 } -files { 502 510 alter.test alter3.test 503 511 analyze.test analyze3.test analyze4.test analyze5.test analyze6.test 504 - analyze7.test analyze8.test analyze9.test analyzeA.test 512 + analyze7.test analyze8.test analyze9.test analyzeA.test analyzeB.test 505 513 auth.test bind.test blob.test capi2.test capi3.test collate1.test 506 514 collate2.test collate3.test collate4.test collate5.test collate6.test 507 515 conflict.test date.test delete.test expr.test fkey1.test func.test 508 516 hook.test index.test insert2.test insert.test interrupt.test in.test 509 517 intpkey.test ioerr.test join2.test join.test lastinsert.test 510 518 laststmtchanges.test limit.test lock2.test lock.test main.test 511 519 memdb.test minmax.test misc1.test misc2.test misc3.test notnull.test
Changes to test/shell1.test.
718 718 catchcmd {test.db} {.print "this\nis\ta\\test" 'this\nis\ta\\test'} 719 719 } [list 0 "this\nis\ta\\test this\\nis\\ta\\\\test"] 720 720 721 721 722 722 # Test the output of the ".dump" command 723 723 # 724 724 do_test shell1-4.1 { 725 + db close 726 + forcedelete test.db 727 + sqlite3 db test.db 725 728 db eval { 729 + PRAGMA encoding=UTF16; 726 730 CREATE TABLE t1(x); 727 731 INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f'); 728 732 } 729 733 catchcmd test.db {.dump} 730 734 } {0 {PRAGMA foreign_keys=OFF; 731 735 BEGIN TRANSACTION; 732 736 CREATE TABLE t1(x); ................................................................................ 748 752 INSERT INTO t1 VALUES(2.25); 749 753 INSERT INTO t1 VALUES('hello'); 750 754 INSERT INTO t1 VALUES(X'807f');}} 751 755 752 756 # Test the output of ".mode tcl" 753 757 # 754 758 do_test shell1-4.3 { 759 + db close 760 + forcedelete test.db 761 + sqlite3 db test.db 762 + db eval { 763 + PRAGMA encoding=UTF8; 764 + CREATE TABLE t1(x); 765 + INSERT INTO t1 VALUES(null), (''), (1), (2.25), ('hello'), (x'807f'); 766 + } 755 767 catchcmd test.db ".mode tcl\nselect * from t1;" 756 768 } {0 {"" 757 769 "" 758 770 "1" 759 771 "2.25" 760 772 "hello" 761 773 "\200\177"}}
Changes to test/where.test.
1299 1299 do_test where-17.5 { 1300 1300 execsql { 1301 1301 CREATE TABLE tother(a, b); 1302 1302 INSERT INTO tother VALUES(1, 3.7); 1303 1303 SELECT id, a FROM tbooking, tother WHERE id>a; 1304 1304 } 1305 1305 } {42 1 43 1} 1306 + 1307 +# Ticket [be84e357c035d068135f20bcfe82761bbf95006b] 2013-09-03 1308 +# Segfault during query involving LEFT JOIN column in the ORDER BY clause. 1309 +# 1310 +do_execsql_test where-18.1 { 1311 + CREATE TABLE t181(a); 1312 + CREATE TABLE t182(b,c); 1313 + INSERT INTO t181 VALUES(1); 1314 + SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL; 1315 +} {1} 1316 +do_execsql_test where-18.2 { 1317 + SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c; 1318 +} {1} 1319 +do_execsql_test where-18.3 { 1320 + SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c; 1321 +} {1} 1322 +do_execsql_test where-18.4 { 1323 + INSERT INTO t181 VALUES(1),(1),(1),(1); 1324 + SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c; 1325 +} {1} 1326 +do_execsql_test where-18.5 { 1327 + INSERT INTO t181 VALUES(2); 1328 + SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a; 1329 +} {1 2} 1330 +do_execsql_test where-18.6 { 1331 + INSERT INTO t181 VALUES(2); 1332 + SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +a, +c IS NULL; 1333 +} {1 2} 1334 + 1306 1335 1307 1336 finish_test