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

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