SQLite

Check-in [e476408e3c]
Login

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

Overview
Comment:Ignore IS NOT NULL and NOT NULL constraints on NOT NULL columns.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e476408e3c5ba5f3ba5e98ff264167c163d72e3f
User & Date: drh 2013-08-20 17:00:55.048
Context
2013-08-20
17:14
Fix an invalid assert() in where.c. Also a crash that can occur in the EXPLAIN QUERY PLAN code under obscure circumstances. (check-in: ef192abb82 user: dan tags: trunk)
17:00
Ignore IS NOT NULL and NOT NULL constraints on NOT NULL columns. (check-in: e476408e3c user: drh tags: trunk)
16:08
Test script changes to support testing sqlite3_interrupt(). (check-in: 0cede9f898 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
4332
4333
4334
4335
4336
4337
4338
4339
4340
4341
4342
4343
4344
4345
4346
4347
4348
4349
4350
4351
4352

  saved_prereq = pNew->prereq;
  saved_nOut = pNew->nOut;
  pNew->rSetup = 0;
  rLogSize = estLog(whereCost(pProbe->aiRowEst[0]));
  for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
    int nIn = 0;
    if( pTerm->prereqRight & pNew->maskSelf ) continue;
#ifdef SQLITE_ENABLE_STAT3
    if( (pTerm->wtFlags & TERM_VNULL)!=0
     && (iCol<0 || pSrc->pTab->aCol[iCol].notNull)
    ){
      continue; /* skip IS NOT NULL constraints on a NOT NULL column */
    }
#endif
    pNew->wsFlags = saved_wsFlags;
    pNew->u.btree.nEq = saved_nEq;
    pNew->nLTerm = saved_nLTerm;
    if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
    pNew->aLTerm[pNew->nLTerm++] = pTerm;
    pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
    pNew->rRun = rLogSize; /* Baseline cost is log2(N).  Adjustments below */








<
|


|

<








4332
4333
4334
4335
4336
4337
4338

4339
4340
4341
4342
4343

4344
4345
4346
4347
4348
4349
4350

  saved_prereq = pNew->prereq;
  saved_nOut = pNew->nOut;
  pNew->rSetup = 0;
  rLogSize = estLog(whereCost(pProbe->aiRowEst[0]));
  for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
    int nIn = 0;
    if( pTerm->prereqRight & pNew->maskSelf ) continue;

    if( (pTerm->eOperator==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0)
     && (iCol<0 || pSrc->pTab->aCol[iCol].notNull)
    ){
      continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */
    }

    pNew->wsFlags = saved_wsFlags;
    pNew->u.btree.nEq = saved_nEq;
    pNew->nLTerm = saved_nLTerm;
    if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
    pNew->aLTerm[pNew->nLTerm++] = pTerm;
    pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
    pNew->rRun = rLogSize; /* Baseline cost is log2(N).  Adjustments below */
︙︙
Changes to test/where3.test.
︙︙
242
243
244
245
246
247
248






249
250
251
252
253
254
255

do_execsql_test where3-3.1 {
  explain query plan
  SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
} {
  0 0 1 {SCAN TABLE t302} 
  0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)}
}







if 0 {  # Query planner no longer does this
# Verify that when there are multiple tables in a join which must be
# full table scans that the query planner attempts put the table with
# the fewest number of output rows as the outer loop.
#
do_execsql_test where3-4.0 {








>
>
>
>
>
>








242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261

do_execsql_test where3-3.1 {
  explain query plan
  SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
} {
  0 0 1 {SCAN TABLE t302} 
  0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)}
}
do_execsql_test where3-3.2 {
  SELECT * FROM t301 WHERE c=3 AND a IS NULL;
} {}
do_execsql_test where3-3.3 {
  SELECT * FROM t301 WHERE c=3 AND a IS NOT NULL;
} {1 2 3}

if 0 {  # Query planner no longer does this
# Verify that when there are multiple tables in a join which must be
# full table scans that the query planner attempts put the table with
# the fewest number of output rows as the outer loop.
#
do_execsql_test where3-4.0 {
︙︙
Changes to test/whereA.test.
︙︙
64
65
66
67
68
69
70






71
72
73
74
75
76
77

  sqlite3 db test.db
  db eval {
    PRAGMA reverse_unordered_selects=1;
    VACUUM;
    SELECT * FROM t1;
  }
} {3 4.53 {} 2 hello world 1 2 3}







do_test whereA-2.1 {
  db eval {
    PRAGMA reverse_unordered_selects=0;
    SELECT * FROM t1 WHERE a>0;
  }
} {1 2 3 2 hello world 3 4.53 {}}








>
>
>
>
>
>








64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

  sqlite3 db test.db
  db eval {
    PRAGMA reverse_unordered_selects=1;
    VACUUM;
    SELECT * FROM t1;
  }
} {3 4.53 {} 2 hello world 1 2 3}
do_execsql_test whereA-1.8 {
  SELECT * FROM t1 WHERE b=2 AND a IS NULL;
} {}
do_execsql_test whereA-1.9 {
  SELECT * FROM t1 WHERE b=2 AND a IS NOT NULL;
} {1 2 3}

do_test whereA-2.1 {
  db eval {
    PRAGMA reverse_unordered_selects=0;
    SELECT * FROM t1 WHERE a>0;
  }
} {1 2 3 2 hello world 3 4.53 {}}
︙︙