xref: /sqlite-3.40.0/test/minmax.test (revision bb113518) 
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.2 2002/05/27 01:04:51 drh Exp $
17
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20
21do_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
50do_test minmax-1.1 {
51  set sqlite_search_count 0
52  execsql {SELECT min(x) FROM t1}
53} {1}
54do_test minmax-1.2 {
55  set sqlite_search_count
56} {19}
57do_test minmax-1.3 {
58  set sqlite_search_count 0
59  execsql {SELECT max(x) FROM t1}
60} {20}
61do_test minmax-1.4 {
62  set sqlite_search_count
63} {19}
64do_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}
69do_test minmax-1.6 {
70  set sqlite_search_count
71} {1}
72do_test minmax-1.7 {
73  set sqlite_search_count 0
74  execsql {SELECT max(x) FROM t1}
75} {20}
76do_test minmax-1.8 {
77  set sqlite_search_count
78} {1}
79do_test minmax-1.9 {
80  set sqlite_search_count 0
81  execsql {SELECT max(y) FROM t1}
82} {5}
83do_test minmax-1.10 {
84  set sqlite_search_count
85} {19}
86
87do_test minmax-2.0 {
88  execsql {
89    CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
90    INSERT INTO t2 SELECT * FROM t1;
91  }
92  set sqlite_search_count 0
93  execsql {SELECT min(a) FROM t2}
94} {1}
95do_test minmax-2.1 {
96  set sqlite_search_count
97} {0}
98do_test minmax-2.2 {
99  set sqlite_search_count 0
100  execsql {SELECT max(a) FROM t2}
101} {20}
102do_test minmax-2.3 {
103  set sqlite_search_count
104} {0}
105
106do_test minmax-3.0 {
107  execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
108  set sqlite_search_count 0
109  execsql {SELECT max(a) FROM t2}
110} {21}
111do_test minmax-3.1 {
112  set sqlite_search_count
113} {0}
114do_test minmax-3.2 {
115  execsql {INSERT INTO t2 VALUES((SELECT max(a) FROM t2)+1,999)}
116  set sqlite_search_count 0
117  execsql {
118    SELECT b FROM t2 WHERE a=(SELECT max(a) FROM t2)
119  }
120} {999}
121do_test minmax-3.3 {
122  set sqlite_search_count
123} {0}
124
125do_test minmax-4.1 {
126  execsql {
127    SELECT coalesce(min(x),-1), coalesce(max(x),-1) FROM
128      (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
129  }
130} {-1 -1}
131
132finish_test
133