wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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