lang/sql/sqlite/test/e_createtable.test

   1 # 2010 September 25
   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 tests to verify that the "testable statements" in
  13 # the lang_createtable.html document are correct.
  14 #
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18
  19 set ::testprefix e_createtable
  20
  21 # Test organization:
  22 #
  23 #   e_createtable-0.*: Test that the syntax diagrams are correct.
  24 #
  25 #   e_createtable-1.*: Test statements related to table and database names,
  26 #       the TEMP and TEMPORARY keywords, and the IF NOT EXISTS clause.
  27 #
  28 #   e_createtable-2.*: Test "CREATE TABLE AS" statements.
  29 #
  30
  31 proc do_createtable_tests {nm args} {
  32   uplevel do_select_tests [list e_createtable-$nm] $args
  33 }
  34
  35
  36 #-------------------------------------------------------------------------
  37 # This command returns a serialized tcl array mapping from the name of
  38 # each attached database to a list of tables in that database. For example,
  39 # if the database schema is created with:
  40 #
  41 #   CREATE TABLE t1(x);
  42 #   CREATE TEMP TABLE t2(x);
  43 #   CREATE TEMP TABLE t3(x);
  44 #
  45 # Then this command returns "main t1 temp {t2 t3}".
  46 #
  47 proc table_list {} {
  48   set res [list]
  49   db eval { pragma database_list } a {
  50     set dbname $a(name)
  51     set master $a(name).sqlite_master
  52     if {$dbname == "temp"} { set master sqlite_temp_master }
  53     lappend res $dbname [
  54       db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name"
  55     ]
  56   }
  57   set res
  58 }
  59
  60
  61 # EVIDENCE-OF: R-25262-01881 -- syntax diagram type-name
  62 #
  63 do_createtable_tests 0.1.1 -repair {
  64   drop_all_tables
  65 } {
  66   1   "CREATE TABLE t1(c1 one)"                        {}
  67   2   "CREATE TABLE t1(c1 one two)"                    {}
  68   3   "CREATE TABLE t1(c1 one two three)"              {}
  69   4   "CREATE TABLE t1(c1 one two three four)"         {}
  70   5   "CREATE TABLE t1(c1 one two three four(14))"     {}
  71   6   "CREATE TABLE t1(c1 one two three four(14, 22))" {}
  72   7   "CREATE TABLE t1(c1 var(+14, -22.3))"            {}
  73   8   "CREATE TABLE t1(c1 var(1.0e10))"                {}
  74 }
  75 do_createtable_tests 0.1.2 -error {
  76   near "%s": syntax error
  77 } {
  78   1   "CREATE TABLE t1(c1 one(number))"                {number}
  79 }
  80
  81
  82 # EVIDENCE-OF: R-18762-12428 -- syntax diagram column-constraint
  83 #
  84 #   Note: Not shown in the syntax diagram is the "NULL" constraint. This
  85 #         is the opposite of "NOT NULL" - it implies that the column may
  86 #         take a NULL value. This is the default anyway, so this type of
  87 #         constraint is rarely used.
  88 #
  89 do_createtable_tests 0.2.1 -repair {
  90   drop_all_tables
  91   execsql { CREATE TABLE t2(x PRIMARY KEY) }
  92 } {
  93   1.1   "CREATE TABLE t1(c1 text PRIMARY KEY)"                         {}
  94   1.2   "CREATE TABLE t1(c1 text PRIMARY KEY ASC)"                     {}
  95   1.3   "CREATE TABLE t1(c1 text PRIMARY KEY DESC)"                    {}
  96   1.4   "CREATE TABLE t1(c1 text CONSTRAINT cons PRIMARY KEY DESC)"    {}
  97
  98   2.1   "CREATE TABLE t1(c1 text NOT NULL)"                            {}
  99   2.2   "CREATE TABLE t1(c1 text CONSTRAINT nm NOT NULL)"              {}
 100   2.3   "CREATE TABLE t1(c1 text NULL)"                                {}
 101   2.4   "CREATE TABLE t1(c1 text CONSTRAINT nm NULL)"                  {}
 102
 103   3.1   "CREATE TABLE t1(c1 text UNIQUE)"                              {}
 104   3.2   "CREATE TABLE t1(c1 text CONSTRAINT un UNIQUE)"                {}
 105
 106   4.1   "CREATE TABLE t1(c1 text CHECK(c1!=0))"                        {}
 107   4.2   "CREATE TABLE t1(c1 text CONSTRAINT chk CHECK(c1!=0))"         {}
 108
 109   5.1   "CREATE TABLE t1(c1 text DEFAULT 1)"                           {}
 110   5.2   "CREATE TABLE t1(c1 text DEFAULT -1)"                          {}
 111   5.3   "CREATE TABLE t1(c1 text DEFAULT +1)"                          {}
 112   5.4   "CREATE TABLE t1(c1 text DEFAULT -45.8e22)"                    {}
 113   5.5   "CREATE TABLE t1(c1 text DEFAULT (1+1))"                       {}
 114   5.6   "CREATE TABLE t1(c1 text CONSTRAINT \"1 2\" DEFAULT (1+1))"    {}
 115
 116   6.1   "CREATE TABLE t1(c1 text COLLATE nocase)"        {}
 117   6.2   "CREATE TABLE t1(c1 text CONSTRAINT 'a x' COLLATE nocase)"     {}
 118
 119   7.1   "CREATE TABLE t1(c1 REFERENCES t2)"                            {}
 120   7.2   "CREATE TABLE t1(c1 CONSTRAINT abc REFERENCES t2)"             {}
 121
 122   8.1   {
 123     CREATE TABLE t1(c1
 124       PRIMARY KEY NOT NULL UNIQUE CHECK(c1 IS 'ten') DEFAULT 123 REFERENCES t1
 125     );
 126   } {}
 127   8.2   {
 128     CREATE TABLE t1(c1
 129       REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY
 130     );
 131   } {}
 132 }
 133
 134 # EVIDENCE-OF: R-17905-31923 -- syntax diagram table-constraint
 135 #
 136 do_createtable_tests 0.3.1 -repair {
 137   drop_all_tables
 138   execsql { CREATE TABLE t2(x PRIMARY KEY) }
 139 } {
 140   1.1   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))"                         {}
 141   1.2   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))"                     {}
 142   1.3   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2) ON CONFLICT IGNORE)"  {}
 143
 144   2.1   "CREATE TABLE t1(c1, c2, UNIQUE(c1))"                              {}
 145   2.2   "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2))"                          {}
 146   2.3   "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)"       {}
 147
 148   3.1   "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))"                     {}
 149
 150   4.1   "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)"           {}
 151 }
 152
 153 # EVIDENCE-OF: R-18765-31171 -- syntax diagram column-def
 154 #
 155 do_createtable_tests 0.4.1 -repair {
 156   drop_all_tables
 157 } {
 158   1     {CREATE TABLE t1(
 159            col1,
 160            col2 TEXT,
 161            col3 INTEGER UNIQUE,
 162            col4 VARCHAR(10, 10) PRIMARY KEY,
 163            "name with spaces" REFERENCES t1
 164          );
 165         } {}
 166 }
 167
 168 # EVIDENCE-OF: R-59573-11075 -- syntax diagram create-table-stmt
 169 #
 170 do_createtable_tests 0.5.1 -repair {
 171   drop_all_tables
 172   execsql { CREATE TABLE t2(a, b, c) }
 173 } {
 174   1     "CREATE TABLE t1(a, b, c)"                                    {}
 175   2     "CREATE TEMP TABLE t1(a, b, c)"                               {}
 176   3     "CREATE TEMPORARY TABLE t1(a, b, c)"                          {}
 177   4     "CREATE TABLE IF NOT EXISTS t1(a, b, c)"                      {}
 178   5     "CREATE TEMP TABLE IF NOT EXISTS t1(a, b, c)"                 {}
 179   6     "CREATE TEMPORARY TABLE IF NOT EXISTS t1(a, b, c)"            {}
 180
 181   7     "CREATE TABLE main.t1(a, b, c)"                               {}
 182   8     "CREATE TEMP TABLE temp.t1(a, b, c)"                          {}
 183   9     "CREATE TEMPORARY TABLE temp.t1(a, b, c)"                     {}
 184   10    "CREATE TABLE IF NOT EXISTS main.t1(a, b, c)"                 {}
 185   11    "CREATE TEMP TABLE IF NOT EXISTS temp.t1(a, b, c)"            {}
 186   12    "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)"       {}
 187
 188   13    "CREATE TABLE t1 AS SELECT * FROM t2"                         {}
 189   14    "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2"              {}
 190   15    "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2"  {}
 191 }
 192
 193 # EVIDENCE-OF: R-32138-02228 -- syntax diagram foreign-key-clause
 194 #
 195 #   1:         Explicit parent-key columns.
 196 #   2:         Implicit child-key columns.
 197 #
 198 #   1:         MATCH FULL
 199 #   2:         MATCH PARTIAL
 200 #   3:         MATCH SIMPLE
 201 #   4:         MATCH STICK
 202 #   5:
 203 #
 204 #   1:         ON DELETE SET NULL
 205 #   2:         ON DELETE SET DEFAULT
 206 #   3:         ON DELETE CASCADE
 207 #   4:         ON DELETE RESTRICT
 208 #   5:         ON DELETE NO ACTION
 209 #   6:
 210 #
 211 #   1:         ON UPDATE SET NULL
 212 #   2:         ON UPDATE SET DEFAULT
 213 #   3:         ON UPDATE CASCADE
 214 #   4:         ON UPDATE RESTRICT
 215 #   5:         ON UPDATE NO ACTION
 216 #   6:
 217 #
 218 #   1:         NOT DEFERRABLE INITIALLY DEFERRED
 219 #   2:         NOT DEFERRABLE INITIALLY IMMEDIATE
 220 #   3:         NOT DEFERRABLE
 221 #   4:         DEFERRABLE INITIALLY DEFERRED
 222 #   5:         DEFERRABLE INITIALLY IMMEDIATE
 223 #   6:         DEFERRABLE
 224 #   7:
 225 #
 226 do_createtable_tests 0.6.1 -repair {
 227   drop_all_tables
 228   execsql { CREATE TABLE t2(x PRIMARY KEY, y) }
 229   execsql { CREATE TABLE t3(i, j, UNIQUE(i, j) ) }
 230 } {
 231   11146 { CREATE TABLE t1(a
 232     REFERENCES t2(x) MATCH FULL
 233     ON DELETE SET NULL ON UPDATE RESTRICT DEFERRABLE
 234   )} {}
 235   11412 { CREATE TABLE t1(a
 236     REFERENCES t2(x)
 237     ON DELETE RESTRICT ON UPDATE SET NULL MATCH FULL
 238     NOT DEFERRABLE INITIALLY IMMEDIATE
 239   )} {}
 240   12135 { CREATE TABLE t1(a
 241     REFERENCES t2(x) MATCH PARTIAL
 242     ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY IMMEDIATE
 243   )} {}
 244   12427 { CREATE TABLE t1(a
 245     REFERENCES t2(x) MATCH PARTIAL
 246     ON DELETE RESTRICT ON UPDATE SET DEFAULT
 247   )} {}
 248   12446 { CREATE TABLE t1(a
 249     REFERENCES t2(x) MATCH PARTIAL
 250     ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE
 251   )} {}
 252   12522 { CREATE TABLE t1(a
 253     REFERENCES t2(x) MATCH PARTIAL
 254     ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
 255   )} {}
 256   13133 { CREATE TABLE t1(a
 257     REFERENCES t2(x) MATCH SIMPLE
 258     ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE
 259   )} {}
 260   13216 { CREATE TABLE t1(a
 261     REFERENCES t2(x) MATCH SIMPLE
 262     ON DELETE SET DEFAULT ON UPDATE SET NULL DEFERRABLE
 263   )} {}
 264   13263 { CREATE TABLE t1(a
 265     REFERENCES t2(x) MATCH SIMPLE
 266     ON DELETE SET DEFAULT  NOT DEFERRABLE
 267   )} {}
 268   13421 { CREATE TABLE t1(a
 269     REFERENCES t2(x) MATCH SIMPLE
 270     ON DELETE RESTRICT ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY DEFERRED
 271   )} {}
 272   13432 { CREATE TABLE t1(a
 273     REFERENCES t2(x) MATCH SIMPLE
 274     ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
 275   )} {}
 276   13523 { CREATE TABLE t1(a
 277     REFERENCES t2(x) MATCH SIMPLE
 278     ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE
 279   )} {}
 280   14336 { CREATE TABLE t1(a
 281     REFERENCES t2(x) MATCH STICK
 282     ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE
 283   )} {}
 284   14611 { CREATE TABLE t1(a
 285     REFERENCES t2(x) MATCH STICK
 286     ON UPDATE SET NULL NOT DEFERRABLE INITIALLY DEFERRED
 287   )} {}
 288   15155 { CREATE TABLE t1(a
 289     REFERENCES t2(x)
 290     ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
 291   )} {}
 292   15453 { CREATE TABLE t1(a
 293     REFERENCES t2(x) ON DELETE RESTRICT ON UPDATE NO ACTION NOT DEFERRABLE
 294   )} {}
 295   15661 { CREATE TABLE t1(a
 296     REFERENCES t2(x) NOT DEFERRABLE INITIALLY DEFERRED
 297   )} {}
 298   21115 { CREATE TABLE t1(a
 299     REFERENCES t2 MATCH FULL
 300     ON DELETE SET NULL ON UPDATE SET NULL DEFERRABLE INITIALLY IMMEDIATE
 301   )} {}
 302   21123 { CREATE TABLE t1(a
 303     REFERENCES t2 MATCH FULL
 304     ON DELETE SET NULL ON UPDATE SET DEFAULT NOT DEFERRABLE
 305   )} {}
 306   21217 { CREATE TABLE t1(a
 307     REFERENCES t2 MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET NULL
 308   )} {}
 309   21362 { CREATE TABLE t1(a
 310     REFERENCES t2 MATCH FULL
 311     ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
 312   )} {}
 313   22143 { CREATE TABLE t1(a
 314     REFERENCES t2 MATCH PARTIAL
 315     ON DELETE SET NULL ON UPDATE RESTRICT NOT DEFERRABLE
 316   )} {}
 317   22156 { CREATE TABLE t1(a
 318     REFERENCES t2 MATCH PARTIAL
 319     ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE
 320   )} {}
 321   22327 { CREATE TABLE t1(a
 322     REFERENCES t2 MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET DEFAULT
 323   )} {}
 324   22663 { CREATE TABLE t1(a
 325     REFERENCES t2 MATCH PARTIAL NOT DEFERRABLE
 326   )} {}
 327   23236 { CREATE TABLE t1(a
 328     REFERENCES t2 MATCH SIMPLE
 329     ON DELETE SET DEFAULT ON UPDATE CASCADE DEFERRABLE
 330   )} {}
 331   24155 { CREATE TABLE t1(a
 332     REFERENCES t2 MATCH STICK
 333     ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
 334   )} {}
 335   24522 { CREATE TABLE t1(a
 336     REFERENCES t2 MATCH STICK
 337     ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
 338   )} {}
 339   24625 { CREATE TABLE t1(a
 340     REFERENCES t2 MATCH STICK
 341     ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE
 342   )} {}
 343   25454 { CREATE TABLE t1(a
 344     REFERENCES t2
 345     ON DELETE RESTRICT ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED
 346   )} {}
 347 }
 348
 349 #-------------------------------------------------------------------------
 350 # Test cases e_createtable-1.* - test statements related to table and
 351 # database names, the TEMP and TEMPORARY keywords, and the IF NOT EXISTS
 352 # clause.
 353 #
 354 drop_all_tables
 355 forcedelete test.db2 test.db3
 356
 357 do_execsql_test e_createtable-1.0 {
 358   ATTACH 'test.db2' AS auxa;
 359   ATTACH 'test.db3' AS auxb;
 360 } {}
 361
 362 # EVIDENCE-OF: R-17899-04554 Table names that begin with "sqlite_" are
 363 # reserved for internal use. It is an error to attempt to create a table
 364 # with a name that starts with "sqlite_".
 365 #
 366 do_createtable_tests 1.1.1 -error {
 367   object name reserved for internal use: %s
 368 } {
 369   1    "CREATE TABLE sqlite_abc(a, b, c)"        sqlite_abc
 370   2    "CREATE TABLE temp.sqlite_helloworld(x)"  sqlite_helloworld
 371   3    {CREATE TABLE auxa."sqlite__"(x, y)}      sqlite__
 372   4    {CREATE TABLE auxb."sqlite_"(z)}          sqlite_
 373   5    {CREATE TABLE "SQLITE_TBL"(z)}            SQLITE_TBL
 374 }
 375 do_createtable_tests 1.1.2 {
 376   1    "CREATE TABLE sqlit_abc(a, b, c)"         {}
 377   2    "CREATE TABLE temp.sqlitehelloworld(x)"   {}
 378   3    {CREATE TABLE auxa."sqlite"(x, y)}        {}
 379   4    {CREATE TABLE auxb."sqlite-"(z)}          {}
 380   5    {CREATE TABLE "SQLITE-TBL"(z)}            {}
 381 }
 382
 383
 384 # EVIDENCE-OF: R-10195-31023 If a <database-name> is specified, it
 385 # must be either "main", "temp", or the name of an attached database.
 386 #
 387 # EVIDENCE-OF: R-39822-07822 In this case the new table is created in
 388 # the named database.
 389 #
 390 #   Test cases 1.2.* test the first of the two requirements above. The
 391 #   second is verified by cases 1.3.*.
 392 #
 393 do_createtable_tests 1.2.1 -error {
 394   unknown database %s
 395 } {
 396   1    "CREATE TABLE george.t1(a, b)"            george
 397   2    "CREATE TABLE _.t1(a, b)"                 _
 398 }
 399 do_createtable_tests 1.2.2 {
 400   1    "CREATE TABLE main.abc(a, b, c)"          {}
 401   2    "CREATE TABLE temp.helloworld(x)"         {}
 402   3    {CREATE TABLE auxa."t 1"(x, y)}           {}
 403   4    {CREATE TABLE auxb.xyz(z)}                {}
 404 }
 405 drop_all_tables
 406 do_createtable_tests 1.3 -tclquery {
 407   unset -nocomplain X
 408   array set X [table_list]
 409   list $X(main) $X(temp) $X(auxa) $X(auxb)
 410 } {
 411   1    "CREATE TABLE main.abc(a, b, c)"  {abc {} {} {}}
 412   2    "CREATE TABLE main.t1(a, b, c)"   {{abc t1} {} {} {}}
 413   3    "CREATE TABLE temp.tmp(a, b, c)"  {{abc t1} tmp {} {}}
 414   4    "CREATE TABLE auxb.tbl(x, y)"     {{abc t1} tmp {} tbl}
 415   5    "CREATE TABLE auxb.t1(k, v)"      {{abc t1} tmp {} {t1 tbl}}
 416   6    "CREATE TABLE auxa.next(c, d)"    {{abc t1} tmp next {t1 tbl}}
 417 }
 418
 419 # EVIDENCE-OF: R-18895-27365 If the "TEMP" or "TEMPORARY" keyword occurs
 420 # between the "CREATE" and "TABLE" then the new table is created in the
 421 # temp database.
 422 #
 423 drop_all_tables
 424 do_createtable_tests 1.4 -tclquery {
 425   unset -nocomplain X
 426   array set X [table_list]
 427   list $X(main) $X(temp) $X(auxa) $X(auxb)
 428 } {
 429   1    "CREATE TEMP TABLE t1(a, b)"      {{} t1 {} {}}
 430   2    "CREATE TEMPORARY TABLE t2(a, b)" {{} {t1 t2} {} {}}
 431 }
 432
 433 # EVIDENCE-OF: R-49439-47561 It is an error to specify both a
 434 # <database-name> and the TEMP or TEMPORARY keyword, unless the
 435 # <database-name> is "temp".
 436 #
 437 drop_all_tables
 438 do_createtable_tests 1.5.1 -error {
 439   temporary table name must be unqualified
 440 } {
 441   1    "CREATE TEMP TABLE main.t1(a, b)"        {}
 442   2    "CREATE TEMPORARY TABLE auxa.t2(a, b)"   {}
 443   3    "CREATE TEMP TABLE auxb.t3(a, b)"        {}
 444   4    "CREATE TEMPORARY TABLE main.xxx(x)"     {}
 445 }
 446 drop_all_tables
 447 do_createtable_tests 1.5.2 -tclquery {
 448   unset -nocomplain X
 449   array set X [table_list]
 450   list $X(main) $X(temp) $X(auxa) $X(auxb)
 451 } {
 452   1    "CREATE TEMP TABLE temp.t1(a, b)"        {{} t1 {} {}}
 453   2    "CREATE TEMPORARY TABLE temp.t2(a, b)"   {{} {t1 t2} {} {}}
 454   3    "CREATE TEMP TABLE TEMP.t3(a, b)"        {{} {t1 t2 t3} {} {}}
 455   4    "CREATE TEMPORARY TABLE TEMP.xxx(x)"     {{} {t1 t2 t3 xxx} {} {}}
 456 }
 457
 458 # EVIDENCE-OF: R-00917-09393 If no database name is specified and the
 459 # TEMP keyword is not present then the table is created in the main
 460 # database.
 461 #
 462 drop_all_tables
 463 do_createtable_tests 1.6 -tclquery {
 464   unset -nocomplain X
 465   array set X [table_list]
 466   list $X(main) $X(temp) $X(auxa) $X(auxb)
 467 } {
 468   1    "CREATE TABLE t1(a, b)"   {t1 {} {} {}}
 469   2    "CREATE TABLE t2(a, b)"   {{t1 t2} {} {} {}}
 470   3    "CREATE TABLE t3(a, b)"   {{t1 t2 t3} {} {} {}}
 471   4    "CREATE TABLE xxx(x)"     {{t1 t2 t3 xxx} {} {} {}}
 472 }
 473
 474 drop_all_tables
 475 do_execsql_test e_createtable-1.7.0 {
 476   CREATE TABLE t1(x, y);
 477   CREATE INDEX i1 ON t1(x);
 478   CREATE VIEW  v1 AS SELECT * FROM t1;
 479
 480   CREATE TABLE auxa.tbl1(x, y);
 481   CREATE INDEX auxa.idx1 ON tbl1(x);
 482   CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
 483 } {}
 484
 485 # EVIDENCE-OF: R-01232-54838 It is usually an error to attempt to create
 486 # a new table in a database that already contains a table, index or view
 487 # of the same name.
 488 #
 489 #   Test cases 1.7.1.* verify that creating a table in a database with a
 490 #   table/index/view of the same name does fail. 1.7.2.* tests that creating
 491 #   a table with the same name as a table/index/view in a different database
 492 #   is Ok.
 493 #
 494 do_createtable_tests 1.7.1 -error { %s } {
 495   1    "CREATE TABLE t1(a, b)"   {{table t1 already exists}}
 496   2    "CREATE TABLE i1(a, b)"   {{there is already an index named i1}}
 497   3    "CREATE TABLE v1(a, b)"   {{table v1 already exists}}
 498   4    "CREATE TABLE auxa.tbl1(a, b)"   {{table tbl1 already exists}}
 499   5    "CREATE TABLE auxa.idx1(a, b)"   {{there is already an index named idx1}}
 500   6    "CREATE TABLE auxa.view1(a, b)"  {{table view1 already exists}}
 501 }
 502 do_createtable_tests 1.7.2 {
 503   1    "CREATE TABLE auxa.t1(a, b)"   {}
 504   2    "CREATE TABLE auxa.i1(a, b)"   {}
 505   3    "CREATE TABLE auxa.v1(a, b)"   {}
 506   4    "CREATE TABLE tbl1(a, b)"      {}
 507   5    "CREATE TABLE idx1(a, b)"      {}
 508   6    "CREATE TABLE view1(a, b)"     {}
 509 }
 510
 511 # EVIDENCE-OF: R-33917-24086 However, if the "IF NOT EXISTS" clause is
 512 # specified as part of the CREATE TABLE statement and a table or view of
 513 # the same name already exists, the CREATE TABLE command simply has no
 514 # effect (and no error message is returned).
 515 #
 516 drop_all_tables
 517 do_execsql_test e_createtable-1.8.0 {
 518   CREATE TABLE t1(x, y);
 519   CREATE INDEX i1 ON t1(x);
 520   CREATE VIEW  v1 AS SELECT * FROM t1;
 521   CREATE TABLE auxa.tbl1(x, y);
 522   CREATE INDEX auxa.idx1 ON tbl1(x);
 523   CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
 524 } {}
 525 do_createtable_tests 1.8 {
 526   1    "CREATE TABLE IF NOT EXISTS t1(a, b)"          {}
 527   2    "CREATE TABLE IF NOT EXISTS auxa.tbl1(a, b)"   {}
 528   3    "CREATE TABLE IF NOT EXISTS v1(a, b)"          {}
 529   4    "CREATE TABLE IF NOT EXISTS auxa.view1(a, b)"  {}
 530 }
 531
 532 # EVIDENCE-OF: R-16465-40078 An error is still returned if the table
 533 # cannot be created because of an existing index, even if the "IF NOT
 534 # EXISTS" clause is specified.
 535 #
 536 do_createtable_tests 1.9 -error { %s } {
 537   1    "CREATE TABLE IF NOT EXISTS i1(a, b)"
 538        {{there is already an index named i1}}
 539   2    "CREATE TABLE IF NOT EXISTS auxa.idx1(a, b)"
 540        {{there is already an index named idx1}}
 541 }
 542
 543 # EVIDENCE-OF: R-05513-33819 It is not an error to create a table that
 544 # has the same name as an existing trigger.
 545 #
 546 drop_all_tables
 547 do_execsql_test e_createtable-1.10.0 {
 548   CREATE TABLE t1(x, y);
 549   CREATE TABLE auxb.t2(x, y);
 550
 551   CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
 552     SELECT 1;
 553   END;
 554   CREATE TRIGGER auxb.tr2 AFTER INSERT ON t2 BEGIN
 555     SELECT 1;
 556   END;
 557 } {}
 558 do_createtable_tests 1.10 {
 559   1    "CREATE TABLE tr1(a, b)"          {}
 560   2    "CREATE TABLE tr2(a, b)"          {}
 561   3    "CREATE TABLE auxb.tr1(a, b)"     {}
 562   4    "CREATE TABLE auxb.tr2(a, b)"     {}
 563 }
 564
 565 # EVIDENCE-OF: R-22283-14179 Tables are removed using the DROP TABLE
 566 # statement.
 567 #
 568 drop_all_tables
 569 do_execsql_test e_createtable-1.11.0 {
 570   CREATE TABLE t1(a, b);
 571   CREATE TABLE t2(a, b);
 572   CREATE TABLE auxa.t3(a, b);
 573   CREATE TABLE auxa.t4(a, b);
 574 } {}
 575
 576 do_execsql_test e_createtable-1.11.1.1 {
 577   SELECT * FROM t1;
 578   SELECT * FROM t2;
 579   SELECT * FROM t3;
 580   SELECT * FROM t4;
 581 } {}
 582 do_execsql_test  e_createtable-1.11.1.2 { DROP TABLE t1 } {}
 583 do_catchsql_test e_createtable-1.11.1.3 {
 584   SELECT * FROM t1
 585 } {1 {no such table: t1}}
 586 do_execsql_test  e_createtable-1.11.1.4 { DROP TABLE t3 } {}
 587 do_catchsql_test e_createtable-1.11.1.5 {
 588   SELECT * FROM t3
 589 } {1 {no such table: t3}}
 590
 591 do_execsql_test e_createtable-1.11.2.1 {
 592   SELECT name FROM sqlite_master;
 593   SELECT name FROM auxa.sqlite_master;
 594 } {t2 t4}
 595 do_execsql_test  e_createtable-1.11.2.2 { DROP TABLE t2 } {}
 596 do_execsql_test  e_createtable-1.11.2.3 { DROP TABLE t4 } {}
 597 do_execsql_test e_createtable-1.11.2.4 {
 598   SELECT name FROM sqlite_master;
 599   SELECT name FROM auxa.sqlite_master;
 600 } {}
 601
 602 #-------------------------------------------------------------------------
 603 # Test cases e_createtable-2.* - test statements related to the CREATE
 604 # TABLE AS ... SELECT statement.
 605 #
 606
 607 # Three Tcl commands:
 608 #
 609 #   select_column_names SQL
 610 #     The argument must be a SELECT statement. Return a list of the names
 611 #     of the columns of the result-set that would be returned by executing
 612 #     the SELECT.
 613 #
 614 #   table_column_names TBL
 615 #     The argument must be a table name. Return a list of column names, from
 616 #     left to right, for the table.
 617 #
 618 #   table_column_decltypes TBL
 619 #     The argument must be a table name. Return a list of column declared
 620 #     types, from left to right, for the table.
 621 #
 622 proc sci {select cmd} {
 623   set res [list]
 624   set STMT [sqlite3_prepare_v2 db $select -1 dummy]
 625   for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
 626     lappend res [$cmd $STMT $i]
 627   }
 628   sqlite3_finalize $STMT
 629   set res
 630 }
 631 proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd }
 632 proc select_column_names    {sql} { sci $sql sqlite3_column_name }
 633 proc table_column_names     {tbl} { tci $tbl sqlite3_column_name }
 634 proc table_column_decltypes {tbl} { tci $tbl sqlite3_column_decltype }
 635
 636 # Create a database schema. This schema is used by tests 2.1.* through 2.3.*.
 637 #
 638 drop_all_tables
 639 do_execsql_test e_createtable-2.0 {
 640   CREATE TABLE t1(a, b, c);
 641   CREATE TABLE t2(d, e, f);
 642   CREATE TABLE t3(g BIGINT, h VARCHAR(10));
 643   CREATE TABLE t4(i BLOB, j ANYOLDATA);
 644   CREATE TABLE t5(k FLOAT, l INTEGER);
 645   CREATE TABLE t6(m DEFAULT 10, n DEFAULT 5, PRIMARY KEY(m, n));
 646   CREATE TABLE t7(x INTEGER PRIMARY KEY);
 647   CREATE TABLE t8(o COLLATE nocase DEFAULT 'abc');
 648   CREATE TABLE t9(p NOT NULL, q DOUBLE CHECK (q!=0), r STRING UNIQUE);
 649 } {}
 650
 651 # EVIDENCE-OF: R-64828-59568 The table has the same number of columns as
 652 # the rows returned by the SELECT statement. The name of each column is
 653 # the same as the name of the corresponding column in the result set of
 654 # the SELECT statement.
 655 #
 656 do_createtable_tests 2.1 -tclquery {
 657   table_column_names x1
 658 } -repair {
 659   catchsql { DROP TABLE x1 }
 660 } {
 661   1    "CREATE TABLE x1 AS SELECT * FROM t1"                     {a b c}
 662   2    "CREATE TABLE x1 AS SELECT c, b, a FROM t1"               {c b a}
 663   3    "CREATE TABLE x1 AS SELECT * FROM t1, t2"                 {a b c d e f}
 664   4    "CREATE TABLE x1 AS SELECT count(*) FROM t1"              {count(*)}
 665   5    "CREATE TABLE x1 AS SELECT count(a) AS a, max(b) FROM t1" {a max(b)}
 666 }
 667
 668 # EVIDENCE-OF: R-37111-22855 The declared type of each column is
 669 # determined by the expression affinity of the corresponding expression
 670 # in the result set of the SELECT statement, as follows: Expression
 671 # Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT"
 672 # REAL "REAL" NONE "" (empty string)
 673 #
 674 do_createtable_tests 2.2 -tclquery {
 675   table_column_decltypes x1
 676 } -repair {
 677   catchsql { DROP TABLE x1 }
 678 } {
 679   1    "CREATE TABLE x1 AS SELECT a FROM t1"     {""}
 680   2    "CREATE TABLE x1 AS SELECT * FROM t3"     {INT TEXT}
 681   3    "CREATE TABLE x1 AS SELECT * FROM t4"     {"" NUM}
 682   4    "CREATE TABLE x1 AS SELECT * FROM t5"     {REAL INT}
 683 }
 684
 685 # EVIDENCE-OF: R-16667-09772 A table created using CREATE TABLE AS has
 686 # no PRIMARY KEY and no constraints of any kind. The default value of
 687 # each column is NULL. The default collation sequence for each column of
 688 # the new table is BINARY.
 689 #
 690 #   The following tests create tables based on SELECT statements that read
 691 #   from tables that have primary keys, constraints and explicit default
 692 #   collation sequences. None of this is transfered to the definition of
 693 #   the new table as stored in the sqlite_master table.
 694 #
 695 #   Tests 2.3.2.* show that the default value of each column is NULL.
 696 #
 697 do_createtable_tests 2.3.1 -query {
 698   SELECT sql FROM sqlite_master ORDER BY rowid DESC LIMIT 1
 699 } {
 700   1    "CREATE TABLE x1 AS SELECT * FROM t6" {{CREATE TABLE x1(m,n)}}
 701   2    "CREATE TABLE x2 AS SELECT * FROM t7" {{CREATE TABLE x2(x INT)}}
 702   3    "CREATE TABLE x3 AS SELECT * FROM t8" {{CREATE TABLE x3(o)}}
 703   4    "CREATE TABLE x4 AS SELECT * FROM t9" {{CREATE TABLE x4(p,q REAL,r NUM)}}
 704 }
 705 do_execsql_test e_createtable-2.3.2.1 {
 706   INSERT INTO x1 DEFAULT VALUES;
 707   INSERT INTO x2 DEFAULT VALUES;
 708   INSERT INTO x3 DEFAULT VALUES;
 709   INSERT INTO x4 DEFAULT VALUES;
 710 } {}
 711 db nullvalue null
 712 do_execsql_test e_createtable-2.3.2.2 { SELECT * FROM x1 } {null null}
 713 do_execsql_test e_createtable-2.3.2.3 { SELECT * FROM x2 } {null}
 714 do_execsql_test e_createtable-2.3.2.4 { SELECT * FROM x3 } {null}
 715 do_execsql_test e_createtable-2.3.2.5 { SELECT * FROM x4 } {null null null}
 716 db nullvalue {}
 717
 718 drop_all_tables
 719 do_execsql_test e_createtable-2.4.0 {
 720   CREATE TABLE t1(x, y);
 721   INSERT INTO t1 VALUES('i',   'one');
 722   INSERT INTO t1 VALUES('ii',  'two');
 723   INSERT INTO t1 VALUES('iii', 'three');
 724 } {}
 725
 726 # EVIDENCE-OF: R-24153-28352 Tables created using CREATE TABLE AS are
 727 # initially populated with the rows of data returned by the SELECT
 728 # statement.
 729 #
 730 # EVIDENCE-OF: R-08224-30249 Rows are assigned contiguously ascending
 731 # rowid values, starting with 1, in the order that they are returned by
 732 # the SELECT statement.
 733 #
 734 #   Each test case below is specified as the name of a table to create
 735 #   using "CREATE TABLE ... AS SELECT ..." and a SELECT statement to use in
 736 #   creating it. The table is created.
 737 #
 738 #   Test cases 2.4.*.1 check that after it has been created, the data in the
 739 #   table is the same as the data returned by the SELECT statement executed as
 740 #   a standalone command, verifying the first testable statement above.
 741 #
 742 #   Test cases 2.4.*.2 check that the rowids were allocated contiguously
 743 #   as required by the second testable statement above. That the rowids
 744 #   from the contiguous block were allocated to rows in the order rows are
 745 #   returned by the SELECT statement is verified by 2.4.*.1.
 746 #
 747 # EVIDENCE-OF: R-32365-09043 A "CREATE TABLE ... AS SELECT" statement
 748 # creates and populates a database table based on the results of a
 749 # SELECT statement.
 750 #
 751 #   The above is also considered to be tested by the following. It is
 752 #   clear that tables are being created and populated by the command in
 753 #   question.
 754 #
 755 foreach {tn tbl select} {
 756   1   x1   "SELECT * FROM t1"
 757   2   x2   "SELECT * FROM t1 ORDER BY x DESC"
 758   3   x3   "SELECT * FROM t1 ORDER BY x ASC"
 759 } {
 760   # Create the table using a "CREATE TABLE ... AS SELECT ..." command.
 761   execsql [subst {CREATE TABLE $tbl AS $select}]
 762
 763   # Check that the rows inserted into the table, sorted in ascending rowid
 764   # order, match those returned by executing the SELECT statement as a
 765   # standalone command.
 766   do_execsql_test e_createtable-2.4.$tn.1 [subst {
 767     SELECT * FROM $tbl ORDER BY rowid;
 768   }] [execsql $select]
 769
 770   # Check that the rowids in the new table are a contiguous block starting
 771   # with rowid 1. Note that this will fail if SELECT statement $select
 772   # returns 0 rows (as max(rowid) will be NULL).
 773   do_execsql_test e_createtable-2.4.$tn.2 [subst {
 774     SELECT min(rowid), count(rowid)==max(rowid) FROM $tbl
 775   }] {1 1}
 776 }
 777
 778 #--------------------------------------------------------------------------
 779 # Test cases for column defintions in CREATE TABLE statements that do not
 780 # use a SELECT statement. Not including data constraints. In other words,
 781 # tests for the specification of:
 782 #
 783 #   * declared types,
 784 #   * default values, and
 785 #   * default collation sequences.
 786 #
 787
 788 # EVIDENCE-OF: R-27219-49057 Unlike most SQL databases, SQLite does not
 789 # restrict the type of data that may be inserted into a column based on
 790 # the columns declared type.
 791 #
 792 #   Test this by creating a few tables with varied declared types, then
 793 #   inserting various different types of values into them.
 794 #
 795 drop_all_tables
 796 do_execsql_test e_createtable-3.1.0 {
 797   CREATE TABLE t1(x VARCHAR(10), y INTEGER, z DOUBLE);
 798   CREATE TABLE t2(a DATETIME, b STRING, c REAL);
 799   CREATE TABLE t3(o, t);
 800 } {}
 801
 802 # value type -> declared column type
 803 # ----------------------------------
 804 # integer    -> VARCHAR(10)
 805 # string     -> INTEGER
 806 # blob       -> DOUBLE
 807 #
 808 do_execsql_test e_createtable-3.1.1 {
 809   INSERT INTO t1 VALUES(14, 'quite a lengthy string', X'555655');
 810   SELECT * FROM t1;
 811 } {14 {quite a lengthy string} UVU}
 812
 813 # string     -> DATETIME
 814 # integer    -> STRING
 815 # time       -> REAL
 816 #
 817 do_execsql_test e_createtable-3.1.2 {
 818   INSERT INTO t2 VALUES('not a datetime', 13, '12:41:59');
 819   SELECT * FROM t2;
 820 } {{not a datetime} 13 12:41:59}
 821
 822 # EVIDENCE-OF: R-10565-09557 The declared type of a column is used to
 823 # determine the affinity of the column only.
 824 #
 825 #     Affinities are tested in more detail elsewhere (see document
 826 #     datatype3.html). Here, just test that affinity transformations
 827 #     consistent with the expected affinity of each column (based on
 828 #     the declared type) appear to take place.
 829 #
 830 # Affinities of t1 (test cases 3.2.1.*): TEXT, INTEGER, REAL
 831 # Affinities of t2 (test cases 3.2.2.*): NUMERIC, NUMERIC, REAL
 832 # Affinities of t3 (test cases 3.2.3.*): NONE, NONE
 833 #
 834 do_execsql_test e_createtable-3.2.0 { DELETE FROM t1; DELETE FROM t2; } {}
 835
 836 do_createtable_tests 3.2.1 -query {
 837   SELECT quote(x), quote(y), quote(z) FROM t1 ORDER BY rowid DESC LIMIT 1;
 838 } {
 839   1   "INSERT INTO t1 VALUES(15,   '22.0', '14')"   {'15' 22 14.0}
 840   2   "INSERT INTO t1 VALUES(22.0, 22.0, 22.0)"     {'22.0' 22 22.0}
 841 }
 842 do_createtable_tests 3.2.2 -query {
 843   SELECT quote(a), quote(b), quote(c) FROM t2 ORDER BY rowid DESC LIMIT 1;
 844 } {
 845   1   "INSERT INTO t2 VALUES(15,   '22.0', '14')"   {15   22  14.0}
 846   2   "INSERT INTO t2 VALUES(22.0, 22.0, 22.0)"     {22   22  22.0}
 847 }
 848 do_createtable_tests 3.2.3 -query {
 849   SELECT quote(o), quote(t) FROM t3 ORDER BY rowid DESC LIMIT 1;
 850 } {
 851   1   "INSERT INTO t3 VALUES('15', '22.0')"         {'15' '22.0'}
 852   2   "INSERT INTO t3 VALUES(15, 22.0)"             {15 22.0}
 853 }
 854
 855 # EVIDENCE-OF: R-42316-09582 If there is no explicit DEFAULT clause
 856 # attached to a column definition, then the default value of the column
 857 # is NULL.
 858 #
 859 #     None of the columns in table t1 have an explicit DEFAULT clause.
 860 #     So testing that the default value of all columns in table t1 is
 861 #     NULL serves to verify the above.
 862 #
 863 do_createtable_tests 3.2.3 -query {
 864   SELECT quote(x), quote(y), quote(z) FROM t1
 865 } -repair {
 866   execsql { DELETE FROM t1 }
 867 } {
 868   1   "INSERT INTO t1(x, y) VALUES('abc', 'xyz')"   {'abc' 'xyz' NULL}
 869   2   "INSERT INTO t1(x, z) VALUES('abc', 'xyz')"   {'abc' NULL 'xyz'}
 870   3   "INSERT INTO t1 DEFAULT VALUES"               {NULL NULL NULL}
 871 }
 872
 873 # EVIDENCE-OF: R-62940-43005 An explicit DEFAULT clause may specify that
 874 # the default value is NULL, a string constant, a blob constant, a
 875 # signed-number, or any constant expression enclosed in parentheses. An
 876 # explicit default value may also be one of the special case-independent
 877 # keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
 878 #
 879 do_execsql_test e_createtable-3.3.1 {
 880   CREATE TABLE t4(
 881     a DEFAULT NULL,
 882     b DEFAULT 'string constant',
 883     c DEFAULT X'424C4F42',
 884     d DEFAULT 1,
 885     e DEFAULT -1,
 886     f DEFAULT 3.14,
 887     g DEFAULT -3.14,
 888     h DEFAULT ( substr('abcd', 0, 2) || 'cd' ),
 889     i DEFAULT CURRENT_TIME,
 890     j DEFAULT CURRENT_DATE,
 891     k DEFAULT CURRENT_TIMESTAMP
 892   );
 893 } {}
 894
 895 # EVIDENCE-OF: R-10288-43169 For the purposes of the DEFAULT clause, an
 896 # expression is considered constant provided that it does not contain
 897 # any sub-queries or string constants enclosed in double quotes.
 898 #
 899 do_createtable_tests 3.4.1 -error {
 900   default value of column [x] is not constant
 901 } {
 902   1   {CREATE TABLE t5(x DEFAULT ( (SELECT 1) ))}  {}
 903   2   {CREATE TABLE t5(x DEFAULT ( "abc" ))}  {}
 904   3   {CREATE TABLE t5(x DEFAULT ( 1 IN (SELECT 1) ))}  {}
 905   4   {CREATE TABLE t5(x DEFAULT ( EXISTS (SELECT 1) ))}  {}
 906 }
 907 do_createtable_tests 3.4.2 -repair {
 908   catchsql { DROP TABLE t5 }
 909 } {
 910   1   {CREATE TABLE t5(x DEFAULT ( 'abc' ))}  {}
 911   2   {CREATE TABLE t5(x DEFAULT ( 1 IN (1, 2, 3) ))}  {}
 912 }
 913
 914 # EVIDENCE-OF: R-18814-23501 Each time a row is inserted into the table
 915 # by an INSERT statement that does not provide explicit values for all
 916 # table columns the values stored in the new row are determined by their
 917 # default values
 918 #
 919 #     Verify this with some assert statements for which all, some and no
 920 #     columns lack explicit values.
 921 #
 922 set sqlite_current_time 1000000000
 923 do_createtable_tests 3.5 -query {
 924   SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f),
 925          quote(g), quote(h), quote(i), quote(j), quote(k)
 926   FROM t4 ORDER BY rowid DESC LIMIT 1;
 927 } {
 928   1 "INSERT INTO t4 DEFAULT VALUES" {
 929     NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14
 930     'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
 931   }
 932
 933   2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" {
 934     1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
 935   }
 936
 937   3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" {
 938     NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1
 939   }
 940
 941   4 "INSERT INTO t4(a,b,c,d,e,f,g,h,i,j,k) VALUES(1,2,3,4,5,6,7,8,9,10,11)" {
 942     1 2 3 4 5 6 7 8 9 10 11
 943   }
 944 }
 945
 946 # EVIDENCE-OF: R-12572-62501 If the default value of the column is a
 947 # constant NULL, text, blob or signed-number value, then that value is
 948 # used directly in the new row.
 949 #
 950 do_execsql_test e_createtable-3.6.1 {
 951   CREATE TABLE t5(
 952     a DEFAULT NULL,
 953     b DEFAULT 'text value',
 954     c DEFAULT X'424C4F42',
 955     d DEFAULT -45678.6,
 956     e DEFAULT 394507
 957   );
 958 } {}
 959 do_execsql_test e_createtable-3.6.2 {
 960   INSERT INTO t5 DEFAULT VALUES;
 961   SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5;
 962 } {NULL {'text value'} X'424C4F42' -45678.6 394507}
 963
 964 # EVIDENCE-OF: R-60616-50251 If the default value of a column is an
 965 # expression in parentheses, then the expression is evaluated once for
 966 # each row inserted and the results used in the new row.
 967 #
 968 #   Test case 3.6.4 demonstrates that the expression is evaluated
 969 #   separately for each row if the INSERT is an "INSERT INTO ... SELECT ..."
 970 #   command.
 971 #
 972 set ::nextint 0
 973 proc nextint {} { incr ::nextint }
 974 db func nextint nextint
 975
 976 do_execsql_test e_createtable-3.7.1 {
 977   CREATE TABLE t6(a DEFAULT ( nextint() ), b DEFAULT ( nextint() ));
 978 } {}
 979 do_execsql_test e_createtable-3.7.2 {
 980   INSERT INTO t6 DEFAULT VALUES;
 981   SELECT quote(a), quote(b) FROM t6;
 982 } {1 2}
 983 do_execsql_test e_createtable-3.7.3 {
 984   INSERT INTO t6(a) VALUES('X');
 985   SELECT quote(a), quote(b) FROM t6;
 986 } {1 2 'X' 3}
 987 do_execsql_test e_createtable-3.7.4 {
 988   INSERT INTO t6(a) SELECT a FROM t6;
 989   SELECT quote(a), quote(b) FROM t6;
 990 } {1 2 'X' 3 1 4 'X' 5}
 991
 992 # EVIDENCE-OF: R-18683-56219 If the default value of a column is
 993 # CURRENT_TIME, CURRENT_DATE or CURRENT_DATETIME, then the value used in
 994 # the new row is a text representation of the current UTC date and/or
 995 # time.
 996 #
 997 #     This is difficult to test literally without knowing what time the
 998 #     user will run the tests. Instead, we test that the three cases
 999 #     above set the value to the current date and/or time according to
