Return to analyze4.test CVS log

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

Annotation of embedaddon/sqlite3/test/analyze4.test, revision 1.1

1.1     ! misho       1: # 2011 January 04
        !             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 implements regression tests for SQLite library. This file 
        !            13: # implements tests for ANALYZE to verify that multiple rows containing
        !            14: # a NULL value count as distinct rows for the purposes of analyze 
        !            15: # statistics.
        !            16: #
        !            17: # Also include test cases for collating sequences on indices.
        !            18: #
        !            19: 
        !            20: set testdir [file dirname $argv0]
        !            21: source $testdir/tester.tcl
        !            22: 
        !            23: do_test analyze4-1.0 {
        !            24:   db eval {
        !            25:     CREATE TABLE t1(a,b);
        !            26:     CREATE INDEX t1a ON t1(a);
        !            27:     CREATE INDEX t1b ON t1(b);
        !            28:     INSERT INTO t1 VALUES(1,NULL);
        !            29:     INSERT INTO t1 SELECT a+1, b FROM t1;
        !            30:     INSERT INTO t1 SELECT a+2, b FROM t1;
        !            31:     INSERT INTO t1 SELECT a+4, b FROM t1;
        !            32:     INSERT INTO t1 SELECT a+8, b FROM t1;
        !            33:     INSERT INTO t1 SELECT a+16, b FROM t1;
        !            34:     INSERT INTO t1 SELECT a+32, b FROM t1;
        !            35:     INSERT INTO t1 SELECT a+64, b FROM t1;
        !            36:     ANALYZE;
        !            37:   }
        !            38: 
        !            39:   # Should choose the t1a index since it is more specific than t1b.
        !            40:   db eval {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=5 AND b IS NULL}
        !            41: } {0 0 0 {SEARCH TABLE t1 USING INDEX t1a (a=?) (~1 rows)}}
        !            42: 
        !            43: # Verify that the t1b index shows that it does not narrow down the
        !            44: # search any at all.
        !            45: #
        !            46: do_test analyze4-1.1 {
        !            47:   db eval {
        !            48:     SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
        !            49:   }
        !            50: } {t1a {128 1} t1b {128 128}}
        !            51: 
        !            52: # Change half of the b values from NULL to a constant.  Verify
        !            53: # that the number of rows selected in stat1 is half the total 
        !            54: # number of rows.
        !            55: #
        !            56: do_test analyze4-1.2 {
        !            57:   db eval {
        !            58:     UPDATE t1 SET b='x' WHERE a%2;
        !            59:     ANALYZE;
        !            60:     SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
        !            61:   }
        !            62: } {t1a {128 1} t1b {128 64}}
        !            63: 
        !            64: # Change the t1.b values all back to NULL.  Add columns t1.c and t1.d.
        !            65: # Create a multi-column indices using t1.b and verify that ANALYZE 
        !            66: # processes them correctly.
        !            67: #
        !            68: do_test analyze4-1.3 {
        !            69:   db eval {
        !            70:     UPDATE t1 SET b=NULL;
        !            71:     ALTER TABLE t1 ADD COLUMN c;
        !            72:     ALTER TABLE t1 ADD COLUMN d;
        !            73:     UPDATE t1 SET c=a/4, d=a/2;
        !            74:     CREATE INDEX t1bcd ON t1(b,c,d);
        !            75:     CREATE INDEX t1cdb ON t1(c,d,b);
        !            76:     CREATE INDEX t1cbd ON t1(c,b,d);
        !            77:     ANALYZE;
        !            78:     SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t1' ORDER BY idx;
        !            79:   }
        !            80: } {t1a {128 1} t1b {128 128} t1bcd {128 128 4 2} t1cbd {128 4 4 2} t1cdb {128 4 2 2}}
        !            81: 
        !            82: # Verify that collating sequences are taken into account when computing
        !            83: # ANALYZE statistics.
        !            84: #
        !            85: do_test analyze4-2.0 {
        !            86:   db eval {
        !            87:     CREATE TABLE t2(
        !            88:       x INTEGER PRIMARY KEY,
        !            89:       a TEXT COLLATE nocase,
        !            90:       b TEXT COLLATE rtrim,
        !            91:       c TEXT COLLATE binary
        !            92:     );
        !            93:     CREATE INDEX t2a ON t2(a);
        !            94:     CREATE INDEX t2b ON t2(b);
        !            95:     CREATE INDEX t2c ON t2(c);
        !            96:     CREATE INDEX t2c2 ON t2(c COLLATE nocase);
        !            97:     CREATE INDEX t2c3 ON t2(c COLLATE rtrim);
        !            98:     INSERT INTO t2 VALUES(1, 'abc', 'abc', 'abc');
        !            99:     INSERT INTO t2 VALUES(2, 'abC', 'abC', 'abC');
        !           100:     INSERT INTO t2 VALUES(3, 'abc ', 'abc ', 'abc ');
        !           101:     INSERT INTO t2 VALUES(4, 'abC ', 'abC ', 'abC ');
        !           102:     INSERT INTO t2 VALUES(5, 'aBc', 'aBc', 'aBc');
        !           103:     INSERT INTO t2 VALUES(6, 'aBC', 'aBC', 'aBC');
        !           104:     INSERT INTO t2 VALUES(7, 'aBc ', 'aBc ', 'aBc ');
        !           105:     INSERT INTO t2 VALUES(8, 'aBC ', 'aBC ', 'aBC ');
        !           106:     ANALYZE;
        !           107:     SELECT idx, stat FROM sqlite_stat1 WHERE tbl='t2' ORDER BY idx;
        !           108:   }
        !           109: } {t2a {8 4} t2b {8 2} t2c {8 1} t2c2 {8 4} t2c3 {8 2}}
        !           110: 
        !           111: finish_test