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