SQLite4
Check-in [4dbb499c39]
Not logged in

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

Overview
Comment:Allow multiple NULL values in a UNIQUE index.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4dbb499c396362648dba9835a9304cdd61ecc212
User & Date: dan 2012-04-23 12:07:25
Context
2012-04-23
12:47
Remove the sqlite4_get_table() API. check-in: ff68adaca1 user: drh tags: trunk
12:07
Allow multiple NULL values in a UNIQUE index. check-in: 4dbb499c39 user: dan tags: trunk
11:45
Fix a bug in CREATE INDEX where the number of columns in the index is fewer than the number of columns in the primary key. check-in: b0ac144cf5 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/insert.c.

1297
1298
1299
1300
1301
1302
1303

1304
1305


1306
1307
1308
1309
1310
1311
1312
....
1355
1356
1357
1358
1359
1360
1361

1362
1363
1364
1365
1366
1367
1368
    /* If Index.onError==OE_None, then pIdx is not a UNIQUE or PRIMARY KEY 
    ** index. In this case there is no need to test the index for uniqueness
    ** - all that is required is to populate the aRegIdx[iCur] register. Jump 
    ** to the next iteration of the loop if this is the case.  */
    onError = pIdx->onError;
    if( onError!=OE_None ){
      int iTest;                  /* Address of OP_IsUnique instruction */

      int iTest2 = 0;             /* Address of OP_Eq instruction */
      int regOut = 0;             /* PK of row to replace */



      /* Figure out what to do if a UNIQUE constraint is encountered. 
      **
      ** TODO: If a previous constraint is a REPLACE, why change IGNORE to
      ** REPLACE and FAIL to ABORT here?  */
      if( overrideError!=OE_Default ){
        onError = overrideError;
................................................................................
          seenReplace = 1;
          break;
        }
      }

      /* If the OP_IsUnique passes (no constraint violation) jump here */
      sqlite4VdbeJumpHere(v, iTest);

      if( iTest2 ) sqlite4VdbeJumpHere(v, iTest2);
    }

    sqlite4ReleaseTempRange(pParse, regTmp, nTmpReg);
  }
  
  if( pbMayReplace ){







>


>
>







 







>







1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
....
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
    /* If Index.onError==OE_None, then pIdx is not a UNIQUE or PRIMARY KEY 
    ** index. In this case there is no need to test the index for uniqueness
    ** - all that is required is to populate the aRegIdx[iCur] register. Jump 
    ** to the next iteration of the loop if this is the case.  */
    onError = pIdx->onError;
    if( onError!=OE_None ){
      int iTest;                  /* Address of OP_IsUnique instruction */
      int iTest3;                 /* Address of OP_IsNull */
      int iTest2 = 0;             /* Address of OP_Eq instruction */
      int regOut = 0;             /* PK of row to replace */

      iTest3 = sqlite4VdbeAddOp3(v, OP_IsNull, regTmp, 0, pIdx->nColumn);

      /* Figure out what to do if a UNIQUE constraint is encountered. 
      **
      ** TODO: If a previous constraint is a REPLACE, why change IGNORE to
      ** REPLACE and FAIL to ABORT here?  */
      if( overrideError!=OE_Default ){
        onError = overrideError;
................................................................................
          seenReplace = 1;
          break;
        }
      }

      /* If the OP_IsUnique passes (no constraint violation) jump here */
      sqlite4VdbeJumpHere(v, iTest);
      sqlite4VdbeJumpHere(v, iTest3);
      if( iTest2 ) sqlite4VdbeJumpHere(v, iTest2);
    }

    sqlite4ReleaseTempRange(pParse, regTmp, nTmpReg);
  }
  
  if( pbMayReplace ){

Changes to src/vdbe.c.

2039
2040
2041
2042
2043
2044
2045
2046
2047
2048


2049
2050

2051



2052
2053

2054


2055
2056
2057
2058
2059
2060
2061
  }
  if( c ){
    pc = pOp->p2-1;
  }
  break;
}

/* Opcode: IsNull P1 P2 * * *
**
** Jump to P2 if the value in register P1 is NULL.


*/
case OP_IsNull: {            /* same as TK_ISNULL, jump, in1 */

  pIn1 = &aMem[pOp->p1];



  if( (pIn1->flags & MEM_Null)!=0 ){
    pc = pOp->p2 - 1;

  }


  break;
}

