AryaWu/sqlite
0
1# 2001 September 152#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing aggregate functions and the13# 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 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20# Build some test data21#22do_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 COMMIT33 }34 execsql {SELECT DISTINCT log FROM t1 ORDER BY log}35} {0 1 2 3 4 5}36 37# Basic aggregate functions.38#39do_test select3-1.1 {40 execsql {SELECT count(*) FROM t1}41} {31}42do_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 t146 }47} {1 0 31 5 496 124 16.0 4.0}48do_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 clauses53#54do_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}57do_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}60do_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}63do_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}66do_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}69do_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}72do_test select3-2.6 {73 execsql {74 SELECT log*2+1 as x, count(*) FROM t1 GROUP BY x ORDER BY x75 }76} {1 1 3 1 5 2 7 4 9 8 11 15}77do_test select3-2.7 {78 execsql {79 SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY y, x80 }81} {1 1 3 1 5 2 7 4 9 8 11 15}82do_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}}92do_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}}97do_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}}102do_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 BY109do_test select3-2.13 {110 catchsql {111 SELECT log, count(*) FROM t1 GROUP BY ORDER BY log;112 }113} {1 {near "ORDER": syntax error}}114do_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 BY121# 122# Update: As of 3.39.0, you can.123#124do_execsql_test select3-3.1 {125 SELECT log, count(*) FROM t1 HAVING log>=4126} {}127do_execsql_test select3-3.2 {128 SELECT count(*) FROM t1 HAVING log>=4129} {}130do_execsql_test select3-3.3 {131 SELECT count(*) FROM t1 HAVING log!=400132} {31}133 134# Toss in some HAVING clauses135#136do_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}139do_test select3-4.2 {140 execsql {141 SELECT log, count(*) FROM t1 142 GROUP BY log 143 HAVING count(*)>=4 144 ORDER BY log145 }146} {3 4 4 8 5 15}147do_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)+0153 }154} {3 4 4 8 5 15}155do_test select3-4.4 {156 execsql {157 SELECT log AS x, count(*) AS y FROM t1 158 GROUP BY x159 HAVING y>=4 160 ORDER BY max(n)+0161 }162} {3 4 4 8 5 15}163do_test select3-4.5 {164 execsql {165 SELECT log AS x FROM t1 166 GROUP BY x167 HAVING count(*)>=4 168 ORDER BY max(n)+0169 }170} {3 4 5}171 172do_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)+0177 }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}179do_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))+0184 }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 index188# on the GROUP BY column.189#190do_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}195do_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}200do_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}205do_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}210do_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}216do_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}221do_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}226do_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#234do_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} {}241do_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 values248# in it, the values are stored as integers to take up less space. The249# 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#253do_test select3-8.1 {254 execsql {255 CREATE TABLE A (256 A1 DOUBLE,257 A2 VARCHAR COLLATE NOCASE,258 A3 DOUBLE259 );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}265do_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://sqlite.org/src/tktview/6c1d3febc00b22d457c7272#273unset -nocomplain x274foreach {id x} {275 100 127276 101 128277 102 -127278 103 -128279 104 -129280 110 32767281 111 32768282 112 -32767283 113 -32768284 114 -32769285 120 2147483647286 121 2147483648287 122 -2147483647288 123 -2147483648289 124 -2147483649290 130 140737488355327291 131 140737488355328292 132 -140737488355327293 133 -140737488355328294 134 -140737488355329295 140 9223372036854775807296 141 -9223372036854775807297 142 -9223372036854775808298 143 9223372036854775806299 144 9223372036854775805300 145 -9223372036854775806301 146 -9223372036854775805302 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 e0c2ad1aa8a9c691317reset_db318do_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 324reset_db325do_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#-------------------------------------------------------------------------334# dbsqlfuzz crash-8e17857db2c5a9294c975123ac807156a6559f13.txt335# Associated with the flatten-left-join branch circa 2022-06-23.336#337foreach {tn sql} {338 1 {339 CREATE TABLE t1(a TEXT);340 CREATE TABLE t2(x INT);341 CREATE INDEX t2x ON t2(x);342 INSERT INTO t1 VALUES('abc');343 }344 2 {345 CREATE TABLE t1(a TEXT);346 CREATE TABLE t2(x INT);347 INSERT INTO t1 VALUES('abc');348 }349 3 {350 CREATE TABLE t1(a TEXT);351 CREATE TABLE t2(x INT);352 INSERT INTO t1 VALUES('abc');353 PRAGMA automatic_index=OFF;354 }355} {356 reset_db357 do_execsql_test select3-11.$tn.1 $sql 358 do_execsql_test select3.11.$tn.2 {359 SELECT max(a), val FROM t1 LEFT JOIN (360 SELECT 'constant' AS val FROM t2 WHERE x=1234361 )362 } {abc {}}363 do_execsql_test select3.11.$tn.3 {364 INSERT INTO t2 VALUES(123);365 SELECT max(a), val FROM t1 LEFT JOIN (366 SELECT 'constant' AS val FROM t2 WHERE x=1234367 )368 } {abc {}}369 do_execsql_test select3.11.$tn.4 {370 INSERT INTO t2 VALUES(1234);371 SELECT max(a), val FROM t1 LEFT JOIN (372 SELECT 'constant' AS val FROM t2 WHERE x=1234373 )374 } {abc constant}375}376 377reset_db378do_execsql_test 12.0 {379 CREATE TABLE t1(a);380 CREATE TABLE t2(x);381}382do_execsql_test 12.1 {383 SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;384}385do_execsql_test 12.2 {386 INSERT INTO t1 VALUES(1), (1), (2), (3);387 SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;388} {389 0 {}390 0 {}391 0 {}392}393do_execsql_test 12.3 {394 INSERT INTO t2 VALUES(45);395 SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;396} {397 2 59398 1 59399 1 59400}401do_execsql_test 12.4 {402 INSERT INTO t2 VALUES(210);403 SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;404} {405 4 59406 2 59407 2 59408}409do_execsql_test 12.5 {410 INSERT INTO t2 VALUES(NULL);411 SELECT count(x), m FROM t1 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;412} {413 4 59414 2 59415 2 59416}417do_execsql_test 12.6 {418 DELETE FROM t2;419 DELETE FROM t1;420 INSERT INTO t1 VALUES('value');421 INSERT INTO t2 VALUES('hello');422} {}423do_execsql_test 12.7 {424 SELECT group_concat(x), m FROM t1 425 LEFT JOIN (SELECT x, 59 AS m FROM t2) GROUP BY a;426} {427 hello 59428}429do_execsql_test 12.8 {430 SELECT group_concat(x), m, n FROM t1 431 LEFT JOIN (SELECT x, 59 AS m, 60 AS n FROM t2) GROUP BY a;432} {433 hello 59 60434}435 436finish_test437