test/without_rowid1.test

   1 # 2013-10-30
   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 #
  12 # This file implements regression tests for SQLite library.  The
  13 # focus of this file is testing WITHOUT ROWID tables.
  14 #
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18 set testprefix without_rowid1
  19
  20 # Create and query a WITHOUT ROWID table.
  21 #
  22 do_execsql_test without_rowid1-1.0 {
  23   CREATE TABLE t1(a,b,c,d, PRIMARY KEY(c,a)) WITHOUT ROWID;
  24   CREATE INDEX t1bd ON t1(b, d);
  25   INSERT INTO t1 VALUES('journal','sherman','ammonia','helena');
  26   INSERT INTO t1 VALUES('dynamic','juliet','flipper','command');
  27   INSERT INTO t1 VALUES('journal','sherman','gamma','patriot');
  28   INSERT INTO t1 VALUES('arctic','sleep','ammonia','helena');
  29   SELECT *, '|' FROM t1 ORDER BY c, a;
  30 } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
  31
  32 integrity_check without_rowid1-1.0ic
  33
  34 do_execsql_test without_rowid1-1.1 {
  35   SELECT *, '|' FROM t1 ORDER BY +c, a;
  36 } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
  37
  38 do_execsql_test without_rowid1-1.2 {
  39   SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC;
  40 } {journal sherman gamma patriot | dynamic juliet flipper command | journal sherman ammonia helena | arctic sleep ammonia helena |}
  41
  42 do_execsql_test without_rowid1-1.11 {
  43   SELECT *, '|' FROM t1 ORDER BY b, d;
  44 } {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
  45
  46 do_execsql_test without_rowid1-1.12 {
  47   SELECT *, '|' FROM t1 ORDER BY +b, d;
  48 } {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
  49
  50 # Trying to insert a duplicate PRIMARY KEY fails.
  51 #
  52 do_test without_rowid1-1.21 {
  53   catchsql {
  54     INSERT INTO t1 VALUES('dynamic','phone','flipper','harvard');
  55   }
  56 } {1 {UNIQUE constraint failed: t1.c, t1.a}}
  57
  58 # REPLACE INTO works, however.
  59 #
  60 do_execsql_test without_rowid1-1.22 {
  61   REPLACE INTO t1 VALUES('dynamic','phone','flipper','harvard');
  62   SELECT *, '|' FROM t1 ORDER BY c, a;
  63 } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic phone flipper harvard | journal sherman gamma patriot |}
  64
  65 do_execsql_test without_rowid1-1.23 {
  66   SELECT *, '|' FROM t1 ORDER BY b, d;
  67 } {dynamic phone flipper harvard | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
  68
  69 # UPDATE statements.
  70 #
  71 do_execsql_test without_rowid1-1.31 {
  72   UPDATE t1 SET d=3.1415926 WHERE a='journal';
  73   SELECT *, '|' FROM t1 ORDER BY c, a;
  74 } {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | dynamic phone flipper harvard | journal sherman gamma 3.1415926 |}
  75 do_execsql_test without_rowid1-1.32 {
  76   SELECT *, '|' FROM t1 ORDER BY b, d;
  77 } {dynamic phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
  78
  79 do_execsql_test without_rowid1-1.35 {
  80   UPDATE t1 SET a=1250 WHERE b='phone';
  81   SELECT *, '|' FROM t1 ORDER BY c, a;
  82 } {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | 1250 phone flipper harvard | journal sherman gamma 3.1415926 |}
  83 integrity_check without_rowid1-1.36
  84
  85 do_execsql_test without_rowid1-1.37 {
  86   SELECT *, '|' FROM t1 ORDER BY b, d;
  87 } {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
  88
  89 do_execsql_test without_rowid1-1.40 {
  90   VACUUM;
  91   SELECT *, '|' FROM t1 ORDER BY b, d;
  92 } {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
  93 integrity_check without_rowid1-1.41
  94
  95 # Verify that ANALYZE works
  96 #
  97 do_execsql_test without_rowid1-1.50 {
  98   ANALYZE;
  99   SELECT * FROM sqlite_stat1 ORDER BY idx;
 100 } {t1 t1 {4 2 1} t1 t1bd {4 2 2}}
 101 ifcapable stat3 {
 102   do_execsql_test without_rowid1-1.51 {
 103     SELECT DISTINCT tbl, idx FROM sqlite_stat3 ORDER BY idx;
 104   } {t1 t1 t1 t1bd}
 105 }
 106 ifcapable stat4 {
 107   do_execsql_test without_rowid1-1.52 {
 108     SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx;
 109   } {t1 t1 t1 t1bd}
 110 }
 111
 112 #----------
 113
 114 do_execsql_test 2.1.1 {
 115   CREATE TABLE t4 (a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID;
 116   INSERT INTO t4 VALUES('abc', 'def');
 117   SELECT * FROM t4;
 118 } {abc def}
 119 do_execsql_test 2.1.2 {
 120   UPDATE t4 SET a = 'ABC';
 121   SELECT * FROM t4;
 122 } {ABC def}
 123
 124 do_execsql_test 2.2.1 {
 125   DROP TABLE t4;
 126   CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID;
 127   INSERT INTO t4(a, b) VALUES('abc', 'def');
 128   SELECT * FROM t4;
 129 } {def abc}
 130
 131 do_execsql_test 2.2.2 {
 132   UPDATE t4 SET a = 'ABC', b = 'xyz';
 133   SELECT * FROM t4;
 134 } {xyz ABC}
 135
 136 do_execsql_test 2.3.1 {
 137   CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID;
 138   INSERT INTO t5(a, b) VALUES('abc', 'def');
 139   UPDATE t5 SET a='abc', b='def';
 140 } {}
 141
 142 do_execsql_test 2.4.1 {
 143   CREATE TABLE t6 (
 144     a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a)
 145   ) WITHOUT ROWID;
 146
 147   INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi');
 148   UPDATE t6 SET a='ABC', c='ghi';
 149 } {}
 150
 151 do_execsql_test 2.4.2 {
 152   SELECT * FROM t6 ORDER BY b, a;
 153   SELECT * FROM t6 ORDER BY c;
 154 } {ABC def ghi ABC def ghi}
 155
 156 #-------------------------------------------------------------------------
 157 # Unless the destination table is completely empty, the xfer optimization
 158 # is disabled for WITHOUT ROWID tables. The following tests check for
 159 # some problems that might occur if this were not the case.
 160 #
 161 reset_db
 162 do_execsql_test 3.1.1 {
 163   CREATE TABLE t1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
 164   CREATE UNIQUE INDEX i1 ON t1(b);
 165
 166   CREATE TABLE t2(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
 167   CREATE UNIQUE INDEX i2 ON t2(b);
 168
 169   INSERT INTO t1 VALUES('one', 'two');
 170   INSERT INTO t2 VALUES('three', 'two');
 171 }
 172
 173 do_execsql_test 3.1.2 {
 174   INSERT OR REPLACE INTO t1 SELECT * FROM t2;
 175   SELECT * FROM t1;
 176 } {three two}
 177
 178 do_execsql_test 3.1.3 {
 179   DELETE FROM t1;
 180   INSERT INTO t1 SELECT * FROM t2;
 181   SELECT * FROM t1;
 182 } {three two}
 183
 184 do_catchsql_test 3.1.4 {
 185   INSERT INTO t2 VALUES('four', 'four');
 186   INSERT INTO t2 VALUES('six', 'two');
 187   INSERT INTO t1 SELECT * FROM t2;
 188 } {1 {UNIQUE constraint failed: t2.b}}
 189
 190 do_execsql_test 3.1.5 {
 191   CREATE TABLE t3(a PRIMARY KEY);
 192   CREATE TABLE t4(a PRIMARY KEY);
 193
 194   INSERT INTO t4 VALUES('i');
 195   INSERT INTO t4 VALUES('ii');
 196   INSERT INTO t4 VALUES('iii');
 197
 198   INSERT INTO t3 SELECT * FROM t4;
 199   SELECT * FROM t3;
 200 } {i ii iii}
 201
 202 ############################################################################
 203 # Ticket [c34d0557f740c450709d6e33df72d4f3f651a3cc]
 204 # Name resolution issue with WITHOUT ROWID
 205 #
 206 do_execsql_test 4.1 {
 207   CREATE TABLE t41(a PRIMARY KEY) WITHOUT ROWID;
 208   INSERT INTO t41 VALUES('abc');
 209   CREATE TABLE t42(x);
 210   INSERT INTO t42 VALUES('xyz');
 211   SELECT t42.rowid FROM t41, t42;
 212 } {1}
 213 do_execsql_test 4.2 {
 214   SELECT t42.rowid FROM t42, t41;
 215 } {1}
 216
 217
 218 #--------------------------------------------------------------------------
 219 # The following tests verify that the trailing PK fields added to each
 220 # entry in an index on a WITHOUT ROWID table are used correctly.
 221 #
 222 do_execsql_test 5.0 {
 223   CREATE TABLE t45(a PRIMARY KEY, b, c) WITHOUT ROWID;
 224   CREATE INDEX i45 ON t45(b);
 225
 226   INSERT INTO t45 VALUES(2, 'one', 'x');
 227   INSERT INTO t45 VALUES(4, 'one', 'x');
 228   INSERT INTO t45 VALUES(6, 'one', 'x');
 229   INSERT INTO t45 VALUES(8, 'one', 'x');
 230   INSERT INTO t45 VALUES(10, 'one', 'x');
 231
 232   INSERT INTO t45 VALUES(1, 'two', 'x');
 233   INSERT INTO t45 VALUES(3, 'two', 'x');
 234   INSERT INTO t45 VALUES(5, 'two', 'x');
 235   INSERT INTO t45 VALUES(7, 'two', 'x');
 236   INSERT INTO t45 VALUES(9, 'two', 'x');
 237 }
 238
 239 do_eqp_test 5.1 {
 240   SELECT * FROM t45 WHERE b=? AND a>?
 241 } {/*USING INDEX i45 (b=? AND a>?)*/}
 242
 243 do_execsql_test 5.2 {
 244   SELECT * FROM t45 WHERE b='two' AND a>4
 245 } {5 two x 7 two x 9 two x}
 246
 247 do_execsql_test 5.3 {
 248   SELECT * FROM t45 WHERE b='one' AND a<8
 249 } { 2 one x 4 one x 6 one x }
 250
 251 do_execsql_test 5.4 {
 252   CREATE TABLE t46(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID;
 253   WITH r(x) AS (
 254     SELECT 1 UNION ALL SELECT x+1 FROM r WHERE x<100
 255   )
 256   INSERT INTO t46 SELECT x / 20, x % 20, x % 10, x FROM r;
 257 }
 258
 259 set queries {
 260   1    2    "c = 5 AND a = 1"          {/*i46 (c=? AND a=?)*/}
 261   2    6    "c = 4 AND a < 3"          {/*i46 (c=? AND a<?)*/}
 262   3    4    "c = 2 AND a >= 3"         {/*i46 (c=? AND a>?)*/}
 263   4    1    "c = 2 AND a = 1 AND b<10" {/*i46 (c=? AND a=? AND b<?)*/}
 264   5    1    "c = 0 AND a = 0 AND b>5"  {/*i46 (c=? AND a=? AND b>?)*/}
 265 }
 266
 267 foreach {tn cnt where eqp} $queries {
 268   do_execsql_test 5.5.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt
 269 }
 270
 271 do_execsql_test 5.6 {
 272   CREATE INDEX i46 ON t46(c);
 273 }
 274
 275 foreach {tn cnt where eqp} $queries {
 276   do_execsql_test 5.7.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt
 277   do_eqp_test 5.7.$tn.2  "SELECT count(*) FROM t46 WHERE $where" $eqp
 278 }
 279
 280 #-------------------------------------------------------------------------
 281 # Check that redundant UNIQUE constraints do not cause a problem.
 282 #
 283 do_execsql_test 6.0 {
 284   CREATE TABLE t47(a, b UNIQUE PRIMARY KEY) WITHOUT ROWID;
 285   CREATE INDEX i47 ON t47(a);
 286   INSERT INTO t47 VALUES(1, 2);
 287   INSERT INTO t47 VALUES(2, 4);
 288   INSERT INTO t47 VALUES(3, 6);
 289   INSERT INTO t47 VALUES(4, 8);
 290
 291   VACUUM;
 292   PRAGMA integrity_check;
 293   SELECT name FROM sqlite_master WHERE tbl_name = 't47';
 294 } {ok t47 i47}
 295
 296 do_execsql_test 6.1 {
 297   CREATE TABLE t48(
 298     a UNIQUE UNIQUE,
 299     b UNIQUE,
 300     PRIMARY KEY(a),
 301     UNIQUE(a)
 302   ) WITHOUT ROWID;
 303   INSERT INTO t48 VALUES('a', 'b'), ('c', 'd'), ('e', 'f');
 304   VACUUM;
 305   PRAGMA integrity_check;
 306   SELECT name FROM sqlite_master WHERE tbl_name = 't48';
 307 } {
 308   ok  t48   sqlite_autoindex_t48_2
 309 }
 310
 311 # 2015-05-28: CHECK constraints can refer to the rowid in a
 312 # rowid table, but not in a WITHOUT ROWID table.
 313 #
 314 do_execsql_test 7.1 {
 315   CREATE TABLE t70a(
 316      a INT CHECK( rowid!=33 ),
 317      b TEXT PRIMARY KEY
 318   );
 319   INSERT INTO t70a(a,b) VALUES(99,'hello');
 320 } {}
 321 do_catchsql_test 7.2 {
 322   INSERT INTO t70a(rowid,a,b) VALUES(33,99,'xyzzy');
 323 } {1 {CHECK constraint failed: t70a}}
 324 do_catchsql_test 7.3 {
 325   CREATE TABLE t70b(
 326      a INT CHECK( rowid!=33 ),
 327      b TEXT PRIMARY KEY
 328   ) WITHOUT ROWID;
 329 } {1 {no such column: rowid}}
 330
 331 # 2017-07-30: OSSFuzz discovered that an extra entry was being
 332 # added in the sqlite_master table for an "INTEGER PRIMARY KEY UNIQUE"
 333 # WITHOUT ROWID table.  Make sure this has now been fixed.
 334 #
 335 db close
 336 sqlite3 db :memory:
 337 do_execsql_test 8.1 {
 338   CREATE TABLE t1(x INTEGER PRIMARY KEY UNIQUE, b) WITHOUT ROWID;
 339   CREATE INDEX t1x ON t1(x);
 340   INSERT INTO t1(x,b) VALUES('funny','buffalo');
 341   SELECT type, name, '|' FROM sqlite_master;
 342 } {table t1 | index t1x |}
 343
 344
 345
 346 finish_test