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.361 2009/01/08 21:00:03 drh Exp $
*/
#include "sqliteInt.h"

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

        }else if( pOrTerm->leftCursor==iCur ){
          tempWC.a = pOrTerm;
          tempWC.nTerm = 1;
          bestIndex(pParse, &tempWC, pSrc, notReady, 0, &sTermCost);
        }else{
          continue;
        }




        rTotal += sTermCost.rCost;
        nRow += sTermCost.nRow;
        if( rTotal>=pCost->rCost ) break;
      }
      WHERETRACE(("... multi-index OR cost=%.9g nrow=%.9g\n",
                  rTotal, nRow));
      if( rTotal<pCost->rCost ){
        pCost->rCost = rTotal;
        pCost->nRow = nRow;
        pCost->plan.wsFlags = WHERE_MULTI_OR;

# 2008 August 01
#
# 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 test script checks malloc failures in WHERE clause analysis.
# 
# $Id: mallocK.test,v 1.3 2009/01/08 21:00:03 drh Exp $

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


set sql {SELECT * FROM t1, t2 WHERE (a=1 OR a=2)}
for {set x 1} {$x<5} {incr x} {
  append sql " AND b=y"
  do_malloc_test mallocK-1.$x -sqlbody $sql -sqlprep {
    CREATE TABLE t1(a,b);
    CREATE TABLE t2(x,y);
  }
}

set sql {SELECT * FROM t1 WHERE a GLOB 'xyz*' AND (a=1 OR a=2)}
for {set x 1} {$x<5} {incr x} {
  append sql " AND b!=$x"
  do_malloc_test mallocK-2.$x -sqlbody $sql -sqlprep {
    CREATE TABLE t1(a,b);
  }
}

set sql {SELECT * FROM t1 WHERE a BETWEEN 5 AND 10}
for {set x 1} {$x<5} {incr x} {
  append sql " AND b=$x"
  do_malloc_test mallocK-3.$x -sqlbody $sql -sqlprep {
    CREATE TABLE t1(a,b);
  }
}

set sql {SELECT * FROM t1 WHERE b=0}
for {set x 1} {$x<5} {incr x} {
  set term "(b=$x"
  for {set y 0} {$y<$x} {incr y} {
    append term " AND a!=$y"
  }
  append sql " OR $term)"
  do_malloc_test mallocK-4.$x -sqlbody $sql -sqlprep {
    CREATE TABLE t1(a,b);
  }
}

ifcapable vtab {
  set sql {SELECT * FROM t2 WHERE a MATCH 'xyz'}
  for {set x 1} {$x<5} {incr x} {
    append sql " AND b!=$x"
    do_malloc_test mallocK-5.$x -sqlbody $sql -tclprep {
      register_echo_module [sqlite3_connection_pointer db]
      db eval {
        CREATE TABLE t1(a,b);
        CREATE VIRTUAL TABLE t2 USING echo(t1);
      }
    }
  }

# 2008 December 30
#
# 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 testing the multi-index OR clause optimizer.
#
# $Id: where9.test,v 1.5 2009/01/08 21:00:03 drh Exp $

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

ifcapable !or_opt {
  finish_test
  return

    CREATE INDEX t2d ON t2(d,g);
    CREATE INDEX t2e ON t2(e,f,g);
    CREATE INDEX t2f ON t2(f,b,d,c);
    CREATE INDEX t2g ON t2(g,f);
    CREATE TABLE t3(x,y);
    INSERT INTO t3 VALUES(1,80);
    INSERT INTO t3 VALUES(2,80);
    CREATE TABLE t4(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
    INSERT INTO t4 SELECT * FROM t1;
    CREATE INDEX t4b ON t4(b);
    CREATE INDEX t4c ON t4(c);
  }
} {}

do_test where9-1.2.1 {
  count_steps {
    SELECT a FROM t1
     WHERE b IS NULL

    SELECT a FROM t1
     WHERE b IS NULL
        OR c IS NULL
        OR +d IS NULL
    ORDER BY a
  }
} {90 91 92 96 97 99 scan 98 sort 0}
do_test where9-1.2.5 {
breakpoint
  count_steps {
    SELECT a FROM t4
     WHERE b IS NULL
        OR c IS NULL
        OR d IS NULL
    ORDER BY a
  }
} {90 91 92 96 97 99 scan 98 sort 0}

do_test where9-1.3.1 {
  count_steps {
    SELECT a FROM t1
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
    ORDER BY a
  }
} {90 91 92 97 scan 0 sort 0}
do_test where9-1.3.2 {
  count_steps {
    SELECT a FROM t4
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
    ORDER BY a
  }
} {90 91 92 97 scan 98 sort 0}
do_test where9-1.3.3 {
  count_steps {
    SELECT a FROM t4
     WHERE (b NOT NULL AND c NOT NULL AND d IS NULL)
        OR (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
    ORDER BY a
  }
} {90 91 92 97 scan 98 sort 0}
do_test where9-1.3.4 {
  count_steps {
    SELECT a FROM t4
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
    ORDER BY a
  }
} {90 91 92 97 scan 98 sort 0}

do_test where9-1.4 {
  count_steps {
    SELECT a FROM t1
     WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
    ORDER BY a
  }
} {87 88 89 90 91 scan 0 sort 0}

  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a%100 IN (5,31,57,82,83,84,85,86,87);
    ROLLBACK;
  }
} {99 105 131 157 182 183 184 185 186 187}

do_test where9-6.6.1 {
  count_steps {
    BEGIN;
    DELETE FROM t1
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {scan 98 sort 0}   ;# DELETEs rows 90 91 92 97
do_test where9-6.6.2 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
    ROLLBACK;
  }
} {95 85 86 87 88 89 93 94 95 96 98 99}

do_test where9-6.6.3 {
  count_steps {
    BEGIN;
    UPDATE t1 SET a=a+100
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND +c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {scan 98 sort 0}   ;# Add 100 to rowids 90 91 92 97
do_test where9-6.6.4 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
    ROLLBACK;
  }
} {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}

do_test where9-6.7.1 {
  count_steps {
    BEGIN;
    DELETE FROM t1 NOT INDEXED
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {scan 98 sort 0}   ;# DELETEs rows 90 91 92 97
do_test where9-6.7.2 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a BETWEEN 85 AND 100;
    ROLLBACK;
  }
} {95 85 86 87 88 89 93 94 95 96 98 99}

do_test where9-6.7.3 {
  count_steps {
    BEGIN;
    UPDATE t1 NOT INDEXED SET a=a+100
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {scan 98 sort 0}   ;# Add 100 to rowids 90 91 92 97
do_test where9-6.7.4 {
  db eval {
    SELECT count(*) FROM t1 UNION ALL
    SELECT a FROM t1 WHERE a BETWEEN 85 AND 200;
    ROLLBACK;
  }
} {99 85 86 87 88 89 93 94 95 96 98 99 190 191 192 197}

do_test where9-6.8.1 {
  catchsql {
    DELETE FROM t1 INDEXED BY t1b
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {1 {cannot use index: t1b}}
do_test where9-6.8.2 {
  catchsql {
    UPDATE t1 INDEXED BY t1b SET a=a+100
     WHERE (b IS NULL AND c NOT NULL AND d NOT NULL)
        OR (b NOT NULL AND c IS NULL AND d NOT NULL)
        OR (b NOT NULL AND c NOT NULL AND d IS NULL)
  }
} {1 {cannot use index: t1b}}

finish_test