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