1000 #     the xCurrentTime() method of the VFS. Which is usually the same
1001 #     as UTC. In this case, however, we instrument it to always return
1002 #     a time equivalent to "2001-09-09 01:46:40 UTC".
1003 #
1004 set sqlite_current_time 1000000000
1005 do_execsql_test e_createtable-3.8.1 {
1006   CREATE TABLE t7(
1007     a DEFAULT CURRENT_TIME,
1008     b DEFAULT CURRENT_DATE,
1009     c DEFAULT CURRENT_TIMESTAMP
1010   );
1011 } {}
1012 do_execsql_test e_createtable-3.8.2 {
1013   INSERT INTO t7 DEFAULT VALUES;
1014   SELECT quote(a), quote(b), quote(c) FROM t7;
1015 } {'01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}}
1016
1017
1018 # EVIDENCE-OF: R-62327-53843 For CURRENT_TIME, the format of the value
1019 # is "HH:MM:SS".
1020 #
1021 # EVIDENCE-OF: R-03775-43471 For CURRENT_DATE, "YYYY-MM-DD".
1022 #
1023 # EVIDENCE-OF: R-07677-44926 The format for CURRENT_TIMESTAMP is
1024 # "YYYY-MM-DD HH:MM:SS".
1025 #
1026 #     The three above are demonstrated by tests 1, 2 and 3 below.
1027 #     Respectively.
1028 #
1029 do_createtable_tests 3.8.3 -query {
1030   SELECT a, b, c FROM t7 ORDER BY rowid DESC LIMIT 1;
1031 } {
1032   1 "INSERT INTO t7(b, c) VALUES('x', 'y')" {01:46:40 x y}
1033   2 "INSERT INTO t7(c, a) VALUES('x', 'y')" {y 2001-09-09 x}
1034   3 "INSERT INTO t7(a, b) VALUES('x', 'y')" {x y {2001-09-09 01:46:40}}
1035 }
1036
1037 # EVIDENCE-OF: R-55061-47754 The COLLATE clause specifies the name of a
1038 # collating sequence to use as the default collation sequence for the
1039 # column.
1040 #
1041 # EVIDENCE-OF: R-40275-54363 If no COLLATE clause is specified, the
1042 # default collation sequence is BINARY.
1043 #
1044 do_execsql_test e_createtable-3-9.1 {
1045   CREATE TABLE t8(a COLLATE nocase, b COLLATE rtrim, c COLLATE binary, d);
1046   INSERT INTO t8 VALUES('abc',   'abc',   'abc',   'abc');
1047   INSERT INTO t8 VALUES('abc  ', 'abc  ', 'abc  ', 'abc  ');
1048   INSERT INTO t8 VALUES('ABC  ', 'ABC  ', 'ABC  ', 'ABC  ');
1049   INSERT INTO t8 VALUES('ABC',   'ABC',   'ABC',   'ABC');
1050 } {}
1051 do_createtable_tests 3.9 {
1052   2    "SELECT a FROM t8 ORDER BY a, rowid"    {abc ABC {abc  } {ABC  }}
1053   3    "SELECT b FROM t8 ORDER BY b, rowid"    {{ABC  } ABC abc {abc  }}
1054   4    "SELECT c FROM t8 ORDER BY c, rowid"    {ABC {ABC  } abc {abc  }}
1055   5    "SELECT d FROM t8 ORDER BY d, rowid"    {ABC {ABC  } abc {abc  }}
1056 }
1057
1058 # EVIDENCE-OF: R-25473-20557 The number of columns in a table is limited
1059 # by the SQLITE_MAX_COLUMN compile-time parameter.
1060 #
1061 proc columns {n} {
1062   set res [list]
1063   for {set i 0} {$i < $n} {incr i} { lappend res "c$i" }
1064   join $res ", "
1065 }
1066 do_execsql_test e_createtable-3.10.1 [subst {
1067   CREATE TABLE t9([columns $::SQLITE_MAX_COLUMN]);
1068 }] {}
1069 do_catchsql_test e_createtable-3.10.2 [subst {
1070   CREATE TABLE t10([columns [expr $::SQLITE_MAX_COLUMN+1]]);
1071 }] {1 {too many columns on t10}}
1072
1073 # EVIDENCE-OF: R-27775-64721 Both of these limits can be lowered at
1074 # runtime using the sqlite3_limit() C/C++ interface.
1075 #
1076 #   A 30,000 byte blob consumes 30,003 bytes of record space. A record
1077 #   that contains 3 such blobs consumes (30,000*3)+1 bytes of space. Tests
1078 #   3.11.4 and 3.11.5, which verify that SQLITE_MAX_LENGTH may be lowered
1079 #   at runtime, are based on this calculation.
1080 #
1081 sqlite3_limit db SQLITE_LIMIT_COLUMN 500
1082 do_execsql_test e_createtable-3.11.1 [subst {
1083   CREATE TABLE t10([columns 500]);
1084 }] {}
1085 do_catchsql_test e_createtable-3.11.2 [subst {
1086   CREATE TABLE t11([columns 501]);
1087 }] {1 {too many columns on t11}}
1088
1089 # Check that it is not possible to raise the column limit above its
1090 # default compile time value.
1091 #
1092 sqlite3_limit db SQLITE_LIMIT_COLUMN [expr $::SQLITE_MAX_COLUMN+2]
1093 do_catchsql_test e_createtable-3.11.3 [subst {
1094   CREATE TABLE t11([columns [expr $::SQLITE_MAX_COLUMN+1]]);
1095 }] {1 {too many columns on t11}}
1096
1097 sqlite3_limit db SQLITE_LIMIT_LENGTH 90010
1098 do_execsql_test e_createtable-3.11.4 {
1099   CREATE TABLE t12(a, b, c);
1100   INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000));
1101 } {}
1102 do_catchsql_test e_createtable-3.11.5 {
1103   INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000));
1104 } {1 {string or blob too big}}
1105
1106 #-------------------------------------------------------------------------
1107 # Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT
1108 # NULL and CHECK constraints).
1109 #
1110
1111 # EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one
1112 # PRIMARY KEY.
1113 #
1114 # EVIDENCE-OF: R-18080-47271 If there is more than one PRIMARY KEY
1115 # clause in a single CREATE TABLE statement, it is an error.
1116 #
1117 #     To test the two above, show that zero primary keys is Ok, one primary
1118 #     key is Ok, and two or more primary keys is an error.
1119 #
1120 drop_all_tables
1121 do_createtable_tests 4.1.1 {
1122   1    "CREATE TABLE t1(a, b, c)"                                        {}
1123   2    "CREATE TABLE t2(a PRIMARY KEY, b, c)"                            {}
1124   3    "CREATE TABLE t3(a, b, c, PRIMARY KEY(a))"                        {}
1125   4    "CREATE TABLE t4(a, b, c, PRIMARY KEY(c,b,a))"                    {}
1126 }
1127 do_createtable_tests 4.1.2 -error {
1128   table "t5" has more than one primary key
1129 } {
1130   1    "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)"                {}
1131   2    "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))"            {}
1132   3    "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)"        {}
1133   4    "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {}
1134   5    "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))"            {}
1135   6    "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))"    {}
1136 }
1137
1138 proc table_pk {tbl} {
1139   set pk [list]
1140   db eval "pragma table_info($tbl)" a {
1141     if {$a(pk)} { lappend pk $a(name) }
1142   }
1143   set pk
1144 }
1145
1146 # EVIDENCE-OF: R-41411-18837 If the keywords PRIMARY KEY are added to a
1147 # column definition, then the primary key for the table consists of that
1148 # single column.
1149 #
1150 #     The above is tested by 4.2.1.*
1151 #
1152 # EVIDENCE-OF: R-31775-48204 Or, if a PRIMARY KEY clause is specified as
1153 # a table-constraint, then the primary key of the table consists of the
1154 # list of columns specified as part of the PRIMARY KEY clause.
1155 #
1156 #     The above is tested by 4.2.2.*
1157 #
1158 do_createtable_tests 4.2 -repair {
1159   catchsql { DROP TABLE t5 }
1160 } -tclquery {
1161   table_pk t5
1162 } {
1163   1.1    "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)"       {b}
1164   1.2    "CREATE TABLE t5(a PRIMARY KEY, b, c)"               {a}
1165
1166   2.1    "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))"           {a}
1167   2.2    "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))"       {a b c}
1168   2.3    "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)"       {b}
1169 }
1170
1171 # EVIDENCE-OF: R-33986-09410 Each row in a table with a primary key must
1172 # feature a unique combination of values in its primary key columns.
1173 #
1174 # EVIDENCE-OF: R-39102-06737 If an INSERT or UPDATE statement attempts
1175 # to modify the table content so that two or more rows feature identical
1176 # primary key values, it is a constraint violation.
1177 #
1178 drop_all_tables
1179 do_execsql_test 4.3.0 {
1180   CREATE TABLE t1(x PRIMARY KEY, y);
1181   INSERT INTO t1 VALUES(0,          'zero');
1182   INSERT INTO t1 VALUES(45.5,       'one');
1183   INSERT INTO t1 VALUES('brambles', 'two');
1184   INSERT INTO t1 VALUES(X'ABCDEF',  'three');
1185
1186   CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
1187   INSERT INTO t2 VALUES(0,          'zero');
1188   INSERT INTO t2 VALUES(45.5,       'one');
1189   INSERT INTO t2 VALUES('brambles', 'two');
1190   INSERT INTO t2 VALUES(X'ABCDEF',  'three');
1191 } {}
1192
1193 do_createtable_tests 4.3.1 -error { %s not unique } {
1194   1    "INSERT INTO t1 VALUES(0, 0)"                 {"column x is"}
1195   2    "INSERT INTO t1 VALUES(45.5, 'abc')"          {"column x is"}
1196   3    "INSERT INTO t1 VALUES(0.0, 'abc')"           {"column x is"}
1197   4    "INSERT INTO t1 VALUES('brambles', 'abc')"    {"column x is"}
1198   5    "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')"     {"column x is"}
1199
1200   6    "INSERT INTO t2 VALUES(0, 'zero')"            {"columns x, y are"}
1201   7    "INSERT INTO t2 VALUES(45.5, 'one')"          {"columns x, y are"}
1202   8    "INSERT INTO t2 VALUES(0.0, 'zero')"          {"columns x, y are"}
1203   9    "INSERT INTO t2 VALUES('brambles', 'two')"    {"columns x, y are"}
1204   10   "INSERT INTO t2 VALUES(X'ABCDEF', 'three')"   {"columns x, y are"}
1205 }
1206 do_createtable_tests 4.3.2 {
1207   1    "INSERT INTO t1 VALUES(-1, 0)"                {}
1208   2    "INSERT INTO t1 VALUES(45.2, 'abc')"          {}
1209   3    "INSERT INTO t1 VALUES(0.01, 'abc')"          {}
1210   4    "INSERT INTO t1 VALUES('bramble', 'abc')"     {}
1211   5    "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')"     {}
1212
1213   6    "INSERT INTO t2 VALUES(0, 0)"                 {}
1214   7    "INSERT INTO t2 VALUES(45.5, 'abc')"          {}
1215   8    "INSERT INTO t2 VALUES(0.0, 'abc')"           {}
1216   9    "INSERT INTO t2 VALUES('brambles', 'abc')"    {}
1217   10   "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')"     {}
1218 }
1219 do_createtable_tests 4.3.3 -error { %s not unique } {
1220   1    "UPDATE t1 SET x=0           WHERE y='two'"    {"column x is"}
1221   2    "UPDATE t1 SET x='brambles'  WHERE y='three'"  {"column x is"}
1222   3    "UPDATE t1 SET x=45.5        WHERE y='zero'"   {"column x is"}
1223   4    "UPDATE t1 SET x=X'ABCDEF'   WHERE y='one'"    {"column x is"}
1224   5    "UPDATE t1 SET x=0.0         WHERE y='three'"  {"column x is"}
1225
1226   6    "UPDATE t2 SET x=0, y='zero' WHERE y='two'"    {"columns x, y are"}
1227   7    "UPDATE t2 SET x='brambles', y='two' WHERE y='three'"
1228        {"columns x, y are"}
1229   8    "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"}
1230   9    "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'"
1231        {"columns x, y are"}
1232   10   "UPDATE t2 SET x=0.0, y='zero'        WHERE y='three'"
1233        {"columns x, y are"}
1234 }
1235
1236
1237 # EVIDENCE-OF: R-52572-02078 For the purposes of determining the
1238 # uniqueness of primary key values, NULL values are considered distinct
1239 # from all other values, including other NULLs.
1240 #
1241 do_createtable_tests 4.4 {
1242   1    "INSERT INTO t1 VALUES(NULL, 0)"              {}
1243   2    "INSERT INTO t1 VALUES(NULL, 0)"              {}
1244   3    "INSERT INTO t1 VALUES(NULL, 0)"              {}
1245
1246   4    "INSERT INTO t2 VALUES(NULL, 'zero')"         {}
1247   5    "INSERT INTO t2 VALUES(NULL, 'one')"          {}
1248   6    "INSERT INTO t2 VALUES(NULL, 'two')"          {}
1249   7    "INSERT INTO t2 VALUES(NULL, 'three')"        {}
1250
1251   8    "INSERT INTO t2 VALUES(0, NULL)"              {}
1252   9    "INSERT INTO t2 VALUES(45.5, NULL)"           {}
1253   10   "INSERT INTO t2 VALUES(0.0, NULL)"            {}
1254   11   "INSERT INTO t2 VALUES('brambles', NULL)"     {}
1255   12   "INSERT INTO t2 VALUES(X'ABCDEF', NULL)"      {}
1256
1257   13   "INSERT INTO t2 VALUES(NULL, NULL)"           {}
1258   14   "INSERT INTO t2 VALUES(NULL, NULL)"           {}
1259 }
1260
1261 # EVIDENCE-OF: R-61866-38053 Unless the column is an INTEGER PRIMARY KEY
1262 # SQLite allows NULL values in a PRIMARY KEY column.
1263 #
1264 #     If the column is an integer primary key, attempting to insert a NULL
1265 #     into the column triggers the auto-increment behaviour. Attempting
1266 #     to use UPDATE to set an ipk column to a NULL value is an error.
1267 #
1268 do_createtable_tests 4.5.1 {
1269   1    "SELECT count(*) FROM t1 WHERE x IS NULL"                   3
1270   2    "SELECT count(*) FROM t2 WHERE x IS NULL"                   6
1271   3    "SELECT count(*) FROM t2 WHERE y IS NULL"                   7
1272   4    "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL"     2
1273 }
1274 do_execsql_test 4.5.2 {
1275   CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v);
1276   INSERT INTO t3 VALUES(1, NULL, 2);
1277   INSERT INTO t3 VALUES('x', NULL, 'y');
1278   SELECT u FROM t3;
1279 } {1 2}
1280 do_catchsql_test 4.5.3 {
1281   INSERT INTO t3 VALUES(2, 5, 3);
1282   UPDATE t3 SET u = NULL WHERE s = 2;
1283 } {1 {datatype mismatch}}
1284
1285 # EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY
1286 # KEY constraint, except that a single table may have any number of
1287 # UNIQUE constraints.
1288 #
1289 drop_all_tables
1290 do_createtable_tests 4.6 {
1291   1    "CREATE TABLE t1(a UNIQUE, b UNIQUE)"                       {}
1292   2    "CREATE TABLE t2(a UNIQUE, b, c, UNIQUE(c, b))"             {}
1293   3    "CREATE TABLE t3(a, b, c, UNIQUE(a), UNIQUE(b), UNIQUE(c))" {}
1294   4    "CREATE TABLE t4(a, b, c, UNIQUE(a, b, c))"                 {}
1295 }
1296
1297 # EVIDENCE-OF: R-55240-58877 For each UNIQUE constraint on the table,
1298 # each row must feature a unique combination of values in the columns
1299 # identified by the UNIQUE constraint.
1300 #
1301 # EVIDENCE-OF: R-47733-51480 If an INSERT or UPDATE statement attempts
1302 # to modify the table content so that two or more rows feature identical
1303 # values in a set of columns that are subject to a UNIQUE constraint, it
1304 # is a constraint violation.
1305 #
1306 do_execsql_test 4.7.0 {
1307   INSERT INTO t1 VALUES(1, 2);
1308   INSERT INTO t1 VALUES(4.3, 5.5);
1309   INSERT INTO t1 VALUES('reveal', 'variableness');
1310   INSERT INTO t1 VALUES(X'123456', X'654321');
1311
1312   INSERT INTO t4 VALUES('xyx', 1, 1);
1313   INSERT INTO t4 VALUES('xyx', 2, 1);
1314   INSERT INTO t4 VALUES('uvw', 1, 1);
1315 }
1316 do_createtable_tests 4.7.1 -error { %s not unique } {
1317   1    "INSERT INTO t1 VALUES(1, 'one')"             {{column a is}}
1318   2    "INSERT INTO t1 VALUES(4.3, 'two')"           {{column a is}}
1319   3    "INSERT INTO t1 VALUES('reveal', 'three')"    {{column a is}}
1320   4    "INSERT INTO t1 VALUES(X'123456', 'four')"    {{column a is}}
1321
1322   5    "UPDATE t1 SET a = 1 WHERE rowid=2"           {{column a is}}
1323   6    "UPDATE t1 SET a = 4.3 WHERE rowid=3"         {{column a is}}
1324   7    "UPDATE t1 SET a = 'reveal' WHERE rowid=4"    {{column a is}}
1325   8    "UPDATE t1 SET a = X'123456' WHERE rowid=1"   {{column a is}}
1326
1327   9    "INSERT INTO t4 VALUES('xyx', 1, 1)"          {{columns a, b, c are}}
1328   10   "INSERT INTO t4 VALUES('xyx', 2, 1)"          {{columns a, b, c are}}
1329   11   "INSERT INTO t4 VALUES('uvw', 1, 1)"          {{columns a, b, c are}}
1330
1331   12   "UPDATE t4 SET a='xyx' WHERE rowid=3"         {{columns a, b, c are}}
1332   13   "UPDATE t4 SET b=1 WHERE rowid=2"             {{columns a, b, c are}}
1333   14   "UPDATE t4 SET a=0, b=0, c=0"                 {{columns a, b, c are}}
1334 }
1335
1336 # EVIDENCE-OF: R-21289-11559 As with PRIMARY KEY constraints, for the
1337 # purposes of UNIQUE constraints NULL values are considered distinct
1338 # from all other values (including other NULLs).
1339 #
1340 do_createtable_tests 4.8 {
1341   1    "INSERT INTO t1 VALUES(NULL, NULL)"           {}
1342   2    "INSERT INTO t1 VALUES(NULL, NULL)"           {}
1343   3    "UPDATE t1 SET a = NULL"                      {}
1344   4    "UPDATE t1 SET b = NULL"                      {}
1345
1346   5    "INSERT INTO t4 VALUES(NULL, NULL, NULL)"     {}
1347   6    "INSERT INTO t4 VALUES(NULL, NULL, NULL)"     {}
1348   7    "UPDATE t4 SET a = NULL"                      {}
1349   8    "UPDATE t4 SET b = NULL"                      {}
1350   9    "UPDATE t4 SET c = NULL"                      {}
1351 }
1352
1353 # EVIDENCE-OF: R-26983-26377 INTEGER PRIMARY KEY columns aside, both
1354 # UNIQUE and PRIMARY KEY constraints are implemented by creating an
1355 # index in the database (in the same way as a "CREATE UNIQUE INDEX"
1356 # statement would).
1357 do_createtable_tests 4.9 -repair drop_all_tables -query {
1358   SELECT count(*) FROM sqlite_master WHERE type='index'
1359 } {
1360   1    "CREATE TABLE t1(a TEXT PRIMARY KEY, b)"              1
1361   2    "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)"           0
1362   3    "CREATE TABLE t1(a TEXT UNIQUE, b)"                   1
1363   4    "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)"       2
1364   5    "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))"  2
1365 }
1366
1367 # EVIDENCE-OF: R-02252-33116 Such an index is used like any other index
1368 # in the database to optimize queries.
1369 #
1370 do_execsql_test 4.10.0 {
1371   CREATE TABLE t1(a, b PRIMARY KEY);
1372   CREATE TABLE t2(a, b, c, UNIQUE(b, c));
1373 }
1374 do_createtable_tests 4.10 {
1375   1    "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5"
1376        {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}}
1377
1378   2    "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
1379        {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}}
1380
1381   3    "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
1382        {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~2 rows)}}
1383 }
1384
1385 # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a
1386 # column definition or specified as a table constraint. In practice it
1387 # makes no difference.
1388 #
1389 #   All the tests that deal with CHECK constraints below (4.11.* and
1390 #   4.12.*) are run once for a table with the check constraint attached
1391 #   to a column definition, and once with a table where the check
1392 #   condition is specified as a table constraint.
1393 #
1394 # EVIDENCE-OF: R-55435-14303 Each time a new row is inserted into the
1395 # table or an existing row is updated, the expression associated with
1396 # each CHECK constraint is evaluated and cast to a NUMERIC value in the
1397 # same way as a CAST expression. If the result is zero (integer value 0
1398 # or real value 0.0), then a constraint violation has occurred.
1399 #
1400 drop_all_tables
1401 do_execsql_test 4.11 {
1402   CREATE TABLE x1(a TEXT, b INTEGER CHECK( b>0 ));
1403   CREATE TABLE t1(a TEXT, b INTEGER, CHECK( b>0 ));
1404   INSERT INTO x1 VALUES('x', 'xx');
1405   INSERT INTO x1 VALUES('y', 'yy');
1406   INSERT INTO t1 SELECT * FROM x1;
1407
1408   CREATE TABLE x2(a CHECK( a||b ), b);
1409   CREATE TABLE t2(a, b, CHECK( a||b ));
1410   INSERT INTO x2 VALUES(1, 'xx');
1411   INSERT INTO x2 VALUES(1, 'yy');
1412   INSERT INTO t2 SELECT * FROM x2;
1413 }
1414
1415 do_createtable_tests 4.11 -error {constraint failed} {
1416   1a    "INSERT INTO x1 VALUES('one', 0)"       {}
1417   1b    "INSERT INTO t1 VALUES('one', -4.0)"    {}
1418
1419   2a    "INSERT INTO x2 VALUES('abc', 1)"       {}
1420   2b    "INSERT INTO t2 VALUES('abc', 1)"       {}
1421
1422   3a    "INSERT INTO x2 VALUES(0, 'abc')"       {}
1423   3b    "INSERT INTO t2 VALUES(0, 'abc')"       {}
1424
1425   4a    "UPDATE t1 SET b=-1 WHERE rowid=1"      {}
1426   4b    "UPDATE x1 SET b=-1 WHERE rowid=1"      {}
1427
1428   4a    "UPDATE x2 SET a='' WHERE rowid=1"      {}
1429   4b    "UPDATE t2 SET a='' WHERE rowid=1"      {}
1430 }
1431
1432 # EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL,
1433 # or any other non-zero value, it is not a constraint violation.
1434 #
1435 do_createtable_tests 4.12 {
1436   1a    "INSERT INTO x1 VALUES('one', NULL)"    {}
1437   1b    "INSERT INTO t1 VALUES('one', NULL)"    {}
1438
1439   2a    "INSERT INTO x1 VALUES('one', 2)"    {}
1440   2b    "INSERT INTO t1 VALUES('one', 2)"    {}
1441
1442   3a    "INSERT INTO x2 VALUES(1, 'abc')"       {}
1443   3b    "INSERT INTO t2 VALUES(1, 'abc')"       {}
1444 }
1445
1446 # EVIDENCE-OF: R-02060-64547 A NOT NULL constraint may only be attached
1447 # to a column definition, not specified as a table constraint.
1448 #
1449 drop_all_tables
1450 do_createtable_tests 4.13.1 {
1451   1     "CREATE TABLE t1(a NOT NULL, b)"                               {}
1452   2     "CREATE TABLE t2(a PRIMARY KEY NOT NULL, b)"                   {}
1453   3     "CREATE TABLE t3(a NOT NULL, b NOT NULL, c NOT NULL UNIQUE)"   {}
1454 }
1455 do_createtable_tests 4.13.2 -error {
1456   near "NOT": syntax error
1457 } {
1458   1     "CREATE TABLE t4(a, b, NOT NULL(a))"                   {}
1459   2     "CREATE TABLE t4(a PRIMARY KEY, b, NOT NULL(a))"       {}
1460   3     "CREATE TABLE t4(a, b, c UNIQUE, NOT NULL(a, b, c))"   {}
1461 }
1462
1463 # EVIDENCE-OF: R-31795-57643 a NOT NULL constraint dictates that the
1464 # associated column may not contain a NULL value. Attempting to set the
1465 # column value to NULL when inserting a new row or updating an existing
1466 # one causes a constraint violation.
1467 #
1468 #     These tests use the tables created by 4.13.
1469 #
1470 do_execsql_test 4.14.0 {
1471   INSERT INTO t1 VALUES('x', 'y');
1472   INSERT INTO t1 VALUES('z', NULL);
1473
1474   INSERT INTO t2 VALUES('x', 'y');
1475   INSERT INTO t2 VALUES('z', NULL);
1476
1477   INSERT INTO t3 VALUES('x', 'y', 'z');
1478   INSERT INTO t3 VALUES(1, 2, 3);
1479 }
1480 do_createtable_tests 4.14 -error {
1481   %s may not be NULL
1482 } {
1483   1    "INSERT INTO t1 VALUES(NULL, 'a')"         {t1.a}
1484   2    "INSERT INTO t2 VALUES(NULL, 'b')"         {t2.a}
1485   3    "INSERT INTO t3 VALUES('c', 'd', NULL)"    {t3.c}
1486   4    "INSERT INTO t3 VALUES('e', NULL, 'f')"    {t3.b}
1487   5    "INSERT INTO t3 VALUES(NULL, 'g', 'h')"    {t3.a}
1488 }
1489
1490 # EVIDENCE-OF: R-42511-39459 PRIMARY KEY, UNIQUE and NOT NULL
1491 # constraints may be explicitly assigned a default conflict resolution
1492 # algorithm by including a conflict-clause in their definitions.
1493 #
1494 #     Conflict clauses: ABORT, ROLLBACK, IGNORE, FAIL, REPLACE
1495 #
1496 #     Test cases 4.15.*, 4.16.* and 4.17.* focus on PRIMARY KEY, NOT NULL
1497 #     and UNIQUE constraints, respectively.
1498 #
1499 drop_all_tables
1500 do_execsql_test 4.15.0 {
1501   CREATE TABLE t1_ab(a PRIMARY KEY ON CONFLICT ABORT, b);
1502   CREATE TABLE t1_ro(a PRIMARY KEY ON CONFLICT ROLLBACK, b);
1503   CREATE TABLE t1_ig(a PRIMARY KEY ON CONFLICT IGNORE, b);
1504   CREATE TABLE t1_fa(a PRIMARY KEY ON CONFLICT FAIL, b);
1505   CREATE TABLE t1_re(a PRIMARY KEY ON CONFLICT REPLACE, b);
1506   CREATE TABLE t1_xx(a PRIMARY KEY, b);
1507
1508   INSERT INTO t1_ab VALUES(1, 'one');
1509   INSERT INTO t1_ab VALUES(2, 'two');
1510   INSERT INTO t1_ro SELECT * FROM t1_ab;
1511   INSERT INTO t1_ig SELECT * FROM t1_ab;
1512   INSERT INTO t1_fa SELECT * FROM t1_ab;
1513   INSERT INTO t1_re SELECT * FROM t1_ab;
1514   INSERT INTO t1_xx SELECT * FROM t1_ab;
1515
1516   CREATE TABLE t2_ab(a, b NOT NULL ON CONFLICT ABORT);
1517   CREATE TABLE t2_ro(a, b NOT NULL ON CONFLICT ROLLBACK);
1518   CREATE TABLE t2_ig(a, b NOT NULL ON CONFLICT IGNORE);
1519   CREATE TABLE t2_fa(a, b NOT NULL ON CONFLICT FAIL);
1520   CREATE TABLE t2_re(a, b NOT NULL ON CONFLICT REPLACE);
1521   CREATE TABLE t2_xx(a, b NOT NULL);
1522
1523   INSERT INTO t2_ab VALUES(1, 'one');
1524   INSERT INTO t2_ab VALUES(2, 'two');
1525   INSERT INTO t2_ro SELECT * FROM t2_ab;
1526   INSERT INTO t2_ig SELECT * FROM t2_ab;
1527   INSERT INTO t2_fa SELECT * FROM t2_ab;
1528   INSERT INTO t2_re SELECT * FROM t2_ab;
1529   INSERT INTO t2_xx SELECT * FROM t2_ab;
1530
1531   CREATE TABLE t3_ab(a, b, UNIQUE(a, b) ON CONFLICT ABORT);
1532   CREATE TABLE t3_ro(a, b, UNIQUE(a, b) ON CONFLICT ROLLBACK);
1533   CREATE TABLE t3_ig(a, b, UNIQUE(a, b) ON CONFLICT IGNORE);
1534   CREATE TABLE t3_fa(a, b, UNIQUE(a, b) ON CONFLICT FAIL);
1535   CREATE TABLE t3_re(a, b, UNIQUE(a, b) ON CONFLICT REPLACE);
1536   CREATE TABLE t3_xx(a, b, UNIQUE(a, b));
1537
1538   INSERT INTO t3_ab VALUES(1, 'one');
1539   INSERT INTO t3_ab VALUES(2, 'two');
1540   INSERT INTO t3_ro SELECT * FROM t3_ab;
1541   INSERT INTO t3_ig SELECT * FROM t3_ab;
1542   INSERT INTO t3_fa SELECT * FROM t3_ab;
1543   INSERT INTO t3_re SELECT * FROM t3_ab;
1544   INSERT INTO t3_xx SELECT * FROM t3_ab;
1545 }
1546
1547 foreach {tn tbl res ac data} {
1548   1   t1_ab    {1 {column a is not unique}} 0 {1 one 2 two 3 three}
1549   2   t1_ro    {1 {column a is not unique}} 1 {1 one 2 two}
1550   3   t1_fa    {1 {column a is not unique}} 0 {1 one 2 two 3 three 4 string}
1551   4   t1_ig    {0 {}} 0 {1 one 2 two 3 three 4 string 6 string}
1552   5   t1_re    {0 {}} 0 {1 one 2 two 4 string 3 string 6 string}
1553   6   t1_xx    {1 {column a is not unique}} 0 {1 one 2 two 3 three}
1554 } {
1555   catchsql COMMIT
1556   do_execsql_test  4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
1557
1558   do_catchsql_test 4.15.$tn.2 "
1559     INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl;
1560   " $res
1561
1562   do_test e_createtable-4.15.$tn.3 { sqlite3_get_autocommit db } $ac
1563   do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data
1564 }
1565 foreach {tn tbl res ac data} {
1566   1   t2_ab    {1 {t2_ab.b may not be NULL}} 0 {1 one 2 two 3 three}
1567   2   t2_ro    {1 {t2_ro.b may not be NULL}} 1 {1 one 2 two}
1568   3   t2_fa    {1 {t2_fa.b may not be NULL}} 0 {1 one 2 two 3 three 4 xx}
1569   4   t2_ig    {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx}
1570   5   t2_re    {1 {t2_re.b may not be NULL}} 0 {1 one 2 two 3 three}
1571   6   t2_xx    {1 {t2_xx.b may not be NULL}} 0 {1 one 2 two 3 three}
1572 } {
1573   catchsql COMMIT
1574   do_execsql_test  4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
1575
1576   do_catchsql_test 4.16.$tn.2 "
1577     INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl
1578   " $res
1579
1580   do_test e_createtable-4.16.$tn.3 { sqlite3_get_autocommit db } $ac
1581   do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data
1582 }
1583 foreach {tn tbl res ac data} {
1584   1   t3_ab    {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three}
1585   2   t3_ro    {1 {columns a, b are not unique}} 1 {1 one 2 two}
1586   3   t3_fa    {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three 4 three}
1587   4   t3_ig    {0 {}} 0 {1 one 2 two 3 three 4 three 6 three}
1588   5   t3_re    {0 {}} 0 {1 one 2 two 4 three 3 three 6 three}
1589   6   t3_xx    {1 {columns a, b are not unique}} 0 {1 one 2 two 3 three}
1590 } {
1591   catchsql COMMIT
1592   do_execsql_test  4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
1593
1594   do_catchsql_test 4.17.$tn.2 "
1595     INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl
1596   " $res
1597
1598   do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac
1599   do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl" $data
1600 }
1601 catchsql COMMIT
1602
1603 # EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not
1604 # include a conflict-clause or it is a CHECK constraint, the default
1605 # conflict resolution algorithm is ABORT.
1606 #
1607 #     The first half of the above is tested along with explicit ON
1608 #     CONFLICT clauses above (specifically, the tests involving t1_xx, t2_xx
1609 #     and t3_xx). The following just tests that the default conflict
1610 #     handling for CHECK constraints is ABORT.
1611 #
1612 do_execsql_test 4.18.1 {
1613   CREATE TABLE t4(a, b CHECK (b!=10));
1614   INSERT INTO t4 VALUES(1, 2);
1615   INSERT INTO t4 VALUES(3, 4);
1616 }
1617 do_execsql_test  4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) }
1618 do_catchsql_test 4.18.3 {
1619   INSERT INTO t4 SELECT a+4, b+4 FROM t4
1620 } {1 {constraint failed}}
1621 do_test e_createtable-4.18.4 { sqlite3_get_autocommit db } 0
1622 do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6}
1623
1624 # EVIDENCE-OF: R-19114-56113 Different constraints within the same table
1625 # may have different default conflict resolution algorithms.
1626 #
1627 do_execsql_test 4.19.0 {
1628   CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT);
1629 }
1630 do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}}
1631 do_execsql_test  4.19.2 { SELECT * FROM t5 } {}
1632 do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \
1633   {1 {t5.b may not be NULL}}
1634 do_execsql_test  4.19.4 { SELECT * FROM t5 } {}
1635
1636 #------------------------------------------------------------------------
1637 # Tests for INTEGER PRIMARY KEY and rowid related statements.
1638 #
1639
1640 # EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one
1641 # of the special case-independent names "rowid", "oid", or "_rowid_" in
1642 # place of a column name.
1643 #
1644 drop_all_tables
1645 do_execsql_test 5.1.0 {
1646   CREATE TABLE t1(x, y);
1647   INSERT INTO t1 VALUES('one', 'first');
1648   INSERT INTO t1 VALUES('two', 'second');
1649   INSERT INTO t1 VALUES('three', 'third');
1650 }
1651 do_createtable_tests 5.1 {
1652   1   "SELECT rowid FROM t1"        {1 2 3}
1653   2   "SELECT oid FROM t1"          {1 2 3}
1654   3   "SELECT _rowid_ FROM t1"      {1 2 3}
1655   4   "SELECT ROWID FROM t1"        {1 2 3}
1656   5   "SELECT OID FROM t1"          {1 2 3}
1657   6   "SELECT _ROWID_ FROM t1"      {1 2 3}
1658   7   "SELECT RoWiD FROM t1"        {1 2 3}
1659   8   "SELECT OiD FROM t1"          {1 2 3}
1660   9   "SELECT _RoWiD_ FROM t1"      {1 2 3}
1661 }
1662
1663 # EVIDENCE-OF: R-26501-17306 If a table contains a user defined column
1664 # named "rowid", "oid" or "_rowid_", then that name always refers the
1665 # explicitly declared column and cannot be used to retrieve the integer
1666 # rowid value.
1667 #
1668 do_execsql_test 5.2.0 {
1669   CREATE TABLE t2(oid, b);
1670   CREATE TABLE t3(a, _rowid_);
1671   CREATE TABLE t4(a, b, rowid);
1672
1673   INSERT INTO t2 VALUES('one', 'two');
1674   INSERT INTO t2 VALUES('three', 'four');
1675
1676   INSERT INTO t3 VALUES('five', 'six');
1677   INSERT INTO t3 VALUES('seven', 'eight');
1678
1679   INSERT INTO t4 VALUES('nine', 'ten', 'eleven');
1680   INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen');
1681 }
1682 do_createtable_tests 5.2 {
1683   1   "SELECT oid, rowid, _rowid_ FROM t2"   {one 1 1      three 2 2}
1684   2   "SELECT oid, rowid, _rowid_ FROM t3"   {1 1 six      2 2 eight}
1685   3   "SELECT oid, rowid, _rowid_ FROM t4"   {1 eleven 1   2 fourteen 2}
1686 }
1687
1688
1689 # Argument $tbl is the name of a table in the database. Argument $col is
1690 # the name of one of the tables columns. Return 1 if $col is an alias for
1691 # the rowid, or 0 otherwise.
1692 #
1693 proc is_integer_primary_key {tbl col} {
1694   lindex [db eval [subst {
1695     DELETE FROM $tbl;
1696     INSERT INTO $tbl ($col) VALUES(0);
1697     SELECT (rowid==$col) FROM $tbl;
1698     DELETE FROM $tbl;
1699   }]] 0
1700 }
1701
1702 # EVIDENCE-OF: R-53738-31673 With one exception, if a table has a
1703 # primary key that consists of a single column, and the declared type of
1704 # that column is "INTEGER" in any mixture of upper and lower case, then
1705 # the column becomes an alias for the rowid.
1706 #
1707 # EVIDENCE-OF: R-45951-08347 if the declaration of a column with
1708 # declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does
1709 # not become an alias for the rowid and is not classified as an integer
1710 # primary key.
1711 #
1712 do_createtable_tests 5.3 -tclquery {
1713   is_integer_primary_key t5 pk
1714 } -repair {
1715   catchsql { DROP TABLE t5 }
1716 } {
1717   1   "CREATE TABLE t5(pk integer primary key)"                         1
1718   2   "CREATE TABLE t5(pk integer, primary key(pk))"                    1
1719   3   "CREATE TABLE t5(pk integer, v integer, primary key(pk))"         1
1720   4   "CREATE TABLE t5(pk integer, v integer, primary key(pk, v))"      0
1721   5   "CREATE TABLE t5(pk int, v integer, primary key(pk, v))"          0
1722   6   "CREATE TABLE t5(pk int, v integer, primary key(pk))"             0
1723   7   "CREATE TABLE t5(pk int primary key, v integer)"                  0
1724   8   "CREATE TABLE t5(pk inTEger primary key)"                         1
1725   9   "CREATE TABLE t5(pk inteGEr, primary key(pk))"                    1
1726   10  "CREATE TABLE t5(pk INTEGER, v integer, primary key(pk))"         1
1727 }
1728
1729 # EVIDENCE-OF: R-41444-49665 Other integer type names like "INT" or
1730 # "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary
1731 # key column to behave as an ordinary table column with integer affinity
1732 # and a unique index, not as an alias for the rowid.
1733 #
1734 do_execsql_test 5.4.1 {
1735   CREATE TABLE t6(pk INT primary key);
1736   CREATE TABLE t7(pk BIGINT primary key);
1737   CREATE TABLE t8(pk SHORT INTEGER primary key);
1738   CREATE TABLE t9(pk UNSIGNED INTEGER primary key);
1739 }
1740 do_test e_createtable-5.4.2.1 { is_integer_primary_key t6 pk } 0
1741 do_test e_createtable-5.4.2.2 { is_integer_primary_key t7 pk } 0
1742 do_test e_createtable-5.4.2.3 { is_integer_primary_key t8 pk } 0
1743 do_test e_createtable-5.4.2.4 { is_integer_primary_key t9 pk } 0
1744
1745 do_execsql_test 5.4.3 {
1746   INSERT INTO t6 VALUES('2.0');
1747   INSERT INTO t7 VALUES('2.0');
1748   INSERT INTO t8 VALUES('2.0');
1749   INSERT INTO t9 VALUES('2.0');
1750   SELECT typeof(pk), pk FROM t6;
1751   SELECT typeof(pk), pk FROM t7;
1752   SELECT typeof(pk), pk FROM t8;
1753   SELECT typeof(pk), pk FROM t9;
1754 } {integer 2 integer 2 integer 2 integer 2}
1755
1756 do_catchsql_test 5.4.4.1 {
1757   INSERT INTO t6 VALUES(2)
1758 } {1 {column pk is not unique}}
1759 do_catchsql_test 5.4.4.2 {
1760   INSERT INTO t7 VALUES(2)
1761 } {1 {column pk is not unique}}
1762 do_catchsql_test 5.4.4.3 {
1763   INSERT INTO t8 VALUES(2)
1764 } {1 {column pk is not unique}}
1765 do_catchsql_test 5.4.4.4 {
1766   INSERT INTO t9 VALUES(2)
1767 } {1 {column pk is not unique}}
1768
1769 # EVIDENCE-OF: R-56094-57830 the following three table declarations all
1770 # cause the column "x" to be an alias for the rowid (an integer primary
1771 # key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE
1772 # t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y,
1773 # z, PRIMARY KEY(x DESC));
1774 #
1775 # EVIDENCE-OF: R-20149-25884 the following declaration does not result
1776 # in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY
1777 # KEY DESC, y, z);
1778 #
1779 do_createtable_tests 5 -tclquery {
1780   is_integer_primary_key t x
1781 } -repair {
1782   catchsql { DROP TABLE t }
1783 } {
1784   5.1    "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)"      1
1785   5.2    "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC))"  1
1786   5.3    "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC))" 1
1787   6.1    "CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z)"     0
1788 }
1789
1790 # EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an
1791 # UPDATE statement in the same way as any other column value can, either
1792 # using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by
1793 # using an alias created by an integer primary key.
1794 #
1795 do_execsql_test 5.7.0 {
1796   CREATE TABLE t10(a, b);
1797   INSERT INTO t10 VALUES('ten', 10);
1798
1799   CREATE TABLE t11(a, b INTEGER PRIMARY KEY);
1800   INSERT INTO t11 VALUES('ten', 10);
1801 }
1802 do_createtable_tests 5.7.1 -query {
1803   SELECT rowid, _rowid_, oid FROM t10;
1804 } {
1805   1    "UPDATE t10 SET rowid = 5"   {5 5 5}
1806   2    "UPDATE t10 SET _rowid_ = 6" {6 6 6}
1807   3    "UPDATE t10 SET oid = 7"     {7 7 7}
1808 }
1809 do_createtable_tests 5.7.2 -query {
1810   SELECT rowid, _rowid_, oid, b FROM t11;
1811 } {
1812   1    "UPDATE t11 SET rowid = 5"   {5 5 5 5}
1813   2    "UPDATE t11 SET _rowid_ = 6" {6 6 6 6}
1814   3    "UPDATE t11 SET oid = 7"     {7 7 7 7}
1815   4    "UPDATE t11 SET b = 8"       {8 8 8 8}
1816 }
1817
1818 # EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide
1819 # a value to use as the rowid for each row inserted.
1820 #
1821 do_createtable_tests 5.8.1 -query {
1822   SELECT rowid, _rowid_, oid FROM t10;
1823 } -repair {
1824   execsql { DELETE FROM t10 }
1825 } {
1826   1    "INSERT INTO t10(oid) VALUES(15)"           {15 15 15}
1827   2    "INSERT INTO t10(rowid) VALUES(16)"         {16 16 16}
1828   3    "INSERT INTO t10(_rowid_) VALUES(17)"       {17 17 17}
1829   4    "INSERT INTO t10(a, b, oid) VALUES(1,2,3)"  {3 3 3}
1830 }
1831 do_createtable_tests 5.8.2 -query {
1832   SELECT rowid, _rowid_, oid, b FROM t11;
1833 } -repair {
1834   execsql { DELETE FROM t11 }
1835 } {
1836   1    "INSERT INTO t11(oid) VALUES(15)"           {15 15 15 15}
1837   2    "INSERT INTO t11(rowid) VALUES(16)"         {16 16 16 16}
1838   3    "INSERT INTO t11(_rowid_) VALUES(17)"       {17 17 17 17}
1839   4    "INSERT INTO t11(a, b) VALUES(1,2)"         {2 2 2 2}
1840 }
1841
1842 # EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer
1843 # primary key or rowid column must contain integer values. Integer
1844 # primary key or rowid columns are not able to hold floating point
1845 # values, strings, BLOBs, or NULLs.
1846 #
1847 #     This is considered by the tests for the following 3 statements,
1848 #     which show that:
1849 #
1850 #       1. Attempts to UPDATE a rowid column to a non-integer value fail,
1851 #       2. Attempts to INSERT a real, string or blob value into a rowid
1852 #          column fail, and
1853 #       3. Attempting to INSERT a NULL value into a rowid column causes the
1854 #          system to automatically select an integer value to use.
1855 #
1856
1857
1858 # EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an
1859 # integer primary key or rowid column to a NULL or blob value, or to a
1860 # string or real value that cannot be losslessly converted to an
1861 # integer, a "datatype mismatch" error occurs and the statement is
1862 # aborted.
1863 #
1864 drop_all_tables
1865 do_execsql_test 5.9.0 {
1866   CREATE TABLE t12(x INTEGER PRIMARY KEY, y);
1867   INSERT INTO t12 VALUES(5, 'five');
1868 }
1869 do_createtable_tests 5.9.1 -query { SELECT typeof(x), x FROM t12 } {
1870   1   "UPDATE t12 SET x = 4"       {integer 4}
1871   2   "UPDATE t12 SET x = 10.0"    {integer 10}
1872   3   "UPDATE t12 SET x = '12.0'"  {integer 12}
1873   4   "UPDATE t12 SET x = '-15.0'" {integer -15}
1874 }
1875 do_createtable_tests 5.9.2 -error {
1876   datatype mismatch
1877 } {
1878   1   "UPDATE t12 SET x = 4.1"         {}
1879   2   "UPDATE t12 SET x = 'hello'"     {}
1880   3   "UPDATE t12 SET x = NULL"        {}
1881   4   "UPDATE t12 SET x = X'ABCD'"     {}
1882   5   "UPDATE t12 SET x = X'3900'"     {}
1883   6   "UPDATE t12 SET x = X'39'"       {}
1884 }
1885
1886 # EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a
1887 # blob value, or a string or real value that cannot be losslessly
1888 # converted to an integer into an integer primary key or rowid column, a
1889 # "datatype mismatch" error occurs and the statement is aborted.
1890 #
1891 do_execsql_test 5.10.0 { DELETE FROM t12 }
1892 do_createtable_tests 5.10.1 -error {
1893   datatype mismatch
1894 } {
1895   1   "INSERT INTO t12(x) VALUES(4.1)"     {}
1896   2   "INSERT INTO t12(x) VALUES('hello')" {}
1897   3   "INSERT INTO t12(x) VALUES(X'ABCD')" {}
1898   4   "INSERT INTO t12(x) VALUES(X'3900')" {}
1899   5   "INSERT INTO t12(x) VALUES(X'39')"   {}
1900 }
1901 do_createtable_tests 5.10.2 -query {
1902   SELECT typeof(x), x FROM t12
1903 } -repair {
1904   execsql { DELETE FROM t12 }
1905 } {
1906   1   "INSERT INTO t12(x) VALUES(4)"       {integer 4}
1907   2   "INSERT INTO t12(x) VALUES(10.0)"    {integer 10}
1908   3   "INSERT INTO t12(x) VALUES('12.0')"  {integer 12}
1909   4   "INSERT INTO t12(x) VALUES('4e3')"   {integer 4000}
1910   5   "INSERT INTO t12(x) VALUES('-14.0')" {integer -14}
1911 }
1912
1913 # EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a
1914 # NULL value into a rowid or integer primary key column, the system
1915 # chooses an integer value to use as the rowid automatically.
1916 #
1917 do_execsql_test 5.11.0 { DELETE FROM t12 }
1918 do_createtable_tests 5.11 -query {
1919   SELECT typeof(x), x FROM t12 WHERE y IS (SELECT max(y) FROM t12)
1920 } {
1921   1   "INSERT INTO t12 DEFAULT VALUES"                {integer 1}
1922   2   "INSERT INTO t12(y)   VALUES(5)"                {integer 2}
1923   3   "INSERT INTO t12(x,y) VALUES(NULL, 10)"         {integer 3}
1924   4   "INSERT INTO t12(x,y) SELECT NULL, 15 FROM t12"
1925       {integer 4 integer 5 integer 6}
1926   5   "INSERT INTO t12(y) SELECT 20 FROM t12 LIMIT 3"
1927       {integer 7 integer 8 integer 9}
1928 }
1929
1930 finish_test