SQLite

# 2011 April 1
#
# 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.
# This file implements tests for the ANALYZE command when an idnex
# name is given as the argument.
#

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

# There is nothing to test if ANALYZE is disable for this build.
#
ifcapable {!analyze} {
  finish_test
  return
}

# Generate some test data
#
do_test analyze7-1.0 {
  execsql {
    CREATE TABLE sequence(x INTEGER PRIMARY KEY);
    INSERT INTO sequence VALUES(1);
    INSERT INTO sequence VALUES(2);
    INSERT INTO sequence SELECT x+2 FROM sequence;
    INSERT INTO sequence SELECT x+4 FROM sequence;
    INSERT INTO sequence SELECT x+8 FROM sequence;
    INSERT INTO sequence SELECT x+16 FROM sequence;
    INSERT INTO sequence SELECT x+32 FROM sequence;
    INSERT INTO sequence SELECT x+64 FROM sequence;
    INSERT INTO sequence SELECT x+128 FROM sequence;
    INSERT INTO sequence SELECT x+256 FROM sequence;
    CREATE TABLE t1(a,b,c,d);
    CREATE INDEX t1a ON t1(a);
    CREATE INDEX t1b ON t1(b);
    CREATE INDEX t1cd ON t1(c,d);
    INSERT INTO t1 SELECT x, x, x/100, x FROM sequence;
    EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;
  }
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~10 rows)}}
do_test analyze7-1.1 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~10 rows)}}
do_test analyze7-1.2 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~10 rows)}}

# Run an analyze on one of the three indices.  Verify that this
# effects the row-count estimate on the one query that uses that
# one index.
#
do_test analyze7-2.0 {
  execsql {ANALYZE t1a;}
  db cache flush
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
do_test analyze7-2.1 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~10 rows)}}
do_test analyze7-2.2 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~10 rows)}}

# Verify that since the query planner now things that t1a is more
# selective than t1b, it prefers to use t1a.
#
do_test analyze7-2.3 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}

# Run an analysis on another of the three indices.  Verify  that this
# new analysis works and does not disrupt the previous analysis.
#
do_test analyze7-3.0 {
  execsql {ANALYZE t1cd;}
  db cache flush;
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
do_test analyze7-3.1 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~10 rows)}}
do_test analyze7-3.2 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~102 rows)}}
do_test analyze7-3.3 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
do_test analyze7-3.4 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}}
do_test analyze7-3.5 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
do_test analyze7-3.6 {
  execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123}
} {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?) (~1 rows)}}

finish_test