SQLite

** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is responsible 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.353 2008/12/30 15:26:30 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** Trace output macros
*/
#if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)

        /* Figure out the collation sequence required from an index for
        ** it to be useful for optimising expression pX. Store this
        ** value in variable pColl.
        */
        assert(pX->pLeft);
        pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
        assert(pColl || pParse->nErr);



        for(j=0; pIdx->aiColumn[j]!=iColumn; j++){
          if( NEVER(j>=pIdx->nColumn) ) return 0;
        }
        if( pColl && sqlite3StrICmp(pColl->zName, pIdx->azColl[j]) ) continue;
      }
      return pTerm;
    }
  }
  return 0;
}

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The focus
# is testing of where.c. More specifically, the focus is the optimization
# of WHERE clauses that feature the OR operator.
#
# $Id: where8.test,v 1.3 2008/12/30 15:26:30 danielk1977 Exp $

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

# Test organization:
#
#   where8-1.*: Tests to demonstrate simple cases work with a single table

#-----------------------------------------------------------------------
# The following tests - where8-4.* - verify that adding or removing 
# indexes does not change the results returned by various queries.
#
do_test where8-4.1 {
  execsql {
    BEGIN;
    CREATE TABLE t3(a INTEGER, b REAL, c TEXT);
    CREATE TABLE t4(f INTEGER, g REAL, h TEXT);
    INSERT INTO t3 VALUES('hills', NULL, 1415926535);
    INSERT INTO t3 VALUES('and', 'of', NULL);
    INSERT INTO t3 VALUES('have', 'towering', 53594.08128);
    INSERT INTO t3 VALUES(NULL, 45.64856692, 'Not');
    INSERT INTO t3 VALUES('same', 5028841971, NULL);
    INSERT INTO t3 VALUES('onlookers', 'in', 8214808651);
    INSERT INTO t3 VALUES(346.0348610, 2643383279, NULL);
    INSERT INTO t3 VALUES(1415926535, 'of', 'are');
    INSERT INTO t3 VALUES(NULL, 0.4811174502, 'snapshots');
    INSERT INTO t3 VALUES('over', 'the', 8628034825);
    INSERT INTO t3 VALUES(8628034825, 66.59334461, 2847564.823);
    INSERT INTO t3 VALUES('onlookers', 'same', 'and');
    INSERT INTO t3 VALUES(NULL, 'light', 6939937510);
    INSERT INTO t3 VALUES('from', 'their', 'viewed');
    INSERT INTO t3 VALUES('from', 'Alpine', 'snapshots');
    INSERT INTO t3 VALUES('from', 'sometimes', 'unalike');
    INSERT INTO t3 VALUES(1339.360726, 'light', 'have');
    INSERT INTO t3 VALUES(6939937510, 3282306647, 'other');
    INSERT INTO t3 VALUES('paintings', 8628034825, 'all');
    INSERT INTO t3 VALUES('paintings', NULL, 'same');
    INSERT INTO t3 VALUES('Alpine', 378678316.5, 'unalike');
    INSERT INTO t3 VALUES('Alpine', NULL, 'same');
    INSERT INTO t3 VALUES(1339.360726, 2847564.823, 'over');
    INSERT INTO t3 VALUES('villages', 'their', 'have');
    INSERT INTO t3 VALUES('unalike', 'remarkably', 'in');
    INSERT INTO t3 VALUES('and', 8979323846, 'and');
    INSERT INTO t3 VALUES(NULL, 1415926535, 'an');
    INSERT INTO t3 VALUES(271.2019091, 8628034825, 0.4811174502);
    INSERT INTO t3 VALUES('all', 3421170679, 'the');
    INSERT INTO t3 VALUES('Not', 'and', 1415926535);
    INSERT INTO t3 VALUES('of', 'other', 'light');
    INSERT INTO t3 VALUES(NULL, 'towering', 'Not');
    INSERT INTO t3 VALUES(346.0348610, NULL, 'other');
    INSERT INTO t3 VALUES('Not', 378678316.5, NULL);
    INSERT INTO t3 VALUES('snapshots', 8628034825, 'of');
    INSERT INTO t3 VALUES(3282306647, 271.2019091, 'and');
    INSERT INTO t3 VALUES(50.58223172, 378678316.5, 5028841971);
    INSERT INTO t3 VALUES(50.58223172, 2643383279, 'snapshots');
    INSERT INTO t3 VALUES('writings', 8979323846, 8979323846);
    INSERT INTO t3 VALUES('onlookers', 'his', 'in');
    INSERT INTO t3 VALUES('unalike', 8628034825, 1339.360726);
    INSERT INTO t3 VALUES('of', 'Alpine', 'and');
    INSERT INTO t3 VALUES('onlookers', NULL, 'from');
    INSERT INTO t3 VALUES('writings', 'it', 1339.360726);
    INSERT INTO t3 VALUES('it', 'and', 'villages');
    INSERT INTO t3 VALUES('an', 'the', 'villages');
    INSERT INTO t3 VALUES(8214808651, 8214808651, 'same');
    INSERT INTO t3 VALUES(346.0348610, 'light', 1415926535);
    INSERT INTO t3 VALUES(NULL, 8979323846, 'and');
    INSERT INTO t3 VALUES(NULL, 'same', 1339.360726);
    INSERT INTO t4 VALUES('his', 'from', 'an');
    INSERT INTO t4 VALUES('snapshots', 'or', NULL);
    INSERT INTO t4 VALUES('Alpine', 'have', 'it');
    INSERT INTO t4 VALUES('have', 'peak', 'remarkably');
    INSERT INTO t4 VALUES('hills', NULL, 'Not');
    INSERT INTO t4 VALUES('same', 'from', 2643383279);
    INSERT INTO t4 VALUES('have', 'angle', 8628034825);
    INSERT INTO t4 VALUES('sometimes', 'it', 2847564.823);
    INSERT INTO t4 VALUES(0938446095, 'peak', 'of');
    INSERT INTO t4 VALUES(8628034825, 'and', 'same');
    INSERT INTO t4 VALUES('and', 271.2019091, 'their');
    INSERT INTO t4 VALUES('the', 'of', 'remarkably');
    INSERT INTO t4 VALUES('and', 3421170679, 1415926535);
    INSERT INTO t4 VALUES('and', 'in', 'all');
    INSERT INTO t4 VALUES(378678316.5, 0.4811174502, 'snapshots');
    INSERT INTO t4 VALUES('it', 'are', 'have');
    INSERT INTO t4 VALUES('angle', 'snapshots', 378678316.5);
    INSERT INTO t4 VALUES('from', 1415926535, 8628034825);
    INSERT INTO t4 VALUES('snapshots', 'angle', 'have');
    INSERT INTO t4 VALUES(3421170679, 0938446095, 'Not');
    INSERT INTO t4 VALUES('peak', NULL, 0.4811174502);
    INSERT INTO t4 VALUES('same', 'have', 'Alpine');
    INSERT INTO t4 VALUES(271.2019091, 66.59334461, 0938446095);
    INSERT INTO t4 VALUES(8979323846, 'his', 'an');
    INSERT INTO t4 VALUES(NULL, 'and', 3282306647);
    INSERT INTO t4 VALUES('remarkably', NULL, 'Not');
    INSERT INTO t4 VALUES('villages', 4543.266482, 'his');
    INSERT INTO t4 VALUES(2643383279, 'paintings', 'onlookers');
    INSERT INTO t4 VALUES(1339.360726, 'of', 'the');
    INSERT INTO t4 VALUES('peak', 'other', 'peak');
    INSERT INTO t4 VALUES('it', 'or', 8979323846);
    INSERT INTO t4 VALUES('onlookers', 'Not', 'towering');
    INSERT INTO t4 VALUES(NULL, 'peak', 'Not');
    INSERT INTO t4 VALUES('of', 'have', 6939937510);
    INSERT INTO t4 VALUES('light', 'hills', 0.4811174502);
    INSERT INTO t4 VALUES(5028841971, 'Not', 'it');
    INSERT INTO t4 VALUES('and', 'Not', NULL);
    INSERT INTO t4 VALUES(346.0348610, 'villages', NULL);
    INSERT INTO t4 VALUES(8979323846, NULL, 6939937510);
    INSERT INTO t4 VALUES('an', 'light', 'peak');
    INSERT INTO t4 VALUES(5028841971, 6939937510, 'light');
    INSERT INTO t4 VALUES('sometimes', 'peak', 'peak');
    INSERT INTO t4 VALUES(378678316.5, 5028841971, 'an');
    INSERT INTO t4 VALUES(378678316.5, 'his', 'Alpine');
    INSERT INTO t4 VALUES('from', 'of', 'all');
    INSERT INTO t4 VALUES(0938446095, 'same', NULL);
    INSERT INTO t4 VALUES(0938446095, 'Alpine', NULL);
    INSERT INTO t4 VALUES('his', 'of', 378678316.5);
    INSERT INTO t4 VALUES(271.2019091, 'viewed', 3282306647);
    INSERT INTO t4 VALUES('hills', 'all', 'peak');
    COMMIT;
  }
} {}

