Return to analyze7.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

Annotation of embedaddon/sqlite3/test/analyze7.test, revision 1.1.1.1

1.1       misho       1: # 2011 April 1
                      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: # This file implements regression tests for SQLite library.
                     12: # This file implements tests for the ANALYZE command when an idnex
                     13: # name is given as the argument.
                     14: #
                     15: 
                     16: set testdir [file dirname $argv0]
                     17: source $testdir/tester.tcl
                     18: 
                     19: # There is nothing to test if ANALYZE is disable for this build.
                     20: #
                     21: ifcapable {!analyze||!vtab} {
                     22:   finish_test
                     23:   return
                     24: }
                     25: 
                     26: # Generate some test data
                     27: #
                     28: do_test analyze7-1.0 {
                     29:   register_wholenumber_module db
                     30:   execsql {
                     31:     CREATE TABLE t1(a,b,c,d);
                     32:     CREATE INDEX t1a ON t1(a);
                     33:     CREATE INDEX t1b ON t1(b);
                     34:     CREATE INDEX t1cd ON t1(c,d);
                     35:     CREATE VIRTUAL TABLE nums USING wholenumber;
                     36:     INSERT INTO t1 SELECT value, value, value/100, value FROM nums
                     37:                     WHERE value BETWEEN 1 AND 256;
                     38:     EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;
                     39:   }
                     40: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~10 rows)}}
                     41: do_test analyze7-1.1 {
                     42:   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
                     43: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~10 rows)}}
                     44: do_test analyze7-1.2 {
                     45:   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
                     46: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~10 rows)}}
                     47: 
                     48: # Run an analyze on one of the three indices.  Verify that this
                     49: # effects the row-count estimate on the one query that uses that
                     50: # one index.
                     51: #
                     52: do_test analyze7-2.0 {
                     53:   execsql {ANALYZE t1a;}
                     54:   db cache flush
                     55:   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
                     56: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
                     57: do_test analyze7-2.1 {
                     58:   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
                     59: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~10 rows)}}
                     60: do_test analyze7-2.2 {
                     61:   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
                     62: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~10 rows)}}
                     63: 
                     64: # Verify that since the query planner now things that t1a is more
                     65: # selective than t1b, it prefers to use t1a.
                     66: #
                     67: do_test analyze7-2.3 {
                     68:   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
                     69: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
                     70: 
                     71: # Run an analysis on another of the three indices.  Verify  that this
                     72: # new analysis works and does not disrupt the previous analysis.
                     73: #
                     74: do_test analyze7-3.0 {
                     75:   execsql {ANALYZE t1cd;}
                     76:   db cache flush;
                     77:   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123;}
                     78: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
                     79: do_test analyze7-3.1 {
                     80:   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b=123;}
                     81: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~10 rows)}}
                     82: do_test analyze7-3.2.1 {
                     83:   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=?;}
                     84: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~86 rows)}}
                     85: ifcapable stat3 {
                     86:   # If ENABLE_STAT3 is defined, SQLite comes up with a different estimated
                     87:   # row count for (c=2) than it does for (c=?).
                     88:   do_test analyze7-3.2.2 {
                     89:     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
                     90:   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~57 rows)}}
                     91: } else {
                     92:   # If ENABLE_STAT3 is not defined, the expected row count for (c=2) is the
                     93:   # same as that for (c=?).
                     94:   do_test analyze7-3.2.3 {
                     95:     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=2;}
                     96:   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=?) (~86 rows)}}
                     97: }
                     98: do_test analyze7-3.3 {
                     99:   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND b=123}
                    100: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
                    101: ifcapable {!stat3} {
                    102:   do_test analyze7-3.4 {
                    103:     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND b=123}
                    104:   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~2 rows)}}
                    105:   do_test analyze7-3.5 {
                    106:     execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123 AND c=123}
                    107:   } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
                    108: }
                    109: do_test analyze7-3.6 {
                    110:   execsql {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE c=123 AND d=123 AND b=123}
                    111: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1cd (c=? AND d=?) (~1 rows)}}
                    112: 
                    113: finish_test