gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/select3.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 # $Id: select3.test,v 1.23 2008/01/16 18:20:42 danielk1977 Exp $ 16 17 set testdir [file dirname $argv0] 18 source $testdir/tester.tcl 19 20 # Build some test data 21 # 22 do_test select3-1.0 { 23 execsql { 24 CREATE TABLE t1(n int, log int); 25 BEGIN; 26 } 27 for {set i 1} {$i<32} {incr i} { 28 for {set j 0} {(1<<$j)<$i} {incr j} {} 29 execsql "INSERT INTO t1 VALUES($i,$j)" 30 } 31 execsql { 32 COMMIT 33 } 34 execsql {SELECT DISTINCT log FROM t1 ORDER BY log} 35 } {0 1 2 3 4 5} 36 37 # Basic aggregate functions. 38 # 39 do_test select3-1.1 { 40 execsql {SELECT count(*) FROM t1} 41 } {31} 42 do_test select3-1.2 { 43 execsql { 44 SELECT min(n),min(log),max(n),max(log),sum(n),sum(log),avg(n),avg(log) 45 FROM t1 46 } 47 } {1 0 31 5 496 124 16.0 4.0} 48 do_test select3-1.3 { 49 execsql {SELECT max(n)/avg(n), max(log)/avg(log) FROM t1} 50 } {1.9375 1.25} 51 52 # Try some basic GROUP BY clauses 53 # 54 do_test select3-2.1 { 55 execsql {SELECT log, count(*) FROM t1 GROUP BY log ORDER BY log} 56 } {0 1 1 1 2 2 3 4 4 8 5 15} 57 do_test select3-2.2 { 58 execsql {SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log} 59 } {0 1 1 2 2 3 3 5 4 9 5 17} 60 do_test select3-2.3.1 { 61 execsql {SELECT log, avg(n) FROM t1 GROUP BY log ORDER BY log} 62 } {0 1.0 1 2.0 2 3.5 3 6.5 4 12.5 5 24.0} 63 do_test select3-2.3.2 { 64 execsql {SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log} 65 } {0 2.0 1 3.0 2 4.5 3 7.5 4 13.5 5 25.0} 66 do_test select3-2.4 { 67 execsql {SELECT log, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log} 68 } {0 0.0 1 0.0 2 0.5 3 1.5 4 3.5 5 7.0} 69 do_test select3-2.5 { 70 execsql {SELECT log*2+1, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log} 71 } {1 0.0 3 0.0 5 0.5 7 1.5 9 3.5 11 7.0} 72 do_test select3-2.6 { 73 execsql { 74 SELECT log*2+1 as x, count(*) FROM t1 GROUP BY x ORDER BY x 75 } 76 } {1 1 3 1 5 2 7 4 9 8 11 15} 77 do_test select3-2.7 { 78 execsql { 79 SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY y, x 80 } 81 } {1 1 3 1 5 2 7 4 9 8 11 15} 82 do_test select3-2.8 { 83 execsql { 84 SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y) 85 } 86 } {11 15 9 8 7 4 5 2 3 1 1 1} 87 #do_test select3-2.9 { 88 # catchsql { 89 # SELECT log, count(*) FROM t1 GROUP BY 'x' ORDER BY log; 90 # } 91 #} {1 {GROUP BY terms must not be non-integer constants}} 92 do_test select3-2.10 { 93 catchsql { 94 SELECT log, count(*) FROM t1 GROUP BY 0 ORDER BY log; 95 } 96 } {1 {1st GROUP BY term out of range - should be between 1 and 2}} 97 do_test select3-2.11 { 98 catchsql { 99 SELECT log, count(*) FROM t1 GROUP BY 3 ORDER BY log; 100 } 101 } {1 {1st GROUP BY term out of range - should be between 1 and 2}} 102 do_test select3-2.12 { 103 catchsql { 104 SELECT log, count(*) FROM t1 GROUP BY 1 ORDER BY log; 105 } 106 } {0 {0 1 1 1 2 2 3 4 4 8 5 15}} 107 108 # Cannot have an empty GROUP BY 109 do_test select3-2.13 { 110 catchsql { 111 SELECT log, count(*) FROM t1 GROUP BY ORDER BY log; 112 } 113 } {1 {near "ORDER": syntax error}} 114 do_test select3-2.14 { 115 catchsql { 116 SELECT log, count(*) FROM t1 GROUP BY; 117 } 118 } {1 {near ";": syntax error}} 119 120 # Cannot have a HAVING without a GROUP BY 121 # 122 # Update: As of 3.39.0, you can. 123 # 124 do_execsql_test select3-3.1 { 125 SELECT log, count(*) FROM t1 HAVING log>=4 126 } {} 127 do_execsql_test select3-3.2 { 128 SELECT count(*) FROM t1 HAVING log>=4 129 } {} 130 do_execsql_test select3-3.3 { 131 SELECT count(*) FROM t1 HAVING log!=400 132 } {31} 133 134 # Toss in some HAVING clauses 135 # 136 do_test select3-4.1 { 137 execsql {SELECT log, count(*) FROM t1 GROUP BY log HAVING log>=4 ORDER BY log} 138 } {4 8 5 15} 139 do_test select3-4.2 { 140 execsql { 141 SELECT log, count(*) FROM t1 142 GROUP BY log 143 HAVING count(*)>=4 144 ORDER BY log 145 } 146 } {3 4 4 8 5 15} 147 do_test select3-4.3 { 148 execsql { 149 SELECT log, count(*) FROM t1 150 GROUP BY log 151 HAVING count(*)>=4 152 ORDER BY max(n)+0 153 } 154 } {3 4 4 8 5 15} 155 do_test select3-4.4 { 156 execsql { 157 SELECT log AS x, count(*) AS y FROM t1 158 GROUP BY x 159 HAVING y>=4 160 ORDER BY max(n)+0 161 } 162 } {3 4 4 8 5 15} 163 do_test select3-4.5 { 164 execsql { 165 SELECT log AS x FROM t1 166 GROUP BY x 167 HAVING count(*)>=4 168 ORDER BY max(n)+0 169 } 170 } {3 4 5} 171 172 do_test select3-5.1 { 173 execsql { 174 SELECT log, count(*), avg(n), max(n+log*2) FROM t1 175 GROUP BY log 176 ORDER BY max(n+log*2)+0, avg(n)+0 177 } 178 } {0 1 1.0 1 1 1 2.0 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24.0 41} 179 do_test select3-5.2 { 180 execsql { 181 SELECT log, count(*), avg(n), max(n+log*2) FROM t1 182 GROUP BY log 183 ORDER BY max(n+log*2)+0, min(log,avg(n))+0 184 } 185 } {0 1 1.0 1 1 1 2.0 4 2 2 3.5 8 3 4 6.5 14 4 8 12.5 24 5 15 24.0 41} 186 187 # Test sorting of GROUP BY results in the presence of an index 188 # on the GROUP BY column. 189 # 190 do_test select3-6.1 { 191 execsql { 192 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log; 193 } 194 } {0 1 1 2 2 3 3 5 4 9 5 17} 195 do_test select3-6.2 { 196 execsql { 197 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC; 198 } 199 } {5 17 4 9 3 5 2 3 1 2 0 1} 200 do_test select3-6.3 { 201 execsql { 202 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1; 203 } 204 } {0 1 1 2 2 3 3 5 4 9 5 17} 205 do_test select3-6.4 { 206 execsql { 207 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC; 208 } 209 } {5 17 4 9 3 5 2 3 1 2 0 1} 210 do_test select3-6.5 { 211 execsql { 212 CREATE INDEX i1 ON t1(log); 213 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log; 214 } 215 } {0 1 1 2 2 3 3 5 4 9 5 17} 216 do_test select3-6.6 { 217 execsql { 218 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log DESC; 219 } 220 } {5 17 4 9 3 5 2 3 1 2 0 1} 221 do_test select3-6.7 { 222 execsql { 223 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1; 224 } 225 } {0 1 1 2 2 3 3 5 4 9 5 17} 226 do_test select3-6.8 { 227 execsql { 228 SELECT log, min(n) FROM t1 GROUP BY log ORDER BY 1 DESC; 229 } 230 } {5 17 4 9 3 5 2 3 1 2 0 1} 231 232 # Sometimes an aggregate query can return no rows at all. 233 # 234 do_test select3-7.1 { 235 execsql { 236 CREATE TABLE t2(a,b); 237 INSERT INTO t2 VALUES(1,2); 238 SELECT a, sum(b) FROM t2 WHERE b=5 GROUP BY a; 239 } 240 } {} 241 do_test select3-7.2 { 242 execsql { 243 SELECT a, sum(b) FROM t2 WHERE b=5; 244 } 245 } {{} {}} 246 247 # If a table column is of type REAL but we are storing integer values 248 # in it, the values are stored as integers to take up less space. The 249 # values are converted by to REAL as they are read out of the table. 250 # Make sure the GROUP BY clause does this conversion correctly. 251 # Ticket #2251. 252 # 253 do_test select3-8.1 { 254 execsql { 255 CREATE TABLE A ( 256 A1 DOUBLE, 257 A2 VARCHAR COLLATE NOCASE, 258 A3 DOUBLE 259 ); 260 INSERT INTO A VALUES(39136,'ABC',1201900000); 261 INSERT INTO A VALUES(39136,'ABC',1207000000); 262 SELECT typeof(sum(a3)) FROM a; 263 } 264 } {real} 265 do_test select3-8.2 { 266 execsql { 267 SELECT typeof(sum(a3)) FROM a GROUP BY a1; 268 } 269 } {real} 270 271 # 2019-05-09 ticket https://www.sqlite.org/src/tktview/6c1d3febc00b22d457c7 272 # 273 unset -nocomplain x 274 foreach {id x} { 275 100 127 276 101 128 277 102 -127 278 103 -128 279 104 -129 280 110 32767 281 111 32768 282 112 -32767 283 113 -32768 284 114 -32769 285 120 2147483647 286 121 2147483648 287 122 -2147483647 288 123 -2147483648 289 124 -2147483649 290 130 140737488355327 291 131 140737488355328 292 132 -140737488355327 293 133 -140737488355328 294 134 -140737488355329 295 140 9223372036854775807 296 141 -9223372036854775807 297 142 -9223372036854775808 298 143 9223372036854775806 299 144 9223372036854775805 300 145 -9223372036854775806 301 146 -9223372036854775805 302 303 } { 304 set x [expr {$x+0}] 305 do_execsql_test select3-8.$id { 306 DROP TABLE IF EXISTS t1; 307 CREATE TABLE t1 (c0, c1 REAL PRIMARY KEY); 308 INSERT INTO t1(c0, c1) VALUES (0, $x), (0, 0); 309 UPDATE t1 SET c0 = NULL; 310 UPDATE OR REPLACE t1 SET c1 = 1; 311 SELECT DISTINCT * FROM t1 WHERE (t1.c0 IS NULL); 312 PRAGMA integrity_check; 313 } {{} 1.0 ok} 314 } 315 316 # 2020-03-10 ticket e0c2ad1aa8a9c691 317 reset_db 318 do_execsql_test select3-9.100 { 319 CREATE TABLE t0(c0 REAL, c1 REAL GENERATED ALWAYS AS (c0)); 320 INSERT INTO t0(c0) VALUES (1); 321 SELECT * FROM t0 GROUP BY c0; 322 } {1.0 1.0} 323 324 reset_db 325 do_execsql_test select3.10.100 { 326 CREATE TABLE t1(a, b); 327 CREATE TABLE t2(c, d); 328 SELECT max(t1.a), 329 (SELECT 'xyz' FROM (SELECT * FROM t2 WHERE 0) WHERE t1.b=1) 330 FROM t1; 331 } {{} {}} 332 333 finish_test