Artifact ff7a3b4631c896a51e7404268796c61d74117e9f:

• File test/analyze8.test — part of check-in [7e914aa9] at 2011-08-06 01:22:50 on branch query-planner-tweaks — Fix a compiler warning when STAT2 is off. More test cases. Fix legacy tests to deal with the new STAT2 logic. (user: drh size: 4555)

# 2011 August 5
#
# 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 under STAT2.
# Testing the logic that computes the number of copies of each sample.
#

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

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

# Generate some test data
#
do_test analyze8-1.0 {
  set x 100
  set y 1
  set ycnt 0
  set yinc 10
  execsql {
    CREATE TABLE t1(x,y);
    ANALYZE;
    BEGIN;
    CREATE INDEX t1x ON t1(x);
    CREATE INDEX t1y ON t1(y);
  }
  for {set i 0} {$i<20} {incr i} {
    for {set j 0} {$j<300} {incr j} {
      execsql {INSERT INTO t1 VALUES($x,$y)}
      incr ycnt
      if {$ycnt>=$yinc} {set ycnt 0; incr y}
    }
    for {set j 0} {$j<100} {incr j} {
      incr x
      execsql {INSERT INTO t1 VALUES($x,$y)}
      incr ycnt
      if {$ycnt>=$yinc} {set ycnt 0; incr y}
    }
  }
  execsql {
    COMMIT;
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno;
  }
} {200 301 400 301 600 301 800 301 1000 301 1200 301 1400 301 1600 301 1800 301 2000 301}
do_test analyze8-1.1 {
  execsql {
    SELECT count(*) FROM t1 WHERE x=200;
  }
} {301}

do_test analyze8-2.0 {
  execsql {
    BEGIN;
    DELETE FROM t1;
  }
  for {set x 1} {$x<200} {incr x} {
    execsql {INSERT INTO t1 VALUES($x,$x)}
  }
  for {set i 0} {$i<200} {incr i} {
    execsql {INSERT INTO t1 VALUES(999,999)}
  }
  execsql {
    COMMIT;
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno;
  }
} {20 1 60 1 100 1 140 1 180 1 999 200 999 200 999 200 999 200 999 200}
do_test analyze8-2.1 {
  for {set i 0} {$i<200} {incr i} {
    execsql {INSERT INTO t1 VALUES(0,999)}
  }
  execsql {
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno;
  }
} {0 200 0 200 0 200 10 1 70 1 130 1 190 1 999 200 999 200 999 200}

do_test analyze8-3.0 {
  execsql {
    BEGIN;
    DROP TABLE t1;
    CREATE TABLE t1(a,b);
    CREATE INDEX t1all ON t1(a,b);
    INSERT INTO t1 VALUES(0,1);
    INSERT INTO t1 VALUES(0,2);
    INSERT INTO t1 VALUES(0,3);
    INSERT INTO t1 VALUES(1,4);
    INSERT INTO t1 SELECT a+2, b+4 FROM t1;
    INSERT INTO t1 SELECT a+4, b+8 FROM t1;
    INSERT INTO t1 SELECT a+8, b+16 FROM t1;
    COMMIT;
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
  }
} {0 3 2 3 4 3 5 1 6 3 8 3 10 3 12 3 13 1 14 3}
do_test analyze8-3.1 {
  execsql {
    DELETE FROM t1;
    INSERT INTO t1 VALUES(1,1);
    INSERT INTO t1 VALUES(2,2);
    INSERT INTO t1 SELECT a+2, b+2 FROM t1;
    INSERT INTO t1 SELECT a+4, b+4 FROM t1;
    INSERT INTO t1 SELECT a+8, b+8 FROM t1;
    INSERT INTO t1 SELECT a+16, b+16 FROM t1;
    DELETE FROM t1 WHERE a>21;
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
  }
} {2 1 4 1 6 1 8 1 10 1 12 1 14 1 16 1 18 1 20 1}
do_test analyze8-3.2 {
  execsql {
    UPDATE t1 SET a=123;
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
  }
} {123 21 123 21 123 21 123 21 123 21 123 21 123 21 123 21 123 21 123 21}
do_test analyze8-3.3 {
  execsql {
    DELETE FROM t1 WHERE b=1 OR b=2;
    ANALYZE;
    SELECT count(*) FROM t1;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
  }
} {19}
do_test analyze8-3.4 {
  execsql {
    UPDATE t1 SET a=b;
    INSERT INTO t1 VALUES(1,1);
    INSERT INTO t1 VALUES(2,2);
    INSERT INTO t1 SELECT a, b FROM t1;
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
  }
} {2 2 4 2 6 2 8 2 10 2 12 2 14 2 16 2 18 2 20 2}
do_test analyze8-3.5 {
  execsql {
    UPDATE t1 SET a=1 WHERE b<20;
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
  }
} {1 38 1 38 1 38 1 38 1 38 1 38 1 38 1 38 1 38 20 2}
do_test analyze8-3.6 {
  execsql {
    UPDATE t1 SET a=b;
    UPDATE t1 SET a=20 WHERE b>2;
    ANALYZE;
    SELECT sample, cnt FROM sqlite_stat2 WHERE idx='t1all' ORDER BY sampleno;
  }
} {2 2 20 38 20 38 20 38 20 38 20 38 20 38 20 38 20 38 20 38}