test/count.test

   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_test count-2.9 {
 130   catchsql {SELECT count(*) FROM t2 HAVING count(*)>1}
 131 } {1 {a GROUP BY clause is required before HAVING}}
 132 do_test count-2.10 {
 133   uses_op_count {SELECT count(*) FROM (SELECT 1)}
 134 } {0}
 135 do_test count-2.11 {
 136   execsql { CREATE VIEW v1 AS SELECT 1 AS a }
 137   uses_op_count {SELECT count(*) FROM v1}
 138 } {0}
 139 do_test count-2.12 {
 140   uses_op_count {SELECT count(*), max(a) FROM t2}
 141 } {0}
 142 do_test count-2.13 {
 143   uses_op_count {SELECT count(*) FROM t1, t2}
 144 } {0}
 145
 146 ifcapable vtab {
 147   register_echo_module [sqlite3_connection_pointer db]
 148   do_test count-2.14 {
 149     execsql { CREATE VIRTUAL TABLE techo USING echo(t1); }
 150     uses_op_count {SELECT count(*) FROM techo}
 151   } {0}
 152 }
 153
 154 do_test count-3.1 {
 155   execsql {
 156     CREATE TABLE t3(a, b);
 157     SELECT a FROM (SELECT count(*) AS a FROM t3) WHERE a==0;
 158   }
 159 } {0}
 160 do_test count-3.2 {
 161   execsql {
 162     SELECT a FROM (SELECT count(*) AS a FROM t3) WHERE a==1;
 163   }
 164 } {}
 165
 166 do_test count-4.1 {
 167   execsql {
 168     CREATE TABLE t4(a, b);
 169     INSERT INTO t4 VALUES('a', 'b');
 170     CREATE INDEX t4i1 ON t4(b, a);
 171     SELECT count(*) FROM t4;
 172   }
 173 } {1}
 174 do_test count-4.2 {
 175   execsql {
 176     CREATE INDEX t4i2 ON t4(b);
 177     SELECT count(*) FROM t4;
 178   }
 179 } {1}
 180 do_test count-4.3 {
 181   execsql {
 182     DROP INDEX t4i1;
 183     CREATE INDEX t4i1 ON t4(b, a);
 184     SELECT count(*) FROM t4;
 185   }
 186 } {1}
 187
 188 do_execsql_test count-5.1 {
 189   CREATE TABLE t5(a TEXT PRIMARY KEY, b VARCHAR(50)) WITHOUT ROWID;
 190   INSERT INTO t5 VALUES('bison','jazz');
 191   SELECT count(*) FROM t5;
 192 } {1}
 193
 194 do_catchsql_test count-6.1 {
 195   CREATE TABLE t6(x);
 196   SELECT count(DISTINCT) FROM t6 GROUP BY x;
 197 } {1 {DISTINCT aggregates must have exactly one argument}}
 198
 199 # 2020-05-08.
 200 # The count() optimization should honor the NOT INDEXED clause
 201 #
 202 reset_db
 203 do_execsql_test count-7.1 {
 204   CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c VARCHAR(1000));
 205   CREATE INDEX t1b ON t1(b);
 206   INSERT INTO t1(a,b,c) values(1,2,'count.test cases for NOT INDEXED');
 207   ANALYZE;
 208   UPDATE sqlite_stat1 SET stat='1000000 10' WHERE idx='t1b';
 209   ANALYZE sqlite_master;
 210 }
 211 do_eqp_test count-7.2 {
 212   SELECT count(1) FROM t1;
 213 } {
 214   QUERY PLAN
 215   `--SCAN t1 USING COVERING INDEX t1b
 216 }
 217 do_eqp_test count-7.3 {
 218   SELECT count(1) FROM t1 NOT INDEXED
 219 } {
 220   QUERY PLAN
 221   `--SCAN t1
 222 }
 223 do_eqp_test count-7.3 {
 224   SELECT count(*) FROM t1;
 225 } {
 226   QUERY PLAN
 227   `--SCAN t1 USING COVERING INDEX t1b
 228 }
 229 do_eqp_test count-7.4 {
 230   SELECT count(*) FROM t1 NOT INDEXED
 231 } {
 232   QUERY PLAN
 233   `--SCAN t1
 234 }
 235
 236 do_execsql_test count-8.0 {
 237   CREATE TABLE t7(a INT,b TEXT,c BLOB,d REAL);
 238   CREATE TABLE t8(a INT,b TEXT,c BLOB,d REAL);
 239   CREATE INDEX t8a ON t8(a);
 240 }
 241 do_catchsql_test count-8.1 {
 242   SELECT * FROM t8 WHERE (a, b) IN (
 243       SELECT count(t8.b), count(*) FROM t7 AS ra0 ORDER BY count(*)
 244   ) AND t8.b=0;
 245 } {1 {misuse of aggregate: count()}}
 246
 247
 248 finish_test