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