Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | NULL values in a row of a unique index cause the row to be distinct. Ticket #1301. More testing and optimization needs to be done on this before closing the ticket. (CVS 2526) |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
06a71b162b032fc5b56d18919a784d4e |
| User & Date: | drh 2005-06-24 03:53:06 |
|
2005-06-25
| ||
| 18:42 | Remove the blob(), text() and numeric() functions added in (2524) and replace them with the standard CAST operator. Ticket #1287. (CVS 2527) (check-in: 17631785 user: drh tags: trunk) | |
|
2005-06-24
| ||
| 03:53 | NULL values in a row of a unique index cause the row to be distinct. Ticket #1301. More testing and optimization needs to be done on this before closing the ticket. (CVS 2526) (check-in: 06a71b16 user: drh tags: trunk) | |
|
2005-06-23
| ||
| 03:15 | Make sure the String8 opcode always has a non-null P3 argument in the foreign_key_list pragma. Ticket #1297. (CVS 2525) (check-in: bcf62dc7 user: drh tags: trunk) | |
Changes to src/build.c.
18 18 ** CREATE INDEX 19 19 ** DROP INDEX 20 20 ** creating ID lists 21 21 ** BEGIN TRANSACTION 22 22 ** COMMIT 23 23 ** ROLLBACK 24 24 ** 25 -** $Id: build.c,v 1.327 2005/06/14 02:12:46 drh Exp $ 25 +** $Id: build.c,v 1.328 2005/06/24 03:53:06 drh Exp $ 26 26 */ 27 27 #include "sqliteInt.h" 28 28 #include <ctype.h> 29 29 30 30 /* 31 31 ** This routine is called when a new SQL statement is beginning to 32 32 ** be parsed. Initialize the pParse structure as needed. ................................................................................ 1969 1969 static void sqlite3RefillIndex(Parse *pParse, Index *pIndex, int memRootPage){ 1970 1970 Table *pTab = pIndex->pTable; /* The table that is indexed */ 1971 1971 int iTab = pParse->nTab; /* Btree cursor used for pTab */ 1972 1972 int iIdx = pParse->nTab+1; /* Btree cursor used for pIndex */ 1973 1973 int addr1; /* Address of top of loop */ 1974 1974 int tnum; /* Root page of index */ 1975 1975 Vdbe *v; /* Generate code into this virtual machine */ 1976 - int isUnique; /* True for a unique index */ 1977 1976 1978 1977 #ifndef SQLITE_OMIT_AUTHORIZATION 1979 1978 if( sqlite3AuthCheck(pParse, SQLITE_REINDEX, pIndex->zName, 0, 1980 1979 pParse->db->aDb[pIndex->iDb].zName ) ){ 1981 1980 return; 1982 1981 } 1983 1982 #endif ................................................................................ 2003 2002 sqlite3VdbeOp3(v, OP_OpenWrite, iIdx, tnum, 2004 2003 (char*)&pIndex->keyInfo, P3_KEYINFO); 2005 2004 sqlite3VdbeAddOp(v, OP_Integer, pTab->iDb, 0); 2006 2005 sqlite3VdbeAddOp(v, OP_OpenRead, iTab, pTab->tnum); 2007 2006 sqlite3VdbeAddOp(v, OP_SetNumColumns, iTab, pTab->nCol); 2008 2007 addr1 = sqlite3VdbeAddOp(v, OP_Rewind, iTab, 0); 2009 2008 sqlite3GenerateIndexKey(v, pIndex, iTab); 2010 - isUnique = pIndex->onError!=OE_None; 2011 - sqlite3VdbeAddOp(v, OP_IdxInsert, iIdx, isUnique); 2012 - if( isUnique ){ 2013 - sqlite3VdbeChangeP3(v, -1, "indexed columns are not unique", P3_STATIC); 2009 + if( pIndex->onError!=OE_None ){ 2010 + int curaddr = sqlite3VdbeCurrentAddr(v); 2011 + int addr2 = curaddr+4; 2012 + sqlite3VdbeChangeP2(v, curaddr-1, addr2); 2013 + sqlite3VdbeAddOp(v, OP_Rowid, iTab, 0); 2014 + sqlite3VdbeAddOp(v, OP_AddImm, 1, 0); 2015 + sqlite3VdbeAddOp(v, OP_IsUnique, iIdx, addr2); 2016 + sqlite3VdbeOp3(v, OP_Halt, SQLITE_CONSTRAINT, OE_Abort, 2017 + "indexed columns are not unique", P3_STATIC); 2018 + assert( addr2==sqlite3VdbeCurrentAddr(v) ); 2014 2019 } 2020 + sqlite3VdbeAddOp(v, OP_IdxInsert, iIdx, 0); 2015 2021 sqlite3VdbeAddOp(v, OP_Next, iTab, addr1+1); 2016 2022 sqlite3VdbeChangeP2(v, addr1, sqlite3VdbeCurrentAddr(v)); 2017 2023 sqlite3VdbeAddOp(v, OP_Close, iTab, 0); 2018 2024 sqlite3VdbeAddOp(v, OP_Close, iIdx, 0); 2019 2025 } 2020 2026 2021 2027 /*
Changes to src/delete.c.
8 8 ** May you find forgiveness for yourself and forgive others. 9 9 ** May you share freely, never taking more than you give. 10 10 ** 11 11 ************************************************************************* 12 12 ** This file contains C code routines that are called by the parser 13 13 ** in order to generate code for DELETE FROM statements. 14 14 ** 15 -** $Id: delete.c,v 1.106 2005/06/12 21:35:52 drh Exp $ 15 +** $Id: delete.c,v 1.107 2005/06/24 03:53:06 drh Exp $ 16 16 */ 17 17 #include "sqliteInt.h" 18 18 19 19 /* 20 20 ** Look up every table that is named in pSrc. If any table is not found, 21 21 ** add an error message to pParse->zErrMsg and return NULL. If all tables 22 22 ** are found, return a pointer to the last table. ................................................................................ 438 438 if( idx==pTab->iPKey ){ 439 439 sqlite3VdbeAddOp(v, OP_Dup, j, 0); 440 440 }else{ 441 441 sqlite3VdbeAddOp(v, OP_Column, iCur, idx); 442 442 sqlite3ColumnDefault(v, pTab, idx); 443 443 } 444 444 } 445 - sqlite3VdbeAddOp(v, OP_MakeRecord, pIdx->nColumn, (1<<24)); 445 + sqlite3VdbeAddOp(v, OP_MakeIdxRec, pIdx->nColumn, 0); 446 446 sqlite3IndexAffinityStr(v, pIdx); 447 447 }
Changes to src/insert.c.
8 8 ** May you find forgiveness for yourself and forgive others. 9 9 ** May you share freely, never taking more than you give. 10 10 ** 11 11 ************************************************************************* 12 12 ** This file contains C code routines that are called by the parser 13 13 ** to handle INSERT statements in SQLite. 14 14 ** 15 -** $Id: insert.c,v 1.139 2005/06/12 21:35:52 drh Exp $ 15 +** $Id: insert.c,v 1.140 2005/06/24 03:53:06 drh Exp $ 16 16 */ 17 17 #include "sqliteInt.h" 18 18 19 19 /* 20 20 ** Set P3 of the most recently inserted opcode to a column affinity 21 21 ** string for index pIdx. A column affinity string has one character 22 22 ** for each column in the table, according to the affinity of the column: ................................................................................ 945 945 int idx = pIdx->aiColumn[i]; 946 946 if( idx==pTab->iPKey ){ 947 947 sqlite3VdbeAddOp(v, OP_Dup, i+extra+nCol+1, 1); 948 948 }else{ 949 949 sqlite3VdbeAddOp(v, OP_Dup, i+extra+nCol-idx, 1); 950 950 } 951 951 } 952 - jumpInst1 = sqlite3VdbeAddOp(v, OP_MakeRecord, pIdx->nColumn, (1<<24)); 952 + jumpInst1 = sqlite3VdbeAddOp(v, OP_MakeIdxRec, pIdx->nColumn, 0); 953 953 sqlite3IndexAffinityStr(v, pIdx); 954 954 955 955 /* Find out what action to take in case there is an indexing conflict */ 956 956 onError = pIdx->onError; 957 957 if( onError==OE_None ) continue; /* pIdx is not a UNIQUE index */ 958 958 if( overrideError!=OE_Default ){ 959 959 onError = overrideError; ................................................................................ 1015 1015 sqlite3VdbeAddOp(v, OP_MoveGe, base, 0); 1016 1016 } 1017 1017 seenReplace = 1; 1018 1018 break; 1019 1019 } 1020 1020 } 1021 1021 contAddr = sqlite3VdbeCurrentAddr(v); 1022 - assert( contAddr<(1<<24) ); 1023 1022 #if NULL_DISTINCT_FOR_UNIQUE 1024 - sqlite3VdbeChangeP2(v, jumpInst1, contAddr | (1<<24)); 1023 + sqlite3VdbeChangeP2(v, jumpInst1, contAddr); 1025 1024 #endif 1026 1025 sqlite3VdbeChangeP2(v, jumpInst2, contAddr); 1027 1026 } 1028 1027 } 1029 1028 1030 1029 /* 1031 1030 ** This routine generates code to finish the INSERT or UPDATE operation
Changes to src/vdbe.c.
39 39 ** 40 40 ** Various scripts scan this source file in order to generate HTML 41 41 ** documentation, headers files, or other derived files. The formatting 42 42 ** of the code in this file is, therefore, important. See other comments 43 43 ** in this file for details. If in doubt, do not deviate from existing 44 44 ** commenting and indentation practices when changing or adding code. 45 45 ** 46 -** $Id: vdbe.c,v 1.470 2005/06/22 02:36:37 drh Exp $ 46 +** $Id: vdbe.c,v 1.471 2005/06/24 03:53:06 drh Exp $ 47 47 */ 48 48 #include "sqliteInt.h" 49 49 #include "os.h" 50 50 #include <ctype.h> 51 51 #include "vdbeInt.h" 52 52 53 53 /* ................................................................................ 629 629 case OP_Return: { /* no-push */ 630 630 assert( p->returnDepth>0 ); 631 631 p->returnDepth--; 632 632 pc = p->returnStack[p->returnDepth] - 1; 633 633 break; 634 634 } 635 635 636 -/* Opcode: Halt P1 P2 * 636 +/* Opcode: Halt P1 P2 P3 637 637 ** 638 638 ** Exit immediately. All open cursors, Lists, Sorts, etc are closed 639 639 ** automatically. 640 640 ** 641 641 ** P1 is the result code returned by sqlite3_exec(), sqlite3_reset(), 642 642 ** or sqlite3_finalize(). For a normal halt, this should be SQLITE_OK (0). 643 643 ** For errors, it can be some other value. If P1!=0 then P2 will determine 644 644 ** whether or not to rollback the current transaction. Do not rollback 645 645 ** if P2==OE_Fail. Do the rollback if P2==OE_Rollback. If P2==OE_Abort, 646 646 ** then back out all changes that have occurred during this execution of the 647 647 ** VDBE, but do not rollback the transaction. 648 +** 649 +** If P3 is not null then it is an error message string. 648 650 ** 649 651 ** There is an implied "Halt 0 0 0" instruction inserted at the very end of 650 652 ** every program. So a jump past the last instruction of the program 651 653 ** is the same as executing Halt. 652 654 */ 653 655 case OP_Halt: { /* no-push */ 654 656 p->pTos = pTos; ................................................................................ 1996 1998 ** sqlite3VdbeRecordCompare function will correctly compare two encoded 1997 1999 ** records. Refer to source code comments for the details of the record 1998 2000 ** format. 1999 2001 ** 2000 2002 ** The original stack entries are popped from the stack if P1>0 but 2001 2003 ** remain on the stack if P1<0. 2002 2004 ** 2003 -** The P2 argument is divided into two 16-bit words before it is processed. 2004 -** If the hi-word is non-zero, then an extra integer is read from the stack 2005 -** and appended to the record as a varint. If the low-word of P2 is not 2006 -** zero and one or more of the entries are NULL, then jump to the value of 2007 -** the low-word of P2. This feature can be used to skip a uniqueness test 2008 -** on indices. 2005 +** If P2 is not zero and one or more of the entries are NULL, then jump 2006 +** to the address given by P2. This feature can be used to skip a 2007 +** uniqueness test on indices. 2009 2008 ** 2010 2009 ** P3 may be a string that is P1 characters long. The nth character of the 2011 2010 ** string indicates the column affinity that should be used for the nth 2012 2011 ** field of the index key (i.e. the first character of P3 corresponds to the 2013 2012 ** lowest element on the stack). 2014 2013 ** 2015 2014 ** The mapping from character to affinity is as follows: 2016 2015 ** 'n' = NUMERIC. 2017 2016 ** 'i' = INTEGER. 2018 2017 ** 't' = TEXT. 2019 2018 ** 'o' = NONE. 2020 2019 ** 2021 2020 ** If P3 is NULL then all index fields have the affinity NONE. 2021 +** 2022 +** See also OP_MakeIdxRec 2022 2023 */ 2024 +/* Opcode: MakeRecordI P1 P2 P3 2025 +** 2026 +** This opcode works just OP_MakeRecord except that it reads an extra 2027 +** integer from the stack (thus reading a total of abs(P1+1) entries) 2028 +** and appends that extra integer to the end of the record as a varint. 2029 +** This results in an index key. 2030 +*/ 2031 +case OP_MakeIdxRec: 2023 2032 case OP_MakeRecord: { 2024 2033 /* Assuming the record contains N fields, the record format looks 2025 2034 ** like this: 2026 2035 ** 2027 2036 ** ------------------------------------------------------------------------ 2028 2037 ** | hdr-size | type 0 | type 1 | ... | type N-1 | data0 | ... | data N-1 | 2029 2038 ** ------------------------------------------------------------------------ ................................................................................ 2053 2062 int nField; /* Number of fields in the record */ 2054 2063 int jumpIfNull; /* Jump here if non-zero and any entries are NULL. */ 2055 2064 int addRowid; /* True to append a rowid column at the end */ 2056 2065 char *zAffinity; /* The affinity string for the record */ 2057 2066 2058 2067 leaveOnStack = ((pOp->p1<0)?1:0); 2059 2068 nField = pOp->p1 * (leaveOnStack?-1:1); 2060 - jumpIfNull = (pOp->p2 & 0x00FFFFFF); 2061 - addRowid = ((pOp->p2>>24) & 0x0000FFFF)?1:0; 2069 + jumpIfNull = pOp->p2; 2070 + addRowid = pOp->opcode==OP_MakeIdxRec; 2062 2071 zAffinity = pOp->p3; 2063 2072 2064 2073 pData0 = &pTos[1-nField]; 2065 2074 assert( pData0>=p->aStack ); 2066 2075 containsNull = 0; 2067 2076 2068 2077 /* Loop through the elements that will make up the record to figure ................................................................................ 3440 3449 }else{ 3441 3450 pC->nullRow = 1; 3442 3451 } 3443 3452 pC->rowidIsValid = 0; 3444 3453 break; 3445 3454 } 3446 3455 3447 -/* Opcode: IdxInsert P1 P2 P3 3456 +/* Opcode: IdxInsert P1 * * 3448 3457 ** 3449 3458 ** The top of the stack holds a SQL index key made using the 3450 3459 ** MakeIdxKey instruction. This opcode writes that key into the 3451 3460 ** index P1. Data for the entry is nil. 3452 3461 ** 3453 -** If P2==1, then the key must be unique. If the key is not unique, 3454 -** the program aborts with a SQLITE_CONSTRAINT error and the database 3455 -** is rolled back. If P3 is not null, then it becomes part of the 3456 -** error message returned with the SQLITE_CONSTRAINT. 3457 -** 3458 3462 ** This instruction only works for indices. The equivalent instruction 3459 3463 ** for tables is OP_Insert. 3460 3464 */ 3461 3465 case OP_IdxInsert: { /* no-push */ 3462 3466 int i = pOp->p1; 3463 3467 Cursor *pC; 3464 3468 BtCursor *pCrsr; 3465 3469 assert( pTos>=p->aStack ); 3466 3470 assert( i>=0 && i<p->nCursor ); 3467 3471 assert( p->apCsr[i]!=0 ); 3468 3472 assert( pTos->flags & MEM_Blob ); 3473 + assert( pOp->p2==0 ); 3469 3474 if( (pCrsr = (pC = p->apCsr[i])->pCursor)!=0 ){ 3470 3475 int nKey = pTos->n; 3471 3476 const char *zKey = pTos->z; 3472 - if( pOp->p2 ){ 3473 - int res; 3474 - int len; 3475 - 3476 - /* 'len' is the length of the key minus the rowid at the end */ 3477 - len = nKey - sqlite3VdbeIdxRowidLen(nKey, zKey); 3478 - 3479 - rc = sqlite3BtreeMoveto(pCrsr, zKey, len, &res); 3480 - if( rc!=SQLITE_OK ) goto abort_due_to_error; 3481 - while( res!=0 && !sqlite3BtreeEof(pCrsr) ){ 3482 - int c; 3483 - if( sqlite3VdbeIdxKeyCompare(pC, len, zKey, &c)==SQLITE_OK && c==0 ){ 3484 - rc = SQLITE_CONSTRAINT; 3485 - if( pOp->p3 && pOp->p3[0] ){ 3486 - sqlite3SetString(&p->zErrMsg, pOp->p3, (char*)0); 3487 - } 3488 - goto abort_due_to_error; 3489 - } 3490 - if( res<0 ){ 3491 - sqlite3BtreeNext(pCrsr, &res); 3492 - res = +1; 3493 - }else{ 3494 - break; 3495 - } 3496 - } 3497 - } 3498 3477 assert( pC->isTable==0 ); 3499 3478 rc = sqlite3BtreeInsert(pCrsr, zKey, nKey, "", 0); 3500 3479 assert( pC->deferredMoveto==0 ); 3501 3480 pC->cacheValid = 0; 3502 3481 } 3503 3482 Release(pTos); 3504 3483 pTos--;
Changes to test/unique.test.
8 8 # May you share freely, never taking more than you give. 9 9 # 10 10 #*********************************************************************** 11 11 # This file implements regression tests for SQLite library. The 12 12 # focus of this file is testing the CREATE UNIQUE INDEX statement, 13 13 # and primary keys, and the UNIQUE constraint on table columns 14 14 # 15 -# $Id: unique.test,v 1.7 2003/08/05 13:13:39 drh Exp $ 15 +# $Id: unique.test,v 1.8 2005/06/24 03:53:06 drh Exp $ 16 16 17 17 set testdir [file dirname $argv0] 18 18 source $testdir/tester.tcl 19 19 20 20 # Try to create a table with two primary keys. 21 21 # (This is allowed in SQLite even that it is not valid SQL) 22 22 # ................................................................................ 192 192 } 193 193 } {0 {}} 194 194 do_test unique-4.5 { 195 195 execsql { 196 196 SELECT * FROM t4 197 197 } 198 198 } {1 2 3 {} 2 {} {} 3 4 2 2 {}} 199 -integrity_check unique-4.6 199 + 200 +# Ticket #1301. Any NULL value in a set of unique columns should 201 +# cause the rows to be distinct. 202 +# 203 +do_test unique-4.6 { 204 + catchsql { 205 + INSERT INTO t4 VALUES(NULL, 2, NULL); 206 + } 207 +} {0 {}} 208 +do_test unique-4.7 { 209 + execsql {SELECT * FROM t4} 210 +} {1 2 3 {} 2 {} {} 3 4 2 2 {} {} 2 {}} 211 +do_test unique-4.8 { 212 + catchsql {CREATE UNIQUE INDEX i4a ON t4(a,b)} 213 +} {0 {}} 214 +do_test unique-4.9 { 215 + catchsql {CREATE UNIQUE INDEX i4b ON t4(a,b,c)} 216 +} {0 {}} 217 +do_test unique-4.10 { 218 + catchsql {CREATE UNIQUE INDEX i4c ON t4(b)} 219 +} {1 {indexed columns are not unique}} 220 +integrity_check unique-4.99 200 221 201 222 # Test the error message generation logic. In particular, make sure we 202 223 # do not overflow the static buffer used to generate the error message. 203 224 # 204 225 do_test unique-5.1 { 205 226 execsql { 206 227 CREATE TABLE t5(