/ Check-in [e3b1f625]
Login

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

Overview
Comment:Do not assume an index contains unique entries unless it is declared UNIQUE and NOT NULL is specified for all columns. Fix for [7b4fee9f6c].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e3b1f625518edc0e925116668dca5d25c3232b59
User & Date: dan 2015-04-11 11:44:27
References
2015-04-11
11:45 Closed ticket [7b4fee9f]: Expressions like (a IS NULL AND b = ?) optimized by a UNIQUE index matching a single row only plus 6 other changes artifact: 51edb7d0 user: dan
Context
2015-04-11
11:53
Update tests in whereD.test to account for the change in the previous commit. check-in: da49700c user: dan tags: trunk
11:44
Do not assume an index contains unique entries unless it is declared UNIQUE and NOT NULL is specified for all columns. Fix for [7b4fee9f6c]. check-in: e3b1f625 user: dan tags: trunk
02:08
Detect and report oversized records constructed from multiple zeroblobs. check-in: 9e139afd user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  4777   4777         }
  4778   4778         assert( nIn>0 );  /* RHS always has 2 or more terms...  The parser
  4779   4779                           ** changes "x IN (?)" into "x=?". */
  4780   4780   
  4781   4781       }else if( eOp & (WO_EQ) ){
  4782   4782         pNew->wsFlags |= WHERE_COLUMN_EQ;
  4783   4783         if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){
  4784         -        if( iCol>=0 && !IsUniqueIndex(pProbe) ){
         4784  +        if( iCol>=0 && pProbe->uniqNotNull==0 ){
  4785   4785             pNew->wsFlags |= WHERE_UNQ_WANTED;
  4786   4786           }else{
  4787   4787             pNew->wsFlags |= WHERE_ONEROW;
  4788   4788           }
  4789   4789         }
  4790   4790       }else if( eOp & WO_ISNULL ){
  4791   4791         pNew->wsFlags |= WHERE_COLUMN_NULL;

Changes to test/null.test.

   274    274   } {1}
   275    275   do_test null-8.15 {
   276    276     execsql {
   277    277       SELECT x FROM t4 WHERE y!=33 ORDER BY x;
   278    278     }
   279    279   } {1}
   280    280   
          281  +do_execsql_test null-9.1 {
          282  +  CREATE TABLE t5(a, b, c);
          283  +  CREATE UNIQUE INDEX t5ab ON t5(a, b);
          284  +
          285  +  INSERT INTO t5 VALUES(1, NULL, 'one');
          286  +  INSERT INTO t5 VALUES(1, NULL, 'i');
          287  +  INSERT INTO t5 VALUES(NULL, 'x', 'two');
          288  +  INSERT INTO t5 VALUES(NULL, 'x', 'ii');
          289  +}
          290  +
          291  +do_execsql_test null-9.2 {
          292  +  SELECT * FROM t5 WHERE a = 1 AND b IS NULL;
          293  +} {1 {} one 1 {} i}
          294  +
          295  +do_execsql_test null-9.3 {
          296  +  SELECT * FROM t5 WHERE a IS NULL AND b = 'x';
          297  +} {{} x two {} x ii}
   281    298   
   282    299   
   283    300   finish_test