1: # 2007 July 17
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 SELECT statements that contain
13: # aggregate min() and max() functions and which are handled as
14: # as a special case. This file makes sure that the min/max
15: # optimization works right in the presence of descending
16: # indices. Ticket #2514.
17: #
18: # $Id: minmax2.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
19:
20: set testdir [file dirname $argv0]
21: source $testdir/tester.tcl
22:
23: do_test minmax2-1.0 {
24: execsql {
25: PRAGMA legacy_file_format=0;
26: BEGIN;
27: CREATE TABLE t1(x, y);
28: INSERT INTO t1 VALUES(1,1);
29: INSERT INTO t1 VALUES(2,2);
30: INSERT INTO t1 VALUES(3,2);
31: INSERT INTO t1 VALUES(4,3);
32: INSERT INTO t1 VALUES(5,3);
33: INSERT INTO t1 VALUES(6,3);
34: INSERT INTO t1 VALUES(7,3);
35: INSERT INTO t1 VALUES(8,4);
36: INSERT INTO t1 VALUES(9,4);
37: INSERT INTO t1 VALUES(10,4);
38: INSERT INTO t1 VALUES(11,4);
39: INSERT INTO t1 VALUES(12,4);
40: INSERT INTO t1 VALUES(13,4);
41: INSERT INTO t1 VALUES(14,4);
42: INSERT INTO t1 VALUES(15,4);
43: INSERT INTO t1 VALUES(16,5);
44: INSERT INTO t1 VALUES(17,5);
45: INSERT INTO t1 VALUES(18,5);
46: INSERT INTO t1 VALUES(19,5);
47: INSERT INTO t1 VALUES(20,5);
48: COMMIT;
49: SELECT DISTINCT y FROM t1 ORDER BY y;
50: }
51: } {1 2 3 4 5}
52:
53: do_test minmax2-1.1 {
54: set sqlite_search_count 0
55: execsql {SELECT min(x) FROM t1}
56: } {1}
57: do_test minmax2-1.2 {
58: set sqlite_search_count
59: } {19}
60: do_test minmax2-1.3 {
61: set sqlite_search_count 0
62: execsql {SELECT max(x) FROM t1}
63: } {20}
64: do_test minmax2-1.4 {
65: set sqlite_search_count
66: } {19}
67: do_test minmax2-1.5 {
68: execsql {CREATE INDEX t1i1 ON t1(x DESC)}
69: set sqlite_search_count 0
70: execsql {SELECT min(x) FROM t1}
71: } {1}
72: do_test minmax2-1.6 {
73: set sqlite_search_count
74: } {1}
75: do_test minmax2-1.7 {
76: set sqlite_search_count 0
77: execsql {SELECT max(x) FROM t1}
78: } {20}
79: do_test minmax2-1.8 {
80: set sqlite_search_count
81: } {0}
82: do_test minmax2-1.9 {
83: set sqlite_search_count 0
84: execsql {SELECT max(y) FROM t1}
85: } {5}
86: do_test minmax2-1.10 {
87: set sqlite_search_count
88: } {19}
89:
90: do_test minmax2-2.0 {
91: execsql {
92: CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
93: INSERT INTO t2 SELECT * FROM t1;
94: }
95: set sqlite_search_count 0
96: execsql {SELECT min(a) FROM t2}
97: } {1}
98: do_test minmax2-2.1 {
99: set sqlite_search_count
100: } {0}
101: do_test minmax2-2.2 {
102: set sqlite_search_count 0
103: execsql {SELECT max(a) FROM t2}
104: } {20}
105: do_test minmax2-2.3 {
106: set sqlite_search_count
107: } {0}
108:
109: do_test minmax2-3.0 {
110: ifcapable subquery {
111: execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
112: } else {
113: db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
114: execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
115: }
116: set sqlite_search_count 0
117: execsql {SELECT max(a) FROM t2}
118: } {21}
119: do_test minmax2-3.1 {
120: set sqlite_search_count
121: } {0}
122: do_test minmax2-3.2 {
123: ifcapable subquery {
124: execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
125: } else {
126: db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
127: execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
128: }
129: set sqlite_search_count 0
130: ifcapable subquery {
131: execsql { SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) }
132: } else {
133: execsql { SELECT b FROM t2 WHERE a=max_a_t2() }
134: }
135: } {999}
136: do_test minmax2-3.3 {
137: set sqlite_search_count
138: } {0}
139:
140: ifcapable {compound && subquery} {
141: do_test minmax2-4.1 {
142: execsql {
143: SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
144: (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
145: }
146: } {1 20}
147: do_test minmax2-4.2 {
148: execsql {
149: SELECT y, coalesce(sum(x),0) FROM
150: (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1)
151: GROUP BY y ORDER BY y;
152: }
153: } {1 1 2 5 3 22 4 92 5 90 6 0}
154: do_test minmax2-4.3 {
155: execsql {
156: SELECT y, count(x), count(*) FROM
157: (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1)
158: GROUP BY y ORDER BY y;
159: }
160: } {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1}
161: } ;# ifcapable compound
162:
163: # Make sure the min(x) and max(x) optimizations work on empty tables
164: # including empty tables with indices. Ticket #296.
165: #
166: do_test minmax2-5.1 {
167: execsql {
168: CREATE TABLE t3(x INTEGER UNIQUE NOT NULL);
169: SELECT coalesce(min(x),999) FROM t3;
170: }
171: } {999}
172: do_test minmax2-5.2 {
173: execsql {
174: SELECT coalesce(min(rowid),999) FROM t3;
175: }
176: } {999}
177: do_test minmax2-5.3 {
178: execsql {
179: SELECT coalesce(max(x),999) FROM t3;
180: }
181: } {999}
182: do_test minmax2-5.4 {
183: execsql {
184: SELECT coalesce(max(rowid),999) FROM t3;
185: }
186: } {999}
187: do_test minmax2-5.5 {
188: execsql {
189: SELECT coalesce(max(rowid),999) FROM t3 WHERE rowid<25;
190: }
191: } {999}
192:
193: # Make sure the min(x) and max(x) optimizations work when there
194: # is a LIMIT clause. Ticket #396.
195: #
196: do_test minmax2-6.1 {
197: execsql {
198: SELECT min(a) FROM t2 LIMIT 1
199: }
200: } {1}
201: do_test minmax2-6.2 {
202: execsql {
203: SELECT max(a) FROM t2 LIMIT 3
204: }
205: } {22}
206: do_test minmax2-6.3 {
207: execsql {
208: SELECT min(a) FROM t2 LIMIT 0,100
209: }
210: } {1}
211: do_test minmax2-6.4 {
212: execsql {
213: SELECT max(a) FROM t2 LIMIT 1,100
214: }
215: } {}
216: do_test minmax2-6.5 {
217: execsql {
218: SELECT min(x) FROM t3 LIMIT 1
219: }
220: } {{}}
221: do_test minmax2-6.6 {
222: execsql {
223: SELECT max(x) FROM t3 LIMIT 0
224: }
225: } {}
226: do_test minmax2-6.7 {
227: execsql {
228: SELECT max(a) FROM t2 LIMIT 0
229: }
230: } {}
231:
232: # Make sure the max(x) and min(x) optimizations work for nested
233: # queries. Ticket #587.
234: #
235: do_test minmax2-7.1 {
236: execsql {
237: SELECT max(x) FROM t1;
238: }
239: } 20
240: ifcapable subquery {
241: do_test minmax2-7.2 {
242: execsql {
243: SELECT * FROM (SELECT max(x) FROM t1);
244: }
245: } 20
246: }
247: do_test minmax2-7.3 {
248: execsql {
249: SELECT min(x) FROM t1;
250: }
251: } 1
252: ifcapable subquery {
253: do_test minmax2-7.4 {
254: execsql {
255: SELECT * FROM (SELECT min(x) FROM t1);
256: }
257: } 1
258: }
259:
260: # Make sure min(x) and max(x) work correctly when the datatype is
261: # TEXT instead of NUMERIC. Ticket #623.
262: #
263: do_test minmax2-8.1 {
264: execsql {
265: CREATE TABLE t4(a TEXT);
266: INSERT INTO t4 VALUES('1234');
267: INSERT INTO t4 VALUES('234');
268: INSERT INTO t4 VALUES('34');
269: SELECT min(a), max(a) FROM t4;
270: }
271: } {1234 34}
272: do_test minmax2-8.2 {
273: execsql {
274: CREATE TABLE t5(a INTEGER);
275: INSERT INTO t5 VALUES('1234');
276: INSERT INTO t5 VALUES('234');
277: INSERT INTO t5 VALUES('34');
278: SELECT min(a), max(a) FROM t5;
279: }
280: } {34 1234}
281:
282: # Ticket #658: Test the min()/max() optimization when the FROM clause
283: # is a subquery.
284: #
285: ifcapable {compound && subquery} {
286: do_test minmax2-9.1 {
287: execsql {
288: SELECT max(rowid) FROM (
289: SELECT max(rowid) FROM t4 UNION SELECT max(rowid) FROM t5
290: )
291: }
292: } {1}
293: do_test minmax2-9.2 {
294: execsql {
295: SELECT max(rowid) FROM (
296: SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
297: )
298: }
299: } {{}}
300: } ;# ifcapable compound&&subquery
301:
302: # If there is a NULL in an aggregate max() or min(), ignore it. An
303: # aggregate min() or max() will only return NULL if all values are NULL.
304: #
305: do_test minmax2-10.1 {
306: execsql {
307: CREATE TABLE t6(x);
308: INSERT INTO t6 VALUES(1);
309: INSERT INTO t6 VALUES(2);
310: INSERT INTO t6 VALUES(NULL);
311: SELECT coalesce(min(x),-1) FROM t6;
312: }
313: } {1}
314: do_test minmax2-10.2 {
315: execsql {
316: SELECT max(x) FROM t6;
317: }
318: } {2}
319: do_test minmax2-10.3 {
320: execsql {
321: CREATE INDEX i6 ON t6(x DESC);
322: SELECT coalesce(min(x),-1) FROM t6;
323: }
324: } {1}
325: do_test minmax2-10.4 {
326: execsql {
327: SELECT max(x) FROM t6;
328: }
329: } {2}
330: do_test minmax2-10.5 {
331: execsql {
332: DELETE FROM t6 WHERE x NOT NULL;
333: SELECT count(*) FROM t6;
334: }
335: } 1
336: do_test minmax2-10.6 {
337: execsql {
338: SELECT count(x) FROM t6;
339: }
340: } 0
341: ifcapable subquery {
342: do_test minmax2-10.7 {
343: execsql {
344: SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
345: }
346: } {{} {}}
347: }
348: do_test minmax2-10.8 {
349: execsql {
350: SELECT min(x), max(x) FROM t6;
351: }
352: } {{} {}}
353: do_test minmax2-10.9 {
354: execsql {
355: INSERT INTO t6 SELECT * FROM t6;
356: INSERT INTO t6 SELECT * FROM t6;
357: INSERT INTO t6 SELECT * FROM t6;
358: INSERT INTO t6 SELECT * FROM t6;
359: INSERT INTO t6 SELECT * FROM t6;
360: INSERT INTO t6 SELECT * FROM t6;
361: INSERT INTO t6 SELECT * FROM t6;
362: INSERT INTO t6 SELECT * FROM t6;
363: INSERT INTO t6 SELECT * FROM t6;
364: INSERT INTO t6 SELECT * FROM t6;
365: SELECT count(*) FROM t6;
366: }
367: } 1024
368: do_test minmax2-10.10 {
369: execsql {
370: SELECT count(x) FROM t6;
371: }
372: } 0
373: ifcapable subquery {
374: do_test minmax2-10.11 {
375: execsql {
376: SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
377: }
378: } {{} {}}
379: }
380: do_test minmax2-10.12 {
381: execsql {
382: SELECT min(x), max(x) FROM t6;
383: }
384: } {{} {}}
385:
386:
387: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to minmax2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test