Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Allow multiple NULL values in a UNIQUE index. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
4dbb499c396362648dba9835a9304cdd |
User & Date: | dan 2012-04-23 12:07:25.198 |
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
Changes to src/insert.c.
︙ | ︙ | |||
1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 | /* 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; | > > > | 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 | /* 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; |
︙ | ︙ | |||
1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 | 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 ){ | > | 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 | 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 | } if( c ){ pc = pOp->p2-1; } break; } | | > > | > > > > | | > | > > | 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 858 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 862 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 |