catch {unset results}
set A 2
foreach idxsql {
  { 
    /* No indexes */ 
  } {
    CREATE INDEX i5 ON t3(a);
  } {
    CREATE INDEX i5 ON t3(a, b);
    CREATE INDEX i6 ON t4(f);
  } {
    CREATE UNIQUE INDEX i5 ON t3(a, b);
    CREATE INDEX i7 ON t3(c);
    CREATE INDEX i6 ON t4(f);
    CREATE INDEX i8 ON t4(h);
  } {
    CREATE INDEX i5 ON t3(a, b, c);
    CREATE INDEX i6 ON t4(f, g, h);
    CREATE INDEX i7 ON t3(c, b, a);
    CREATE INDEX i8 ON t4(h, g, f);
  }
} {

  execsql {
    DROP INDEX IF EXISTS i5;
    DROP INDEX IF EXISTS i6;
    DROP INDEX IF EXISTS i7;
    DROP INDEX IF EXISTS i8;
  }
  execsql $idxsql

  foreach {B sql} {
 1  { SELECT * FROM t3 WHERE c LIKE b }
 2  { SELECT * FROM t3 WHERE c||'' LIKE 'the%' }
 3  { SELECT * FROM t3 WHERE rowid LIKE '12%' }
 4  { SELECT * FROM t3 WHERE +c LIKE 'the%' }
 5  { SELECT * FROM t3 WHERE c LIKE 'the%' }
 6  { SELECT * FROM t3 WHERE c GLOB '*llo' }

 7  { SELECT * FROM t3 WHERE a = 'angle' }
 8  { SELECT * FROM t3 WHERE a = 'it' OR b = 6939937510 }
 9  { SELECT * FROM t3, t4 WHERE a = 'painting' OR a = 'are' OR a = f }
10  { SELECT * FROM t3, t4 WHERE a = 'all' OR a = 'and' OR a = h }
11  { SELECT * FROM t3, t4 WHERE a < 'of' OR b > 346 AND c IS NULL }
12  { SELECT * FROM t3, t4 WHERE 'the' > a OR b > 'have' AND c = 1415926535 }

13  { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a = 3421170679 }
14  { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a IS NULL }
15  { SELECT * FROM t3 WHERE c > 'one' OR c >= 'one' OR c LIKE 'one%' }
16  { SELECT * FROM t3 WHERE c > 'one' OR c = c OR c = a }
17  { SELECT * FROM t3 WHERE c IS NULL OR a >= 'peak' }
18  { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') OR a>1 }
19  { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') AND a>1 }
20  { SELECT * FROM t3 WHERE c IS NULL AND a>'one' }
21  { SELECT * FROM t3 WHERE c IS NULL OR a>'one' }

  } {
    do_test where8-4.$A.$B.1 {
      set R [execsql $sql]
      if {![info exists results($B)]} {
        set results($B) $R
      }
      list
    } {}

    do_test where8-4.$A.$B.2 { lsort $R } [lsort $results($B)]
  }
  incr A
}

finish_test