/* Opcode: NotNull P1 P2 * * *
**
** Jump to P2 if the value in register P1 is not NULL.  
*/







|

|
>
>


>

>
>
>
|
|
>
|
>
>







2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
  }
  if( c ){
    pc = pOp->p2-1;
  }
  break;
}

/* Opcode: IsNull P1 P2 P3 * *
**
** P1 is the first in an array of P3 registers. Or, if P3 is 0, the first
** in an array of a single register. If any registers in the array are
** NULL, jump to instruction P2.
*/
case OP_IsNull: {            /* same as TK_ISNULL, jump, in1 */
  Mem *pEnd;
  pIn1 = &aMem[pOp->p1];
  pEnd = &aMem[pOp->p1+pOp->p3];

  do {
    if( (pIn1->flags & MEM_Null)!=0 ){
      pc = pOp->p2 - 1;
      break;
    }
  }while( (++pIn1)<pEnd );

  break;
}

/* Opcode: NotNull P1 P2 * * *
**
** Jump to P2 if the value in register P1 is not NULL.  
*/

Changes to test/permutations.test.

131
132
133
134
135
136
137

138
139
140
141
142
143
144
#
lappend ::testsuitelist xxx

test_suite "src4" -prefix "" -description {
} -files {
  simple.test fkey1.test conflict.test trigger2.test select1.test
  where.test select3.test select5.test select7.test select8.test

}

test_suite "veryquick" -prefix "" -description {
  "Very" quick test suite. Runs in less than 5 minutes on a workstation. 
  This test suite is the same as the "quick" tests, except that some files
  that test malloc and IO errors are omitted.
} -files [







>







131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
#
lappend ::testsuitelist xxx

test_suite "src4" -prefix "" -description {
} -files {
  simple.test fkey1.test conflict.test trigger2.test select1.test
  where.test select3.test select5.test select7.test select8.test
  selectB.test selectC.test
}

test_suite "veryquick" -prefix "" -description {
  "Very" quick test suite. Runs in less than 5 minutes on a workstation. 
  This test suite is the same as the "quick" tests, except that some files
  that test malloc and IO errors are omitted.
} -files [

Changes to test/simple.test.

844
845
846
847
848
849
850




851
852
853
854
855
856
857
...
859
860
861
862
863
864
865



866




















867
868
869
#-------------------------------------------------------------------------
reset_db

do_execsql_test 45.1 {
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(d, e, f);





  INSERT INTO t2 VALUES(3,   6,  9);
  INSERT INTO t2 VALUES(12, 15, 18);
  INSERT INTO t2 VALUES(21, 24, 27);
}

do_execsql_test 45.2 {
  SELECT d FROM t2 EXCEPT SELECT d FROM t2;
................................................................................

do_execsql_test 45.3 {
  SELECT d FROM t2 
  EXCEPT 
  SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
} {}


























finish_test








>
>
>
>







 







>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
...
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
#-------------------------------------------------------------------------
reset_db

do_execsql_test 45.1 {
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(d, e, f);

  INSERT INTO t1 VALUES( 2,  4,  6);
  INSERT INTO t1 VALUES( 8, 10, 12);
  INSERT INTO t1 VALUES(14, 16, 18);

  INSERT INTO t2 VALUES(3,   6,  9);
  INSERT INTO t2 VALUES(12, 15, 18);
  INSERT INTO t2 VALUES(21, 24, 27);
}

do_execsql_test 45.2 {
  SELECT d FROM t2 EXCEPT SELECT d FROM t2;
................................................................................

do_execsql_test 45.3 {
  SELECT d FROM t2 
  EXCEPT 
  SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
} {}

do_execsql_test 45.4 {
   SELECT c, d FROM t1 LEFT JOIN t2 ON (c=d)
} {6 {} 12 12 18 {}}

do_execsql_test 45.5 {
   SELECT a AS x, b AS y FROM t1
     UNION ALL
   SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
     UNION ALL
   SELECT a*100, b*100 FROM t1

   ORDER BY 1;
} {2 4 8 10 14 16 20.1 {} 80.1 180.1 140.1 {} 200 400 800 1000 1400 1600}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 46.1 {
  CREATE TABLE t1(x);
  CREATE UNIQUE INDEX i1 ON t1(x);
}

do_execsql_test 46.2 { INSERT INTO t1 VALUES(NULL) }
do_execsql_test 46.3 { INSERT INTO t1 VALUES(NULL) }

finish_test