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