sqlite3/test/select5.test

   1 # 2001 September 15
   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 # This file implements regression tests for SQLite library.  The
  12 # focus of this file is testing aggregate functions and the
  13 # GROUP BY and HAVING clauses of SELECT statements.
  14 #
  15 # $Id: select5.test,v 1.9 2005/01/26 03:58:36 danielk1977 Exp $
  16
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19
  20 # Build some test data
  21 #
  22 execsql {
  23   CREATE TABLE t1(x int, y int);
  24   BEGIN;
  25 }
  26 for {set i 1} {$i<32} {incr i} {
  27   for {set j 0} {pow(2,$j)<$i} {incr j} {}
  28   execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
  29 }
  30 execsql {
  31   COMMIT
  32 }
  33
  34 do_test select5-1.0 {
  35   execsql {SELECT DISTINCT y FROM t1 ORDER BY y}
  36 } {5 6 7 8 9 10}
  37
  38 # Sort by an aggregate function.
  39 #
  40 do_test select5-1.1 {
  41   execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY y}
  42 } {5 15 6 8 7 4 8 2 9 1 10 1}
  43 do_test select5-1.2 {
  44   execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY count(*), y}
  45 } {9 1 10 1 8 2 7 4 6 8 5 15}
  46 do_test select5-1.3 {
  47   execsql {SELECT count(*), y FROM t1 GROUP BY y ORDER BY count(*), y}
  48 } {1 9 1 10 2 8 4 7 8 6 15 5}
  49
  50 # Some error messages associated with aggregates and GROUP BY
  51 #
  52 do_test select5-2.1 {
  53   set v [catch {execsql {
  54     SELECT y, count(*) FROM t1 GROUP BY z ORDER BY y
  55   }} msg]
  56   lappend v $msg
  57 } {1 {no such column: z}}
  58 do_test select5-2.2 {
  59   set v [catch {execsql {
  60     SELECT y, count(*) FROM t1 GROUP BY z(y) ORDER BY y
  61   }} msg]
  62   lappend v $msg
  63 } {1 {no such function: z}}
  64 do_test select5-2.3 {
  65   set v [catch {execsql {
  66     SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<3 ORDER BY y
  67   }} msg]
  68   lappend v $msg
  69 } {0 {8 2 9 1 10 1}}
  70 do_test select5-2.4 {
  71   set v [catch {execsql {
  72     SELECT y, count(*) FROM t1 GROUP BY y HAVING z(y)<3 ORDER BY y
  73   }} msg]
  74   lappend v $msg
  75 } {1 {no such function: z}}
  76 do_test select5-2.5 {
  77   set v [catch {execsql {
  78     SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<z ORDER BY y
  79   }} msg]
  80   lappend v $msg
  81 } {1 {no such column: z}}
  82
  83 # Get the Agg function to rehash in vdbe.c
  84 #
  85 do_test select5-3.1 {
  86   execsql {
  87     SELECT x, count(*), avg(y) FROM t1 GROUP BY x HAVING x<4 ORDER BY x
  88   }
  89 } {1 1 5.0 2 1 5.0 3 1 5.0}
  90
  91 # Run various aggregate functions when the count is zero.
  92 #
  93 do_test select5-4.1 {
  94   execsql {
  95     SELECT avg(x) FROM t1 WHERE x>100
  96   }
  97 } {{}}
  98 do_test select5-4.2 {
  99   execsql {
 100     SELECT count(x) FROM t1 WHERE x>100
 101   }
 102 } {0}
 103 do_test select5-4.3 {
 104   execsql {
 105     SELECT min(x) FROM t1 WHERE x>100
 106   }
 107 } {{}}
 108 do_test select5-4.4 {
 109   execsql {
 110     SELECT max(x) FROM t1 WHERE x>100
 111   }
 112 } {{}}
 113 do_test select5-4.5 {
 114   execsql {
 115     SELECT sum(x) FROM t1 WHERE x>100
 116   }
 117 } {0.0}
 118
 119 # Some tests for queries with a GROUP BY clause but no aggregate functions.
 120 #
 121 # Note: The query in test case 5-5.5 are not legal SQL. So if the
 122 # implementation changes in the future and it returns different results,
 123 # this is not such a big deal.
 124 #
 125 do_test select5-5.1 {
 126   execsql {
 127     CREATE TABLE t2(a, b, c);
 128     INSERT INTO t2 VALUES(1, 2, 3);
 129     INSERT INTO t2 VALUES(1, 4, 5);
 130     INSERT INTO t2 VALUES(6, 4, 7);
 131     CREATE INDEX t2_idx ON t2(a);
 132   }
 133 } {}
 134 do_test select5-5.2 {
 135   execsql {
 136     SELECT a FROM t2 GROUP BY a;
 137   }
 138 } {1 6}
 139 do_test select5-5.3 {
 140   execsql {
 141     SELECT a FROM t2 WHERE a>2 GROUP BY a;
 142   }
 143 } {6}
 144 do_test select5-5.4 {
 145   execsql {
 146     SELECT a, b FROM t2 GROUP BY a, b;
 147   }
 148 } {1 2 1 4 6 4}
 149 do_test select5-5.5 {
 150   execsql {
 151     SELECT a, b FROM t2 GROUP BY a;
 152   }
 153 } {1 2 6 4}
 154
 155 finish_test
 156