Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Manifest types in indices. At the moment indices use manifest typing, but some other parts of the SQL engine do not, which can lead to some strange results. (CVS 1368) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
9f2b6d9d3a07e25fcdb7e8290da7a182 |
User & Date: | danielk1977 2004-05-13 05:16:16.000 |
Context
2004-05-13
| ||
05:20 | Commit vdbeaux.c, which should of gone in with the previous commit. (CVS 1369) (check-in: 5378a64040 user: danielk1977 tags: trunk) | |
05:16 | Manifest types in indices. At the moment indices use manifest typing, but some other parts of the SQL engine do not, which can lead to some strange results. (CVS 1368) (check-in: 9f2b6d9d3a user: danielk1977 tags: trunk) | |
01:12 | Allocate more overflow data onto overflow pages, thus wasting less disk space. (CVS 1367) (check-in: 1d52a4bb47 user: drh tags: trunk) | |
Changes
Changes to src/vdbe.c.
︙ | ︙ | |||
39 40 41 42 43 44 45 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** | | | 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | ** ** Various scripts scan this source file in order to generate HTML ** documentation, headers files, or other derived files. The formatting ** of the code in this file is, therefore, important. See other comments ** in this file for details. If in doubt, do not deviate from existing ** commenting and indentation practices when changing or adding code. ** ** $Id: vdbe.c,v 1.285 2004/05/13 05:16:16 danielk1977 Exp $ */ #include "sqliteInt.h" #include "os.h" #include <ctype.h> #include "vdbeInt.h" /* |
︙ | ︙ | |||
2177 2178 2179 2180 2181 2182 2183 | ** P3 is a string that is P1 characters long. Each character is either ** an 'n' or a 't' to indicates if the argument should be numeric or ** text. The first character corresponds to the lowest element on the ** stack. If P3 is null then all arguments are assumed to be numeric. ** ** See also: MakeKey, SortMakeKey */ | | | | 2177 2178 2179 2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 | ** P3 is a string that is P1 characters long. Each character is either ** an 'n' or a 't' to indicates if the argument should be numeric or ** text. The first character corresponds to the lowest element on the ** stack. If P3 is null then all arguments are assumed to be numeric. ** ** See also: MakeKey, SortMakeKey */ case OP_MakeIdxKey2: case OP_MakeKey2: { char *zNewKey; int nByte; int nField; int addRowid; int i, j; int containsNull = 0; Mem *pRec; |
︙ | ︙ | |||
2276 2277 2278 2279 2280 2281 2282 | }else{ pTos->z = zNewKey; pTos->flags = MEM_Str | MEM_Dyn; } break; } | | | | > > | > > > | > | | | > | > > | > | > > > | 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 2302 2303 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314 2315 2316 2317 2318 2319 2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 | }else{ pTos->z = zNewKey; pTos->flags = MEM_Str | MEM_Dyn; } break; } /* Opcode: MakeIdxKey3 P1 P2 * ** ** Convert the top P1 entries of the stack into a single entry suitable ** for use as the key in an index. In addition, take one additional integer ** off of the stack, treat that integer as an eight-byte record number, and ** append the integer to the key as a varint. Thus a total of P1+1 entries ** are popped from the stack for this instruction and a single entry is ** pushed back. The first P1 entries that are popped are strings and the ** last entry (the lowest on the stack) is an integer record number. */ case OP_MakeKey: case OP_MakeIdxKey: { Mem *pRec; Mem *pData0; int nField; u64 rowid; int nByte = 0; int addRowid; int containsNull = 0; char *zKey; /* The new key */ int offset = 0; nField = pOp->p1; pData0 = &pTos[1-nField]; assert( pData0>=p->aStack ); addRowid = ((pOp->opcode==OP_MakeIdxKey)?1:0); /* Calculate the number of bytes required for the new index key and ** store that number in nByte. Also set rowid to the record number to ** append to the index key. */ for(pRec=pData0; pRec<=pTos; pRec++){ u64 serial_type = sqlite3VdbeSerialType(pRec); if( serial_type==0 ){ containsNull = 1; } nByte += sqlite3VarintLen(serial_type); nByte += sqlite3VdbeSerialTypeLen(serial_type); } if( addRowid ){ pRec = &pTos[0-nField]; assert( pRec>=p->aStack ); Integerify(pRec); rowid = pRec->i; nByte += sqlite3VarintLen(rowid); nByte++; } /* Allocate space for the new key */ zKey = (char *)sqliteMalloc(nByte); if( !zKey ){ rc = SQLITE_NOMEM; goto abort_due_to_error; } /* Build the key in the buffer pointed to by zKey. */ for(pRec=pData0; pRec<=pTos; pRec++){ offset += sqlite3PutVarint(&zKey[offset], sqlite3VdbeSerialType(pRec)); offset += sqlite3VdbeSerialPut(&zKey[offset], pRec); } if( addRowid ){ zKey[offset++] = '\0'; offset += sqlite3PutVarint(&zKey[offset], rowid); } assert( offset==nByte ); /* Pop the consumed values off the stack and push on the new key. */ if( addRowid||(pOp->p2==0) ){ popStack(&pTos, nField+addRowid); } pTos++; pTos->flags = MEM_Str|MEM_Dyn; /* TODO: should eventually be MEM_Blob */ pTos->z = zKey; pTos->n = nByte; if( pOp->p2 && containsNull ){ pc = pOp->p2 - 1; } break; } /* Opcode: IncrKey * * * ** ** The top of the stack should contain an index key generated by ** The MakeKey opcode. This routine increases the least significant |
︙ | ︙ | |||
2653 2654 2655 2656 2657 2658 2659 | if( expandCursorArraySize(p, i) ) goto no_mem; pCur = &p->aCsr[i]; sqlite3VdbeCleanupCursor(pCur); memset(pCur, 0, sizeof(Cursor)); pCur->nullRow = 1; if( pX==0 ) break; do{ | > > > > | > | 2666 2667 2668 2669 2670 2671 2672 2673 2674 2675 2676 2677 2678 2679 2680 2681 2682 2683 2684 2685 | if( expandCursorArraySize(p, i) ) goto no_mem; pCur = &p->aCsr[i]; sqlite3VdbeCleanupCursor(pCur); memset(pCur, 0, sizeof(Cursor)); pCur->nullRow = 1; if( pX==0 ) break; do{ /* When opening cursors, always supply the comparison function ** sqlite3VdbeKeyCompare(). If the table being opened is of type ** INTKEY, the btree layer won't call the comparison function anyway. */ rc = sqlite3BtreeCursor(pX, p2, wrFlag, sqlite3VdbeKeyCompare, 0, &pCur->pCursor); switch( rc ){ case SQLITE_BUSY: { if( db->xBusyCallback==0 ){ p->pc = pc; p->rc = SQLITE_BUSY; p->pTos = &pTos[1 + (pOp->p2<=0)]; /* Operands must remain on stack */ return SQLITE_BUSY; |
︙ | ︙ | |||
2942 2943 2944 2945 2946 2947 2948 | ** See also: Distinct, NotFound, NotExists, Found */ case OP_IsUnique: { int i = pOp->p1; Mem *pNos = &pTos[-1]; Cursor *pCx; BtCursor *pCrsr; | | | > > | > > | < | | | | | | > > | < | | | 2960 2961 2962 2963 2964 2965 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 2986 2987 2988 2989 2990 2991 2992 2993 2994 2995 2996 2997 2998 2999 3000 3001 3002 3003 3004 3005 3006 3007 3008 3009 3010 3011 3012 3013 3014 3015 3016 3017 3018 3019 3020 3021 3022 3023 3024 3025 3026 3027 3028 3029 3030 3031 3032 3033 3034 3035 3036 3037 3038 | ** See also: Distinct, NotFound, NotExists, Found */ case OP_IsUnique: { int i = pOp->p1; Mem *pNos = &pTos[-1]; Cursor *pCx; BtCursor *pCrsr; i64 R; /* Pop the value R off the top of the stack */ assert( pNos>=p->aStack ); Integerify(pTos); R = pTos->i; pTos--; assert( i>=0 && i<=p->nCursor ); pCx = &p->aCsr[i]; pCrsr = pCx->pCursor; if( pCrsr!=0 ){ int res, rc; i64 v; /* The record number on the P1 entry that matches K */ char *zKey; /* The value of K */ int nKey; /* Number of bytes in K */ int len; /* Number of bytes in K without the rowid at the end */ /* Make sure K is a string and make zKey point to K */ Stringify(pNos); zKey = pNos->z; nKey = pNos->n; assert( nKey >= 2 ); len = nKey-2; while( zKey[len] && --len ); /* Search for an entry in P1 where all but the last four bytes match K. ** If there is no such entry, jump immediately to P2. */ assert( p->aCsr[i].deferredMoveto==0 ); rc = sqlite3BtreeMoveto(pCrsr, zKey, len, &res); if( rc!=SQLITE_OK ) goto abort_due_to_error; if( res<0 ){ rc = sqlite3BtreeNext(pCrsr, &res); if( res ){ pc = pOp->p2 - 1; break; } } rc = sqlite3VdbeIdxKeyCompare(pCrsr, len, zKey, 0, &res); if( rc!=SQLITE_OK ) goto abort_due_to_error; if( res>0 ){ pc = pOp->p2 - 1; break; } /* At this point, pCrsr is pointing to an entry in P1 where all but ** the final varint (the rowid) matches K. Check to see if the ** final varint is different from R. If it equals R then jump ** immediately to P2. */ rc = sqlite3VdbeIdxRowid(pCrsr, &v); if( rc!=SQLITE_OK ){ goto abort_due_to_error; } if( v==R ){ pc = pOp->p2 - 1; break; } /* The final varint of the key is different from R. Push it onto ** the stack. (The record number of an entry that violates a UNIQUE ** constraint.) */ pTos++; pTos->i = v; pTos->flags = MEM_Int; } break; } |
︙ | ︙ | |||
3597 3598 3599 3600 3601 3602 3603 3604 | assert( i>=0 && i<p->nCursor ); assert( pTos->flags & MEM_Str ); if( (pCrsr = p->aCsr[i].pCursor)!=0 ){ int nKey = pTos->n; const char *zKey = pTos->z; if( pOp->p2 ){ int res; u64 n; | > | > > > > | | < | | > | 3619 3620 3621 3622 3623 3624 3625 3626 3627 3628 3629 3630 3631 3632 3633 3634 3635 3636 3637 3638 3639 3640 3641 3642 3643 3644 3645 3646 3647 3648 3649 3650 3651 3652 3653 3654 3655 3656 3657 3658 3659 3660 3661 3662 3663 | assert( i>=0 && i<p->nCursor ); assert( pTos->flags & MEM_Str ); if( (pCrsr = p->aCsr[i].pCursor)!=0 ){ int nKey = pTos->n; const char *zKey = pTos->z; if( pOp->p2 ){ int res; int len; u64 n; /* 'len' is the length of the key minus the rowid at the end */ len = nKey-2; while( zKey[len] && --len ); rc = sqlite3BtreeMoveto(pCrsr, zKey, len, &res); if( rc!=SQLITE_OK ) goto abort_due_to_error; while( res!=0 ){ int c; sqlite3BtreeKeySize(pCrsr, &n); if( n==nKey && sqlite3VdbeIdxKeyCompare(pCrsr, len, zKey, 0, &c)==SQLITE_OK && c==0 ){ rc = SQLITE_CONSTRAINT; if( pOp->p3 && pOp->p3[0] ){ sqlite3SetString(&p->zErrMsg, pOp->p3, (char*)0); } goto abort_due_to_error; } if( res<0 ){ sqlite3BtreeNext(pCrsr, &res); res = +1; }else{ break; } } } assert( p->aCsr[i].intKey==0 ); rc = sqlite3BtreeInsert(pCrsr, zKey, nKey, "", 0); assert( p->aCsr[i].deferredMoveto==0 ); } Release(pTos); pTos--; break; } |
︙ | ︙ | |||
3657 3658 3659 3660 3661 3662 3663 | Release(pTos); pTos--; break; } /* Opcode: IdxRecno P1 * * ** | | | | < < > > > > > > > > > > > > > | > > > > > < > > | > > | > > > | 3684 3685 3686 3687 3688 3689 3690 3691 3692 3693 3694 3695 3696 3697 3698 3699 3700 3701 3702 3703 3704 3705 3706 3707 3708 3709 3710 3711 3712 3713 3714 3715 3716 3717 3718 3719 3720 3721 3722 3723 3724 3725 3726 3727 3728 3729 3730 3731 3732 3733 3734 3735 3736 3737 3738 3739 3740 3741 3742 3743 3744 3745 | Release(pTos); pTos--; break; } /* Opcode: IdxRecno P1 * * ** ** Push onto the stack an integer which is the varint located at the ** end of the index key pointed to by cursor P1. These integer should be ** the record number of the table entry to which this index entry points. ** ** See also: Recno, MakeIdxKey. */ case OP_IdxRecno: { int i = pOp->p1; BtCursor *pCrsr; assert( i>=0 && i<p->nCursor ); pTos++; if( (pCrsr = p->aCsr[i].pCursor)!=0 ){ u64 sz; int len; char buf[9]; assert( p->aCsr[i].deferredMoveto==0 ); assert( p->aCsr[i].intKey==0 ); /* Read the final 9 bytes of the key into buf[]. If the whole key is ** less than 9 bytes then just load the whole thing. Set len to the ** number of bytes read. */ sqlite3BtreeKeySize(pCrsr, &sz); len = ((sz>9)?9:sz); rc = sqlite3BtreeKey(pCrsr, sz-len, len, buf); if( rc!=SQLITE_OK ){ goto abort_due_to_error; } len--; if( buf[len]&0x80 ){ /* If the last byte read has the 0x80 bit set, then the key does ** not end with a varint. Push a NULL onto the stack instead. */ pTos->flags = MEM_Null; }else{ /* Find the start of the varint by searching backwards for a 0x00 ** byte. If one does not exists, then intepret the whole 9 bytes as a ** varint. */ while( len && buf[len-1] ){ len--; } sqlite3GetVarint(&buf[len], &sz); pTos->flags = MEM_Int; pTos->i = sz; } }else{ pTos->flags = MEM_Null; } break; } |
︙ | ︙ | |||
3728 3729 3730 3731 3732 3733 3734 | assert( i>=0 && i<p->nCursor ); assert( pTos>=p->aStack ); if( (pCrsr = p->aCsr[i].pCursor)!=0 ){ int res, rc; Stringify(pTos); assert( p->aCsr[i].deferredMoveto==0 ); | < | | 3777 3778 3779 3780 3781 3782 3783 3784 3785 3786 3787 3788 3789 3790 3791 | assert( i>=0 && i<p->nCursor ); assert( pTos>=p->aStack ); if( (pCrsr = p->aCsr[i].pCursor)!=0 ){ int res, rc; Stringify(pTos); assert( p->aCsr[i].deferredMoveto==0 ); rc = sqlite3VdbeIdxKeyCompare(pCrsr, pTos->n, pTos->z, 0, &res); if( rc!=SQLITE_OK ){ break; } if( pOp->opcode==OP_IdxLT ){ res = -res; }else if( pOp->opcode==OP_IdxGE ){ res++; |
︙ | ︙ |
Changes to src/vdbe.h.
︙ | ︙ | |||
11 12 13 14 15 16 17 | ************************************************************************* ** Header file for the Virtual DataBase Engine (VDBE) ** ** This header defines the interface to the virtual database engine ** or VDBE. The VDBE implements an abstract machine that runs a ** simple program to access and modify the underlying database. ** | | | 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | ************************************************************************* ** Header file for the Virtual DataBase Engine (VDBE) ** ** This header defines the interface to the virtual database engine ** or VDBE. The VDBE implements an abstract machine that runs a ** simple program to access and modify the underlying database. ** ** $Id: vdbe.h,v 1.75 2004/05/13 05:16:17 danielk1977 Exp $ */ #ifndef _SQLITE_VDBE_H_ #define _SQLITE_VDBE_H_ #include <stdio.h> /* ** A single VDBE is an opaque structure named "Vdbe". Only routines |
︙ | ︙ | |||
104 105 106 107 108 109 110 | int sqlite3VdbeFinalize(Vdbe*,char**); void sqlite3VdbeResolveLabel(Vdbe*, int); int sqlite3VdbeCurrentAddr(Vdbe*); void sqlite3VdbeTrace(Vdbe*,FILE*); void sqlite3VdbeCompressSpace(Vdbe*,int); int sqlite3VdbeReset(Vdbe*,char **); int sqliteVdbeSetVariables(Vdbe*,int,const char**); | | < | 104 105 106 107 108 109 110 111 112 113 | int sqlite3VdbeFinalize(Vdbe*,char**); void sqlite3VdbeResolveLabel(Vdbe*, int); int sqlite3VdbeCurrentAddr(Vdbe*); void sqlite3VdbeTrace(Vdbe*,FILE*); void sqlite3VdbeCompressSpace(Vdbe*,int); int sqlite3VdbeReset(Vdbe*,char **); int sqliteVdbeSetVariables(Vdbe*,int,const char**); int sqlite3VdbeKeyCompare(void*,int,const void*,int, const void*); #endif |
Changes to src/vdbeInt.h.
︙ | ︙ | |||
318 319 320 321 322 323 324 | #endif int sqlite3VdbeSerialTypeLen(u64); u64 sqlite3VdbeSerialType(const Mem *); int sqlite3VdbeSerialPut(unsigned char *, const Mem *); int sqlite3VdbeSerialGet(const unsigned char *, u64, Mem *); int sqlite2BtreeKeyCompare(BtCursor *, const void *, int, int, int *); | > > | 318 319 320 321 322 323 324 325 326 | #endif int sqlite3VdbeSerialTypeLen(u64); u64 sqlite3VdbeSerialType(const Mem *); int sqlite3VdbeSerialPut(unsigned char *, const Mem *); int sqlite3VdbeSerialGet(const unsigned char *, u64, Mem *); int sqlite2BtreeKeyCompare(BtCursor *, const void *, int, int, int *); int sqlite3VdbeIdxKeyCompare(BtCursor*, int , const unsigned char*, int, int*); int sqlite3VdbeIdxRowid(BtCursor *, i64 *); |
Changes to test/insert.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the INSERT statement. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the INSERT statement. # # $Id: insert.test,v 1.17 2004/05/13 05:16:17 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to insert into a non-existant table. # do_test insert-1.1 { |
︙ | ︙ | |||
141 142 143 144 145 146 147 148 149 | execsql { DELETE FROM test2; CREATE INDEX index9 ON test2(f1,f2); CREATE INDEX indext ON test2(f4,f5); SELECT * from test2; } } {} do_test insert-3.2 { execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')} | > > > > | | | 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 | execsql { DELETE FROM test2; CREATE INDEX index9 ON test2(f1,f2); CREATE INDEX indext ON test2(f4,f5); SELECT * from test2; } } {} # Update for sqlite v3: # Change the 111 to '111' in the following two test cases, because # the default value is being inserted as a string. TODO: It shouldn't be. do_test insert-3.2 { execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')} execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33} } {111 -3.33 hi hum {}} do_test insert-3.3 { execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')} execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33} } {111 -3.33 hi hum {}} do_test insert-3.4 { execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44} } {22 -4.44 hi abc-123 wham} integrity_check insert-3.5 # Test of expressions in the VALUES clause |
︙ | ︙ |
Changes to test/select2.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the SELECT statement. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the SELECT statement. # # $Id: select2.test,v 1.19 2004/05/13 05:16:17 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create a table with some data # execsql {CREATE TABLE tbl1(f1 int, f2 int)} |
︙ | ︙ | |||
79 80 81 82 83 84 85 86 87 88 | execsql {SELECT count(*) FROM tbl2 WHERE f2>1000} } {29500} do_test select2-3.1 { execsql {SELECT f1 FROM tbl2 WHERE 1000=f2} } {500} do_test select2-3.2a { execsql {CREATE INDEX idx1 ON tbl2(f2)} } {} | > > > < | | | | | 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 | execsql {SELECT count(*) FROM tbl2 WHERE f2>1000} } {29500} do_test select2-3.1 { execsql {SELECT f1 FROM tbl2 WHERE 1000=f2} } {500} # SQLite v3: Change the expressions in the following four test cases # from 1000=f2 to '1000'=f2. This is because fields read in using # the COPY command have manifest type TEXT. do_test select2-3.2a { execsql {CREATE INDEX idx1 ON tbl2(f2)} } {} do_test select2-3.2b { execsql {SELECT f1 FROM tbl2 WHERE '1000'=f2} } {500} do_test select2-3.2c { execsql {SELECT f1 FROM tbl2 WHERE f2='1000'} } {500} do_test select2-3.2d { set sqlite_search_count 0 execsql {SELECT * FROM tbl2 WHERE '1000'=f2} set sqlite_search_count } {3} do_test select2-3.2e { set sqlite_search_count 0 execsql {SELECT * FROM tbl2 WHERE f2='1000'} set sqlite_search_count } {3} # Make sure queries run faster with an index than without # do_test select2-3.3 { execsql {DROP INDEX idx1} |
︙ | ︙ |
Changes to test/select4.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing UNION, INTERSECT and EXCEPT operators # in SELECT statements. # | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing UNION, INTERSECT and EXCEPT operators # in SELECT statements. # # $Id: select4.test,v 1.14 2004/05/13 05:16:17 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # set fd [open data1.txt w] |
︙ | ︙ | |||
192 193 194 195 196 197 198 199 200 | execsql { SELECT DISTINCT log FROM t1 INTERSECT SELECT n FROM t1 WHERE log=3 ORDER BY log; } } {5} do_test select4-4.1.2 { execsql { | > > > > > | | | | 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 | execsql { SELECT DISTINCT log FROM t1 INTERSECT SELECT n FROM t1 WHERE log=3 ORDER BY log; } } {5} # Update for sqlite 3: # Change the "UNION ALL SELECT 6" in each of the select statements # for the next three test cases to "UNION ALL SELECT '6'". This is # to accomadate manifest typing. do_test select4-4.1.2 { execsql { SELECT DISTINCT log FROM t1 UNION ALL SELECT '6' INTERSECT SELECT n FROM t1 WHERE log=3 ORDER BY log; } } {5 6} do_test select4-4.1.3 { execsql { CREATE TABLE t2 AS SELECT DISTINCT log FROM t1 UNION ALL SELECT '6' INTERSECT SELECT n FROM t1 WHERE log=3 ORDER BY log; SELECT * FROM t2; } } {5 6} execsql {DROP TABLE t2} do_test select4-4.1.4 { execsql { CREATE TABLE t2 AS SELECT DISTINCT log FROM t1 UNION ALL SELECT '6' INTERSECT SELECT n FROM t1 WHERE log=3 ORDER BY log DESC; SELECT * FROM t2; } } {6 5} execsql {DROP TABLE t2} |
︙ | ︙ | |||
465 466 467 468 469 470 471 472 473 474 475 | SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2) ORDER BY n LIMIT 2 } } {n 1 log 0 n 2 log 1} # Make sure DISTINCT works appropriately on TEXT and NUMERIC columns. # do_test select4-8.1 { execsql { BEGIN; CREATE TABLE t3(a text, b float, c text); | > > | | | | | | | 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 | SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2) ORDER BY n LIMIT 2 } } {n 1 log 0 n 2 log 1} # Make sure DISTINCT works appropriately on TEXT and NUMERIC columns. # # Update for sqlite v3: # Insert X+0.0 instead of X to make sure X has manifest type NUMERIC. do_test select4-8.1 { execsql { BEGIN; CREATE TABLE t3(a text, b float, c text); INSERT INTO t3 VALUES(1, 1.1 + 0.0, '1.1'); INSERT INTO t3 VALUES(2, 1.10 + 0.0, '1.10'); INSERT INTO t3 VALUES(3, 1.10 + 0.0, '1.1'); INSERT INTO t3 VALUES(4, 1.1 + 0.0, '1.10'); INSERT INTO t3 VALUES(5, 1.2 + 0.0, '1.2'); INSERT INTO t3 VALUES(6, 1.3 + 0.0, '1.3'); COMMIT; } execsql { SELECT DISTINCT b FROM t3 ORDER BY c; } } {1.1 1.2 1.3} do_test select4-8.2 { |
︙ | ︙ |