SQLite

# 2008 December 23
#
# 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.



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

ifcapable !or_opt {
  finish_test
  return

    FROM t302 JOIN t301 ON t302.c8 = t301.c8
    WHERE t302.c2 = 19571
      AND t302.c3 > 1287603136
      AND (t301.c4 = 1407449685622784
           OR t301.c8 = 1407424651264000)
   ORDER BY t302.c5 LIMIT 200;
} {
  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~5 rows)} 
  0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

finish_test

# 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.
#


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

ifcapable !or_opt {
  finish_test
  return

  do_execsql_test where9-3.1 {
    EXPLAIN QUERY PLAN
    SELECT t2.a FROM t1, t2
    WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)}
  }
  do_execsql_test where9-3.2 {
    EXPLAIN QUERY PLAN
    SELECT coalesce(t2.a,9999)
    FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
    WHERE t1.a=80
  } {
    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 
    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 
    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~5 rows)}
  }
} 

# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
#
do_test where9-4.1 {

ifcapable explain {
  # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
  # the former is an equality test which is expected to return fewer rows.
  #
  do_execsql_test where9-5.1 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
  } {
    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~2 rows)} 
    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~2 rows)}
  }

  # In contrast, b=1000 is preferred over any OR-clause.
  #
  do_execsql_test where9-5.2 {
    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
  } {

  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}}

############################################################################
# Test cases where terms inside an OR series are combined with AND terms
# external to the OR clause.  In other words, cases where
#
#              x AND (y OR z)
#
# is able to use indices on x,y and x,z, or indices y,x and z,x.
#
do_test where9-7.0 {
  execsql {
    CREATE TABLE t5(a, b, c, d, e, f, g, x, y);
    INSERT INTO t5
     SELECT a, b, c, e, d, f, g,
            CASE WHEN (a&1)!=0 THEN 'y' ELSE 'n' END,
            CASE WHEN (a&2)!=0 THEN 'y' ELSE 'n' END
       FROM t1;
    CREATE INDEX t5xb ON t5(x, b);
    CREATE INDEX t5xc ON t5(x, c);
    CREATE INDEX t5xd ON t5(x, d);
    CREATE INDEX t5xe ON t5(x, e);
    CREATE INDEX t5xf ON t5(x, f);
    CREATE INDEX t5xg ON t5(x, g);
    CREATE INDEX t5yb ON t5(y, b);
    CREATE INDEX t5yc ON t5(y, c);
    CREATE INDEX t5yd ON t5(y, d);
    CREATE INDEX t5ye ON t5(y, e);
    CREATE INDEX t5yf ON t5(y, f);
    CREATE INDEX t5yg ON t5(y, g);
    CREATE TABLE t6(a, b, c, e, d, f, g, x, y);
    INSERT INTO t6 SELECT * FROM t5;
    ANALYZE t5;
  }
} {}
do_test where9-7.1.1 {
  count_steps {
    SELECT a FROM t5 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
  }
} {79 81 83 scan 0 sort 1}
do_test where9-7.1.2 {
  execsql {
    SELECT a FROM t6 WHERE x='y' AND (b=913 OR c=27027) ORDER BY a;
  }
} {79 81 83}
do_test where9-7.1.3 {
  count_steps {
    SELECT a FROM t5 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
  }
} {80 scan 0 sort 1}
do_test where9-7.1.4 {
  execsql {
    SELECT a FROM t6 WHERE x='n' AND (b=913 OR c=27027) ORDER BY a;
  }
} {80}
do_test where9-7.2.1 {
  count_steps {
    SELECT a FROM t5 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
  }
} {83 scan 0 sort 1}
do_test where9-7.2.2 {
  execsql {
    SELECT a FROM t6 WHERE (x='y' OR y='y') AND b=913 ORDER BY a;
  }
} {83}
do_test where9-7.3.1 {
  count_steps {
    SELECT a FROM t5 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
  }
} {79 81 scan 0 sort 1}
do_test where9-7.3.2 {
  execsql {
    SELECT a FROM t6 WHERE (x='y' OR y='y') AND c=27027 ORDER BY a;
  }
} {79 81}


finish_test