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 16 set testdir [file dirname $argv0] 17 source $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 32 do_test count-0.1 { 33 db eval { 34 SELECT count(*) FROM sqlite_master; 35 } 36 } {0} 37 38 set iTest 0 39 foreach 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 98 proc uses_op_count {sql} { 99 if {[lsearch [execsql "EXPLAIN $sql"] Count]>=0} { 100 return 1; 101 } 102 return 0 103 } 104 105 do_test count-2.1 { 106 execsql { 107 CREATE TABLE t2(a, b); 108 } 109 uses_op_count {SELECT count(*) FROM t2} 110 } {1} 111 do_test count-2.2 { 112 catchsql {SELECT count(DISTINCT *) FROM t2} 113 } {1 {near "*": syntax error}} 114 do_test count-2.3 { 115 uses_op_count {SELECT count(DISTINCT a) FROM t2} 116 } {0} 117 do_test count-2.4 { 118 uses_op_count {SELECT count(a) FROM t2} 119 } {0} 120 do_test count-2.5 { 121 uses_op_count {SELECT count() FROM t2} 122 } {1} 123 do_test count-2.6 { 124 catchsql {SELECT count(DISTINCT) FROM t2} 125 } {1 {DISTINCT aggregates must have exactly one argument}} 126 do_test count-2.7 { 127 uses_op_count {SELECT count(*)+1 FROM t2} 128 } {0} 129 do_test count-2.8 { 130 uses_op_count {SELECT count(*) FROM t2 WHERE a IS NOT NULL} 131 } {0} 132 do_test count-2.9 { 133 catchsql {SELECT count(*) FROM t2 HAVING count(*)>1} 134 } {1 {a GROUP BY clause is required before HAVING}} 135 do_test count-2.10 { 136 uses_op_count {SELECT count(*) FROM (SELECT 1)} 137 } {0} 138 do_test count-2.11 { 139 execsql { CREATE VIEW v1 AS SELECT 1 AS a } 140 uses_op_count {SELECT count(*) FROM v1} 141 } {0} 142 do_test count-2.12 { 143 uses_op_count {SELECT count(*), max(a) FROM t2} 144 } {0} 145 do_test count-2.13 { 146 uses_op_count {SELECT count(*) FROM t1, t2} 147 } {0} 148 149 ifcapable 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 157 do_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} 163 do_test count-3.2 { 164 execsql { 165 SELECT a FROM (SELECT count(*) AS a FROM t3) WHERE a==1; 166 } 167 } {} 168 169 do_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} 177 do_test count-4.2 { 178 execsql { 179 CREATE INDEX t4i2 ON t4(b); 180 SELECT count(*) FROM t4; 181 } 182 } {1} 183 do_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 192 finish_test 193
