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
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18
  19 # Build some test data
  20 #
  21 execsql {
  22   CREATE TABLE t1(x int, y int);
  23   BEGIN;
  24 }
  25 for {set i 1} {$i<32} {incr i} {
  26   for {set j 0} {(1<<$j)<$i} {incr j} {}
  27   execsql "INSERT INTO t1 VALUES([expr {32-$i}],[expr {10-$j}])"
  28 }
  29 execsql {
  30   COMMIT
  31 }
  32
  33 do_test select5-1.0 {
  34   execsql {SELECT DISTINCT y FROM t1 ORDER BY y}
  35 } {5 6 7 8 9 10}
  36
  37 # Sort by an aggregate function.
  38 #
  39 do_test select5-1.1 {
  40   execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY y}
  41 } {5 15 6 8 7 4 8 2 9 1 10 1}
  42 do_test select5-1.2 {
  43   execsql {SELECT y, count(*) FROM t1 GROUP BY y ORDER BY count(*), y}
  44 } {9 1 10 1 8 2 7 4 6 8 5 15}
  45 do_test select5-1.3 {
  46   execsql {SELECT count(*), y FROM t1 GROUP BY y ORDER BY count(*), y}
  47 } {1 9 1 10 2 8 4 7 8 6 15 5}
  48
  49 # Some error messages associated with aggregates and GROUP BY
  50 #
  51 do_test select5-2.1.1 {
  52   catchsql {
  53     SELECT y, count(*) FROM t1 GROUP BY z ORDER BY y
  54   }
  55 } {1 {no such column: z}}
  56 do_test select5-2.1.2 {
  57   catchsql {
  58     SELECT y, count(*) FROM t1 GROUP BY temp.t1.y ORDER BY y
  59   }
  60 } {1 {no such column: temp.t1.y}}
  61 do_test select5-2.2 {
  62   set v [catch {execsql {
  63     SELECT y, count(*) FROM t1 GROUP BY z(y) ORDER BY y
  64   }} msg]
  65   lappend v $msg
  66 } {1 {no such function: z}}
  67 do_test select5-2.3 {
  68   set v [catch {execsql {
  69     SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<3 ORDER BY y
  70   }} msg]
  71   lappend v $msg
  72 } {0 {8 2 9 1 10 1}}
  73 do_test select5-2.4 {
  74   set v [catch {execsql {
  75     SELECT y, count(*) FROM t1 GROUP BY y HAVING z(y)<3 ORDER BY y
  76   }} msg]
  77   lappend v $msg
  78 } {1 {no such function: z}}
  79 do_test select5-2.5 {
  80   set v [catch {execsql {
  81     SELECT y, count(*) FROM t1 GROUP BY y HAVING count(*)<z ORDER BY y
  82   }} msg]
  83   lappend v $msg
  84 } {1 {no such column: z}}
  85
  86 # Get the Agg function to rehash in vdbe.c
  87 #
  88 do_test select5-3.1 {
  89   execsql {
  90     SELECT x, count(*), avg(y) FROM t1 GROUP BY x HAVING x<4 ORDER BY x
  91   }
  92 } {1 1 5.0 2 1 5.0 3 1 5.0}
  93
  94 # Run various aggregate functions when the count is zero.
  95 #
  96 do_test select5-4.1 {
  97   execsql {
  98     SELECT avg(x) FROM t1 WHERE x>100
  99   }
 100 } {{}}
 101 do_test select5-4.2 {
 102   execsql {
 103     SELECT count(x) FROM t1 WHERE x>100
 104   }
 105 } {0}
 106 do_test select5-4.3 {
 107   execsql {
 108     SELECT min(x) FROM t1 WHERE x>100
 109   }
 110 } {{}}
 111 do_test select5-4.4 {
 112   execsql {
 113     SELECT max(x) FROM t1 WHERE x>100
 114   }
 115 } {{}}
 116 do_test select5-4.5 {
 117   execsql {
 118     SELECT sum(x) FROM t1 WHERE x>100
 119   }
 120 } {{}}
 121
 122 # Some tests for queries with a GROUP BY clause but no aggregate functions.
 123 #
 124 # Note: The query in test cases 5.1 through 5.5 are not legal SQL. So if the
 125 # implementation changes in the future and it returns different results,
 126 # this is not such a big deal.
 127 #
 128 do_test select5-5.1 {
 129   execsql {
 130     CREATE TABLE t2(a, b, c);
 131     INSERT INTO t2 VALUES(1, 2, 3);
 132     INSERT INTO t2 VALUES(1, 4, 5);
 133     INSERT INTO t2 VALUES(6, 4, 7);
 134     CREATE INDEX t2_idx ON t2(a);
 135   }
 136 } {}
 137 do_test select5-5.2 {
 138   execsql {
 139     SELECT a FROM t2 GROUP BY a;
 140   }
 141 } {1 6}
 142 do_test select5-5.3 {
 143   execsql {
 144     SELECT a FROM t2 WHERE a>2 GROUP BY a;
 145   }
 146 } {6}
 147 do_test select5-5.4 {
 148   execsql {
 149     SELECT a, b FROM t2 GROUP BY a, b;
 150   }
 151 } {1 2 1 4 6 4}
 152 do_test select5-5.5 {
 153   execsql {
 154     SELECT a, b FROM t2 GROUP BY a;
 155   }
 156 } {1 2 6 4}
 157
 158 # Test rendering of columns for the GROUP BY clause.
 159 #
 160 do_test select5-5.11 {
 161   execsql {
 162     SELECT max(c), b*a, b, a FROM t2 GROUP BY b*a, b, a
 163   }
 164 } {3 2 2 1 5 4 4 1 7 24 4 6}
 165
 166 # NULL compare equal to each other for the purposes of processing
 167 # the GROUP BY clause.
 168 #
 169 do_test select5-6.1 {
 170   execsql {
 171     CREATE TABLE t3(x,y);
 172     INSERT INTO t3 VALUES(1,NULL);
 173     INSERT INTO t3 VALUES(2,NULL);
 174     INSERT INTO t3 VALUES(3,4);
 175     SELECT count(x), y FROM t3 GROUP BY y ORDER BY 1
 176   }
 177 } {1 4 2 {}}
 178 do_test select5-6.2 {
 179   execsql {
 180     CREATE TABLE t4(x,y,z);
 181     INSERT INTO t4 VALUES(1,2,NULL);
 182     INSERT INTO t4 VALUES(2,3,NULL);
 183     INSERT INTO t4 VALUES(3,NULL,5);
 184     INSERT INTO t4 VALUES(4,NULL,6);
 185     INSERT INTO t4 VALUES(4,NULL,6);
 186     INSERT INTO t4 VALUES(5,NULL,NULL);
 187     INSERT INTO t4 VALUES(5,NULL,NULL);
 188     INSERT INTO t4 VALUES(6,7,8);
 189     SELECT max(x), count(x), y, z FROM t4 GROUP BY y, z ORDER BY 1
 190   }
 191 } {1 1 2 {} 2 1 3 {} 3 1 {} 5 4 2 {} 6 5 2 {} {} 6 1 7 8}
 192
 193 do_test select5-7.2 {
 194   execsql {
 195     SELECT count(*), count(x) as cnt FROM t4 GROUP BY y ORDER BY cnt;
 196   }
 197 } {1 1 1 1 1 1 5 5}
 198
 199 # See ticket #3324.
 200 #
 201 do_test select5-8.1 {
 202   execsql {
 203     CREATE TABLE t8a(a,b);
 204     CREATE TABLE t8b(x);
 205     INSERT INTO t8a VALUES('one', 1);
 206     INSERT INTO t8a VALUES('one', 2);
 207     INSERT INTO t8a VALUES('two', 3);
 208     INSERT INTO t8a VALUES('one', NULL);
 209     INSERT INTO t8b(rowid,x) VALUES(1,111);
 210     INSERT INTO t8b(rowid,x) VALUES(2,222);
 211     INSERT INTO t8b(rowid,x) VALUES(3,333);
 212     SELECT a, count(b) FROM t8a, t8b WHERE b=t8b.rowid GROUP BY a ORDER BY a;
 213   }
 214 } {one 2 two 1}
 215 do_test select5-8.2 {
 216   execsql {
 217     SELECT a, count(b) FROM t8a, t8b WHERE b=+t8b.rowid GROUP BY a ORDER BY a;
 218   }
 219 } {one 2 two 1}
 220 do_test select5-8.3 {
 221   execsql {
 222     SELECT t8a.a, count(t8a.b) FROM t8a, t8b WHERE t8a.b=t8b.rowid
 223      GROUP BY 1 ORDER BY 1;
 224   }
 225 } {one 2 two 1}
 226 do_test select5-8.4 {
 227   execsql {
 228     SELECT a, count(*) FROM t8a, t8b WHERE b=+t8b.rowid GROUP BY a ORDER BY a;
 229   }
 230 } {one 2 two 1}
 231 do_test select5-8.5 {
 232   execsql {
 233     SELECT a, count(b) FROM t8a, t8b WHERE b<x GROUP BY a ORDER BY a;
 234   }
 235 } {one 6 two 3}
 236 do_test select5-8.6 {
 237   execsql {
 238     SELECT a, count(t8a.b) FROM t8a, t8b WHERE b=t8b.rowid
 239      GROUP BY a ORDER BY 2;
 240   }
 241 } {two 1 one 2}
 242 do_test select5-8.7 {
 243   execsql {
 244     SELECT a, count(b) FROM t8a, t8b GROUP BY a ORDER BY 2;
 245   }
 246 } {two 3 one 6}
 247 do_test select5-8.8 {
 248   execsql {
 249     SELECT a, count(*) FROM t8a, t8b GROUP BY a ORDER BY 2;
 250   }
 251 } {two 3 one 9}
 252
 253 # 2021-04-26 forum https://sqlite.org/forum/forumpost/74330094d8
 254 reset_db
 255 do_execsql_test select5-9.1 {
 256   CREATE TABLE t1(a INT, b INT);
 257   INSERT INTO t1(a,b) VALUES(1,null),(null,null),(1,null);
 258   CREATE UNIQUE INDEX t1b ON t1(abs(b));
 259   SELECT quote(a), quote(b), '|' FROM t1 GROUP BY a, abs(b);
 260 } {NULL NULL | 1 NULL |}
 261
 262 finish_test