/ Check-in [06a71b16]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
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:06a71b162b032fc5b56d18919a784d4ee94dde7c
User & Date: drh 2005-06-24 03:53:06
Context
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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(