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