OLD | NEW |
(Empty) | |
| 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 |
OLD | NEW |