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 proc do_execsql_test_if_vtab {tn sql {res {}}} {
  21   ifcapable vtab { uplevel [list do_execsql_test $tn $sql $res] }
  22 }
  23
  24 # Create and query a WITHOUT ROWID table.
  25 #
  26 do_execsql_test without_rowid1-1.0 {
  27   CREATE TABLE t1(a,b,c,d, PRIMARY KEY(c,a)) WITHOUT ROWID;
  28   CREATE INDEX t1bd ON t1(b, d);
  29   INSERT INTO t1 VALUES('journal','sherman','ammonia','helena');
  30   INSERT INTO t1 VALUES('dynamic','juliet','flipper','command');
  31   INSERT INTO t1 VALUES('journal','sherman','gamma','patriot');
  32   INSERT INTO t1 VALUES('arctic','sleep','ammonia','helena');
  33   SELECT *, '|' FROM t1 ORDER BY c, a;
  34 } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
  35
  36 integrity_check without_rowid1-1.0ic
  37
  38 do_execsql_test_if_vtab without_rowid1-1.0ixi {
  39   SELECT name, key FROM pragma_index_xinfo('t1');
  40 } {c 1 a 1 b 0 d 0}
  41 do_execsql_test_if_vtab without_rowid1-1.0tl {
  42   SELECT wr FROM pragma_table_list('t1');
  43 } {1}
  44
  45 do_execsql_test without_rowid1-1.1 {
  46   SELECT *, '|' FROM t1 ORDER BY +c, a;
  47 } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic juliet flipper command | journal sherman gamma patriot |}
  48
  49 do_execsql_test without_rowid1-1.2 {
  50   SELECT *, '|' FROM t1 ORDER BY c DESC, a DESC;
  51 } {journal sherman gamma patriot | dynamic juliet flipper command | journal sherman ammonia helena | arctic sleep ammonia helena |}
  52
  53 do_execsql_test without_rowid1-1.11 {
  54   SELECT *, '|' FROM t1 ORDER BY b, d;
  55 } {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
  56
  57 do_execsql_test without_rowid1-1.12 {
  58   SELECT *, '|' FROM t1 ORDER BY +b, d;
  59 } {dynamic juliet flipper command | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
  60
  61 # Trying to insert a duplicate PRIMARY KEY fails.
  62 #
  63 do_test without_rowid1-1.21 {
  64   catchsql {
  65     INSERT INTO t1 VALUES('dynamic','phone','flipper','harvard');
  66   }
  67 } {1 {UNIQUE constraint failed: t1.c, t1.a}}
  68
  69 # REPLACE INTO works, however.
  70 #
  71 do_execsql_test without_rowid1-1.22 {
  72   REPLACE INTO t1 VALUES('dynamic','phone','flipper','harvard');
  73   SELECT *, '|' FROM t1 ORDER BY c, a;
  74 } {arctic sleep ammonia helena | journal sherman ammonia helena | dynamic phone flipper harvard | journal sherman gamma patriot |}
  75
  76 do_execsql_test without_rowid1-1.23 {
  77   SELECT *, '|' FROM t1 ORDER BY b, d;
  78 } {dynamic phone flipper harvard | journal sherman ammonia helena | journal sherman gamma patriot | arctic sleep ammonia helena |}
  79
  80 # UPDATE statements.
  81 #
  82 do_execsql_test without_rowid1-1.31 {
  83   UPDATE t1 SET d=3.1415926 WHERE a='journal';
  84   SELECT *, '|' FROM t1 ORDER BY c, a;
  85 } {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | dynamic phone flipper harvard | journal sherman gamma 3.1415926 |}
  86 do_execsql_test without_rowid1-1.32 {
  87   SELECT *, '|' FROM t1 ORDER BY b, d;
  88 } {dynamic phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
  89
  90 do_execsql_test without_rowid1-1.35 {
  91   UPDATE t1 SET a=1250 WHERE b='phone';
  92   SELECT *, '|' FROM t1 ORDER BY c, a;
  93 } {arctic sleep ammonia helena | journal sherman ammonia 3.1415926 | 1250 phone flipper harvard | journal sherman gamma 3.1415926 |}
  94 integrity_check without_rowid1-1.36
  95
  96 do_execsql_test without_rowid1-1.37 {
  97   SELECT *, '|' FROM t1 ORDER BY b, d;
  98 } {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
  99
 100 do_execsql_test without_rowid1-1.40 {
 101   VACUUM;
 102   SELECT *, '|' FROM t1 ORDER BY b, d;
 103 } {1250 phone flipper harvard | journal sherman ammonia 3.1415926 | journal sherman gamma 3.1415926 | arctic sleep ammonia helena |}
 104 integrity_check without_rowid1-1.41
 105
 106 # Verify that ANALYZE works
 107 #
 108 do_execsql_test without_rowid1-1.50 {
 109   ANALYZE;
 110   SELECT * FROM sqlite_stat1 ORDER BY idx;
 111 } {t1 t1 {4 2 1} t1 t1bd {4 2 2}}
 112 ifcapable stat4 {
 113   do_execsql_test without_rowid1-1.52 {
 114     SELECT DISTINCT tbl, idx FROM sqlite_stat4 ORDER BY idx;
 115   } {t1 t1 t1 t1bd}
 116 }
 117
 118 #----------
 119
 120 do_execsql_test 2.1.1 {
 121   CREATE TABLE t4 (a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID;
 122   INSERT INTO t4 VALUES('abc', 'def');
 123   SELECT * FROM t4;
 124 } {abc def}
 125 do_execsql_test 2.1.2 {
 126   UPDATE t4 SET a = 'ABC';
 127   SELECT * FROM t4;
 128 } {ABC def}
 129 do_execsql_test_if_vtab 2.1.3 {
 130   SELECT name, coll, key FROM pragma_index_xinfo('t4');
 131 } {a nocase 1 b BINARY 0}
 132
 133 do_execsql_test 2.2.1 {
 134   DROP TABLE t4;
 135   CREATE TABLE t4 (b, a COLLATE nocase PRIMARY KEY) WITHOUT ROWID;
 136   INSERT INTO t4(a, b) VALUES('abc', 'def');
 137   SELECT * FROM t4;
 138 } {def abc}
 139
 140 do_execsql_test 2.2.2 {
 141   UPDATE t4 SET a = 'ABC', b = 'xyz';
 142   SELECT * FROM t4;
 143 } {xyz ABC}
 144
 145 do_execsql_test_if_vtab 2.2.3 {
 146   SELECT name, coll, key FROM pragma_index_xinfo('t4');
 147 } {a nocase 1 b BINARY 0}
 148
 149
 150 do_execsql_test 2.3.1 {
 151   CREATE TABLE t5 (a, b, PRIMARY KEY(b, a)) WITHOUT ROWID;
 152   INSERT INTO t5(a, b) VALUES('abc', 'def');
 153   UPDATE t5 SET a='abc', b='def';
 154 } {}
 155
 156 do_execsql_test_if_vtab 2.3.2 {
 157   SELECT name, coll, key FROM pragma_index_xinfo('t5');
 158 } {b BINARY 1 a BINARY 1}
 159
 160
 161 do_execsql_test 2.4.1 {
 162   CREATE TABLE t6 (
 163     a COLLATE nocase, b, c UNIQUE, PRIMARY KEY(b, a)
 164   ) WITHOUT ROWID;
 165
 166   INSERT INTO t6(a, b, c) VALUES('abc', 'def', 'ghi');
 167   UPDATE t6 SET a='ABC', c='ghi';
 168 } {}
 169
 170 do_execsql_test 2.4.2 {
 171   SELECT * FROM t6 ORDER BY b, a;
 172   SELECT * FROM t6 ORDER BY c;
 173 } {ABC def ghi ABC def ghi}
 174
 175 do_execsql_test_if_vtab 2.4.3 {
 176   SELECT name, coll, key FROM pragma_index_xinfo('t6');
 177 } {b BINARY 1 a nocase 1 c BINARY 0}
 178
 179
 180 #-------------------------------------------------------------------------
 181 # Unless the destination table is completely empty, the xfer optimization
 182 # is disabled for WITHOUT ROWID tables. The following tests check for
 183 # some problems that might occur if this were not the case.
 184 #
 185 reset_db
 186 do_execsql_test 3.1.1 {
 187   CREATE TABLE t1(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
 188   CREATE UNIQUE INDEX i1 ON t1(b);
 189
 190   CREATE TABLE t2(a, b, PRIMARY KEY(a)) WITHOUT ROWID;
 191   CREATE UNIQUE INDEX i2 ON t2(b);
 192
 193   INSERT INTO t1 VALUES('one', 'two');
 194   INSERT INTO t2 VALUES('three', 'two');
 195 }
 196
 197 do_execsql_test 3.1.2 {
 198   INSERT OR REPLACE INTO t1 SELECT * FROM t2;
 199   SELECT * FROM t1;
 200 } {three two}
 201
 202 do_execsql_test 3.1.3 {
 203   DELETE FROM t1;
 204   INSERT INTO t1 SELECT * FROM t2;
 205   SELECT * FROM t1;
 206 } {three two}
 207
 208 do_catchsql_test 3.1.4 {
 209   INSERT INTO t2 VALUES('four', 'four');
 210   INSERT INTO t2 VALUES('six', 'two');
 211   INSERT INTO t1 SELECT * FROM t2;
 212 } {1 {UNIQUE constraint failed: t2.b}}
 213
 214 do_execsql_test 3.1.5 {
 215   CREATE TABLE t3(a PRIMARY KEY);
 216   CREATE TABLE t4(a PRIMARY KEY);
 217
 218   INSERT INTO t4 VALUES('i');
 219   INSERT INTO t4 VALUES('ii');
 220   INSERT INTO t4 VALUES('iii');
 221
 222   INSERT INTO t3 SELECT * FROM t4;
 223   SELECT * FROM t3;
 224 } {i ii iii}
 225
 226 ############################################################################
 227 # Ticket [c34d0557f740c450709d6e33df72d4f3f651a3cc]
 228 # Name resolution issue with WITHOUT ROWID
 229 #
 230 do_execsql_test 4.1 {
 231   CREATE TABLE t41(a PRIMARY KEY) WITHOUT ROWID;
 232   INSERT INTO t41 VALUES('abc');
 233   CREATE TABLE t42(x);
 234   INSERT INTO t42 VALUES('xyz');
 235   SELECT t42.rowid FROM t41, t42;
 236 } {1}
 237 do_execsql_test 4.2 {
 238   SELECT t42.rowid FROM t42, t41;
 239 } {1}
 240
 241
 242 #--------------------------------------------------------------------------
 243 # The following tests verify that the trailing PK fields added to each
 244 # entry in an index on a WITHOUT ROWID table are used correctly.
 245 #
 246 do_execsql_test 5.0 {
 247   CREATE TABLE t45(a PRIMARY KEY, b, c) WITHOUT ROWID;
 248   CREATE INDEX i45 ON t45(b);
 249
 250   INSERT INTO t45 VALUES(2, 'one', 'x');
 251   INSERT INTO t45 VALUES(4, 'one', 'x');
 252   INSERT INTO t45 VALUES(6, 'one', 'x');
 253   INSERT INTO t45 VALUES(8, 'one', 'x');
 254   INSERT INTO t45 VALUES(10, 'one', 'x');
 255
 256   INSERT INTO t45 VALUES(1, 'two', 'x');
 257   INSERT INTO t45 VALUES(3, 'two', 'x');
 258   INSERT INTO t45 VALUES(5, 'two', 'x');
 259   INSERT INTO t45 VALUES(7, 'two', 'x');
 260   INSERT INTO t45 VALUES(9, 'two', 'x');
 261 }
 262
 263 do_eqp_test 5.1 {
 264   SELECT * FROM t45 WHERE b=? AND a>?
 265 } {USING INDEX i45 (b=? AND a>?)}
 266
 267 do_execsql_test 5.2 {
 268   SELECT * FROM t45 WHERE b='two' AND a>4
 269 } {5 two x 7 two x 9 two x}
 270
 271 do_execsql_test 5.3 {
 272   SELECT * FROM t45 WHERE b='one' AND a<8
 273 } { 2 one x 4 one x 6 one x }
 274
 275 do_execsql_test 5.4 {
 276   CREATE TABLE t46(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID;
 277   WITH r(x) AS (
 278     SELECT 1 UNION ALL SELECT x+1 FROM r WHERE x<100
 279   )
 280   INSERT INTO t46 SELECT x / 20, x % 20, x % 10, x FROM r;
 281 }
 282
 283 set queries {
 284   1    2    "c = 5 AND a = 1"          {i46 (c=? AND a=?)}
 285   2    6    "c = 4 AND a < 3"          {i46 (c=? AND a<?)}
 286   3    4    "c = 2 AND a >= 3"         {i46 (c=? AND a>?)}
 287   4    1    "c = 2 AND a = 1 AND b<10" {i46 (c=? AND a=? AND b<?)}
 288   5    1    "c = 0 AND a = 0 AND b>5"  {i46 (c=? AND a=? AND b>?)}
 289 }
 290
 291 foreach {tn cnt where eqp} $queries {
 292   do_execsql_test 5.5.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt
 293 }
 294
 295 do_execsql_test 5.6 {
 296   CREATE INDEX i46 ON t46(c);
 297 }
 298
 299 foreach {tn cnt where eqp} $queries {
 300   do_execsql_test 5.7.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt
 301   do_eqp_test 5.7.$tn.2  "SELECT count(*) FROM t46 WHERE $where" $eqp
 302 }
 303
 304 #-------------------------------------------------------------------------
 305 # Check that redundant UNIQUE constraints do not cause a problem.
 306 #
 307 do_execsql_test 6.0 {
 308   CREATE TABLE t47(a, b UNIQUE PRIMARY KEY) WITHOUT ROWID;
 309   CREATE INDEX i47 ON t47(a);
 310   INSERT INTO t47 VALUES(1, 2);
 311   INSERT INTO t47 VALUES(2, 4);
 312   INSERT INTO t47 VALUES(3, 6);
 313   INSERT INTO t47 VALUES(4, 8);
 314
 315   VACUUM;
 316   PRAGMA integrity_check;
 317   SELECT name FROM sqlite_master WHERE tbl_name = 't47';
 318 } {ok t47 i47}
 319
 320 do_execsql_test 6.1 {
 321   CREATE TABLE t48(
 322     a UNIQUE UNIQUE,
 323     b UNIQUE,
 324     PRIMARY KEY(a),
 325     UNIQUE(a)
 326   ) WITHOUT ROWID;
 327   INSERT INTO t48 VALUES('a', 'b'), ('c', 'd'), ('e', 'f');
 328   VACUUM;
 329   PRAGMA integrity_check;
 330   SELECT name FROM sqlite_master WHERE tbl_name = 't48';
 331 } {
 332   ok  t48   sqlite_autoindex_t48_2
 333 }
 334
 335 # 2015-05-28: CHECK constraints can refer to the rowid in a
 336 # rowid table, but not in a WITHOUT ROWID table.
 337 #
 338 do_execsql_test 7.1 {
 339   CREATE TABLE t70a(
 340      a INT CHECK( rowid!=33 ),
 341      b TEXT PRIMARY KEY
 342   );
 343   INSERT INTO t70a(a,b) VALUES(99,'hello');
 344 } {}
 345 do_catchsql_test 7.2 {
 346   INSERT INTO t70a(rowid,a,b) VALUES(33,99,'xyzzy');
 347 } {1 {CHECK constraint failed: rowid!=33}}
 348 do_catchsql_test 7.3 {
 349   CREATE TABLE t70b(
 350      a INT CHECK( rowid!=33 ),
 351      b TEXT PRIMARY KEY
 352   ) WITHOUT ROWID;
 353 } {1 {no such column: rowid}}
 354
 355 # 2017-07-30: OSSFuzz discovered that an extra entry was being
 356 # added in the sqlite_master table for an "INTEGER PRIMARY KEY UNIQUE"
 357 # WITHOUT ROWID table.  Make sure this has now been fixed.
 358 #
 359 db close
 360 sqlite3 db :memory:
 361 do_execsql_test 8.1 {
 362   CREATE TABLE t1(x INTEGER PRIMARY KEY UNIQUE, b) WITHOUT ROWID;
 363   CREATE INDEX t1x ON t1(x);
 364   INSERT INTO t1(x,b) VALUES('funny','buffalo');
 365   SELECT type, name, '|' FROM sqlite_master;
 366 } {table t1 | index t1x |}
 367
 368 # 2018-04-05: OSSFuzz found that the following was accessing an
 369 # unintialized memory cell. Which was not actually causing a
 370 # malfunction, but does cause an assert() to fail.
 371 #
 372 do_execsql_test 9.0 {
 373   CREATE TABLE t2(b, c, PRIMARY KEY(b,c)) WITHOUT ROWID;
 374   CREATE UNIQUE INDEX t2b ON t2(b);
 375   UPDATE t2 SET b=1 WHERE b='';
 376 }
 377
 378 do_execsql_test 10.1 {
 379   DELETE FROM t2 WHERE b=1
 380 }
 381
 382 #-------------------------------------------------------------------------
 383 # UNIQUE constraint violation in an UPDATE with a multi-column PK.
 384 #
 385 reset_db
 386 do_execsql_test 10.0 {
 387   CREATE TABLE t1(a, b, c UNIQUE, PRIMARY KEY(a, b)) WITHOUT ROWID;
 388   INSERT INTO t1 VALUES('a', 'a', 1);
 389   INSERT INTO t1 VALUES('a', 'b', 2);
 390   INSERT INTO t1 VALUES('b', 'a', 3);
 391   INSERT INTO t1 VALUES('b', 'b', 4);
 392 }
 393
 394 do_catchsql_test 10.1 {
 395   UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'a');
 396 } {0 {}}
 397 do_catchsql_test 10.2 {
 398   UPDATE t1 SET c=1 WHERE (a, b) = ('a', 'b');
 399 } {1 {UNIQUE constraint failed: t1.c}}
 400 do_catchsql_test 10.3 {
 401   UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'a');
 402 } {1 {UNIQUE constraint failed: t1.c}}
 403 do_catchsql_test 10.4 {
 404   UPDATE t1 SET c=1 WHERE (a, b) = ('b', 'b');
 405 } {1 {UNIQUE constraint failed: t1.c}}
 406 do_catchsql_test 10.5 {
 407   UPDATE t1 SET c=1 WHERE (a, b) = ('c', 'c');
 408 } {0 {}}
 409
 410 do_execsql_test 10.6 {
 411   CREATE TRIGGER t1_tr BEFORE UPDATE ON t1 BEGIN
 412     DELETE FROM t1 WHERE a = new.a;
 413   END;
 414   UPDATE t1 SET c = c+1 WHERE a = 'a';
 415   SELECT * FROM t1;
 416 } {b a 3  b b 4}
 417
 418 # 2019-04-29 ticket https://www.sqlite.org/src/info/3182d3879020ef3
 419 do_execsql_test 11.1 {
 420   CREATE TABLE t11(a TEXT PRIMARY KEY, b INT) WITHOUT ROWID;
 421   CREATE INDEX t11a ON t11(a COLLATE NOCASE);
 422   INSERT INTO t11(a,b) VALUES ('A',1),('a',2);
 423   PRAGMA integrity_check;
 424   SELECT a FROM t11 ORDER BY a COLLATE binary;
 425 } {ok A a}
 426
 427 # 2019-05-13 ticket https://www.sqlite.org/src/info/bba7b69f9849b5b
 428 do_execsql_test 12.1 {
 429   DROP TABLE IF EXISTS t0;
 430   CREATE TABLE t0 (c0 INTEGER PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT ROWID;
 431   INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5);
 432   REINDEX;
 433   PRAGMA integrity_check;
 434 } {ok}
 435
 436 # 2019-11-07 ticket https://www.sqlite.org/src/info/302027baf1374498
 437 # The xferCompatibleIndex() function confuses a PRIMARY KEY index
 438 # with a UNIQUE index.
 439 #
 440 do_execsql_test 13.10 {
 441   DROP TABLE IF EXISTS t0;
 442   DROP TABLE IF EXISTS t1;
 443   CREATE TABLE t0(
 444     c0,
 445     c1 UNIQUE,
 446     PRIMARY KEY(c1, c1)
 447   ) WITHOUT ROWID;
 448   INSERT INTO t0(c0,c1) VALUES('abc','xyz');
 449   CREATE TABLE t1(
 450     c0,
 451     c1 UNIQUE,
 452     PRIMARY KEY(c1, c1)
 453   ) WITHOUT ROWID;
 454   INSERT INTO t1 SELECT * FROM t0;
 455   PRAGMA integrity_check;
 456   SELECT * FROM t0, t1;
 457 } {ok abc xyz abc xyz}
 458
 459 # 2021-05-13 https://sqlite.org/forum/forumpost/6c8960f545
 460 reset_db
 461 ifcapable altertable {
 462   do_execsql_test 14.1 {
 463     CREATE TABLE t1(a INT PRIMARY KEY) WITHOUT ROWID;
 464     INSERT INTO t1(a) VALUES(10);
 465     ALTER TABLE t1 ADD COLUMN b INT;
 466     SELECT * FROM t1 WHERE a=20 OR (a=10 AND b=10);
 467   } {}
 468   do_execsql_test 14.2 {
 469     CREATE TABLE dual AS SELECT 'X' AS dummy;
 470     EXPLAIN QUERY PLAN SELECT * FROM dual, t1 WHERE a=10 AND b=10;
 471   } {~/b=/}
 472 }
 473
 474 # 2022-01-01 https://sqlite.org/forum/forumpost/b03d86f951 PoC #1
 475 # Omit an assert() from 2013 that no longer serves any purpose and
 476 # is no longer always true.
 477 #
 478 ifcapable altertable {
 479   reset_db
 480   do_execsql_test 15.1 {
 481     PRAGMA writable_schema=ON;
 482     CREATE TABLE sqlite_sequence (name PRIMARY KEY) WITHOUT ROWID;
 483     PRAGMA writable_schema=OFF;
 484     CREATE TABLE c1(x);
 485     INSERT INTO sqlite_sequence(name) VALUES('c0'),('c1'),('c2');
 486     ALTER TABLE c1 RENAME TO a;
 487     SELECT name FROM sqlite_sequence ORDER BY +name;
 488   } {a c0 c2}
 489 }
 490 finish_test