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.1.1.1 2012/02/21 17:04:16 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to count.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test