1: # 2001 September 15
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.
15: #
16: # $Id: minmax.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
17:
18: set testdir [file dirname $argv0]
19: source $testdir/tester.tcl
20:
21: do_test minmax-1.0 {
22: execsql {
23: BEGIN;
24: CREATE TABLE t1(x, y);
25: INSERT INTO t1 VALUES(1,1);
26: INSERT INTO t1 VALUES(2,2);
27: INSERT INTO t1 VALUES(3,2);
28: INSERT INTO t1 VALUES(4,3);
29: INSERT INTO t1 VALUES(5,3);
30: INSERT INTO t1 VALUES(6,3);
31: INSERT INTO t1 VALUES(7,3);
32: INSERT INTO t1 VALUES(8,4);
33: INSERT INTO t1 VALUES(9,4);
34: INSERT INTO t1 VALUES(10,4);
35: INSERT INTO t1 VALUES(11,4);
36: INSERT INTO t1 VALUES(12,4);
37: INSERT INTO t1 VALUES(13,4);
38: INSERT INTO t1 VALUES(14,4);
39: INSERT INTO t1 VALUES(15,4);
40: INSERT INTO t1 VALUES(16,5);
41: INSERT INTO t1 VALUES(17,5);
42: INSERT INTO t1 VALUES(18,5);
43: INSERT INTO t1 VALUES(19,5);
44: INSERT INTO t1 VALUES(20,5);
45: COMMIT;
46: SELECT DISTINCT y FROM t1 ORDER BY y;
47: }
48: } {1 2 3 4 5}
49:
50: do_test minmax-1.1 {
51: set sqlite_search_count 0
52: execsql {SELECT min(x) FROM t1}
53: } {1}
54: do_test minmax-1.2 {
55: set sqlite_search_count
56: } {19}
57: do_test minmax-1.3 {
58: set sqlite_search_count 0
59: execsql {SELECT max(x) FROM t1}
60: } {20}
61: do_test minmax-1.4 {
62: set sqlite_search_count
63: } {19}
64: do_test minmax-1.5 {
65: execsql {CREATE INDEX t1i1 ON t1(x)}
66: set sqlite_search_count 0
67: execsql {SELECT min(x) FROM t1}
68: } {1}
69: do_test minmax-1.6 {
70: set sqlite_search_count
71: } {1}
72: do_test minmax-1.7 {
73: set sqlite_search_count 0
74: execsql {SELECT max(x) FROM t1}
75: } {20}
76: do_test minmax-1.8 {
77: set sqlite_search_count
78: } {0}
79: do_test minmax-1.9 {
80: set sqlite_search_count 0
81: execsql {SELECT max(y) FROM t1}
82: } {5}
83: do_test minmax-1.10 {
84: set sqlite_search_count
85: } {19}
86:
87: do_test minmax-1.21 {
88: execsql {SELECT min(x) FROM t1 WHERE x=5}
89: } {5}
90: do_test minmax-1.22 {
91: execsql {SELECT min(x) FROM t1 WHERE x>=5}
92: } {5}
93: do_test minmax-1.23 {
94: execsql {SELECT min(x) FROM t1 WHERE x>=4.5}
95: } {5}
96: do_test minmax-1.24 {
97: execsql {SELECT min(x) FROM t1 WHERE x<4.5}
98: } {1}
99:
100: do_test minmax-2.0 {
101: execsql {
102: CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
103: INSERT INTO t2 SELECT * FROM t1;
104: }
105: set sqlite_search_count 0
106: execsql {SELECT min(a) FROM t2}
107: } {1}
108: do_test minmax-2.1 {
109: set sqlite_search_count
110: } {0}
111: do_test minmax-2.2 {
112: set sqlite_search_count 0
113: execsql {SELECT max(a) FROM t2}
114: } {20}
115: do_test minmax-2.3 {
116: set sqlite_search_count
117: } {0}
118:
119: do_test minmax-3.0 {
120: ifcapable subquery {
121: execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
122: } else {
123: db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
124: execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
125: }
126: set sqlite_search_count 0
127: execsql {SELECT max(a) FROM t2}
128: } {21}
129: do_test minmax-3.1 {
130: set sqlite_search_count
131: } {0}
132: do_test minmax-3.2 {
133: ifcapable subquery {
134: execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
135: } else {
136: db function max_a_t2 {execsql {SELECT max(a) FROM t2}}
137: execsql {INSERT INTO t2 VALUES(max_a_t2()+1,999)}
138: }
139: set sqlite_search_count 0
140: ifcapable subquery {
141: execsql { SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2) }
142: } else {
143: execsql { SELECT b FROM t2 WHERE a=max_a_t2() }
144: }
145: } {999}
146: do_test minmax-3.3 {
147: set sqlite_search_count
148: } {0}
149:
150: ifcapable {compound && subquery} {
151: do_test minmax-4.1 {
152: execsql {
153: SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
154: (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
155: }
156: } {1 20}
157: do_test minmax-4.2 {
158: execsql {
159: SELECT y, coalesce(sum(x),0) FROM
160: (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1)
161: GROUP BY y ORDER BY y;
162: }
163: } {1 1 2 5 3 22 4 92 5 90 6 0}
164: do_test minmax-4.3 {
165: execsql {
166: SELECT y, count(x), count(*) FROM
167: (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT * FROM t1)
168: GROUP BY y ORDER BY y;
169: }
170: } {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1}
171: } ;# ifcapable compound
172:
173: # Make sure the min(x) and max(x) optimizations work on empty tables
174: # including empty tables with indices. Ticket #296.
175: #
176: do_test minmax-5.1 {
177: execsql {
178: CREATE TABLE t3(x INTEGER UNIQUE NOT NULL);
179: SELECT coalesce(min(x),999) FROM t3;
180: }
181: } {999}
182: do_test minmax-5.2 {
183: execsql {
184: SELECT coalesce(min(rowid),999) FROM t3;
185: }
186: } {999}
187: do_test minmax-5.3 {
188: execsql {
189: SELECT coalesce(max(x),999) FROM t3;
190: }
191: } {999}
192: do_test minmax-5.4 {
193: execsql {
194: SELECT coalesce(max(rowid),999) FROM t3;
195: }
196: } {999}
197: do_test minmax-5.5 {
198: execsql {
199: SELECT coalesce(max(rowid),999) FROM t3 WHERE rowid<25;
200: }
201: } {999}
202:
203: # Make sure the min(x) and max(x) optimizations work when there
204: # is a LIMIT clause. Ticket #396.
205: #
206: do_test minmax-6.1 {
207: execsql {
208: SELECT min(a) FROM t2 LIMIT 1
209: }
210: } {1}
211: do_test minmax-6.2 {
212: execsql {
213: SELECT max(a) FROM t2 LIMIT 3
214: }
215: } {22}
216: do_test minmax-6.3 {
217: execsql {
218: SELECT min(a) FROM t2 LIMIT 0,100
219: }
220: } {1}
221: do_test minmax-6.4 {
222: execsql {
223: SELECT max(a) FROM t2 LIMIT 1,100
224: }
225: } {}
226: do_test minmax-6.5 {
227: execsql {
228: SELECT min(x) FROM t3 LIMIT 1
229: }
230: } {{}}
231: do_test minmax-6.6 {
232: execsql {
233: SELECT max(x) FROM t3 LIMIT 0
234: }
235: } {}
236: do_test minmax-6.7 {
237: execsql {
238: SELECT max(a) FROM t2 LIMIT 0
239: }
240: } {}
241:
242: # Make sure the max(x) and min(x) optimizations work for nested
243: # queries. Ticket #587.
244: #
245: do_test minmax-7.1 {
246: execsql {
247: SELECT max(x) FROM t1;
248: }
249: } 20
250: ifcapable subquery {
251: do_test minmax-7.2 {
252: execsql {
253: SELECT * FROM (SELECT max(x) FROM t1);
254: }
255: } 20
256: }
257: do_test minmax-7.3 {
258: execsql {
259: SELECT min(x) FROM t1;
260: }
261: } 1
262: ifcapable subquery {
263: do_test minmax-7.4 {
264: execsql {
265: SELECT * FROM (SELECT min(x) FROM t1);
266: }
267: } 1
268: }
269:
270: # Make sure min(x) and max(x) work correctly when the datatype is
271: # TEXT instead of NUMERIC. Ticket #623.
272: #
273: do_test minmax-8.1 {
274: execsql {
275: CREATE TABLE t4(a TEXT);
276: INSERT INTO t4 VALUES('1234');
277: INSERT INTO t4 VALUES('234');
278: INSERT INTO t4 VALUES('34');
279: SELECT min(a), max(a) FROM t4;
280: }
281: } {1234 34}
282: do_test minmax-8.2 {
283: execsql {
284: CREATE TABLE t5(a INTEGER);
285: INSERT INTO t5 VALUES('1234');
286: INSERT INTO t5 VALUES('234');
287: INSERT INTO t5 VALUES('34');
288: SELECT min(a), max(a) FROM t5;
289: }
290: } {34 1234}
291:
292: # Ticket #658: Test the min()/max() optimization when the FROM clause
293: # is a subquery.
294: #
295: ifcapable {compound && subquery} {
296: do_test minmax-9.1 {
297: execsql {
298: SELECT max(rowid) FROM (
299: SELECT max(rowid) FROM t4 UNION SELECT max(rowid) FROM t5
300: )
301: }
302: } {1}
303: do_test minmax-9.2 {
304: execsql {
305: SELECT max(rowid) FROM (
306: SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
307: )
308: }
309: } {{}}
310: } ;# ifcapable compound&&subquery
311:
312: # If there is a NULL in an aggregate max() or min(), ignore it. An
313: # aggregate min() or max() will only return NULL if all values are NULL.
314: #
315: do_test minmax-10.1 {
316: execsql {
317: CREATE TABLE t6(x);
318: INSERT INTO t6 VALUES(1);
319: INSERT INTO t6 VALUES(2);
320: INSERT INTO t6 VALUES(NULL);
321: SELECT coalesce(min(x),-1) FROM t6;
322: }
323: } {1}
324: do_test minmax-10.2 {
325: execsql {
326: SELECT max(x) FROM t6;
327: }
328: } {2}
329: do_test minmax-10.3 {
330: execsql {
331: CREATE INDEX i6 ON t6(x);
332: SELECT coalesce(min(x),-1) FROM t6;
333: }
334: } {1}
335: do_test minmax-10.4 {
336: execsql {
337: SELECT max(x) FROM t6;
338: }
339: } {2}
340: do_test minmax-10.5 {
341: execsql {
342: DELETE FROM t6 WHERE x NOT NULL;
343: SELECT count(*) FROM t6;
344: }
345: } 1
346: do_test minmax-10.6 {
347: execsql {
348: SELECT count(x) FROM t6;
349: }
350: } 0
351: ifcapable subquery {
352: do_test minmax-10.7 {
353: execsql {
354: SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
355: }
356: } {{} {}}
357: }
358: do_test minmax-10.8 {
359: execsql {
360: SELECT min(x), max(x) FROM t6;
361: }
362: } {{} {}}
363: do_test minmax-10.9 {
364: execsql {
365: INSERT INTO t6 SELECT * FROM t6;
366: INSERT INTO t6 SELECT * FROM t6;
367: INSERT INTO t6 SELECT * FROM t6;
368: INSERT INTO t6 SELECT * FROM t6;
369: INSERT INTO t6 SELECT * FROM t6;
370: INSERT INTO t6 SELECT * FROM t6;
371: INSERT INTO t6 SELECT * FROM t6;
372: INSERT INTO t6 SELECT * FROM t6;
373: INSERT INTO t6 SELECT * FROM t6;
374: INSERT INTO t6 SELECT * FROM t6;
375: SELECT count(*) FROM t6;
376: }
377: } 1024
378: do_test minmax-10.10 {
379: execsql {
380: SELECT count(x) FROM t6;
381: }
382: } 0
383: ifcapable subquery {
384: do_test minmax-10.11 {
385: execsql {
386: SELECT (SELECT min(x) FROM t6), (SELECT max(x) FROM t6);
387: }
388: } {{} {}}
389: }
390: do_test minmax-10.12 {
391: execsql {
392: SELECT min(x), max(x) FROM t6;
393: }
394: } {{} {}}
395:
396:
397: do_test minmax-11.1 {
398: execsql {
399: CREATE INDEX t1i2 ON t1(y,x);
400: SELECT min(x) FROM t1 WHERE y=5;
401: }
402: } {16}
403: do_test minmax-11.2 {
404: execsql {
405: SELECT max(x) FROM t1 WHERE y=5;
406: }
407: } {20}
408: do_test minmax-11.3 {
409: execsql {
410: SELECT min(x) FROM t1 WHERE y=6;
411: }
412: } {{}}
413: do_test minmax-11.4 {
414: execsql {
415: SELECT max(x) FROM t1 WHERE y=6;
416: }
417: } {{}}
418: do_test minmax-11.5 {
419: execsql {
420: SELECT min(x) FROM t1 WHERE y=1;
421: }
422: } {1}
423: do_test minmax-11.6 {
424: execsql {
425: SELECT max(x) FROM t1 WHERE y=1;
426: }
427: } {1}
428: do_test minmax-11.7 {
429: execsql {
430: SELECT min(x) FROM t1 WHERE y=0;
431: }
432: } {{}}
433: do_test minmax-11.8 {
434: execsql {
435: SELECT max(x) FROM t1 WHERE y=0;
436: }
437: } {{}}
438: do_test minmax-11.9 {
439: execsql {
440: SELECT min(x) FROM t1 WHERE y=5 AND x>=17.5;
441: }
442: } {18}
443: do_test minmax-11.10 {
444: execsql {
445: SELECT max(x) FROM t1 WHERE y=5 AND x>=17.5;
446: }
447: } {20}
448:
449: do_test minmax-12.1 {
450: execsql {
451: CREATE TABLE t7(a,b,c);
452: INSERT INTO t7 SELECT y, x, x*y FROM t1;
453: INSERT INTO t7 SELECT y, x, x*y+1000 FROM t1;
454: CREATE INDEX t7i1 ON t7(a,b,c);
455: SELECT min(a) FROM t7;
456: }
457: } {1}
458: do_test minmax-12.2 {
459: execsql {
460: SELECT max(a) FROM t7;
461: }
462: } {5}
463: do_test minmax-12.3 {
464: execsql {
465: SELECT max(a) FROM t7 WHERE a=5;
466: }
467: } {5}
468: do_test minmax-12.4 {
469: execsql {
470: SELECT min(b) FROM t7 WHERE a=5;
471: }
472: } {16}
473: do_test minmax-12.5 {
474: execsql {
475: SELECT max(b) FROM t7 WHERE a=5;
476: }
477: } {20}
478: do_test minmax-12.6 {
479: execsql {
480: SELECT min(b) FROM t7 WHERE a=4;
481: }
482: } {8}
483: do_test minmax-12.7 {
484: execsql {
485: SELECT max(b) FROM t7 WHERE a=4;
486: }
487: } {15}
488: do_test minmax-12.8 {
489: execsql {
490: SELECT min(c) FROM t7 WHERE a=4 AND b=10;
491: }
492: } {40}
493: do_test minmax-12.9 {
494: execsql {
495: SELECT max(c) FROM t7 WHERE a=4 AND b=10;
496: }
497: } {1040}
498: do_test minmax-12.10 {
499: execsql {
500: SELECT min(rowid) FROM t7;
501: }
502: } {1}
503: do_test minmax-12.11 {
504: execsql {
505: SELECT max(rowid) FROM t7;
506: }
507: } {40}
508: do_test minmax-12.12 {
509: execsql {
510: SELECT min(rowid) FROM t7 WHERE a=3;
511: }
512: } {4}
513: do_test minmax-12.13 {
514: execsql {
515: SELECT max(rowid) FROM t7 WHERE a=3;
516: }
517: } {27}
518: do_test minmax-12.14 {
519: execsql {
520: SELECT min(rowid) FROM t7 WHERE a=3 AND b=5;
521: }
522: } {5}
523: do_test minmax-12.15 {
524: execsql {
525: SELECT max(rowid) FROM t7 WHERE a=3 AND b=5;
526: }
527: } {25}
528: do_test minmax-12.16 {
529: execsql {
530: SELECT min(rowid) FROM t7 WHERE a=3 AND b=5 AND c=1015;
531: }
532: } {25}
533: do_test minmax-12.17 {
534: execsql {
535: SELECT max(rowid) FROM t7 WHERE a=3 AND b=5 AND c=15;
536: }
537: } {5}
538:
539:
540:
541:
542: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to minmax.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