Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch seekeq-experiment Excluding Merge-Ins
This is equivalent to a diff from 445480095e to 997ce6c90b
2015-11-06
| ||
20:22 | Avoid an unnecessary key comparison when doing an indexed lookup against an equality constraint. (check-in: d741e1ccdc user: drh tags: trunk) | |
2015-11-05
| ||
22:30 | Improvements and simplifications to the equality seek logic. Tests are adjusted so that they all pass now. (Closed-Leaf check-in: 997ce6c90b user: drh tags: seekeq-experiment) | |
21:49 | Fix typo in help information for an FTS5 script. (check-in: 777ae8007f user: mistachkin tags: trunk) | |
20:25 | The top of an index equality loop normally starts with OP_SeekGE and OP_IdxGT. This check-in adds a flag to OP_SeekGE such that it fails immediately if the key is not equal, then jumps over the OP_IdxGT, saving a call to the key comparison functions. Consider this check-in a proof-of-concept. It needs improvement before going on trunk. Some tests fail, but only because they new use fewer key comparisons than expected (which is a good thing!). (check-in: 32e31b9bc8 user: drh tags: seekeq-experiment) | |
18:09 | Add the 'hashsize' configuration option to fts5, for configuring the amount of memory allocated to the in-memory hash table while writing. (check-in: 445480095e user: dan tags: trunk) | |
11:47 | Remove a #pragma used to work around an issues with MSVC 2012 that has been overcome but subsequent changes. (check-in: 8303e4cfed user: drh tags: trunk) | |
Changes to main.mk.
︙ | ︙ | |||
578 579 580 581 582 583 584 | # Rules to build opcodes.c and opcodes.h # opcodes.c: opcodes.h $(TOP)/tool/mkopcodec.tcl tclsh $(TOP)/tool/mkopcodec.tcl opcodes.h >opcodes.c | | | 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 | # Rules to build opcodes.c and opcodes.h # opcodes.c: opcodes.h $(TOP)/tool/mkopcodec.tcl tclsh $(TOP)/tool/mkopcodec.tcl opcodes.h >opcodes.c opcodes.h: parse.h $(TOP)/tool/mkopcodeh.tcl cat parse.h $(TOP)/src/vdbe.c | \ tclsh $(TOP)/tool/mkopcodeh.tcl >opcodes.h # Rules to build parse.c and parse.h - the outputs of lemon. # parse.h: parse.c |
︙ | ︙ |
Changes to src/btree.c.
︙ | ︙ | |||
5037 5038 5039 5040 5041 5042 5043 5044 5045 5046 5047 5048 5049 5050 | ** ** *pRes==0 The cursor is left pointing at an entry that ** exactly matches intKey/pIdxKey. ** ** *pRes>0 The cursor is left pointing at an entry that ** is larger than intKey/pIdxKey. ** */ int sqlite3BtreeMovetoUnpacked( BtCursor *pCur, /* The cursor to be moved */ UnpackedRecord *pIdxKey, /* Unpacked index key */ i64 intKey, /* The table key */ int biasRight, /* If true, bias the search to the high end */ int *pRes /* Write search results here */ | > > | 5037 5038 5039 5040 5041 5042 5043 5044 5045 5046 5047 5048 5049 5050 5051 5052 | ** ** *pRes==0 The cursor is left pointing at an entry that ** exactly matches intKey/pIdxKey. ** ** *pRes>0 The cursor is left pointing at an entry that ** is larger than intKey/pIdxKey. ** ** For index tables, the pIdxKey->eqSeen field is set to 1 if there ** exists an entry in the table that exactly matches pIdxKey. */ int sqlite3BtreeMovetoUnpacked( BtCursor *pCur, /* The cursor to be moved */ UnpackedRecord *pIdxKey, /* Unpacked index key */ i64 intKey, /* The table key */ int biasRight, /* If true, bias the search to the high end */ int *pRes /* Write search results here */ |
︙ | ︙ | |||
9640 9641 9642 9643 9644 9645 9646 | } } pBt->btsFlags &= ~BTS_NO_WAL; return rc; } | < < | 9642 9643 9644 9645 9646 9647 9648 9649 9650 9651 9652 9653 9654 9655 9656 9657 9658 9659 9660 9661 9662 9663 9664 9665 9666 9667 | } } pBt->btsFlags &= ~BTS_NO_WAL; return rc; } /* ** Return true if the cursor has a hint specified. This routine is ** only used from within assert() statements */ int sqlite3BtreeCursorHasHint(BtCursor *pCsr, unsigned int mask){ return (pCsr->hints & mask)!=0; } /* ** Return true if the given Btree is read-only. */ int sqlite3BtreeIsReadonly(Btree *p){ return (p->pBt->btsFlags & BTS_READ_ONLY)!=0; } /* ** Return the size of the header added to each page by this module. */ int sqlite3HeaderSizeBtree(void){ return ROUND8(sizeof(MemPage)); } |
Changes to src/btree.h.
︙ | ︙ | |||
253 254 255 256 257 258 259 | char *sqlite3BtreeIntegrityCheck(Btree*, int *aRoot, int nRoot, int, int*); struct Pager *sqlite3BtreePager(Btree*); int sqlite3BtreePutData(BtCursor*, u32 offset, u32 amt, void*); void sqlite3BtreeIncrblobCursor(BtCursor *); void sqlite3BtreeClearCursor(BtCursor *); int sqlite3BtreeSetVersion(Btree *pBt, int iVersion); | < < | 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 | char *sqlite3BtreeIntegrityCheck(Btree*, int *aRoot, int nRoot, int, int*); struct Pager *sqlite3BtreePager(Btree*); int sqlite3BtreePutData(BtCursor*, u32 offset, u32 amt, void*); void sqlite3BtreeIncrblobCursor(BtCursor *); void sqlite3BtreeClearCursor(BtCursor *); int sqlite3BtreeSetVersion(Btree *pBt, int iVersion); int sqlite3BtreeCursorHasHint(BtCursor*, unsigned int mask); int sqlite3BtreeIsReadonly(Btree *pBt); int sqlite3HeaderSizeBtree(void); #ifndef NDEBUG int sqlite3BtreeCursorIsValid(BtCursor*); #endif |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1808 1809 1810 1811 1812 1813 1814 | u16 nXField; /* Number of columns beyond the key columns */ sqlite3 *db; /* The database connection */ u8 *aSortOrder; /* Sort order for each column. */ CollSeq *aColl[1]; /* Collating sequence for each term of the key */ }; /* | < | | | > > > | | > > > > > > > > > > > | > > > > > > < | | > | 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 | u16 nXField; /* Number of columns beyond the key columns */ sqlite3 *db; /* The database connection */ u8 *aSortOrder; /* Sort order for each column. */ CollSeq *aColl[1]; /* Collating sequence for each term of the key */ }; /* ** This object holds a record which has been parsed out into individual ** fields, for the purposes of doing a comparison. ** ** A record is an object that contains one or more fields of data. ** Records are used to store the content of a table row and to store ** the key of an index. A blob encoding of a record is created by ** the OP_MakeRecord opcode of the VDBE and is disassembled by the ** OP_Column opcode. ** ** An instance of this object serves as a "key" for doing a search on ** an index b+tree. The goal of the search is to find the entry that ** is closed to the key described by this object. This object might hold ** just a prefix of the key. The number of fields is given by ** pKeyInfo->nField. ** ** The r1 and r2 fields are the values to return if this key is less than ** or greater than a key in the btree, respectively. These are normally ** -1 and +1 respectively, but might be inverted to +1 and -1 if the b-tree ** is in DESC order. ** ** The key comparison functions actually return default_rc when they find ** an equals comparison. default_rc can be -1, 0, or +1. If there are ** multiple entries in the b-tree with the same key (when only looking ** at the first pKeyInfo->nFields,) then default_rc can be set to -1 to ** cause the search to find the last match, or +1 to cause the search to ** find the first match. ** ** The key comparison functions will set eqSeen to true if they ever ** get and equal results when comparing this structure to a b-tree record. ** When default_rc!=0, the search might end up on the record immediately ** before the first match or immediately after the last match. The ** eqSeen field will indicate whether or not an exact match exists in the ** b-tree. */ struct UnpackedRecord { KeyInfo *pKeyInfo; /* Collation and sort-order information */ Mem *aMem; /* Values */ u16 nField; /* Number of entries in apMem[] */ i8 default_rc; /* Comparison result if keys are equal */ u8 errCode; /* Error detected by xRecordCompare (CORRUPT or NOMEM) */ i8 r1; /* Value to return if (lhs > rhs) */ i8 r2; /* Value to return if (rhs < lhs) */ u8 eqSeen; /* True if an equality comparison has been seen */ }; /* ** Each SQL index is represented in memory by an ** instance of the following structure. ** |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
3592 3593 3594 3595 3596 3597 3598 3599 3600 3601 3602 3603 3604 3605 | ** use the value in register P3 as the key. If cursor P1 refers ** to an SQL index, then P3 is the first in an array of P4 registers ** that are used as an unpacked index key. ** ** Reposition cursor P1 so that it points to the smallest entry that ** is greater than or equal to the key value. If there are no records ** greater than or equal to the key and P2 is not zero, then jump to P2. ** ** This opcode leaves the cursor configured to move in forward order, ** from the beginning toward the end. In other words, the cursor is ** configured to use Next, not Prev. ** ** See also: Found, NotFound, SeekLt, SeekGt, SeekLe */ | > > > > > > > | 3592 3593 3594 3595 3596 3597 3598 3599 3600 3601 3602 3603 3604 3605 3606 3607 3608 3609 3610 3611 3612 | ** use the value in register P3 as the key. If cursor P1 refers ** to an SQL index, then P3 is the first in an array of P4 registers ** that are used as an unpacked index key. ** ** Reposition cursor P1 so that it points to the smallest entry that ** is greater than or equal to the key value. If there are no records ** greater than or equal to the key and P2 is not zero, then jump to P2. ** ** If the cursor P1 was opened using the OPFLAG_SEEKEQ flag, then this ** opcode will always land on a record that equally equals the key, or ** else jump immediately to P2. When the cursor is OPFLAG_SEEKEQ, this ** opcode must be followed by an IdxLE opcode with the same arguments. ** The IdxLE opcode will be skipped if this opcode succeeds, but the ** IdxLE opcode will be used on subsequent loop iterations. ** ** This opcode leaves the cursor configured to move in forward order, ** from the beginning toward the end. In other words, the cursor is ** configured to use Next, not Prev. ** ** See also: Found, NotFound, SeekLt, SeekGt, SeekLe */ |
︙ | ︙ | |||
3651 3652 3653 3654 3655 3656 3657 3658 3659 3660 3661 3662 3663 | ** is less than or equal to the key value. If there are no records ** less than or equal to the key and P2 is not zero, then jump to P2. ** ** This opcode leaves the cursor configured to move in reverse order, ** from the end toward the beginning. In other words, the cursor is ** configured to use Prev, not Next. ** ** See also: Found, NotFound, SeekGt, SeekGe, SeekLt */ case OP_SeekLT: /* jump, in3 */ case OP_SeekLE: /* jump, in3 */ case OP_SeekGE: /* jump, in3 */ case OP_SeekGT: { /* jump, in3 */ | > > > > > > > | | | | | | > | 3658 3659 3660 3661 3662 3663 3664 3665 3666 3667 3668 3669 3670 3671 3672 3673 3674 3675 3676 3677 3678 3679 3680 3681 3682 3683 3684 3685 3686 3687 3688 3689 3690 3691 | ** is less than or equal to the key value. If there are no records ** less than or equal to the key and P2 is not zero, then jump to P2. ** ** This opcode leaves the cursor configured to move in reverse order, ** from the end toward the beginning. In other words, the cursor is ** configured to use Prev, not Next. ** ** If the cursor P1 was opened using the OPFLAG_SEEKEQ flag, then this ** opcode will always land on a record that equally equals the key, or ** else jump immediately to P2. When the cursor is OPFLAG_SEEKEQ, this ** opcode must be followed by an IdxGE opcode with the same arguments. ** The IdxGE opcode will be skipped if this opcode succeeds, but the ** IdxGE opcode will be used on subsequent loop iterations. ** ** See also: Found, NotFound, SeekGt, SeekGe, SeekLt */ case OP_SeekLT: /* jump, in3 */ case OP_SeekLE: /* jump, in3 */ case OP_SeekGE: /* jump, in3 */ case OP_SeekGT: { /* jump, in3 */ int res; /* Comparison result */ int oc; /* Opcode */ VdbeCursor *pC; /* The cursor to seek */ UnpackedRecord r; /* The key to seek for */ int nField; /* Number of columns or fields in the key */ i64 iKey; /* The rowid we are to seek to */ int eqOnly = 0; /* Only interested in == results */ assert( pOp->p1>=0 && pOp->p1<p->nCursor ); assert( pOp->p2!=0 ); pC = p->apCsr[pOp->p1]; assert( pC!=0 ); assert( pC->pseudoTableReg==0 ); assert( OP_SeekLE == OP_SeekLT+1 ); |
︙ | ︙ | |||
3684 3685 3686 3687 3688 3689 3690 | pC->seekOp = pOp->opcode; #endif /* For a cursor with the BTREE_SEEK_EQ hint, only the OP_SeekGE and ** OP_SeekLE opcodes are allowed, and these must be immediately followed ** by an OP_IdxGT or OP_IdxLT opcode, respectively, with the same key. */ | < > < | 3699 3700 3701 3702 3703 3704 3705 3706 3707 3708 3709 3710 3711 3712 3713 3714 3715 3716 3717 3718 3719 3720 3721 | pC->seekOp = pOp->opcode; #endif /* For a cursor with the BTREE_SEEK_EQ hint, only the OP_SeekGE and ** OP_SeekLE opcodes are allowed, and these must be immediately followed ** by an OP_IdxGT or OP_IdxLT opcode, respectively, with the same key. */ if( sqlite3BtreeCursorHasHint(pC->pCursor, BTREE_SEEK_EQ) ){ eqOnly = 1; assert( pOp->opcode==OP_SeekGE || pOp->opcode==OP_SeekLE ); assert( pOp[1].opcode==OP_IdxLT || pOp[1].opcode==OP_IdxGT ); assert( pOp[1].p1==pOp[0].p1 ); assert( pOp[1].p2==pOp[0].p2 ); assert( pOp[1].p3==pOp[0].p3 ); assert( pOp[1].p4.i==pOp[0].p4.i ); } if( pC->isTable ){ /* The input value in P3 might be of any type: integer, real, string, ** blob, or NULL. But it needs to be an integer before we can do ** the seek, so convert it. */ pIn3 = &aMem[pOp->p3]; if( (pIn3->flags & (MEM_Int|MEM_Real|MEM_Str))==MEM_Str ){ |
︙ | ︙ | |||
3743 3744 3745 3746 3747 3748 3749 3750 3751 3752 3753 3754 3755 3756 | } } rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, 0, (u64)iKey, 0, &res); pC->movetoTarget = iKey; /* Used by OP_Delete */ if( rc!=SQLITE_OK ){ goto abort_due_to_error; } }else{ nField = pOp->p4.i; assert( pOp->p4type==P4_INT32 ); assert( nField>0 ); r.pKeyInfo = pC->pKeyInfo; r.nField = (u16)nField; | > | 3757 3758 3759 3760 3761 3762 3763 3764 3765 3766 3767 3768 3769 3770 3771 | } } rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, 0, (u64)iKey, 0, &res); pC->movetoTarget = iKey; /* Used by OP_Delete */ if( rc!=SQLITE_OK ){ goto abort_due_to_error; } if( eqOnly && res ) goto seek_not_found; }else{ nField = pOp->p4.i; assert( pOp->p4type==P4_INT32 ); assert( nField>0 ); r.pKeyInfo = pC->pKeyInfo; r.nField = (u16)nField; |
︙ | ︙ | |||
3768 3769 3770 3771 3772 3773 3774 3775 3776 3777 3778 3779 3780 3781 3782 3783 3784 | assert( oc!=OP_SeekLT || r.default_rc==+1 ); r.aMem = &aMem[pOp->p3]; #ifdef SQLITE_DEBUG { int i; for(i=0; i<r.nField; i++) assert( memIsValid(&r.aMem[i]) ); } #endif ExpandBlob(r.aMem); rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, &r, 0, 0, &res); if( rc!=SQLITE_OK ){ goto abort_due_to_error; } } pC->deferredMoveto = 0; pC->cacheStatus = CACHE_STALE; #ifdef SQLITE_TEST sqlite3_search_count++; #endif | > > > > > | 3783 3784 3785 3786 3787 3788 3789 3790 3791 3792 3793 3794 3795 3796 3797 3798 3799 3800 3801 3802 3803 3804 | assert( oc!=OP_SeekLT || r.default_rc==+1 ); r.aMem = &aMem[pOp->p3]; #ifdef SQLITE_DEBUG { int i; for(i=0; i<r.nField; i++) assert( memIsValid(&r.aMem[i]) ); } #endif ExpandBlob(r.aMem); r.eqSeen = 0; rc = sqlite3BtreeMovetoUnpacked(pC->pCursor, &r, 0, 0, &res); if( rc!=SQLITE_OK ){ goto abort_due_to_error; } if( eqOnly && r.eqSeen==0 ){ assert( res!=0 ); goto seek_not_found; } } pC->deferredMoveto = 0; pC->cacheStatus = CACHE_STALE; #ifdef SQLITE_TEST sqlite3_search_count++; #endif |
︙ | ︙ | |||
3799 3800 3801 3802 3803 3804 3805 3806 3807 3808 3809 3810 3811 3812 3813 3814 3815 3816 | }else{ /* res might be negative because the table is empty. Check to ** see if this is the case. */ res = sqlite3BtreeEof(pC->pCursor); } } assert( pOp->p2>0 ); VdbeBranchTaken(res!=0,2); if( res ){ goto jump_to_p2; } break; } /* Opcode: Seek P1 P2 * * * ** Synopsis: intkey=r[P2] ** | > > > > | 3819 3820 3821 3822 3823 3824 3825 3826 3827 3828 3829 3830 3831 3832 3833 3834 3835 3836 3837 3838 3839 3840 | }else{ /* res might be negative because the table is empty. Check to ** see if this is the case. */ res = sqlite3BtreeEof(pC->pCursor); } } seek_not_found: assert( pOp->p2>0 ); VdbeBranchTaken(res!=0,2); if( res ){ goto jump_to_p2; }else if( eqOnly ){ assert( pOp[1].opcode==OP_IdxLT || pOp[1].opcode==OP_IdxGT ); pOp++; /* Skip the OP_IdxLt or OP_IdxGT that follows */ } break; } /* Opcode: Seek P1 P2 * * * ** Synopsis: intkey=r[P2] ** |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
3965 3966 3967 3968 3969 3970 3971 3972 3973 3974 3975 3976 3977 3978 | /* rc==0 here means that one or both of the keys ran out of fields and ** all the fields up to that point were equal. Return the default_rc ** value. */ assert( CORRUPT_DB || vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, pPKey2->default_rc) || pKeyInfo->db->mallocFailed ); return pPKey2->default_rc; } int sqlite3VdbeRecordCompare( int nKey1, const void *pKey1, /* Left key */ UnpackedRecord *pPKey2 /* Right key */ ){ return sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 0); | > | 3965 3966 3967 3968 3969 3970 3971 3972 3973 3974 3975 3976 3977 3978 3979 | /* rc==0 here means that one or both of the keys ran out of fields and ** all the fields up to that point were equal. Return the default_rc ** value. */ assert( CORRUPT_DB || vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, pPKey2->default_rc) || pKeyInfo->db->mallocFailed ); pPKey2->eqSeen = 1; return pPKey2->default_rc; } int sqlite3VdbeRecordCompare( int nKey1, const void *pKey1, /* Left key */ UnpackedRecord *pPKey2 /* Right key */ ){ return sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 0); |
︙ | ︙ | |||
4064 4065 4066 4067 4068 4069 4070 4071 4072 4073 4074 4075 4076 4077 | /* The first fields of the two keys are equal. Compare the trailing ** fields. */ res = sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1); }else{ /* The first fields of the two keys are equal and there are no trailing ** fields. Return pPKey2->default_rc in this case. */ res = pPKey2->default_rc; } assert( vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, res) ); return res; } /* | > | 4065 4066 4067 4068 4069 4070 4071 4072 4073 4074 4075 4076 4077 4078 4079 | /* The first fields of the two keys are equal. Compare the trailing ** fields. */ res = sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1); }else{ /* The first fields of the two keys are equal and there are no trailing ** fields. Return pPKey2->default_rc in this case. */ res = pPKey2->default_rc; pPKey2->eqSeen = 1; } assert( vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, res) ); return res; } /* |
︙ | ︙ | |||
4110 4111 4112 4113 4114 4115 4116 4117 4118 4119 4120 4121 4122 4123 | if( res==0 ){ res = nStr - pPKey2->aMem[0].n; if( res==0 ){ if( pPKey2->nField>1 ){ res = sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1); }else{ res = pPKey2->default_rc; } }else if( res>0 ){ res = pPKey2->r2; }else{ res = pPKey2->r1; } }else if( res>0 ){ | > | 4112 4113 4114 4115 4116 4117 4118 4119 4120 4121 4122 4123 4124 4125 4126 | if( res==0 ){ res = nStr - pPKey2->aMem[0].n; if( res==0 ){ if( pPKey2->nField>1 ){ res = sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1); }else{ res = pPKey2->default_rc; pPKey2->eqSeen = 1; } }else if( res>0 ){ res = pPKey2->r2; }else{ res = pPKey2->r1; } }else if( res>0 ){ |
︙ | ︙ |
Changes to test/collate4.test.
︙ | ︙ | |||
348 349 350 351 352 353 354 | do_test collate4-2.1.2 { execsql { CREATE INDEX collate4i1 ON collate4t1(a); } count { SELECT * FROM collate4t2, collate4t1 WHERE a = b; } | | | | | | 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 | do_test collate4-2.1.2 { execsql { CREATE INDEX collate4i1 ON collate4t1(a); } count { SELECT * FROM collate4t2, collate4t1 WHERE a = b; } } {A a A A 4} do_test collate4-2.1.3 { count { SELECT * FROM collate4t2, collate4t1 WHERE b = a; } } {A A 19} do_test collate4-2.1.4 { execsql { DROP INDEX collate4i1; CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT); } count { SELECT * FROM collate4t2, collate4t1 WHERE a = b ORDER BY collate4t2.rowid, collate4t1.rowid } } {A a A A 19} do_test collate4-2.1.5 { count { SELECT * FROM collate4t2, collate4t1 WHERE b = a; } } {A A 3} ifcapable subquery { do_test collate4-2.1.6 { count { SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2) ORDER BY rowid } } {a A 10} do_test collate4-2.1.7 { execsql { DROP INDEX collate4i1; CREATE INDEX collate4i1 ON collate4t1(a); } count { SELECT a FROM collate4t1 WHERE a IN (SELECT * FROM collate4t2) ORDER BY rowid } } {a A 5} do_test collate4-2.1.8 { count { SELECT a FROM collate4t1 WHERE a IN ('z', 'a'); } } {a A 4} do_test collate4-2.1.9 { execsql { DROP INDEX collate4i1; CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT); } count { SELECT a FROM collate4t1 WHERE a IN ('z', 'a') ORDER BY rowid; |
︙ | ︙ |
Changes to test/where.test.
︙ | ︙ | |||
408 409 410 411 412 413 414 | SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1; } } {1 0 4 2 1 9 3 1 16 102} do_test where-5.3a { count { SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1; } | | | | | | 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 | SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1; } } {1 0 4 2 1 9 3 1 16 102} do_test where-5.3a { count { SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1; } } {1 0 4 2 1 9 3 1 16 12} do_test where-5.3b { count { SELECT * FROM t1 WHERE w IN (3,-1,1,2) order by 1; } } {1 0 4 2 1 9 3 1 16 12} do_test where-5.3c { count { SELECT * FROM t1 WHERE w IN (3,2,-1,1,2) order by 1; } } {1 0 4 2 1 9 3 1 16 12} do_test where-5.3d { count { SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1 DESC; } } {3 1 16 2 1 9 1 0 4 11} do_test where-5.4 { count { SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1; } } {1 0 4 2 1 9 3 1 16 102} do_test where-5.5 { count { |
︙ | ︙ | |||
461 462 463 464 465 466 467 | ORDER BY 1; } } {2 1 9 4 2 25 103} do_test where-5.9 { count { SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1; } | | | | | | 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 | ORDER BY 1; } } {2 1 9 4 2 25 103} do_test where-5.9 { count { SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1; } } {2 1 9 3 1 16 6} do_test where-5.10 { count { SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1; } } {2 1 9 3 1 16 199} do_test where-5.11 { count { SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1; } } {79 6 6400 89 6 8100 199} do_test where-5.12 { count { SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1; } } {79 6 6400 89 6 8100 7} do_test where-5.13 { count { SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1; } } {2 1 9 3 1 16 6} do_test where-5.14 { count { SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1; } } {2 1 9 5} do_test where-5.15 { count { SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1; } } {2 1 9 3 1 16 9} do_test where-5.100 { db eval { SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969) ORDER BY x, y } } {2 1 9 54 5 3025 62 5 3969} do_test where-5.101 { |
︙ | ︙ |
Changes to test/where4.test.
︙ | ︙ | |||
87 88 89 90 91 92 93 | count {SELECT rowid FROM t1 WHERE w='a' AND x IS NULL AND y='c'} } {4 2} do_test where4-1.10 { count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL} } {6 2} do_test where4-1.11 { count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=123} | | | 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 | count {SELECT rowid FROM t1 WHERE w='a' AND x IS NULL AND y='c'} } {4 2} do_test where4-1.10 { count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL} } {6 2} do_test where4-1.11 { count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=123} } {0} do_test where4-1.12 { count {SELECT rowid FROM t1 WHERE w=x'78' AND x IS NULL AND y=x'7A'} } {6 2} do_test where4-1.13 { count {SELECT rowid FROM t1 WHERE w IS NULL AND x IS NULL} } {7 2} do_test where4-1.14 { |
︙ | ︙ |