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.5 2008/07/12 14:52:20 drh Exp $
12
13set testdir [file dirname $argv0]
14source $testdir/tester.tcl
15
16# Do not use a codec for tests in this file, as the database file is
17# manipulated directly using tcl scripts (using the [hexio_write] command).
18#
19do_not_use_codec
20
21# Do an SQL statement.  Append the search count to the end of the result.
22#
23proc count sql {
24  set ::sqlite_search_count 0
25  return [concat [execsql $sql] $::sqlite_search_count]
26}
27
28# This procedure sets the value of the file-format in file 'test.db'
29# to $newval. Also, the schema cookie is incremented.
30# 
31proc set_file_format {newval} {
32  hexio_write test.db 44 [hexio_render_int32 $newval]
33  set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
34  incr schemacookie
35  hexio_write test.db 40 [hexio_render_int32 $schemacookie]
36  return {}
37}
38
39do_test minmax3-1.0 {
40  execsql {
41    CREATE TABLE t1(x, y, z);
42  }
43  db close
44  set_file_format 4
45  sqlite3 db test.db
46  execsql {
47    BEGIN;
48    INSERT INTO t1 VALUES('1', 'I',   'one');
49    INSERT INTO t1 VALUES('2', 'IV',  'four');
50    INSERT INTO t1 VALUES('2', NULL,  'three');
51    INSERT INTO t1 VALUES('2', 'II',  'two');
52    INSERT INTO t1 VALUES('2', 'V',   'five');
53    INSERT INTO t1 VALUES('3', 'VI',  'six');
54    COMMIT;
55    PRAGMA automatic_index=OFF;
56  }
57} {}
58do_test minmax3-1.1.1 {
59  # Linear scan.
60  count { SELECT max(y) FROM t1 WHERE x = '2'; }
61} {V 5}
62do_test minmax3-1.1.2 {
63  # Index optimizes the WHERE x='2' constraint.
64  execsql { CREATE INDEX i1 ON t1(x) }
65  count   { SELECT max(y) FROM t1 WHERE x = '2'; }
66} {V 9}
67do_test minmax3-1.1.3 {
68  # Index optimizes the WHERE x='2' constraint and the MAX(y).
69  execsql { CREATE INDEX i2 ON t1(x,y) }
70  count   { SELECT max(y) FROM t1 WHERE x = '2'; }
71} {V 1}
72do_test minmax3-1.1.4 {
73  # Index optimizes the WHERE x='2' constraint and the MAX(y).
74  execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) }
75  count   { SELECT max(y) FROM t1 WHERE x = '2'; }
76} {V 1}
77do_test minmax3-1.1.5 {
78  count   { SELECT max(y) FROM t1 WHERE x = '2' AND y != 'V'; }
79} {IV 2}
80do_test minmax3-1.1.6 {
81  count   { SELECT max(y) FROM t1 WHERE x = '2' AND y < 'V'; }
82} {IV 1}
83do_test minmax3-1.1.6 {
84  count   { SELECT max(y) FROM t1 WHERE x = '2' AND z != 'five'; }
85} {IV 4}
86
87do_test minmax3-1.2.1 {
88  # Linear scan of t1.
89  execsql { DROP INDEX i1 ; DROP INDEX i2 }
90  count { SELECT min(y) FROM t1 WHERE x = '2'; }
91} {II 5}
92do_test minmax3-1.2.2 {
93  # Index i1 optimizes the WHERE x='2' constraint.
94  execsql { CREATE INDEX i1 ON t1(x) }
95  count   { SELECT min(y) FROM t1 WHERE x = '2'; }
96} {II 9}
97do_test minmax3-1.2.3 {
98  # Index i2 optimizes the WHERE x='2' constraint and the min(y).
99  execsql { CREATE INDEX i2 ON t1(x,y) }
100  count   { SELECT min(y) FROM t1 WHERE x = '2'; }
101} {II 1}
102do_test minmax3-1.2.4 {
103  # Index optimizes the WHERE x='2' constraint and the MAX(y).
104  execsql { DROP INDEX i2 ; CREATE INDEX i2 ON t1(x, y DESC) }
105  count   { SELECT min(y) FROM t1 WHERE x = '2'; }
106} {II 1}
107
108do_test minmax3-1.3.1 {
109  # Linear scan
110  execsql { DROP INDEX i1 ; DROP INDEX i2 }
111  count   { SELECT min(y) FROM t1; }
112} {I 5}
113do_test minmax3-1.3.2 {
114  # Index i1 optimizes the min(y)
115  execsql { CREATE INDEX i1 ON t1(y) }
116  count   { SELECT min(y) FROM t1; }
117} {I 1}
118do_test minmax3-1.3.3 {
119  # Index i1 optimizes the min(y)
120  execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) }
121  count   { SELECT min(y) FROM t1; }
122} {I 1}
123
124do_test minmax3-1.4.1 {
125  # Linear scan
126  execsql { DROP INDEX i1 }
127  count   { SELECT max(y) FROM t1; }
128} {VI 5}
129do_test minmax3-1.4.2 {
130  # Index i1 optimizes the max(y)
131  execsql { CREATE INDEX i1 ON t1(y) }
132  count   { SELECT max(y) FROM t1; }
133} {VI 0}
134do_test minmax3-1.4.3 {
135  # Index i1 optimizes the max(y)
136  execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(y DESC) }
137  execsql   { SELECT y from t1}
138  count   { SELECT max(y) FROM t1; }
139} {VI 0}
140do_test minmax3-1.4.4 {
141  execsql { DROP INDEX i1 }
142} {}
143
144do_test minmax3-2.1 {
145  execsql {
146    CREATE TABLE t2(a, b);
147    CREATE INDEX i3 ON t2(a, b);
148    INSERT INTO t2 VALUES(1, NULL);
149    INSERT INTO t2 VALUES(1, 1);
150    INSERT INTO t2 VALUES(1, 2);
151    INSERT INTO t2 VALUES(1, 3);
152    INSERT INTO t2 VALUES(2, NULL);
153    INSERT INTO t2 VALUES(2, 1);
154    INSERT INTO t2 VALUES(2, 2);
155    INSERT INTO t2 VALUES(2, 3);
156    INSERT INTO t2 VALUES(3, 1);
157    INSERT INTO t2 VALUES(3, 2);
158    INSERT INTO t2 VALUES(3, 3);
159  }
160} {}
161do_test minmax3-2.2 {
162  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
163} {1}
164do_test minmax3-2.3 {
165  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; }
166} {2}
167do_test minmax3-2.4 {
168  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; }
169} {1}
170do_test minmax3-2.5 {
171  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
172} {1}
173do_test minmax3-2.6 {
174  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; }
175} {1}
176do_test minmax3-2.7 {
177  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; }
178} {{}}
179do_test minmax3-2.8 {
180  execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; }
181} {{}}
182
183do_test minmax3-3.1 {
184  execsql {
185    DROP TABLE t2;
186    CREATE TABLE t2(a, b);
187    CREATE INDEX i3 ON t2(a, b DESC);
188    INSERT INTO t2 VALUES(1, NULL);
189    INSERT INTO t2 VALUES(1, 1);
190    INSERT INTO t2 VALUES(1, 2);
191    INSERT INTO t2 VALUES(1, 3);
192    INSERT INTO t2 VALUES(2, NULL);
193    INSERT INTO t2 VALUES(2, 1);
194    INSERT INTO t2 VALUES(2, 2);
195    INSERT INTO t2 VALUES(2, 3);
196    INSERT INTO t2 VALUES(3, 1);
197    INSERT INTO t2 VALUES(3, 2);
198    INSERT INTO t2 VALUES(3, 3);
199  }
200} {}
201do_test minmax3-3.2 {
202  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
203} {1}
204do_test minmax3-3.3 {
205  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>1; }
206} {2}
207do_test minmax3-3.4 {
208  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b>-1; }
209} {1}
210do_test minmax3-3.5 {
211  execsql { SELECT min(b) FROM t2 WHERE a = 1; }
212} {1}
213do_test minmax3-3.6 {
214  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<2; }
215} {1}
216do_test minmax3-3.7 {
217  execsql { SELECT min(b) FROM t2 WHERE a = 1 AND b<1; }
218} {{}}
219do_test minmax3-3.8 {
220  execsql { SELECT min(b) FROM t2 WHERE a = 3 AND b<1; }
221} {{}}
222
223do_test minmax3-4.1 {
224  execsql {
225    CREATE TABLE t4(x);
226    INSERT INTO t4 VALUES('abc');
227    INSERT INTO t4 VALUES('BCD');
228    SELECT max(x) FROM t4;
229  }
230} {abc}
231do_test minmax3-4.2 {
232  execsql {
233    SELECT max(x COLLATE nocase) FROM t4;
234  }
235} {BCD}
236do_test minmax3-4.3 {
237  execsql {
238    SELECT max(x), max(x COLLATE nocase) FROM t4;
239  }
240} {abc BCD}
241do_test minmax3-4.4 {
242  execsql {
243    SELECT max(x COLLATE binary), max(x COLLATE nocase) FROM t4;
244  }
245} {abc BCD}
246do_test minmax3-4.5 {
247  execsql {
248    SELECT max(x COLLATE nocase), max(x COLLATE rtrim) FROM t4;
249  }
250} {BCD abc}
251do_test minmax3-4.6 {
252  execsql {
253    SELECT max(x COLLATE nocase), max(x) FROM t4;
254  }
255} {BCD abc}
256do_test minmax3-4.10 {
257  execsql {
258    SELECT min(x) FROM t4;
259  }
260} {BCD}
261do_test minmax3-4.11 {
262  execsql {
263    SELECT min(x COLLATE nocase) FROM t4;
264  }
265} {abc}
266do_test minmax3-4.12 {
267  execsql {
268    SELECT min(x), min(x COLLATE nocase) FROM t4;
269  }
270} {BCD abc}
271do_test minmax3-4.13 {
272  execsql {
273    SELECT min(x COLLATE binary), min(x COLLATE nocase) FROM t4;
274  }
275} {BCD abc}
276do_test minmax3-4.14 {
277  execsql {
278    SELECT min(x COLLATE nocase), min(x COLLATE rtrim) FROM t4;
279  }
280} {abc BCD}
281do_test minmax3-4.15 {
282  execsql {
283    SELECT min(x COLLATE nocase), min(x) FROM t4;
284  }
285} {abc BCD}
286
287
288finish_test
289