xref: /sqlite-3.40.0/test/minmax3.test (revision 8a29dfde) 
1# 2008 January 5
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# $Id: minmax3.test,v 1.4 2008/03/28 19:16:57 danielk1977 Exp $
12
13set testdir [file dirname $argv0]
14source $testdir/tester.tcl
15
16# Do an SQL statement.  Append the search count to the end of the result.
17#
18proc count sql {
19  set ::sqlite_search_count 0
20  return [concat [execsql $sql] $::sqlite_search_count]
21}
22
23# This procedure sets the value of the file-format in file 'test.db'
24# to $newval. Also, the schema cookie is incremented.
25#
26proc set_file_format {newval} {
27  hexio_write test.db 44 [hexio_render_int32 $newval]
28  set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
29  incr schemacookie
30  hexio_write test.db 40 [hexio_render_int32 $schemacookie]
31  return {}
32}
33
34do_test minmax3-1.0 {
35  execsql {
36    CREATE TABLE t1(x, y, z);
37  }
38  db close
39  set_file_format 4
40  sqlite3 db test.db
41  execsql {
42    BEGIN;
43    INSERT INTO t1 VALUES('1', 'I',   'one');
44    INSERT INTO t1 VALUES('2', 'IV',  'four');
45    INSERT INTO t1 VALUES('2', NULL,  'three');
46    INSERT INTO t1 VALUES('2', 'II',  'two');
47    INSERT INTO t1 VALUES('2', 'V',   'five');
48    INSERT INTO t1 VALUES('3', 'VI',  'six');
49    COMMIT;
50  }
51} {}
52do_test minmax3-1.1.1 {
53  # Linear scan.
54  count { SELECT max(y) FROM t1 WHERE x = '2'; }
55} {V 5}
56do_test minmax3-1.1.2 {
57  # Index optimizes the WHERE x='2' constraint.
58  execsql { CREATE INDEX i1 ON t1(x) }
59  count   { SELECT max(y) FROM t1 WHERE x = '2'; }
60} {V 9}
61do_test minmax3-1.1.3 {
62  # Index optimizes the WHERE x='2' constraint and the MAX(y).
63  execsql { CREATE INDEX i2 ON t1(x,y) }
64  count   { SELECT max(y) FROM t1 WHERE x = '2'; }
65} {V 1}
66do_test minmax3-1.1.4 {
67  # Index optimizes the WHERE x='2' constraint and the MAX(y).
68  execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) }
69  count   { SELECT max(y) FROM t1 WHERE x = '2'; }
70} {V 1}
71do_test minmax3-1.1.5 {
72  count   { SELECT max(y) FROM t1 WHERE x = '2' AND y != 'V'; }
73} {IV 2}
74do_test minmax3-1.1.6 {
75  count   { SELECT max(y) FROM t1 WHERE x = '2' AND y < 'V'; }
76} {IV 1}
77do_test minmax3-1.1.6 {
78  count   { SELECT max(y) FROM t1 WHERE x = '2' AND z != 'five'; }
79} {IV 4}
80
81do_test minmax3-1.2.1 {
82  # Linear scan of t1.
83  execsql { DROP INDEX i1 ; DROP INDEX i2 }
84  count { SELECT min(y) FROM t1 WHERE x = '2'; }
85} {II 5}
86do_test minmax3-1.2.2 {
87  # Index i1 optimizes the WHERE x='2' constraint.
88  execsql { CREATE INDEX i1 ON t1(x) }
89  count   { SELECT min(y) FROM t1 WHERE x = '2'; }
90} {II 9}
91do_test minmax3-1.2.3 {
92  # Index i2 optimizes the WHERE x='2' constraint and the min(y).
93  execsql { CREATE INDEX i2 ON t1(x,y) }
94  count   { SELECT min(y) FROM t1 WHERE x = '2'; }
95} {II 1}
96do_test minmax3-1.2.4 {
97  # Index optimizes the WHERE x='2' constraint and the MAX(y).
98  execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) }
99  count   { SELECT min(y) FROM t1 WHERE x = '2'; }
100} {II 1}
101
102do_test minmax3-1.3.1 {
103  # Linear scan
104  execsql { DROP INDEX i1 ; DROP INDEX i2 }
105  count   { SELECT min(y) FROM t1; }
106} {I 5}
107do_test minmax3-1.3.2 {
108  # Index i1 optimizes the min(y)
109  execsql { CREATE INDEX i1 ON t1(y) }
110  count   { SELECT min(y) FROM t1; }
111} {I 1}
112do_test minmax3-1.3.3 {
113  # Index i1 optimizes the min(y)
114  execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) }
115  count   { SELECT min(y) FROM t1; }
116} {I 1}
117
118do_test minmax3-1.4.1 {
119  # Linear scan
120  execsql { DROP INDEX i1 }
121  count   { SELECT max(y) FROM t1; }
122} {VI 5}
123do_test minmax3-1.4.2 {
124  # Index i1 optimizes the max(y)
125  execsql { CREATE INDEX i1 ON t1(y) }
126  count   { SELECT max(y) FROM t1; }
127} {VI 0}
128do_test minmax3-1.4.3 {
129  # Index i1 optimizes the max(y)
130  execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) }
131  execsql   { SELECT y from t1}
132  count   { SELECT max(y) FROM t1; }
133} {VI 0}
134do_test minmax3-1.4.4 {
135  execsql { DROP INDEX i1 }
136} {}
137
138do_test minmax3-2.1 {
139  execsql {
140    CREATE TABLE t2(a, b);
141    CREATE INDEX i3 ON t2(a, b);
142    INSERT INTO t2 VALUES(1, NULL);
143    INSERT INTO t2 VALUES(1, 1);
144    INSERT INTO t2 VALUES(1, 2);
145    INSERT INTO t2 VALUES(1, 3);
146    INSERT INTO t2 VALUES(2, NULL);
147    INSERT INTO t2 VALUES(2, 1);
148    INSERT INTO t2 VALUES(2, 2);
149    INSERT INTO t2 VALUES(2, 3);
150    INSERT INTO t2 VALUES(3, 1);
151    INSERT INTO t2 VALUES(3, 2);
152    INSERT INTO t2 VALUES(3, 3);
153  }
154} {}
155do_test minmax3-2.2 {
156  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
157} {1}
158do_test minmax3-2.3 {
159  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; }
160} {2}
161do_test minmax3-2.4 {
162  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; }
163} {1}
164do_test minmax3-2.5 {
165  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
166} {1}
167do_test minmax3-2.6 {
168  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; }
169} {1}
170do_test minmax3-2.7 {
171  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; }
172} {{}}
173do_test minmax3-2.8 {
174  execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; }
175} {{}}
176
177do_test minmax3-2.1 {
178  execsql {
179    DROP TABLE t2;
180    CREATE TABLE t2(a, b);
181    CREATE INDEX i3 ON t2(a, b DESC);
182    INSERT INTO t2 VALUES(1, NULL);
183    INSERT INTO t2 VALUES(1, 1);
184    INSERT INTO t2 VALUES(1, 2);
185    INSERT INTO t2 VALUES(1, 3);
186    INSERT INTO t2 VALUES(2, NULL);
187    INSERT INTO t2 VALUES(2, 1);
188    INSERT INTO t2 VALUES(2, 2);
189    INSERT INTO t2 VALUES(2, 3);
190    INSERT INTO t2 VALUES(3, 1);
191    INSERT INTO t2 VALUES(3, 2);
192    INSERT INTO t2 VALUES(3, 3);
193  }
194} {}
195do_test minmax3-2.2 {
196  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
197} {1}
198do_test minmax3-2.3 {
199  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; }
200} {2}
201do_test minmax3-2.4 {
202  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; }
203} {1}
204do_test minmax3-2.5 {
205  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
206} {1}
207do_test minmax3-2.6 {
208  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; }
209} {1}
210do_test minmax3-2.7 {
211  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; }
212} {{}}
213do_test minmax3-2.8 {
214  execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; }
215} {{}}
216
217finish_test
218
219