SQLite

Check-in [7027368c15]
Login

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

Overview
Comment:Handle "IS NULL" constraints on virtual table scans. IS NULL constraints are not passed to the virtual table layer. Ticket #2759. (CVS 4523)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7027368c15b3270a139bea5612d7c03c2288dcc4
User & Date: danielk1977 2007-11-05 05:12:53.000
Context
2007-11-05
12:46
Use malloc to obtain space for sqlite3_aggregate_context(). Ticket #2751. (CVS 4524) (check-in: bf75058f57 user: danielk1977 tags: trunk)
05:12
Handle "IS NULL" constraints on virtual table scans. IS NULL constraints are not passed to the virtual table layer. Ticket #2759. (CVS 4523) (check-in: 7027368c15 user: danielk1977 tags: trunk)
2007-11-02
12:53
Add the ".timer" command to the CLI. (CVS 4522) (check-in: 56680360d3 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** 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.261 2007/09/13 17:54:40 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** 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.262 2007/11/05 05:12:53 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
1235
1236
1237
1238
1239
1240
1241

1242
1243
1244
1245
1246
1247
1248
    WHERETRACE(("Recomputing index info for %s...\n", pTab->zName));

    /* Count the number of possible WHERE clause constraints referring
    ** to this virtual table */
    for(i=nTerm=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
      if( pTerm->leftCursor != pSrc->iCursor ) continue;
      if( pTerm->eOperator==WO_IN ) continue;

      nTerm++;
    }

    /* If the ORDER BY clause contains only columns in the current 
    ** virtual table then allocate space for the aOrderBy part of
    ** the sqlite3_index_info structure.
    */







>







1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
    WHERETRACE(("Recomputing index info for %s...\n", pTab->zName));

    /* Count the number of possible WHERE clause constraints referring
    ** to this virtual table */
    for(i=nTerm=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
      if( pTerm->leftCursor != pSrc->iCursor ) continue;
      if( pTerm->eOperator==WO_IN ) continue;
      if( pTerm->eOperator==WO_ISNULL ) continue;
      nTerm++;
    }

    /* If the ORDER BY clause contains only columns in the current 
    ** virtual table then allocate space for the aOrderBy part of
    ** the sqlite3_index_info structure.
    */
1282
1283
1284
1285
1286
1287
1288

1289
1290
1291
1292
1293
1294
1295
    *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy;
    *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage =
                                                                     pUsage;

    for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
      if( pTerm->leftCursor != pSrc->iCursor ) continue;
      if( pTerm->eOperator==WO_IN ) continue;

      pIdxCons[j].iColumn = pTerm->leftColumn;
      pIdxCons[j].iTermOffset = i;
      pIdxCons[j].op = pTerm->eOperator;
      /* The direct assignment in the previous line is possible only because
      ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical.  The
      ** following asserts verify this fact. */
      assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ );







>







1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
    *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy;
    *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage =
                                                                     pUsage;

    for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
      if( pTerm->leftCursor != pSrc->iCursor ) continue;
      if( pTerm->eOperator==WO_IN ) continue;
      if( pTerm->eOperator==WO_ISNULL ) continue;
      pIdxCons[j].iColumn = pTerm->leftColumn;
      pIdxCons[j].iTermOffset = i;
      pIdxCons[j].op = pTerm->eOperator;
      /* The direct assignment in the previous line is possible only because
      ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical.  The
      ** following asserts verify this fact. */
      assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ );
Changes to test/vtab1.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2006 June 10
#
# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is creating and dropping virtual tables.
#
# $Id: vtab1.test,v 1.47 2007/10/09 08:29:33 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !vtab||!schema_pragmas {
  finish_test
  return













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2006 June 10
#
# 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is creating and dropping virtual tables.
#
# $Id: vtab1.test,v 1.48 2007/11/05 05:12:53 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable !vtab||!schema_pragmas {
  finish_test
  return
941
942
943
944
945
946
947
948






























949
950
do_test vtab1.12-5 {
  execsql { SELECT * FROM c }
} {3 G H}
do_test vtab1.12-6 {
  execsql { COMMIT }
  execsql { SELECT * FROM c }
} {3 G H}































unset -nocomplain echo_module_begin_fail
finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
do_test vtab1.12-5 {
  execsql { SELECT * FROM c }
} {3 G H}
do_test vtab1.12-6 {
  execsql { COMMIT }
  execsql { SELECT * FROM c }
} {3 G H}

# At one point (ticket #2759), a WHERE clause of the form "<column> IS NULL"
# on a virtual table was causing an assert() to fail in the compiler.
#
# "IS NULL" clauses should not be passed through to the virtual table
# implementation. They are handled by SQLite after the vtab returns it's
# data.
#
do_test vtab1.13-1 {
  execsql { 
    SELECT * FROM echo_c WHERE a IS NULL 
  }
} {}
do_test vtab1.13-2 {
  execsql { 
    INSERT INTO c VALUES(NULL, 15, 16);
    SELECT * FROM echo_c WHERE a IS NULL 
  }
} {{} 15 16}
do_test vtab1.13-3 {
  execsql { 
    INSERT INTO c VALUES(15, NULL, 16);
    SELECT * FROM echo_c WHERE b IS NULL 
  }
} {15 {} 16}
do_test vtab1.13-3 {
  execsql { 
    SELECT * FROM echo_c WHERE b IS NULL AND a = 15;
  }
} {15 {} 16}

unset -nocomplain echo_module_begin_fail
finish_test