Debian Codegraph @

test/count.test

raw
1 # 2009-02-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
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17
18 # Test plan:
19 #
20 # count-0.*: Make sure count(*) works on an empty database. (Ticket #3774)
21 #
22 # count-1.*: Test that the OP_Count instruction appears to work on both
23 # tables and indexes. Test both when they contain 0 entries,
24 # when all entries are on the root page, and when the b-tree
25 # forms a structure 2 and 3 levels deep.
26 #
27 #
28
29 do_test count-0.1 {
30 db eval {
31 SELECT count(*) FROM sqlite_master;
32 }
33 } {0}
34
35 set iTest 0
36 foreach zIndex [list {
37 /* no-op */
38 } {
39 CREATE INDEX i1 ON t1(a);
40 }] {
41 incr iTest
42 do_test count-1.$iTest.1 {
43 execsql {
44 DROP TABLE IF EXISTS t1;
45 CREATE TABLE t1(a, b);
46 }
47 execsql $zIndex
48 execsql { SELECT count(*) FROM t1 }
49 } {0}
50
51 do_test count-1.$iTest.2 {
52 execsql {
53 INSERT INTO t1 VALUES(1, 2);
54 INSERT INTO t1 VALUES(3, 4);
55 SELECT count(*) FROM t1;
56 }
57 } {2}
58
59 do_test count-1.$iTest.3 {
60 execsql {
61 INSERT INTO t1 SELECT * FROM t1; -- 4
62 INSERT INTO t1 SELECT * FROM t1; -- 8
63 INSERT INTO t1 SELECT * FROM t1; -- 16
64 INSERT INTO t1 SELECT * FROM t1; -- 32
65 INSERT INTO t1 SELECT * FROM t1; -- 64
66 INSERT INTO t1 SELECT * FROM t1; -- 128
67 INSERT INTO t1 SELECT * FROM t1; -- 256
68 SELECT count(*) FROM t1;
69 }
70 } {256}
71
72 do_test count-1.$iTest.4 {
73 execsql {
74 INSERT INTO t1 SELECT * FROM t1; -- 512
75 INSERT INTO t1 SELECT * FROM t1; -- 1024
76 INSERT INTO t1 SELECT * FROM t1; -- 2048
77 INSERT INTO t1 SELECT * FROM t1; -- 4096
78 SELECT count(*) FROM t1;
79 }
80 } {4096}
81
82 do_test count-1.$iTest.5 {
83 execsql {
84 BEGIN;
85 INSERT INTO t1 SELECT * FROM t1; -- 8192
86 INSERT INTO t1 SELECT * FROM t1; -- 16384
87 INSERT INTO t1 SELECT * FROM t1; -- 32768
88 INSERT INTO t1 SELECT * FROM t1; -- 65536
89 COMMIT;
90 SELECT count(*) FROM t1;
91 }
92 } {65536}
93 }
94
95 proc uses_op_count {sql} {
96 if {[lsearch [execsql "EXPLAIN $sql"] Count]>=0} {
97 return 1;
98 }
99 return 0
100 }
101
102 do_test count-2.1 {
103 execsql {
104 CREATE TABLE t2(a, b);
105 }
106 uses_op_count {SELECT count(*) FROM t2}
107 } {1}
108 do_test count-2.2 {
109 catchsql {SELECT count(DISTINCT *) FROM t2}
110 } {1 {near "*": syntax error}}
111 do_test count-2.3 {
112 uses_op_count {SELECT count(DISTINCT a) FROM t2}
113 } {0}
114 do_test count-2.4 {
115 uses_op_count {SELECT count(a) FROM t2}
116 } {0}
117 do_test count-2.5 {
118 uses_op_count {SELECT count() FROM t2}
119 } {1}
120 do_test count-2.6 {
121 catchsql {SELECT count(DISTINCT) FROM t2}
122 } {1 {DISTINCT aggregates must have exactly one argument}}
123 do_test count-2.7 {
124 uses_op_count {SELECT count(*)+1 FROM t2}
125 } {0}
126 do_test count-2.8 {
127 uses_op_count {SELECT count(*) FROM t2 WHERE a IS NOT NULL}
128 } {0}
129 do_execsql_test count-2.9a {
130 SELECT count(*) FROM t2 HAVING count(*)>1;
131 } {}
132 do_execsql_test count-2.9b {
133 SELECT count(*) FROM t2 HAVING count(*)<10;
134 } {0}
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 do_execsql_test count-5.1 {
192 CREATE TABLE t5(a TEXT PRIMARY KEY, b VARCHAR(50)) WITHOUT ROWID;
193 INSERT INTO t5 VALUES('bison','jazz');
194 SELECT count(*) FROM t5;
195 } {1}
196
197 do_catchsql_test count-6.1 {
198 CREATE TABLE t6(x);
199 SELECT count(DISTINCT) FROM t6 GROUP BY x;
200 } {1 {DISTINCT aggregates must have exactly one argument}}
201
202 # 2020-05-08.
203 # The count() optimization should honor the NOT INDEXED clause
204 #
205 reset_db
206 do_execsql_test count-7.1 {
207 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c VARCHAR(1000));
208 CREATE INDEX t1b ON t1(b);
209 INSERT INTO t1(a,b,c) values(1,2,'count.test cases for NOT INDEXED');
210 ANALYZE;
211 UPDATE sqlite_stat1 SET stat='1000000 10' WHERE idx='t1b';
212 ANALYZE sqlite_master;
213 }
214 do_eqp_test count-7.2 {
215 SELECT count(1) FROM t1;
216 } {
217 QUERY PLAN
218 `--SCAN t1 USING COVERING INDEX t1b
219 }
220 do_eqp_test count-7.3 {
221 SELECT count(1) FROM t1 NOT INDEXED
222 } {
223 QUERY PLAN
224 `--SCAN t1
225 }
226 do_eqp_test count-7.3 {
227 SELECT count(*) FROM t1;
228 } {
229 QUERY PLAN
230 `--SCAN t1 USING COVERING INDEX t1b
231 }
232 do_eqp_test count-7.4 {
233 SELECT count(*) FROM t1 NOT INDEXED
234 } {
235 QUERY PLAN
236 `--SCAN t1
237 }
238
239 do_execsql_test count-8.0 {
240 CREATE TABLE t7(a INT,b TEXT,c BLOB,d REAL);
241 CREATE TABLE t8(a INT,b TEXT,c BLOB,d REAL);
242 CREATE INDEX t8a ON t8(a);
243 }
244 do_catchsql_test count-8.1 {
245 SELECT * FROM t8 WHERE (a, b) IN (
246 SELECT count(t8.b), count(*) FROM t7 AS ra0 ORDER BY count(*)
247 ) AND t8.b=0;
248 } {1 {misuse of aggregate: count()}}
249
250
251 finish_test