SQLite

Check-in [e3b1f62551]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e3b1f625518edc0e925116668dca5d25c3232b59
User & Date: dan 2015-04-11 11:44:27.202
References
2015-04-11
11:45 • Closed ticket [7b4fee9f6c]: Expressions like (a IS NULL AND b = ?) optimized by a UNIQUE index matching a single row only plus 6 other changes (artifact: 51edb7d06b user: dan)
Context
2015-04-11
11:53
Update tests in whereD.test to account for the change in the previous commit. (check-in: da49700ca1 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: e3b1f62551 user: dan tags: trunk)
02:08
Detect and report oversized records constructed from multiple zeroblobs. (check-in: 9e139afd92 user: drh tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/where.c.
︙
4777
4778
4779
4780
4781
4782
4783
4784

4785
4786
4787
4788
4789
4790
4791

4777
4778
4779
4780
4781
4782
4783

4784
4785
4786
4787
4788
4789
4790
4791








-
+








      }
      assert( nIn>0 );  /* RHS always has 2 or more terms...  The parser
                        ** changes "x IN (?)" into "x=?". */

    }else if( eOp & (WO_EQ) ){
      pNew->wsFlags |= WHERE_COLUMN_EQ;
      if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){
        if( iCol>=0 && !IsUniqueIndex(pProbe) ){
        if( iCol>=0 && pProbe->uniqNotNull==0 ){
          pNew->wsFlags |= WHERE_UNQ_WANTED;
        }else{
          pNew->wsFlags |= WHERE_ONEROW;
        }
      }
    }else if( eOp & WO_ISNULL ){
      pNew->wsFlags |= WHERE_COLUMN_NULL;
︙
Changes to test/null.test.
︙
274
275
276
277
278
279
280

















281
282
283

274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300








+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+




} {1}
do_test null-8.15 {
  execsql {
    SELECT x FROM t4 WHERE y!=33 ORDER BY x;
  }
} {1}

do_execsql_test null-9.1 {
  CREATE TABLE t5(a, b, c);
  CREATE UNIQUE INDEX t5ab ON t5(a, b);

  INSERT INTO t5 VALUES(1, NULL, 'one');
  INSERT INTO t5 VALUES(1, NULL, 'i');
  INSERT INTO t5 VALUES(NULL, 'x', 'two');
  INSERT INTO t5 VALUES(NULL, 'x', 'ii');
}

do_execsql_test null-9.2 {
  SELECT * FROM t5 WHERE a = 1 AND b IS NULL;
} {1 {} one 1 {} i}

do_execsql_test null-9.3 {
  SELECT * FROM t5 WHERE a IS NULL AND b = 'x';
} {{} x two {} x ii}


finish_test