AryaWu/sqlite
0
1# 2009-02-242#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing "SELECT count(*)" statements.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17 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 both23# tables and indexes. Test both when they contain 0 entries,24# when all entries are on the root page, and when the b-tree25# forms a structure 2 and 3 levels deep.26#27#28 29do_test count-0.1 {30 db eval {31 SELECT count(*) FROM sqlite_master;32 }33} {0}34 35set iTest 036foreach zIndex [list {37 /* no-op */38} {39 CREATE INDEX i1 ON t1(a);40}] { 41 incr iTest42 do_test count-1.$iTest.1 {43 execsql {44 DROP TABLE IF EXISTS t1;45 CREATE TABLE t1(a, b);46 }47 execsql $zIndex48 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; -- 462 INSERT INTO t1 SELECT * FROM t1; -- 863 INSERT INTO t1 SELECT * FROM t1; -- 1664 INSERT INTO t1 SELECT * FROM t1; -- 3265 INSERT INTO t1 SELECT * FROM t1; -- 6466 INSERT INTO t1 SELECT * FROM t1; -- 12867 INSERT INTO t1 SELECT * FROM t1; -- 25668 SELECT count(*) FROM t1;69 }70 } {256}71 72 do_test count-1.$iTest.4 {73 execsql {74 INSERT INTO t1 SELECT * FROM t1; -- 51275 INSERT INTO t1 SELECT * FROM t1; -- 102476 INSERT INTO t1 SELECT * FROM t1; -- 204877 INSERT INTO t1 SELECT * FROM t1; -- 409678 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; -- 819286 INSERT INTO t1 SELECT * FROM t1; -- 1638487 INSERT INTO t1 SELECT * FROM t1; -- 3276888 INSERT INTO t1 SELECT * FROM t1; -- 6553689 COMMIT;90 SELECT count(*) FROM t1;91 }92 } {65536}93}94 95proc uses_op_count {sql} {96 if {[lsearch [execsql "EXPLAIN $sql"] Count]>=0} {97 return 1;98 }99 return 0100}101 102do_test count-2.1 {103 execsql {104 CREATE TABLE t2(a, b);105 }106 uses_op_count {SELECT count(*) FROM t2}107} {1}108do_test count-2.2 {109 catchsql {SELECT count(DISTINCT *) FROM t2}110} {1 {near "*": syntax error}}111do_test count-2.3 {112 uses_op_count {SELECT count(DISTINCT a) FROM t2}113} {0}114do_test count-2.4 {115 uses_op_count {SELECT count(a) FROM t2}116} {0}117do_test count-2.5 {118 uses_op_count {SELECT count() FROM t2}119} {1}120do_test count-2.6 {121 catchsql {SELECT count(DISTINCT) FROM t2}122} {1 {DISTINCT aggregates must have exactly one argument}}123do_test count-2.7 {124 uses_op_count {SELECT count(*)+1 FROM t2}125} {0}126do_test count-2.8 {127 uses_op_count {SELECT count(*) FROM t2 WHERE a IS NOT NULL}128} {0}129do_execsql_test count-2.9a {130 SELECT count(*) FROM t2 HAVING count(*)>1;131} {}132do_execsql_test count-2.9b {133 SELECT count(*) FROM t2 HAVING count(*)<10;134} {0}135do_test count-2.10 {136 uses_op_count {SELECT count(*) FROM (SELECT 1)}137} {0}138do_test count-2.11 {139 execsql { CREATE VIEW v1 AS SELECT 1 AS a }140 uses_op_count {SELECT count(*) FROM v1}141} {0}142do_test count-2.12 {143 uses_op_count {SELECT count(*), max(a) FROM t2}144} {0}145do_test count-2.13 {146 uses_op_count {SELECT count(*) FROM t1, t2}147} {0}148 149ifcapable 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 157do_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}163do_test count-3.2 {164 execsql {165 SELECT a FROM (SELECT count(*) AS a FROM t3) WHERE a==1;166 }167} {}168 169do_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}177do_test count-4.2 {178 execsql {179 CREATE INDEX t4i2 ON t4(b);180 SELECT count(*) FROM t4;181 }182} {1}183do_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 191do_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 197do_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 clause204#205reset_db206do_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}214do_eqp_test count-7.2 {215 SELECT count(1) FROM t1;216} {217 QUERY PLAN218 `--SCAN t1 USING COVERING INDEX t1b219}220do_eqp_test count-7.3 {221 SELECT count(1) FROM t1 NOT INDEXED222} {223 QUERY PLAN224 `--SCAN t1225}226do_eqp_test count-7.3 {227 SELECT count(*) FROM t1;228} {229 QUERY PLAN230 `--SCAN t1 USING COVERING INDEX t1b231}232do_eqp_test count-7.4 {233 SELECT count(*) FROM t1 NOT INDEXED234} {235 QUERY PLAN236 `--SCAN t1237}238 239do_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}244do_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 251finish_test252