xref: /sqlite-3.40.0/test/minmax4.test (revision 7aa3ebee) 
1# 2012 February 02
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#
12# Test for queries of the form:
13#
14#    SELECT p, max(q) FROM t1;
15#
16# Demonstration that the value returned for p is on the same row as
17# the maximum q.
18#
19
20set testdir [file dirname $argv0]
21source $testdir/tester.tcl
22
23ifcapable !compound {
24  finish_test
25  return
26}
27
28do_test minmax4-1.1 {
29  db eval {
30    CREATE TABLE t1(p,q);
31    SELECT p, max(q) FROM t1;
32  }
33} {{} {}}
34do_test minmax4-1.2 {
35  db eval {
36    SELECT p, min(q) FROM t1;
37  }
38} {{} {}}
39do_test minmax4-1.3 {
40  db eval {
41    INSERT INTO t1 VALUES(1,2);
42    SELECT p, max(q) FROM t1;
43  }
44} {1 2}
45do_test minmax4-1.4 {
46  db eval {
47    SELECT p, min(q) FROM t1;
48  }
49} {1 2}
50do_test minmax4-1.5 {
51  db eval {
52    INSERT INTO t1 VALUES(3,4);
53    SELECT p, max(q) FROM t1;
54  }
55} {3 4}
56do_test minmax4-1.6 {
57  db eval {
58    SELECT p, min(q) FROM t1;
59    SELECT p FROM (SELECT p, min(q) FROM t1);
60  }
61} {1 2 1}
62do_test minmax4-1.7 {
63  db eval {
64    INSERT INTO t1 VALUES(5,0);
65    SELECT p, max(q) FROM t1;
66    SELECT p FROM (SELECT max(q), p FROM t1);
67  }
68} {3 4 3}
69do_test minmax4-1.8 {
70  db eval {
71    SELECT p, min(q) FROM t1;
72  }
73} {5 0}
74do_test minmax4-1.9 {
75  db eval {
76    INSERT INTO t1 VALUES(6,1);
77    SELECT p, max(q) FROM t1;
78    SELECT p FROM (SELECT max(q), p FROM t1);
79  }
80} {3 4 3}
81do_test minmax4-1.10 {
82  db eval {
83    SELECT p, min(q) FROM t1;
84  }
85} {5 0}
86do_test minmax4-1.11 {
87  db eval {
88    INSERT INTO t1 VALUES(7,NULL);
89    SELECT p, max(q) FROM t1;
90  }
91} {3 4}
92do_test minmax4-1.12 {
93  db eval {
94    SELECT p, min(q) FROM t1;
95  }
96} {5 0}
97do_test minmax4-1.13 {
98  db eval {
99    DELETE FROM t1 WHERE q IS NOT NULL;
100    SELECT p, max(q) FROM t1;
101  }
102} {7 {}}
103do_test minmax4-1.14 {
104  db eval {
105    SELECT p, min(q) FROM t1;
106  }
107} {7 {}}
108
109do_test minmax4-2.1 {
110  db eval {
111    CREATE TABLE t2(a,b,c);
112    INSERT INTO t2 VALUES
113         (1,null,2),
114         (1,2,3),
115         (1,1,4),
116         (2,3,5);
117    SELECT a, max(b), c FROM t2 GROUP BY a ORDER BY a;
118  }
119} {1 2 3 2 3 5}
120do_test minmax4-2.2 {
121  db eval {
122    SELECT a, min(b), c FROM t2 GROUP BY a ORDER BY a;
123  }
124} {1 1 4 2 3 5}
125do_test minmax4-2.3 {
126  db eval {
127    SELECT a, min(b), avg(b), count(b), c FROM t2 GROUP BY a ORDER BY a DESC;
128  }
129} {2 3 3.0 1 5 1 1 1.5 2 4}
130do_test minmax4-2.4 {
131  db eval {
132    SELECT a, min(b), max(b), c FROM t2 GROUP BY a ORDER BY a;
133  }
134} {1 1 2 3 2 3 3 5}
135do_test minmax4-2.5 {
136  db eval {
137    SELECT a, max(b), min(b), c FROM t2 GROUP BY a ORDER BY a;
138  }
139} {1 2 1 4 2 3 3 5}
140do_test minmax4-2.6 {
141  db eval {
142    SELECT a, max(b), b, max(c), c FROM t2 GROUP BY a ORDER BY a;
143  }
144} {1 2 1 4 4 2 3 3 5 5}
145do_test minmax4-2.7 {
146  db eval {
147    SELECT a, min(b), b, min(c), c FROM t2 GROUP BY a ORDER BY a;
148  }
149} {1 1 {} 2 2 2 3 3 5 5}
150
151
152
153finish_test
154