src/third_party/sqlite/src/test/misc1.test

   1 # 2001 September 15.
   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.
  12 #
  13 # This file implements tests for miscellanous features that were
  14 # left out of other test files.
  15 #
  16 # $Id: misc1.test,v 1.42 2007/11/05 14:58:23 drh Exp $
  17
  18 set testdir [file dirname $argv0]
  19 source $testdir/tester.tcl
  20
  21 # Mimic the SQLite 2 collation type NUMERIC.
  22 db collate numeric numeric_collate
  23 proc numeric_collate {lhs rhs} {
  24   if {$lhs == $rhs} {return 0}
  25   return [expr ($lhs>$rhs)?1:-1]
  26 }
  27
  28 # Mimic the SQLite 2 collation type TEXT.
  29 db collate text text_collate
  30 proc numeric_collate {lhs rhs} {
  31   return [string compare $lhs $rhs]
  32 }
  33
  34 # Test the creation and use of tables that have a large number
  35 # of columns.
  36 #
  37 do_test misc1-1.1 {
  38   set cmd "CREATE TABLE manycol(x0 text"
  39   for {set i 1} {$i<=99} {incr i} {
  40     append cmd ",x$i text"
  41   }
  42   append cmd ")";
  43   execsql $cmd
  44   set cmd "INSERT INTO manycol VALUES(0"
  45   for {set i 1} {$i<=99} {incr i} {
  46     append cmd ",$i"
  47   }
  48   append cmd ")";
  49   execsql $cmd
  50   execsql "SELECT x99 FROM manycol"
  51 } 99
  52 do_test misc1-1.2 {
  53   execsql {SELECT x0, x10, x25, x50, x75 FROM manycol}
  54 } {0 10 25 50 75}
  55 do_test misc1-1.3.1 {
  56   for {set j 100} {$j<=1000} {incr j 100} {
  57     set cmd "INSERT INTO manycol VALUES($j"
  58     for {set i 1} {$i<=99} {incr i} {
  59       append cmd ",[expr {$i+$j}]"
  60     }
  61     append cmd ")"
  62     execsql $cmd
  63   }
  64   execsql {SELECT x50 FROM manycol ORDER BY x80+0}
  65 } {50 150 250 350 450 550 650 750 850 950 1050}
  66 do_test misc1-1.3.2 {
  67   execsql {SELECT x50 FROM manycol ORDER BY x80}
  68 } {1050 150 250 350 450 550 650 750 50 850 950}
  69 do_test misc1-1.4 {
  70   execsql {SELECT x75 FROM manycol WHERE x50=350}
  71 } 375
  72 do_test misc1-1.5 {
  73   execsql {SELECT x50 FROM manycol WHERE x99=599}
  74 } 550
  75 do_test misc1-1.6 {
  76   execsql {CREATE INDEX manycol_idx1 ON manycol(x99)}
  77   execsql {SELECT x50 FROM manycol WHERE x99=899}
  78 } 850
  79 do_test misc1-1.7 {
  80   execsql {SELECT count(*) FROM manycol}
  81 } 11
  82 do_test misc1-1.8 {
  83   execsql {DELETE FROM manycol WHERE x98=1234}
  84   execsql {SELECT count(*) FROM manycol}
  85 } 11
  86 do_test misc1-1.9 {
  87   execsql {DELETE FROM manycol WHERE x98=998}
  88   execsql {SELECT count(*) FROM manycol}
  89 } 10
  90 do_test misc1-1.10 {
  91   execsql {DELETE FROM manycol WHERE x99=500}
  92   execsql {SELECT count(*) FROM manycol}
  93 } 10
  94 do_test misc1-1.11 {
  95   execsql {DELETE FROM manycol WHERE x99=599}
  96   execsql {SELECT count(*) FROM manycol}
  97 } 9
  98
  99 # Check GROUP BY expressions that name two or more columns.
 100 #
 101 do_test misc1-2.1 {
 102   execsql {
 103     BEGIN TRANSACTION;
 104     CREATE TABLE agger(one text, two text, three text, four text);
 105     INSERT INTO agger VALUES(1, 'one', 'hello', 'yes');
 106     INSERT INTO agger VALUES(2, 'two', 'howdy', 'no');
 107     INSERT INTO agger VALUES(3, 'thr', 'howareya', 'yes');
 108     INSERT INTO agger VALUES(4, 'two', 'lothere', 'yes');
 109     INSERT INTO agger VALUES(5, 'one', 'atcha', 'yes');
 110     INSERT INTO agger VALUES(6, 'two', 'hello', 'no');
 111     COMMIT
 112   }
 113   execsql {SELECT count(*) FROM agger}
 114 } 6
 115 do_test misc1-2.2 {
 116   execsql {SELECT sum(one), two, four FROM agger
 117            GROUP BY two, four ORDER BY sum(one) desc}
 118 } {8 two no 6 one yes 4 two yes 3 thr yes}
 119 do_test misc1-2.3 {
 120   execsql {SELECT sum((one)), (two), (four) FROM agger
 121            GROUP BY (two), (four) ORDER BY sum(one) desc}
 122 } {8 two no 6 one yes 4 two yes 3 thr yes}
 123
 124 # Here's a test for a bug found by Joel Lucsy.  The code below
 125 # was causing an assertion failure.
 126 #
 127 do_test misc1-3.1 {
 128   set r [execsql {
 129     CREATE TABLE t1(a);
 130     INSERT INTO t1 VALUES('hi');
 131     PRAGMA full_column_names=on;
 132     SELECT rowid, * FROM t1;
 133   }]
 134   lindex $r 1
 135 } {hi}
 136
 137 # Here's a test for yet another bug found by Joel Lucsy.  The code
 138 # below was causing an assertion failure.
 139 #
 140 do_test misc1-4.1 {
 141   execsql {
 142     BEGIN;
 143     CREATE TABLE t2(a);
 144     INSERT INTO t2 VALUES('This is a long string to use up a lot of disk -');
 145     UPDATE t2 SET a=a||a||a||a;
 146     INSERT INTO t2 SELECT '1 - ' || a FROM t2;
 147     INSERT INTO t2 SELECT '2 - ' || a FROM t2;
 148     INSERT INTO t2 SELECT '3 - ' || a FROM t2;
 149     INSERT INTO t2 SELECT '4 - ' || a FROM t2;
 150     INSERT INTO t2 SELECT '5 - ' || a FROM t2;
 151     INSERT INTO t2 SELECT '6 - ' || a FROM t2;
 152     COMMIT;
 153     SELECT count(*) FROM t2;
 154   }
 155 } {64}
 156
 157 # Make sure we actually see a semicolon or end-of-file in the SQL input
 158 # before executing a command.  Thus if "WHERE" is misspelled on an UPDATE,
 159 # the user won't accidently update every record.
 160 #
 161 do_test misc1-5.1 {
 162   catchsql {
 163     CREATE TABLE t3(a,b);
 164     INSERT INTO t3 VALUES(1,2);
 165     INSERT INTO t3 VALUES(3,4);
 166     UPDATE t3 SET a=0 WHEREwww b=2;
 167   }
 168 } {1 {near "WHEREwww": syntax error}}
 169 do_test misc1-5.2 {
 170   execsql {
 171     SELECT * FROM t3 ORDER BY a;
 172   }
 173 } {1 2 3 4}
 174
 175 # Certain keywords (especially non-standard keywords like "REPLACE") can
 176 # also be used as identifiers.  The way this works in the parser is that
 177 # the parser first detects a syntax error, the error handling routine
 178 # sees that the special keyword caused the error, then replaces the keyword
 179 # with "ID" and tries again.
 180 #
 181 # Check the operation of this logic.
 182 #
 183 do_test misc1-6.1 {
 184   catchsql {
 185     CREATE TABLE t4(
 186       abort, asc, begin, cluster, conflict, copy, delimiters, desc, end,
 187       explain, fail, ignore, key, offset, pragma, replace, temp,
 188       vacuum, view
 189     );
 190   }
 191 } {0 {}}
 192 do_test misc1-6.2 {
 193   catchsql {
 194     INSERT INTO t4
 195        VALUES(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19);
 196   }
 197 } {0 {}}
 198 do_test misc1-6.3 {
 199   execsql {
 200     SELECT * FROM t4
 201   }
 202 } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19}
 203 do_test misc1-6.4 {
 204   execsql {
 205     SELECT abort+asc,max(key,pragma,temp) FROM t4
 206   }
 207 } {3 17}
 208
 209 # Test for multi-column primary keys, and for multiple primary keys.
 210 #
 211 do_test misc1-7.1 {
 212   catchsql {
 213     CREATE TABLE error1(
 214       a TYPE PRIMARY KEY,
 215       b TYPE PRIMARY KEY
 216     );
 217   }
 218 } {1 {table "error1" has more than one primary key}}
 219 do_test misc1-7.2 {
 220   catchsql {
 221     CREATE TABLE error1(
 222       a INTEGER PRIMARY KEY,
 223       b TYPE PRIMARY KEY
 224     );
 225   }
 226 } {1 {table "error1" has more than one primary key}}
 227 do_test misc1-7.3 {
 228   execsql {
 229     CREATE TABLE t5(a,b,c,PRIMARY KEY(a,b));
 230     INSERT INTO t5 VALUES(1,2,3);
 231     SELECT * FROM t5 ORDER BY a;
 232   }
 233 } {1 2 3}
 234 do_test misc1-7.4 {
 235   catchsql {
 236     INSERT INTO t5 VALUES(1,2,4);
 237   }
 238 } {1 {columns a, b are not unique}}
 239 do_test misc1-7.5 {
 240   catchsql {
 241     INSERT INTO t5 VALUES(0,2,4);
 242   }
 243 } {0 {}}
 244 do_test misc1-7.6 {
 245   execsql {
 246     SELECT * FROM t5 ORDER BY a;
 247   }
 248 } {0 2 4 1 2 3}
 249
 250 do_test misc1-8.1 {
 251   catchsql {
 252     SELECT *;
 253   }
 254 } {1 {no tables specified}}
 255 do_test misc1-8.2 {
 256   catchsql {
 257     SELECT t1.*;
 258   }
 259 } {1 {no such table: t1}}
 260
 261 execsql {
 262   DROP TABLE t1;
 263   DROP TABLE t2;
 264   DROP TABLE t3;
 265   DROP TABLE t4;
 266 }
 267
 268 # 64-bit integers are represented exactly.
 269 #
 270 do_test misc1-9.1 {
 271   catchsql {
 272     CREATE TABLE t1(a unique not null, b unique not null);
 273     INSERT INTO t1 VALUES('a',1234567890123456789);
 274     INSERT INTO t1 VALUES('b',1234567891123456789);
 275     INSERT INTO t1 VALUES('c',1234567892123456789);
 276     SELECT * FROM t1;
 277   }
 278 } {0 {a 1234567890123456789 b 1234567891123456789 c 1234567892123456789}}
 279
 280 # A WHERE clause is not allowed to contain more than 99 terms.  Check to
 281 # make sure this limit is enforced.
 282 #
 283 # 2005-07-16: There is no longer a limit on the number of terms in a
 284 # WHERE clause.  But keep these tests just so that we have some tests
 285 # that use a large number of terms in the WHERE clause.
 286 #
 287 do_test misc1-10.0 {
 288   execsql {SELECT count(*) FROM manycol}
 289 } {9}
 290 do_test misc1-10.1 {
 291   set ::where {WHERE x0>=0}
 292   for {set i 1} {$i<=99} {incr i} {
 293     append ::where " AND x$i<>0"
 294   }
 295   catchsql "SELECT count(*) FROM manycol $::where"
 296 } {0 9}
 297 do_test misc1-10.2 {
 298   catchsql "SELECT count(*) FROM manycol $::where AND rowid>0"
 299 } {0 9}
 300 do_test misc1-10.3 {
 301   regsub "x0>=0" $::where "x0=0" ::where
 302   catchsql "DELETE FROM manycol $::where"
 303 } {0 {}}
 304 do_test misc1-10.4 {
 305   execsql {SELECT count(*) FROM manycol}
 306 } {8}
 307 do_test misc1-10.5 {
 308   catchsql "DELETE FROM manycol $::where AND rowid>0"
 309 } {0 {}}
 310 do_test misc1-10.6 {
 311   execsql {SELECT x1 FROM manycol WHERE x0=100}
 312 } {101}
 313 do_test misc1-10.7 {
 314   regsub "x0=0" $::where "x0=100" ::where
 315   catchsql "UPDATE manycol SET x1=x1+1 $::where"
 316 } {0 {}}
 317 do_test misc1-10.8 {
 318   execsql {SELECT x1 FROM manycol WHERE x0=100}
 319 } {102}
 320 do_test misc1-10.9 {
 321   catchsql "UPDATE manycol SET x1=x1+1 $::where AND rowid>0"
 322 } {0 {}}
 323 do_test misc1-10.10 {
 324   execsql {SELECT x1 FROM manycol WHERE x0=100}
 325 } {103}
 326
 327 # Make sure the initialization works even if a database is opened while
 328 # another process has the database locked.
 329 #
 330 # Update for v3: The BEGIN doesn't lock the database so the schema is read
 331 # and the SELECT returns successfully.
 332 do_test misc1-11.1 {
 333   execsql {BEGIN}
 334   execsql {UPDATE t1 SET a=0 WHERE 0}
 335   sqlite3 db2 test.db
 336   set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
 337   lappend rc $msg
 338 # v2 result: {1 {database is locked}}
 339 } {0 3}
 340 do_test misc1-11.2 {
 341   execsql {COMMIT}
 342   set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
 343   db2 close
 344   lappend rc $msg
 345 } {0 3}
 346
 347 # Make sure string comparisons really do compare strings in format4+.
 348 # Similar tests in the format3.test file show that for format3 and earlier
 349 # all comparisions where numeric if either operand looked like a number.
 350 #
 351 do_test misc1-12.1 {
 352   execsql {SELECT '0'=='0.0'}
 353 } {0}
 354 do_test misc1-12.2 {
 355   execsql {SELECT '0'==0.0}
 356 } {0}
 357 do_test misc1-12.3 {
 358   execsql {SELECT '12345678901234567890'=='12345678901234567891'}
 359 } {0}
 360 do_test misc1-12.4 {
 361   execsql {
 362     CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE);
 363     INSERT INTO t6 VALUES('0','0.0');
 364     SELECT * FROM t6;
 365   }
 366 } {0 0.0}
 367 ifcapable conflict {
 368   do_test misc1-12.5 {
 369     execsql {
 370       INSERT OR IGNORE INTO t6 VALUES(0.0,'x');
 371       SELECT * FROM t6;
 372     }
 373   } {0 0.0}
 374   do_test misc1-12.6 {
 375     execsql {
 376       INSERT OR IGNORE INTO t6 VALUES('y',0);
 377       SELECT * FROM t6;
 378     }
 379   } {0 0.0 y 0}
 380 }
 381 do_test misc1-12.7 {
 382   execsql {
 383     CREATE TABLE t7(x INTEGER, y TEXT, z);
 384     INSERT INTO t7 VALUES(0,0,1);
 385     INSERT INTO t7 VALUES(0.0,0,2);
 386     INSERT INTO t7 VALUES(0,0.0,3);
 387     INSERT INTO t7 VALUES(0.0,0.0,4);
 388     SELECT DISTINCT x, y FROM t7 ORDER BY z;
 389   }
 390 } {0 0 0 0.0}
 391 do_test misc1-12.8 {
 392   execsql {
 393     SELECT min(z), max(z), count(z) FROM t7 GROUP BY x ORDER BY 1;
 394   }
 395 } {1 4 4}
 396 do_test misc1-12.9 {
 397   execsql {
 398     SELECT min(z), max(z), count(z) FROM t7 GROUP BY y ORDER BY 1;
 399   }
 400 } {1 2 2 3 4 2}
 401
 402 # This used to be an error.  But we changed the code so that arbitrary
 403 # identifiers can be used as a collating sequence.  Collation is by text
 404 # if the identifier contains "text", "blob", or "clob" and is numeric
 405 # otherwise.
 406 #
 407 # Update: In v3, it is an error again.
 408 #
 409 #do_test misc1-12.10 {
 410 #  catchsql {
 411 #    SELECT * FROM t6 ORDER BY a COLLATE unknown;
 412 #  }
 413 #} {0 {0 0 y 0}}
 414 do_test misc1-12.11 {
 415   execsql {
 416     CREATE TABLE t8(x TEXT COLLATE numeric, y INTEGER COLLATE text, z);
 417     INSERT INTO t8 VALUES(0,0,1);
 418     INSERT INTO t8 VALUES(0.0,0,2);
 419     INSERT INTO t8 VALUES(0,0.0,3);
 420     INSERT INTO t8 VALUES(0.0,0.0,4);
 421     SELECT DISTINCT x, y FROM t8 ORDER BY z;
 422   }
 423 } {0 0 0.0 0}
 424 do_test misc1-12.12 {
 425   execsql {
 426     SELECT min(z), max(z), count(z) FROM t8 GROUP BY x ORDER BY 1;
 427   }
 428 } {1 3 2 2 4 2}
 429 do_test misc1-12.13 {
 430   execsql {
 431     SELECT min(z), max(z), count(z) FROM t8 GROUP BY y ORDER BY 1;
 432   }
 433 } {1 4 4}
 434
 435 # There was a problem with realloc() in the OP_MemStore operation of
 436 # the VDBE.  A buffer was being reallocated but some pointers into
 437 # the old copy of the buffer were not being moved over to the new copy.
 438 # The following code tests for the problem.
 439 #
 440 ifcapable subquery {
 441   do_test misc1-13.1 {
 442      execsql {
 443        CREATE TABLE t9(x,y);
 444        INSERT INTO t9 VALUES('one',1);
 445        INSERT INTO t9 VALUES('two',2);
 446        INSERT INTO t9 VALUES('three',3);
 447        INSERT INTO t9 VALUES('four',4);
 448        INSERT INTO t9 VALUES('five',5);
 449        INSERT INTO t9 VALUES('six',6);
 450        INSERT INTO t9 VALUES('seven',7);
 451        INSERT INTO t9 VALUES('eight',8);
 452        INSERT INTO t9 VALUES('nine',9);
 453        INSERT INTO t9 VALUES('ten',10);
 454        INSERT INTO t9 VALUES('eleven',11);
 455        SELECT y FROM t9
 456        WHERE x=(SELECT x FROM t9 WHERE y=1)
 457           OR x=(SELECT x FROM t9 WHERE y=2)
 458           OR x=(SELECT x FROM t9 WHERE y=3)
 459           OR x=(SELECT x FROM t9 WHERE y=4)
 460           OR x=(SELECT x FROM t9 WHERE y=5)
 461           OR x=(SELECT x FROM t9 WHERE y=6)
 462           OR x=(SELECT x FROM t9 WHERE y=7)
 463           OR x=(SELECT x FROM t9 WHERE y=8)
 464           OR x=(SELECT x FROM t9 WHERE y=9)
 465           OR x=(SELECT x FROM t9 WHERE y=10)
 466           OR x=(SELECT x FROM t9 WHERE y=11)
 467           OR x=(SELECT x FROM t9 WHERE y=12)
 468           OR x=(SELECT x FROM t9 WHERE y=13)
 469           OR x=(SELECT x FROM t9 WHERE y=14)
 470        ;
 471      }
 472   } {1 2 3 4 5 6 7 8 9 10 11}
 473 }
 474
 475 # Make sure a database connection still works after changing the
 476 # working directory.
 477 #
 478 do_test misc1-14.1 {
 479   file mkdir tempdir
 480   cd tempdir
 481   execsql {BEGIN}
 482   file exists ./test.db-journal
 483 } {0}
 484 do_test misc1-14.2a {
 485   execsql {UPDATE t1 SET a=a||'x' WHERE 0}
 486   file exists ../test.db-journal
 487 } {0}
 488 do_test misc1-14.2b {
 489   execsql {UPDATE t1 SET a=a||'y' WHERE 1}
 490   file exists ../test.db-journal
 491 } {1}
 492 do_test misc1-14.3 {
 493   cd ..
 494   file delete -force tempdir
 495   execsql {COMMIT}
 496   file exists ./test.db-journal
 497 } {0}
 498
 499 # A failed create table should not leave the table in the internal
 500 # data structures.  Ticket #238.
 501 #
 502 do_test misc1-15.1.1 {
 503   catchsql {
 504     CREATE TABLE t10 AS SELECT c1;
 505   }
 506 } {1 {no such column: c1}}
 507 do_test misc1-15.1.2 {
 508   catchsql {
 509     CREATE TABLE t10 AS SELECT t9.c1;
 510   }
 511 } {1 {no such column: t9.c1}}
 512 do_test misc1-15.1.3 {
 513   catchsql {
 514     CREATE TABLE t10 AS SELECT main.t9.c1;
 515   }
 516 } {1 {no such column: main.t9.c1}}
 517 do_test misc1-15.2 {
 518   catchsql {
 519     CREATE TABLE t10 AS SELECT 1;
 520   }
 521   # The bug in ticket #238 causes the statement above to fail with
 522   # the error "table t10 alread exists"
 523 } {0 {}}
 524
 525 # Test for memory leaks when a CREATE TABLE containing a primary key
 526 # fails.  Ticket #249.
 527 #
 528 do_test misc1-16.1 {
 529   catchsql {SELECT name FROM sqlite_master LIMIT 1}
 530   catchsql {
 531     CREATE TABLE test(a integer, primary key(a));
 532   }
 533 } {0 {}}
 534 do_test misc1-16.2 {
 535   catchsql {
 536     CREATE TABLE test(a integer, primary key(a));
 537   }
 538 } {1 {table test already exists}}
 539 do_test misc1-16.3 {
 540   catchsql {
 541     CREATE TABLE test2(a text primary key, b text, primary key(a,b));
 542   }
 543 } {1 {table "test2" has more than one primary key}}
 544 do_test misc1-16.4 {
 545   execsql {
 546     INSERT INTO test VALUES(1);
 547     SELECT rowid, a FROM test;
 548   }
 549 } {1 1}
 550 do_test misc1-16.5 {
 551   execsql {
 552     INSERT INTO test VALUES(5);
 553     SELECT rowid, a FROM test;
 554   }
 555 } {1 1 5 5}
 556 do_test misc1-16.6 {
 557   execsql {
 558     INSERT INTO test VALUES(NULL);
 559     SELECT rowid, a FROM test;
 560   }
 561 } {1 1 5 5 6 6}
 562
 563 ifcapable trigger&&tempdb {
 564 # Ticket #333: Temp triggers that modify persistent tables.
 565 #
 566 do_test misc1-17.1 {
 567   execsql {
 568     BEGIN;
 569     CREATE TABLE RealTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
 570     CREATE TEMP TABLE TempTable(TestID INTEGER PRIMARY KEY, TestString TEXT);
 571     CREATE TEMP TRIGGER trigTest_1 AFTER UPDATE ON TempTable BEGIN
 572       INSERT INTO RealTable(TestString)
 573          SELECT new.TestString FROM TempTable LIMIT 1;
 574     END;
 575     INSERT INTO TempTable(TestString) VALUES ('1');
 576     INSERT INTO TempTable(TestString) VALUES ('2');
 577     UPDATE TempTable SET TestString = TestString + 1 WHERE TestID=1 OR TestId=2;
 578     COMMIT;
 579     SELECT TestString FROM RealTable ORDER BY 1;
 580   }
 581 } {2 3}
 582 }
 583
 584 do_test misc1-18.1 {
 585   set n [sqlite3_sleep 100]
 586   expr {$n>=100}
 587 } {1}
 588
 589 finish_test