# 2012 February 02 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # Test for queries of the form: # # SELECT p, max(q) FROM t1; # # Demonstration that the value returned for p is on the same row as # the maximum q. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !compound { finish_test return } do_test minmax4-1.1 { db eval { CREATE TABLE t1(p,q); SELECT p, max(q) FROM t1; } } {{} {}} do_test minmax4-1.2 { db eval { SELECT p, min(q) FROM t1; } } {{} {}} do_test minmax4-1.3 { db eval { INSERT INTO t1 VALUES(1,2); SELECT p, max(q) FROM t1; } } {1 2} do_test minmax4-1.4 { db eval { SELECT p, min(q) FROM t1; } } {1 2} do_test minmax4-1.5 { db eval { INSERT INTO t1 VALUES(3,4); SELECT p, max(q) FROM t1; } } {3 4} do_test minmax4-1.6 { db eval { SELECT p, min(q) FROM t1; } } {1 2} do_test minmax4-1.7 { db eval { INSERT INTO t1 VALUES(5,0); SELECT p, max(q) FROM t1; } } {3 4} do_test minmax4-1.8 { db eval { SELECT p, min(q) FROM t1; } } {5 0} do_test minmax4-1.9 { db eval { INSERT INTO t1 VALUES(6,1); SELECT p, max(q) FROM t1; } } {3 4} do_test minmax4-1.10 { db eval { SELECT p, min(q) FROM t1; } } {5 0} do_test minmax4-1.11 { db eval { INSERT INTO t1 VALUES(7,NULL); SELECT p, max(q) FROM t1; } } {3 4} do_test minmax4-1.12 { db eval { SELECT p, min(q) FROM t1; } } {5 0} do_test minmax4-1.13 { db eval { DELETE FROM t1 WHERE q IS NOT NULL; SELECT p, max(q) FROM t1; } } {7 {}} do_test minmax4-1.14 { db eval { SELECT p, min(q) FROM t1; } } {7 {}} do_test minmax4-2.1 { db eval { CREATE TABLE t2(a,b,c); INSERT INTO t2 VALUES (1,null,2), (1,2,3), (1,1,4), (2,3,5); SELECT a, max(b), c FROM t2 GROUP BY a ORDER BY a; } } {1 2 3 2 3 5} do_test minmax4-2.2 { db eval { SELECT a, min(b), c FROM t2 GROUP BY a ORDER BY a; } } {1 1 4 2 3 5} do_test minmax4-2.3 { db eval { SELECT a, min(b), avg(b), count(b), c FROM t2 GROUP BY a ORDER BY a DESC; } } {2 3 3.0 1 5 1 1 1.5 2 4} do_test minmax4-2.4 { db eval { SELECT a, min(b), max(b), c FROM t2 GROUP BY a ORDER BY a; } } {1 1 2 3 2 3 3 5} do_test minmax4-2.5 { db eval { SELECT a, max(b), min(b), c FROM t2 GROUP BY a ORDER BY a; } } {1 2 1 4 2 3 3 5} do_test minmax4-2.6 { db eval { SELECT a, max(b), b, max(c), c FROM t2 GROUP BY a ORDER BY a; } } {1 2 1 4 4 2 3 3 5 5} do_test minmax4-2.7 { db eval { SELECT a, min(b), b, min(c), c FROM t2 GROUP BY a ORDER BY a; } } {1 1 {} 2 2 2 3 3 5 5} finish_test