1# 2009 February 24 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 "SELECT count(*)" statements. 13# 14# $Id: count.test,v 1.6 2009/06/05 17:09:12 drh Exp $ 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19# Test plan: 20# 21# count-0.*: Make sure count(*) works on an empty database. (Ticket #3774) 22# 23# count-1.*: Test that the OP_Count instruction appears to work on both 24# tables and indexes. Test both when they contain 0 entries, 25# when all entries are on the root page, and when the b-tree 26# forms a structure 2 and 3 levels deep. 27# 28# count-2.*: Test that 29# 30# 31 32do_test count-0.1 { 33 db eval { 34 SELECT count(*) FROM sqlite_master; 35 } 36} {0} 37 38set iTest 0 39foreach zIndex [list { 40 /* no-op */ 41} { 42 CREATE INDEX i1 ON t1(a); 43}] { 44 incr iTest 45 do_test count-1.$iTest.1 { 46 execsql { 47 DROP TABLE IF EXISTS t1; 48 CREATE TABLE t1(a, b); 49 } 50 execsql $zIndex 51 execsql { SELECT count(*) FROM t1 } 52 } {0} 53 54 do_test count-1.$iTest.2 { 55 execsql { 56 INSERT INTO t1 VALUES(1, 2); 57 INSERT INTO t1 VALUES(3, 4); 58 SELECT count(*) FROM t1; 59 } 60 } {2} 61 62 do_test count-1.$iTest.3 { 63 execsql { 64 INSERT INTO t1 SELECT * FROM t1; -- 4 65 INSERT INTO t1 SELECT * FROM t1; -- 8 66 INSERT INTO t1 SELECT * FROM t1; -- 16 67 INSERT INTO t1 SELECT * FROM t1; -- 32 68 INSERT INTO t1 SELECT * FROM t1; -- 64 69 INSERT INTO t1 SELECT * FROM t1; -- 128 70 INSERT INTO t1 SELECT * FROM t1; -- 256 71 SELECT count(*) FROM t1; 72 } 73 } {256} 74 75 do_test count-1.$iTest.4 { 76 execsql { 77 INSERT INTO t1 SELECT * FROM t1; -- 512 78 INSERT INTO t1 SELECT * FROM t1; -- 1024 79 INSERT INTO t1 SELECT * FROM t1; -- 2048 80 INSERT INTO t1 SELECT * FROM t1; -- 4096 81 SELECT count(*) FROM t1; 82 } 83 } {4096} 84 85 do_test count-1.$iTest.5 { 86 execsql { 87 BEGIN; 88 INSERT INTO t1 SELECT * FROM t1; -- 8192 89 INSERT INTO t1 SELECT * FROM t1; -- 16384 90 INSERT INTO t1 SELECT * FROM t1; -- 32768 91 INSERT INTO t1 SELECT * FROM t1; -- 65536 92 COMMIT; 93 SELECT count(*) FROM t1; 94 } 95 } {65536} 96} 97 98proc uses_op_count {sql} { 99 if {[lsearch [execsql "EXPLAIN $sql"] Count]>=0} { 100 return 1; 101 } 102 return 0 103} 104 105do_test count-2.1 { 106 execsql { 107 CREATE TABLE t2(a, b); 108 } 109 uses_op_count {SELECT count(*) FROM t2} 110} {1} 111do_test count-2.2 { 112 catchsql {SELECT count(DISTINCT *) FROM t2} 113} {1 {near "*": syntax error}} 114do_test count-2.3 { 115 uses_op_count {SELECT count(DISTINCT a) FROM t2} 116} {0} 117do_test count-2.4 { 118 uses_op_count {SELECT count(a) FROM t2} 119} {0} 120do_test count-2.5 { 121 uses_op_count {SELECT count() FROM t2} 122} {1} 123do_test count-2.6 { 124 catchsql {SELECT count(DISTINCT) FROM t2} 125} {1 {DISTINCT aggregates must have exactly one argument}} 126do_test count-2.7 { 127 uses_op_count {SELECT count(*)+1 FROM t2} 128} {0} 129do_test count-2.8 { 130 uses_op_count {SELECT count(*) FROM t2 WHERE a IS NOT NULL} 131} {0} 132do_test count-2.9 { 133 catchsql {SELECT count(*) FROM t2 HAVING count(*)>1} 134} {1 {a GROUP BY clause is required before HAVING}} 135do_test count-2.10 { 136 uses_op_count {SELECT count(*) FROM (SELECT 1)} 137} {0} 138do_test count-2.11 { 139 execsql { CREATE VIEW v1 AS SELECT 1 AS a } 140 uses_op_count {SELECT count(*) FROM v1} 141} {0} 142do_test count-2.12 { 143 uses_op_count {SELECT count(*), max(a) FROM t2} 144} {0} 145do_test count-2.13 { 146 uses_op_count {SELECT count(*) FROM t1, t2} 147} {0} 148 149ifcapable vtab { 150 register_echo_module [sqlite3_connection_pointer db] 151 do_test count-2.14 { 152 execsql { CREATE VIRTUAL TABLE techo USING echo(t1); } 153 uses_op_count {SELECT count(*) FROM techo} 154 } {0} 155} 156 157do_test count-3.1 { 158 execsql { 159 CREATE TABLE t3(a, b); 160 SELECT a FROM (SELECT count(*) AS a FROM t3) WHERE a==0; 161 } 162} {0} 163do_test count-3.2 { 164 execsql { 165 SELECT a FROM (SELECT count(*) AS a FROM t3) WHERE a==1; 166 } 167} {} 168 169do_test count-4.1 { 170 execsql { 171 CREATE TABLE t4(a, b); 172 INSERT INTO t4 VALUES('a', 'b'); 173 CREATE INDEX t4i1 ON t4(b, a); 174 SELECT count(*) FROM t4; 175 } 176} {1} 177do_test count-4.2 { 178 execsql { 179 CREATE INDEX t4i2 ON t4(b); 180 SELECT count(*) FROM t4; 181 } 182} {1} 183do_test count-4.3 { 184 execsql { 185 DROP INDEX t4i1; 186 CREATE INDEX t4i1 ON t4(b, a); 187 SELECT count(*) FROM t4; 188 } 189} {1} 190 191 192finish_test 193