Return to whereA.test CVS log

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

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

1.1     ! misho       1: # 2009 February 23
        !             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. The
        !            12: # focus of this file is testing the reverse_select_order pragma.
        !            13: #
        !            14: # $Id: whereA.test,v 1.3 2009/06/10 19:33:29 drh Exp $
        !            15: 
        !            16: set testdir [file dirname $argv0]
        !            17: source $testdir/tester.tcl
        !            18: 
        !            19: do_test whereA-1.1 {
        !            20:   db eval {
        !            21:     CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
        !            22:     INSERT INTO t1 VALUES(1,2,3);
        !            23:     INSERT INTO t1 values(2,'hello','world');
        !            24:     INSERT INTO t1 VALUES(3,4.53,NULL);
        !            25:     SELECT * FROM t1
        !            26:   }
        !            27: } {1 2 3 2 hello world 3 4.53 {}}
        !            28: do_test whereA-1.2 {
        !            29:   db eval {
        !            30:     PRAGMA reverse_unordered_selects=1;
        !            31:     SELECT * FROM t1;
        !            32:   }
        !            33: } {3 4.53 {} 2 hello world 1 2 3}
        !            34: 
        !            35: do_test whereA-1.3 {
        !            36:   db close
        !            37:   sqlite3 db test.db
        !            38:   db eval {
        !            39:     PRAGMA reverse_unordered_selects=1;
        !            40:     SELECT * FROM t1;
        !            41:   }
        !            42: } {3 4.53 {} 2 hello world 1 2 3}
        !            43: do_test whereA-1.4 {
        !            44:   db close
        !            45:   sqlite3 db test.db
        !            46:   db eval {
        !            47:     PRAGMA reverse_unordered_selects=1;
        !            48:     SELECT * FROM t1 ORDER BY rowid;
        !            49:   }
        !            50: } {1 2 3 2 hello world 3 4.53 {}}
        !            51: do_test whereA-1.5 {
        !            52:   db eval {
        !            53:     VACUUM;
        !            54:     SELECT * FROM t1 ORDER BY rowid;
        !            55:   }
        !            56: } {1 2 3 2 hello world 3 4.53 {}}
        !            57: do_test whereA-1.6 {
        !            58:   db eval {
        !            59:     PRAGMA reverse_unordered_selects;
        !            60:   }
        !            61: } {1}
        !            62: do_test whereA-1.7 {
        !            63:   db close
        !            64:   sqlite3 db test.db
        !            65:   db eval {
        !            66:     PRAGMA reverse_unordered_selects=1;
        !            67:     VACUUM;
        !            68:     SELECT * FROM t1;
        !            69:   }
        !            70: } {3 4.53 {} 2 hello world 1 2 3}
        !            71: 
        !            72: do_test whereA-2.1 {
        !            73:   db eval {
        !            74:     PRAGMA reverse_unordered_selects=0;
        !            75:     SELECT * FROM t1 WHERE a>0;
        !            76:   }
        !            77: } {1 2 3 2 hello world 3 4.53 {}}
        !            78: do_test whereA-2.2 {
        !            79:   db eval {
        !            80:     PRAGMA reverse_unordered_selects=1;
        !            81:     SELECT * FROM t1 WHERE a>0;
        !            82:   }
        !            83: } {3 4.53 {} 2 hello world 1 2 3}
        !            84: 
        !            85: do_test whereA-2.3 {
        !            86:   db eval {
        !            87:     PRAGMA reverse_unordered_selects=1;
        !            88:     SELECT * FROM t1 WHERE a>0 ORDER BY rowid;
        !            89:   }
        !            90: } {1 2 3 2 hello world 3 4.53 {}}
        !            91: 
        !            92: do_test whereA-3.1 {
        !            93:   db eval {
        !            94:     PRAGMA reverse_unordered_selects=0;
        !            95:     SELECT * FROM t1 WHERE b>0;
        !            96:   }
        !            97: } {1 2 3 3 4.53 {} 2 hello world}
        !            98: do_test whereA-3.2 {
        !            99:   db eval {
        !           100:     PRAGMA reverse_unordered_selects=1;
        !           101:     SELECT * FROM t1 WHERE b>0;
        !           102:   }
        !           103: } {2 hello world 3 4.53 {} 1 2 3}
        !           104: do_test whereA-3.3 {
        !           105:   db eval {
        !           106:     PRAGMA reverse_unordered_selects=1;
        !           107:     SELECT * FROM t1 WHERE b>0 ORDER BY b;
        !           108:   }
        !           109: } {1 2 3 3 4.53 {} 2 hello world}
        !           110: 
        !           111: do_test whereA-4.1 {
        !           112:   db eval {
        !           113:     CREATE TABLE t2(x);
        !           114:     INSERT INTO t2 VALUES(1);
        !           115:     INSERT INTO t2 VALUES(2);
        !           116:     SELECT x FROM t2;
        !           117:   }
        !           118: } {2 1}
        !           119: # Do an SQL statement.  Append the search count to the end of the result.
        !           120: #
        !           121: proc count sql {
        !           122:   set ::sqlite_sort_count 0
        !           123:   return [concat [execsql $sql] $::sqlite_sort_count]
        !           124: }
        !           125: do_test whereA-4.2 {   ;# Ticket #3904
        !           126:   db eval {
        !           127:     CREATE INDEX t2x ON t2(x);
        !           128:   }
        !           129:   count {
        !           130:     SELECT x FROM t2;
        !           131:   }
        !           132: } {2 1 0}
        !           133: do_test whereA-4.3 {
        !           134:   count {
        !           135:     SELECT x FROM t2 ORDER BY x;
        !           136:   }
        !           137: } {1 2 0}
        !           138: do_test whereA-4.4 {
        !           139:   count {
        !           140:     SELECT x FROM t2 ORDER BY x DESC;
        !           141:   }
        !           142: } {2 1 0}
        !           143: do_test whereA-4.5 {
        !           144:   db eval {DROP INDEX t2x;}
        !           145:   count {
        !           146:     SELECT x FROM t2 ORDER BY x;
        !           147:   }
        !           148: } {1 2 1}
        !           149: do_test whereA-4.6 {
        !           150:   count {
        !           151:     SELECT x FROM t2 ORDER BY x DESC;
        !           152:   }
        !           153: } {2 1 1}
        !           154: 
        !           155: 
        !           156: finish_test