Return to whereA.test CVS log

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

sqlite3

    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.1.1.1 2012/02/21 17:04:16 misho 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