gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/select5.test (about) 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