3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
12 # Test for queries of the form:
14 # SELECT p, max(q) FROM t1;
16 # Demonstration that the value returned for p is on the same row as
20 set testdir [file dirname $argv0]
21 source $testdir/tester.tcl
31 SELECT p, max(q) FROM t1;
36 SELECT p, min(q) FROM t1;
41 INSERT INTO t1 VALUES(1,2);
42 SELECT p, max(q) FROM t1;
47 SELECT p, min(q) FROM t1;
52 INSERT INTO t1 VALUES(3,4);
53 SELECT p, max(q) FROM t1;
58 SELECT p, min(q) FROM t1;
63 INSERT INTO t1 VALUES(5,0);
64 SELECT p, max(q) FROM t1;
69 SELECT p, min(q) FROM t1;
74 INSERT INTO t1 VALUES(6,1);
75 SELECT p, max(q) FROM t1;
78 do_test minmax4-1.10 {
80 SELECT p, min(q) FROM t1;
83 do_test minmax4-1.11 {
85 INSERT INTO t1 VALUES(7,NULL);
86 SELECT p, max(q) FROM t1;
89 do_test minmax4-1.12 {
91 SELECT p, min(q) FROM t1;
94 do_test minmax4-1.13 {
96 DELETE FROM t1 WHERE q IS NOT NULL;
97 SELECT p, max(q) FROM t1;
100 do_test minmax4-1.14 {
102 SELECT p, min(q) FROM t1;
106 do_test minmax4-2.1 {
108 CREATE TABLE t2(a,b,c);
109 INSERT INTO t2 VALUES
114 SELECT a, max(b), c FROM t2 GROUP BY a ORDER BY a;
117 do_test minmax4-2.2 {
119 SELECT a, min(b), c FROM t2 GROUP BY a ORDER BY a;
122 do_test minmax4-2.3 {
124 SELECT a, min(b), avg(b), count(b), c FROM t2 GROUP BY a ORDER BY a DESC;
126 } {2 3 3.0 1 5 1 1 1.5 2 4}
127 do_test minmax4-2.4 {
129 SELECT a, min(b), max(b), c FROM t2 GROUP BY a ORDER BY a;
132 do_test minmax4-2.5 {
134 SELECT a, max(b), min(b), c FROM t2 GROUP BY a ORDER BY a;
137 do_test minmax4-2.6 {
139 SELECT a, max(b), b, max(c), c FROM t2 GROUP BY a ORDER BY a;
141 } {1 2 1 4 4 2 3 3 5 5}
142 do_test minmax4-2.7 {
144 SELECT a, min(b), b, min(c), c FROM t2 GROUP BY a ORDER BY a;
146 } {1 1 {} 2 2 2 3 3 5 5}