test/analyzeA.test

   1 # 2013 August 3
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 #
  12 # This file contains automated tests used to verify that the current build
  13 # (which must be either ENABLE_STAT3 or ENABLE_STAT4) works with both stat3
  14 # and stat4 data.
  15 #
  16
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19 set testprefix analyzeA
  20
  21 ifcapable !stat4&&!stat3 {
  22   finish_test
  23   return
  24 }
  25
  26 # Populate the stat3 table according to the current contents of the db
  27 #
  28 proc populate_stat3 {{bDropTable 1}} {
  29   # Open a second connection on database "test.db" and run ANALYZE. If this
  30   # is an ENABLE_STAT3 build, this is all that is required to create and
  31   # populate the sqlite_stat3 table.
  32   #
  33   sqlite3 db2 test.db
  34   execsql { ANALYZE }
  35
  36   # Now, if this is an ENABLE_STAT4 build, create and populate the
  37   # sqlite_stat3 table based on the stat4 data gathered by the ANALYZE
  38   # above. Then drop the sqlite_stat4 table.
  39   #
  40   ifcapable stat4 {
  41     db2 func lindex lindex
  42     execsql {
  43       PRAGMA writable_schema = on;
  44       CREATE TABLE sqlite_stat3(tbl,idx,neq,nlt,ndlt,sample);
  45       INSERT INTO sqlite_stat3
  46       SELECT DISTINCT tbl, idx,
  47         lindex(neq,0), lindex(nlt,0), lindex(ndlt,0), test_extract(sample, 0)
  48       FROM sqlite_stat4;
  49     } db2
  50     if {$bDropTable} { execsql {DROP TABLE sqlite_stat4} db2 }
  51     execsql { PRAGMA writable_schema = off }
  52   }
  53
  54   # Modify the database schema cookie to ensure that the other connection
  55   # reloads the schema.
  56   #
  57   execsql {
  58     CREATE TABLE obscure_tbl_nm(x);
  59     DROP TABLE obscure_tbl_nm;
  60   } db2
  61   db2 close
  62 }
  63
  64 # Populate the stat4 table according to the current contents of the db
  65 #
  66 proc populate_stat4 {{bDropTable 1}} {
  67   sqlite3 db2 test.db
  68   execsql { ANALYZE }
  69
  70   ifcapable stat3 {
  71     execsql {
  72       PRAGMA writable_schema = on;
  73       CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample);
  74       INSERT INTO sqlite_stat4
  75       SELECT tbl, idx, neq, nlt, ndlt, sqlite_record(sample)
  76       FROM sqlite_stat3;
  77     } db2
  78     if {$bDropTable} { execsql {DROP TABLE sqlite_stat3} db2 }
  79     execsql { PRAGMA writable_schema = off }
  80   }
  81
  82   # Modify the database schema cookie to ensure that the other connection
  83   # reloads the schema.
  84   #
  85   execsql {
  86     CREATE TABLE obscure_tbl_nm(x);
  87     DROP TABLE obscure_tbl_nm;
  88   } db2
  89   db2 close
  90 }
  91
  92 # Populate the stat4 table according to the current contents of the db.
  93 # Leave deceptive data in the stat3 table. This data should be ignored
  94 # in favour of that from the stat4 table.
  95 #
  96 proc populate_both {} {
  97   ifcapable stat4 { populate_stat3 0 }
  98   ifcapable stat3 { populate_stat4 0 }
  99
 100   sqlite3 db2 test.db
 101   execsql {
 102     PRAGMA writable_schema = on;
 103     UPDATE sqlite_stat3 SET idx =
 104       CASE idx WHEN 't1b' THEN 't1c' ELSE 't1b'
 105     END;
 106     PRAGMA writable_schema = off;
 107     CREATE TABLE obscure_tbl_nm(x);
 108     DROP TABLE obscure_tbl_nm;
 109   } db2
 110   db2 close
 111 }
 112
 113 foreach {tn analyze_cmd} {
 114   1 populate_stat4
 115   2 populate_stat3
 116   3 populate_both
 117 } {
 118   reset_db
 119   do_test 1.$tn.1 {
 120     execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT) }
 121     for {set i 0} {$i < 100} {incr i} {
 122       set c [expr int(pow(1.1,$i)/100)]
 123       set b [expr 125 - int(pow(1.1,99-$i))/100]
 124       execsql {INSERT INTO t1 VALUES($i, $b, $c)}
 125     }
 126   } {}
 127
 128   execsql { CREATE INDEX t1b ON t1(b) }
 129   execsql { CREATE INDEX t1c ON t1(c) }
 130   $analyze_cmd
 131
 132   do_execsql_test 1.$tn.2.1 { SELECT count(*) FROM t1 WHERE b=31 } 1
 133   do_execsql_test 1.$tn.2.2 { SELECT count(*) FROM t1 WHERE c=0  } 49
 134   do_execsql_test 1.$tn.2.3 { SELECT count(*) FROM t1 WHERE b=125  } 49
 135   do_execsql_test 1.$tn.2.4 { SELECT count(*) FROM t1 WHERE c=16  } 1
 136
 137   do_eqp_test 1.$tn.2.5 {
 138     SELECT * FROM t1 WHERE b = 31 AND c = 0;
 139   } {SEARCH TABLE t1 USING INDEX t1b (b=?)}
 140   do_eqp_test 1.$tn.2.6 {
 141     SELECT * FROM t1 WHERE b = 125 AND c = 16;
 142   } {SEARCH TABLE t1 USING INDEX t1c (c=?)}
 143
 144   do_execsql_test 1.$tn.3.1 {
 145     SELECT count(*) FROM t1 WHERE b BETWEEN 0 AND 50
 146   } {6}
 147   do_execsql_test 1.$tn.3.2 {
 148     SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 50
 149   } {90}
 150   do_execsql_test 1.$tn.3.3 {
 151     SELECT count(*) FROM t1 WHERE b BETWEEN 75 AND 125
 152   } {90}
 153   do_execsql_test 1.$tn.3.4 {
 154     SELECT count(*) FROM t1 WHERE c BETWEEN 75 AND 125
 155   } {6}
 156
 157   do_eqp_test 1.$tn.3.5 {
 158     SELECT * FROM t1 WHERE b BETWEEN 0 AND 50 AND c BETWEEN 0 AND 50
 159   } {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}
 160
 161   do_eqp_test 1.$tn.3.6 {
 162     SELECT * FROM t1 WHERE b BETWEEN 75 AND 125 AND c BETWEEN 75 AND 125
 163   } {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}
 164
 165   do_eqp_test 1.$tn.3.7 {
 166     SELECT * FROM t1 WHERE b BETWEEN +0 AND +50 AND c BETWEEN +0 AND +50
 167   } {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}
 168
 169   do_eqp_test 1.$tn.3.8 {
 170     SELECT * FROM t1
 171      WHERE b BETWEEN cast('0' AS int) AND cast('50.0' AS real)
 172        AND c BETWEEN cast('0' AS numeric) AND cast('50.0' AS real)
 173   } {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}
 174
 175   do_eqp_test 1.$tn.3.9 {
 176     SELECT * FROM t1 WHERE b BETWEEN +75 AND +125 AND c BETWEEN +75 AND +125
 177   } {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}
 178
 179   do_eqp_test 1.$tn.3.10 {
 180     SELECT * FROM t1
 181      WHERE b BETWEEN cast('75' AS int) AND cast('125.0' AS real)
 182        AND c BETWEEN cast('75' AS numeric) AND cast('125.0' AS real)
 183   } {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}
 184 }
 185
 186 finish_test