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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>