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_execsql_test count-2.9a {
 130   SELECT count(*) FROM t2 HAVING count(*)>1;
 131 } {}
 132 do_execsql_test count-2.9b {
 133   SELECT count(*) FROM t2 HAVING count(*)<10;
 134 } {0}
 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 do_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
 197 do_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 clause
 204 #
 205 reset_db
 206 do_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 }
 214 do_eqp_test count-7.2 {
 215   SELECT count(1) FROM t1;
 216 } {
 217   QUERY PLAN
 218   `--SCAN t1 USING COVERING INDEX t1b
 219 }
 220 do_eqp_test count-7.3 {
 221   SELECT count(1) FROM t1 NOT INDEXED
 222 } {
 223   QUERY PLAN
 224   `--SCAN t1
 225 }
 226 do_eqp_test count-7.3 {
 227   SELECT count(*) FROM t1;
 228 } {
 229   QUERY PLAN
 230   `--SCAN t1 USING COVERING INDEX t1b
 231 }
 232 do_eqp_test count-7.4 {
 233   SELECT count(*) FROM t1 NOT INDEXED
 234 } {
 235   QUERY PLAN
 236   `--SCAN t1
 237 }
 238
 239 do_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 }
 244 do_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
 251 finish_test