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