xref: /sqlite-3.40.0/test/minmax4.test (revision 2f56da3f) 
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  }
60} {1 2}
61do_test minmax4-1.7 {
62  db eval {
63    INSERT INTO t1 VALUES(5,0);
64    SELECT p, max(q) FROM t1;
65  }
66} {3 4}
67do_test minmax4-1.8 {
68  db eval {
69    SELECT p, min(q) FROM t1;
70  }
71} {5 0}
72do_test minmax4-1.9 {
73  db eval {
74    INSERT INTO t1 VALUES(6,1);
75    SELECT p, max(q) FROM t1;
76  }
77} {3 4}
78do_test minmax4-1.10 {
79  db eval {
80    SELECT p, min(q) FROM t1;
81  }
82} {5 0}
83do_test minmax4-1.11 {
84  db eval {
85    INSERT INTO t1 VALUES(7,NULL);
86    SELECT p, max(q) FROM t1;
87  }
88} {3 4}
89do_test minmax4-1.12 {
90  db eval {
91    SELECT p, min(q) FROM t1;
92  }
93} {5 0}
94do_test minmax4-1.13 {
95  db eval {
96    DELETE FROM t1 WHERE q IS NOT NULL;
97    SELECT p, max(q) FROM t1;
98  }
99} {7 {}}
100do_test minmax4-1.14 {
101  db eval {
102    SELECT p, min(q) FROM t1;
103  }
104} {7 {}}
105
106do_test minmax4-2.1 {
107  db eval {
108    CREATE TABLE t2(a,b,c);
109    INSERT INTO t2 VALUES
110         (1,null,2),
111         (1,2,3),
112         (1,1,4),
113         (2,3,5);
114    SELECT a, max(b), c FROM t2 GROUP BY a ORDER BY a;
115  }
116} {1 2 3 2 3 5}
117do_test minmax4-2.2 {
118  db eval {
119    SELECT a, min(b), c FROM t2 GROUP BY a ORDER BY a;
120  }
121} {1 1 4 2 3 5}
122do_test minmax4-2.3 {
123  db eval {
124    SELECT a, min(b), avg(b), count(b), c FROM t2 GROUP BY a ORDER BY a DESC;
125  }
126} {2 3 3.0 1 5 1 1 1.5 2 4}
127do_test minmax4-2.4 {
128  db eval {
129    SELECT a, min(b), max(b), c FROM t2 GROUP BY a ORDER BY a;
130  }
131} {1 1 2 3 2 3 3 5}
132do_test minmax4-2.5 {
133  db eval {
134    SELECT a, max(b), min(b), c FROM t2 GROUP BY a ORDER BY a;
135  }
136} {1 2 1 4 2 3 3 5}
137do_test minmax4-2.6 {
138  db eval {
139    SELECT a, max(b), b, max(c), c FROM t2 GROUP BY a ORDER BY a;
140  }
141} {1 2 1 4 4 2 3 3 5 5}
142do_test minmax4-2.7 {
143  db eval {
144    SELECT a, min(b), b, min(c), c FROM t2 GROUP BY a ORDER BY a;
145  }
146} {1 1 {} 2 2 2 3 3 5 5}
147
148
149
150finish_test
151