| # 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; |
| SELECT p FROM (SELECT p, min(q) FROM t1); |
| } |
| } {1 2 1} |
| do_test minmax4-1.7 { |
| db eval { |
| INSERT INTO t1 VALUES(5,0); |
| SELECT p, max(q) FROM t1; |
| SELECT p FROM (SELECT max(q), p FROM t1); |
| } |
| } {3 4 3} |
| 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; |
| SELECT p FROM (SELECT max(q), p FROM t1); |
| } |
| } {3 4 3} |
| 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 |