Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -14,11 +14,11 @@ ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** -** $Id: where.c,v 1.388 2009/04/23 13:22:44 drh Exp $ +** $Id: where.c,v 1.389 2009/04/24 14:51:42 drh Exp $ */ #include "sqliteInt.h" /* ** Trace output macros @@ -224,15 +224,16 @@ ** ISNULL constraints will then not be used on the right table of a left ** join. Tickets #2177 and #2189. */ #define WHERE_ROWID_EQ 0x00001000 /* rowid=EXPR or rowid IN (...) */ #define WHERE_ROWID_RANGE 0x00002000 /* rowidEXPR */ -#define WHERE_COLUMN_EQ 0x00010000 /* x=EXPR or x IN (...) */ +#define WHERE_COLUMN_EQ 0x00010000 /* x=EXPR or x IN (...) or x IS NULL */ #define WHERE_COLUMN_RANGE 0x00020000 /* xEXPR */ #define WHERE_COLUMN_IN 0x00040000 /* x IN (...) */ -#define WHERE_INDEXED 0x00070000 /* Anything that uses an index */ -#define WHERE_IN_ABLE 0x00071000 /* Able to support an IN operator */ +#define WHERE_COLUMN_NULL 0x00080000 /* x IS NULL */ +#define WHERE_INDEXED 0x000f0000 /* Anything that uses an index */ +#define WHERE_IN_ABLE 0x000f1000 /* Able to support an IN operator */ #define WHERE_TOP_LIMIT 0x00100000 /* xEXPR or x>=EXPR constraint */ #define WHERE_IDX_ONLY 0x00800000 /* Use index only - omit table */ #define WHERE_ORDERBY 0x01000000 /* Output will appear in correct order */ #define WHERE_REVERSE 0x02000000 /* Scan in reverse order */ @@ -2028,13 +2029,14 @@ int inMultIsEst = 0; /* True if inMultiplier is an estimate */ WHERETRACE(("... index %s:\n", pProbe->zName)); /* Count the number of columns in the index that are satisfied - ** by x=EXPR constraints or x IN (...) constraints. For a term - ** of the form x=EXPR we only have to do a single binary search. - ** But for x IN (...) we have to do a number of binary searched + ** by x=EXPR or x IS NULL constraints or x IN (...) constraints. + ** For a term of the form x=EXPR or x IS NULL we only have to do + ** a single binary search. But for x IN (...) we have to do a + ** number of binary searched ** equal to the number of entries on the RHS of the IN operator. ** The inMultipler variable with try to estimate the number of ** binary searches needed. */ wsFlags = 0; @@ -2050,10 +2052,12 @@ inMultiplier *= 25; inMultIsEst = 1; }else if( pExpr->x.pList ){ inMultiplier *= pExpr->x.pList->nExpr + 1; } + }else if( pTerm->eOperator & WO_ISNULL ){ + wsFlags |= WHERE_COLUMN_NULL; } } nRow = pProbe->aiRowEst[i] * inMultiplier; /* If inMultiplier is an estimate and that estimate results in an ** nRow it that is more than half number of rows in the table, @@ -2062,13 +2066,16 @@ nRow = pProbe->aiRowEst[0]/2; inMultiplier = nRow/pProbe->aiRowEst[i]; } cost = nRow + inMultiplier*estLog(pProbe->aiRowEst[0]); nEq = i; - if( pProbe->onError!=OE_None && (wsFlags & WHERE_COLUMN_IN)==0 - && nEq==pProbe->nColumn ){ - wsFlags |= WHERE_UNIQUE; + if( pProbe->onError!=OE_None && nEq==pProbe->nColumn ){ + testcase( wsFlags & WHERE_COLUMN_IN ); + testcase( wsFlags & WHERE_COLUMN_NULL ); + if( (wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_NULL))==0 ){ + wsFlags |= WHERE_UNIQUE; + } } WHERETRACE(("...... nEq=%d inMult=%.9g nRow=%.9g cost=%.9g\n", nEq, inMultiplier, nRow, cost)); /* Look for range constraints. Assume that each range constraint @@ -2095,12 +2102,13 @@ } /* Add the additional cost of sorting if that is a factor. */ if( pOrderBy ){ - if( (wsFlags & WHERE_COLUMN_IN)==0 && - isSortingIndex(pParse,pWC->pMaskSet,pProbe,iCur,pOrderBy,nEq,&rev) ){ + if( (wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_NULL))==0 + && isSortingIndex(pParse,pWC->pMaskSet,pProbe,iCur,pOrderBy,nEq,&rev) + ){ if( wsFlags==0 ){ wsFlags = WHERE_COLUMN_RANGE; } wsFlags |= WHERE_ORDERBY; if( rev ){ ADDED test/tkt3824.test Index: test/tkt3824.test ================================================================== --- test/tkt3824.test +++ test/tkt3824.test @@ -0,0 +1,88 @@ +# 2009 April 24 +# +# 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. +# +#*********************************************************************** +# +# Ticket #3824 +# +# When you use an "IS NULL" constraint on a UNIQUE index, the result +# is not necessarily UNIQUE. Make sure the optimizer does not assume +# uniqueness. +# +# $Id: tkt3824.test,v 1.1 2009/04/24 14:51:42 drh Exp $ + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +proc execsql_status {sql {db db}} { + set result [uplevel $db eval [list $sql]] + if {[db status sort]} { + concat $result sort + } else { + concat $result nosort + } +} + +do_test tkt3824-1.1 { + db eval { + CREATE TABLE t1(a,b); + INSERT INTO t1 VALUES(1,NULL); + INSERT INTO t1 VALUES(9,NULL); + INSERT INTO t1 VALUES(5,NULL); + INSERT INTO t1 VALUES(123,NULL); + INSERT INTO t1 VALUES(-10,NULL); + CREATE UNIQUE INDEX t1b ON t1(b); + } + execsql_status { + SELECT a FROM t1 WHERE b IS NULL ORDER BY a; + } +} {-10 1 5 9 123 sort} +do_test tkt3824-1.2 { + execsql_status { + SELECT a FROM t1 WHERE b IS NULL ORDER BY b, a; + } +} {-10 1 5 9 123 sort} + +do_test tkt3824-2.1 { + db eval { + CREATE TABLE t2(a,b,c); + INSERT INTO t2 VALUES(1,1,NULL); + INSERT INTO t2 VALUES(9,2,NULL); + INSERT INTO t2 VALUES(5,2,NULL); + INSERT INTO t2 VALUES(123,3,NULL); + INSERT INTO t2 VALUES(-10,3,NULL); + CREATE UNIQUE INDEX t2bc ON t2(b,c); + } + execsql_status { + SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY a; + } +} {5 9 sort} +do_test tkt3824-2.2 { + execsql_status { + SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY b, a; + } +} {5 9 sort} +do_test tkt3824-2.3 { + lsort [execsql_status { + SELECT a FROM t2 WHERE b=2 AND c IS NULL ORDER BY b; + }] +} {5 9 sort} + +do_test tkt3824-3.1 { + db eval { + CREATE TABLE t3(x,y); + INSERT INTO t3 SELECT a, b FROM t1; + INSERT INTO t3 VALUES(234,567); + CREATE UNIQUE INDEX t3y ON t3(y); + DELETE FROM t3 WHERE y IS NULL; + SELECT * FROM t3; + } +} {234 567} + +finish_test