AryaWu/sqlite

1# 2012 February 022#3# The author disclaims copyright to this source code.  In place of4# 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.tcl22set testprefix minmax423 24ifcapable !compound {25  finish_test26  return27}28 29do_test minmax4-1.1 {30  db eval {31    CREATE TABLE t1(p,q);32    SELECT p, max(q) FROM t1;33  }34} {{} {}}35do_test minmax4-1.2 {36  db eval {37    SELECT p, min(q) FROM t1;38  }39} {{} {}}40do_test minmax4-1.3 {41  db eval {42    INSERT INTO t1 VALUES(1,2);43    SELECT p, max(q) FROM t1;44  }45} {1 2}46do_test minmax4-1.4 {47  db eval {48    SELECT p, min(q) FROM t1;49  }50} {1 2}51do_test minmax4-1.5 {52  db eval {53    INSERT INTO t1 VALUES(3,4);54    SELECT p, max(q) FROM t1;55  }56} {3 4}57do_test minmax4-1.6 {58  db eval {59    SELECT p, min(q) FROM t1;60    SELECT p FROM (SELECT p, min(q) FROM t1);61  }62} {1 2 1}63do_test minmax4-1.7 {64  db eval {65    INSERT INTO t1 VALUES(5,0);66    SELECT p, max(q) FROM t1;67    SELECT p FROM (SELECT max(q), p FROM t1);68  }69} {3 4 3}70do_test minmax4-1.8 {71  db eval {72    SELECT p, min(q) FROM t1;73  }74} {5 0}75do_test minmax4-1.9 {76  db eval {77    INSERT INTO t1 VALUES(6,1);78    SELECT p, max(q) FROM t1;79    SELECT p FROM (SELECT max(q), p FROM t1);80  }81} {3 4 3}82do_test minmax4-1.10 {83  db eval {84    SELECT p, min(q) FROM t1;85  }86} {5 0}87do_test minmax4-1.11 {88  db eval {89    INSERT INTO t1 VALUES(7,NULL);90    SELECT p, max(q) FROM t1;91  }92} {3 4}93do_test minmax4-1.12 {94  db eval {95    SELECT p, min(q) FROM t1;96  }97} {5 0}98do_test minmax4-1.13 {99  db eval {100    DELETE FROM t1 WHERE q IS NOT NULL;101    SELECT p, max(q) FROM t1;102  }103} {7 {}}104do_test minmax4-1.14 {105  db eval {106    SELECT p, min(q) FROM t1;107  }108} {7 {}}109 110do_test minmax4-2.1 {111  db eval {112    CREATE TABLE t2(a,b,c);113    INSERT INTO t2 VALUES114         (1,null,2),115         (1,2,3),116         (1,1,4),117         (2,3,5);118    SELECT a, max(b), c FROM t2 GROUP BY a ORDER BY a;119  }120} {1 2 3 2 3 5}121do_test minmax4-2.2 {122  db eval {123    SELECT a, min(b), c FROM t2 GROUP BY a ORDER BY a;124  }125} {1 1 4 2 3 5}126do_test minmax4-2.3 {127  db eval {128    SELECT a, min(b), avg(b), count(b), c FROM t2 GROUP BY a ORDER BY a DESC;129  }130} {2 3 3.0 1 5 1 1 1.5 2 4}131do_test minmax4-2.4 {132  db eval {133    SELECT a, min(b), max(b), c FROM t2 GROUP BY a ORDER BY a;134  }135} {1 1 2 3 2 3 3 5}136do_test minmax4-2.5 {137  db eval {138    SELECT a, max(b), min(b), c FROM t2 GROUP BY a ORDER BY a;139  }140} {1 2 1 4 2 3 3 5}141do_test minmax4-2.6 {142  db eval {143    SELECT a, max(b), b, max(c), c FROM t2 GROUP BY a ORDER BY a;144  }145} {1 2 1 4 4 2 3 3 5 5}146do_test minmax4-2.7 {147  db eval {148    SELECT a, min(b), b, min(c), c FROM t2 GROUP BY a ORDER BY a;149  }150} {1 1 {} 2 2 2 3 3 5 5}151 152#-------------------------------------------------------------------------153foreach {tn sql} {154  1 { CREATE INDEX i1 ON t1(a) }155  2 { CREATE INDEX i1 ON t1(a DESC) }156  3 { }157} {158  reset_db159  do_execsql_test 3.$tn.0 {160    CREATE TABLE t1(a, b);161    INSERT INTO t1 VALUES(NULL, 1);162  }163  execsql $sql164  do_execsql_test 3.$tn.1 {165    SELECT min(a), b FROM t1;166  } {{} 1}167  do_execsql_test 3.$tn.2 {168    SELECT min(a), b FROM t1 WHERE a<50;169  } {{} {}}170  do_execsql_test 3.$tn.3 {171    INSERT INTO t1 VALUES(2, 2);172  }173  do_execsql_test 3.$tn.4 {174    SELECT min(a), b FROM t1;175  } {2 2}176  do_execsql_test 3.$tn.5 {177    SELECT min(a), b FROM t1 WHERE a<50;178  } {2 2}179}180 181#-------------------------------------------------------------------------182reset_db183do_execsql_test 4.0 {184  CREATE TABLE t0 (c0, c1);185  CREATE INDEX i0 ON t0(c1, c1 + 1 DESC);186  INSERT INTO t0(c0) VALUES (1);187}188do_execsql_test 4.1 {189  SELECT MIN(t0.c1), t0.c0 FROM t0 WHERE t0.c1 ISNULL; 190} {{} 1}191 192#-------------------------------------------------------------------------193reset_db194do_execsql_test 5.0 {195  CREATE TABLE t1 (a, b);196  INSERT INTO t1 VALUES(123, NULL);197  CREATE INDEX i1 ON t1(a, b DESC);198}199do_execsql_test 5.1 {200  SELECT MIN(a) FROM t1 WHERE a=123;201} {123}202 203#-------------------------------------------------------------------------204# Tests for ticket f8a7060ece.205#206reset_db207do_execsql_test 6.1.0 {208  CREATE TABLE t1(a, b, c);209  INSERT INTO t1 VALUES(NULL, 1, 'x');210  CREATE INDEX i1 ON t1(a);211}212do_execsql_test 6.1.1 {213  SELECT min(a), b, c FROM t1 WHERE c='x';214} {{} 1 x}215do_execsql_test 6.1.2 {216  INSERT INTO t1 VALUES(1,    2, 'y');217} {}218do_execsql_test 6.1.3 {219  SELECT min(a), b, c FROM t1 WHERE c='x';220} {{} 1 x}221 222do_execsql_test 6.2.0 {223  CREATE TABLE t0(c0 UNIQUE, c1);224  INSERT INTO t0(c1) VALUES (0);225  INSERT INTO t0(c0) VALUES (0);226  CREATE VIEW v0(c0, c1) AS 227      SELECT t0.c1, t0.c0 FROM t0 WHERE CAST(t0.rowid AS INT) = 1;228}229do_execsql_test 6.2.1 {230  SELECT c0, c1 FROM v0;231} {0 {}}232do_execsql_test 6.2.2 {233  SELECT v0.c0, MIN(v0.c1) FROM v0;234} {0 {}}235 236finish_test237