000001  # 2010 September 25
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  #
000012  # This file implements tests to verify that the "testable statements" in 
000013  # the lang_createtable.html document are correct.
000014  #
000015  
000016  set testdir [file dirname $argv0]
000017  source $testdir/tester.tcl
000018  
000019  set ::testprefix e_createtable
000020  
000021  # Test organization:
000022  #
000023  #   e_createtable-0.*: Test that the syntax diagrams are correct.
000024  #
000025  #   e_createtable-1.*: Test statements related to table and database names, 
000026  #       the TEMP and TEMPORARY keywords, and the IF NOT EXISTS clause.
000027  #
000028  #   e_createtable-2.*: Test "CREATE TABLE AS" statements.
000029  #
000030  
000031  proc do_createtable_tests {nm args} {
000032    uplevel do_select_tests [list e_createtable-$nm] $args
000033  }
000034  
000035  
000036  #-------------------------------------------------------------------------
000037  # This command returns a serialized tcl array mapping from the name of
000038  # each attached database to a list of tables in that database. For example,
000039  # if the database schema is created with:
000040  #
000041  #   CREATE TABLE t1(x);
000042  #   CREATE TEMP TABLE t2(x);
000043  #   CREATE TEMP TABLE t3(x);
000044  #
000045  # Then this command returns "main t1 temp {t2 t3}".
000046  #
000047  proc table_list {} {
000048    set res [list]
000049    db eval { pragma database_list } a {
000050      set dbname $a(name)
000051      set master $a(name).sqlite_master
000052      if {$dbname == "temp"} { set master sqlite_temp_master }
000053      lappend res $dbname [
000054        db eval "SELECT DISTINCT tbl_name FROM $master ORDER BY tbl_name"
000055      ]
000056    }
000057    set res
000058  }
000059  
000060  
000061  do_createtable_tests 0.1.1 -repair {
000062    drop_all_tables
000063  } {
000064    1   "CREATE TABLE t1(c1 one)"                        {}
000065    2   "CREATE TABLE t1(c1 one two)"                    {}
000066    3   "CREATE TABLE t1(c1 one two three)"              {}
000067    4   "CREATE TABLE t1(c1 one two three four)"         {}
000068    5   "CREATE TABLE t1(c1 one two three four(14))"     {}
000069    6   "CREATE TABLE t1(c1 one two three four(14, 22))" {}
000070    7   "CREATE TABLE t1(c1 var(+14, -22.3))"            {}
000071    8   "CREATE TABLE t1(c1 var(1.0e10))"                {}
000072  }
000073  do_createtable_tests 0.1.2 -error {
000074    near "%s": syntax error
000075  } {
000076    1   "CREATE TABLE t1(c1 one(number))"                {number}
000077  }
000078  
000079  
000080  # syntax diagram column-constraint
000081  #
000082  do_createtable_tests 0.2.1 -repair {
000083    drop_all_tables 
000084    execsql { CREATE TABLE t2(x PRIMARY KEY) }
000085  } {
000086    1.1   "CREATE TABLE t1(c1 text PRIMARY KEY)"                         {}
000087    1.2   "CREATE TABLE t1(c1 text PRIMARY KEY ASC)"                     {}
000088    1.3   "CREATE TABLE t1(c1 text PRIMARY KEY DESC)"                    {}
000089    1.4   "CREATE TABLE t1(c1 text CONSTRAINT cons PRIMARY KEY DESC)"    {}
000090  
000091    2.1   "CREATE TABLE t1(c1 text NOT NULL)"                            {}
000092    2.2   "CREATE TABLE t1(c1 text CONSTRAINT nm NOT NULL)"              {}
000093    2.3   "CREATE TABLE t1(c1 text NULL)"                                {}
000094    2.4   "CREATE TABLE t1(c1 text CONSTRAINT nm NULL)"                  {}
000095  
000096    3.1   "CREATE TABLE t1(c1 text UNIQUE)"                              {}
000097    3.2   "CREATE TABLE t1(c1 text CONSTRAINT un UNIQUE)"                {}
000098  
000099    4.1   "CREATE TABLE t1(c1 text CHECK(c1!=0))"                        {}
000100    4.2   "CREATE TABLE t1(c1 text CONSTRAINT chk CHECK(c1!=0))"         {}
000101  
000102    5.1   "CREATE TABLE t1(c1 text DEFAULT 1)"                           {}
000103    5.2   "CREATE TABLE t1(c1 text DEFAULT -1)"                          {}
000104    5.3   "CREATE TABLE t1(c1 text DEFAULT +1)"                          {}
000105    5.4   "CREATE TABLE t1(c1 text DEFAULT -45.8e22)"                    {}
000106    5.5   "CREATE TABLE t1(c1 text DEFAULT (1+1))"                       {}
000107    5.6   "CREATE TABLE t1(c1 text CONSTRAINT \"1 2\" DEFAULT (1+1))"    {}
000108  
000109    6.1   "CREATE TABLE t1(c1 text COLLATE nocase)"        {}
000110    6.2   "CREATE TABLE t1(c1 text CONSTRAINT 'a x' COLLATE nocase)"     {}
000111  
000112    7.1   "CREATE TABLE t1(c1 REFERENCES t2)"                            {}
000113    7.2   "CREATE TABLE t1(c1 CONSTRAINT abc REFERENCES t2)"             {}
000114  
000115    8.1   {
000116      CREATE TABLE t1(c1 
000117        PRIMARY KEY NOT NULL UNIQUE CHECK(c1 IS 'ten') DEFAULT 123 REFERENCES t1
000118      );
000119    } {}
000120    8.2   {
000121      CREATE TABLE t1(c1 
000122        REFERENCES t1 DEFAULT 123 CHECK(c1 IS 'ten') UNIQUE NOT NULL PRIMARY KEY 
000123      );
000124    } {}
000125  }
000126  
000127  # -- syntax diagram table-constraint
000128  #
000129  do_createtable_tests 0.3.1 -repair {
000130    drop_all_tables 
000131    execsql { CREATE TABLE t2(x PRIMARY KEY) }
000132  } {
000133    1.1   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1))"                         {}
000134    1.2   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2))"                     {}
000135    1.3   "CREATE TABLE t1(c1, c2, PRIMARY KEY(c1, c2) ON CONFLICT IGNORE)"  {}
000136  
000137    2.1   "CREATE TABLE t1(c1, c2, UNIQUE(c1))"                              {}
000138    2.2   "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2))"                          {}
000139    2.3   "CREATE TABLE t1(c1, c2, UNIQUE(c1, c2) ON CONFLICT IGNORE)"       {}
000140  
000141    3.1   "CREATE TABLE t1(c1, c2, CHECK(c1 IS NOT c2))"                     {}
000142  
000143    4.1   "CREATE TABLE t1(c1, c2, FOREIGN KEY(c1) REFERENCES t2)"           {}
000144  }
000145  
000146  # -- syntax diagram column-def
000147  #
000148  do_createtable_tests 0.4.1 -repair {
000149    drop_all_tables 
000150  } {
000151    1     {CREATE TABLE t1(
000152             col1,
000153             col2 TEXT,
000154             col3 INTEGER UNIQUE,
000155             col4 VARCHAR(10, 10) PRIMARY KEY,
000156             "name with spaces" REFERENCES t1
000157           );
000158          } {}
000159  }
000160  
000161  # -- syntax diagram create-table-stmt
000162  #
000163  do_createtable_tests 0.5.1 -repair {
000164    drop_all_tables 
000165    execsql { CREATE TABLE t2(a, b, c) }
000166  } {
000167    1     "CREATE TABLE t1(a, b, c)"                                    {}
000168    2     "CREATE TEMP TABLE t1(a, b, c)"                               {}
000169    3     "CREATE TEMPORARY TABLE t1(a, b, c)"                          {}
000170    4     "CREATE TABLE IF NOT EXISTS t1(a, b, c)"                      {}
000171    5     "CREATE TEMP TABLE IF NOT EXISTS t1(a, b, c)"                 {}
000172    6     "CREATE TEMPORARY TABLE IF NOT EXISTS t1(a, b, c)"            {}
000173  
000174    7     "CREATE TABLE main.t1(a, b, c)"                               {}
000175    8     "CREATE TEMP TABLE temp.t1(a, b, c)"                          {}
000176    9     "CREATE TEMPORARY TABLE temp.t1(a, b, c)"                     {}
000177    10    "CREATE TABLE IF NOT EXISTS main.t1(a, b, c)"                 {}
000178    11    "CREATE TEMP TABLE IF NOT EXISTS temp.t1(a, b, c)"            {}
000179    12    "CREATE TEMPORARY TABLE IF NOT EXISTS temp.t1(a, b, c)"       {}
000180  
000181    13    "CREATE TABLE t1 AS SELECT * FROM t2"                         {}
000182    14    "CREATE TEMP TABLE t1 AS SELECT c, b, a FROM t2"              {}
000183    15    "CREATE TABLE t1 AS SELECT count(*), max(b), min(a) FROM t2"  {}
000184  }
000185  
000186  #
000187  #   1:         Explicit parent-key columns.
000188  #   2:         Implicit child-key columns.
000189  #
000190  #   1:         MATCH FULL
000191  #   2:         MATCH PARTIAL
000192  #   3:         MATCH SIMPLE
000193  #   4:         MATCH STICK
000194  #   5:         
000195  #
000196  #   1:         ON DELETE SET NULL
000197  #   2:         ON DELETE SET DEFAULT
000198  #   3:         ON DELETE CASCADE
000199  #   4:         ON DELETE RESTRICT
000200  #   5:         ON DELETE NO ACTION
000201  #   6:
000202  #
000203  #   1:         ON UPDATE SET NULL
000204  #   2:         ON UPDATE SET DEFAULT
000205  #   3:         ON UPDATE CASCADE
000206  #   4:         ON UPDATE RESTRICT
000207  #   5:         ON UPDATE NO ACTION
000208  #   6:
000209  #
000210  #   1:         NOT DEFERRABLE INITIALLY DEFERRED
000211  #   2:         NOT DEFERRABLE INITIALLY IMMEDIATE
000212  #   3:         NOT DEFERRABLE
000213  #   4:         DEFERRABLE INITIALLY DEFERRED
000214  #   5:         DEFERRABLE INITIALLY IMMEDIATE
000215  #   6:         DEFERRABLE
000216  #   7:         
000217  #
000218  do_createtable_tests 0.6.1 -repair {
000219    drop_all_tables 
000220    execsql { CREATE TABLE t2(x PRIMARY KEY, y) }
000221    execsql { CREATE TABLE t3(i, j, UNIQUE(i, j) ) }
000222  } {
000223    11146 { CREATE TABLE t1(a 
000224      REFERENCES t2(x) MATCH FULL 
000225      ON DELETE SET NULL ON UPDATE RESTRICT DEFERRABLE
000226    )} {}
000227    11412 { CREATE TABLE t1(a 
000228      REFERENCES t2(x) 
000229      ON DELETE RESTRICT ON UPDATE SET NULL MATCH FULL 
000230      NOT DEFERRABLE INITIALLY IMMEDIATE
000231    )} {}
000232    12135 { CREATE TABLE t1(a 
000233      REFERENCES t2(x) MATCH PARTIAL 
000234      ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY IMMEDIATE
000235    )} {}
000236    12427 { CREATE TABLE t1(a 
000237      REFERENCES t2(x) MATCH PARTIAL 
000238      ON DELETE RESTRICT ON UPDATE SET DEFAULT 
000239    )} {}
000240    12446 { CREATE TABLE t1(a 
000241      REFERENCES t2(x) MATCH PARTIAL 
000242      ON DELETE RESTRICT ON UPDATE RESTRICT DEFERRABLE
000243    )} {}
000244    12522 { CREATE TABLE t1(a 
000245      REFERENCES t2(x) MATCH PARTIAL 
000246      ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
000247    )} {}
000248    13133 { CREATE TABLE t1(a 
000249      REFERENCES t2(x) MATCH SIMPLE 
000250      ON DELETE SET NULL ON UPDATE CASCADE NOT DEFERRABLE
000251    )} {}
000252    13216 { CREATE TABLE t1(a 
000253      REFERENCES t2(x) MATCH SIMPLE 
000254      ON DELETE SET DEFAULT ON UPDATE SET NULL DEFERRABLE
000255    )} {}
000256    13263 { CREATE TABLE t1(a 
000257      REFERENCES t2(x) MATCH SIMPLE 
000258      ON DELETE SET DEFAULT  NOT DEFERRABLE
000259    )} {}
000260    13421 { CREATE TABLE t1(a 
000261      REFERENCES t2(x) MATCH SIMPLE 
000262      ON DELETE RESTRICT ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY DEFERRED
000263    )} {}
000264    13432 { CREATE TABLE t1(a 
000265      REFERENCES t2(x) MATCH SIMPLE 
000266      ON DELETE RESTRICT ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
000267    )} {}
000268    13523 { CREATE TABLE t1(a 
000269      REFERENCES t2(x) MATCH SIMPLE 
000270      ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE
000271    )} {}
000272    14336 { CREATE TABLE t1(a 
000273      REFERENCES t2(x) MATCH STICK 
000274      ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE
000275    )} {}
000276    14611 { CREATE TABLE t1(a 
000277      REFERENCES t2(x) MATCH STICK 
000278      ON UPDATE SET NULL NOT DEFERRABLE INITIALLY DEFERRED
000279    )} {}
000280    15155 { CREATE TABLE t1(a 
000281      REFERENCES t2(x)
000282      ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
000283    )} {}
000284    15453 { CREATE TABLE t1(a 
000285      REFERENCES t2(x) ON DELETE RESTRICT ON UPDATE NO ACTION NOT DEFERRABLE
000286    )} {}
000287    15661 { CREATE TABLE t1(a 
000288      REFERENCES t2(x) NOT DEFERRABLE INITIALLY DEFERRED
000289    )} {}
000290    21115 { CREATE TABLE t1(a 
000291      REFERENCES t2 MATCH FULL 
000292      ON DELETE SET NULL ON UPDATE SET NULL DEFERRABLE INITIALLY IMMEDIATE
000293    )} {}
000294    21123 { CREATE TABLE t1(a 
000295      REFERENCES t2 MATCH FULL 
000296      ON DELETE SET NULL ON UPDATE SET DEFAULT NOT DEFERRABLE
000297    )} {}
000298    21217 { CREATE TABLE t1(a 
000299      REFERENCES t2 MATCH FULL ON DELETE SET DEFAULT ON UPDATE SET NULL 
000300    )} {}
000301    21362 { CREATE TABLE t1(a 
000302      REFERENCES t2 MATCH FULL 
000303      ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
000304    )} {}
000305    22143 { CREATE TABLE t1(a 
000306      REFERENCES t2 MATCH PARTIAL 
000307      ON DELETE SET NULL ON UPDATE RESTRICT NOT DEFERRABLE
000308    )} {}
000309    22156 { CREATE TABLE t1(a 
000310      REFERENCES t2 MATCH PARTIAL 
000311      ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE
000312    )} {}
000313    22327 { CREATE TABLE t1(a 
000314      REFERENCES t2 MATCH PARTIAL ON DELETE CASCADE ON UPDATE SET DEFAULT 
000315    )} {}
000316    22663 { CREATE TABLE t1(a 
000317      REFERENCES t2 MATCH PARTIAL NOT DEFERRABLE
000318    )} {}
000319    23236 { CREATE TABLE t1(a 
000320      REFERENCES t2 MATCH SIMPLE 
000321      ON DELETE SET DEFAULT ON UPDATE CASCADE DEFERRABLE
000322    )} {}
000323    24155 { CREATE TABLE t1(a 
000324      REFERENCES t2 MATCH STICK 
000325      ON DELETE SET NULL ON UPDATE NO ACTION DEFERRABLE INITIALLY IMMEDIATE
000326    )} {}
000327    24522 { CREATE TABLE t1(a 
000328      REFERENCES t2 MATCH STICK 
000329      ON DELETE NO ACTION ON UPDATE SET DEFAULT NOT DEFERRABLE INITIALLY IMMEDIATE
000330    )} {}
000331    24625 { CREATE TABLE t1(a 
000332      REFERENCES t2 MATCH STICK 
000333      ON UPDATE SET DEFAULT DEFERRABLE INITIALLY IMMEDIATE
000334    )} {}
000335    25454 { CREATE TABLE t1(a 
000336      REFERENCES t2 
000337      ON DELETE RESTRICT ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED
000338    )} {}
000339  }
000340  
000341  #-------------------------------------------------------------------------
000342  # Test cases e_createtable-1.* - test statements related to table and
000343  # database names, the TEMP and TEMPORARY keywords, and the IF NOT EXISTS
000344  # clause.
000345  #
000346  drop_all_tables
000347  forcedelete test.db2 test.db3
000348  
000349  do_execsql_test e_createtable-1.0 {
000350    ATTACH 'test.db2' AS auxa;
000351    ATTACH 'test.db3' AS auxb;
000352  } {}
000353  
000354  # EVIDENCE-OF: R-17899-04554 Table names that begin with "sqlite_" are
000355  # reserved for internal use. It is an error to attempt to create a table
000356  # with a name that starts with "sqlite_".
000357  #
000358  do_createtable_tests 1.1.1 -error {
000359    object name reserved for internal use: %s
000360  } {
000361    1    "CREATE TABLE sqlite_abc(a, b, c)"        sqlite_abc
000362    2    "CREATE TABLE temp.sqlite_helloworld(x)"  sqlite_helloworld
000363    3    {CREATE TABLE auxa."sqlite__"(x, y)}      sqlite__
000364    4    {CREATE TABLE auxb."sqlite_"(z)}          sqlite_
000365    5    {CREATE TABLE "SQLITE_TBL"(z)}            SQLITE_TBL
000366  }
000367  do_createtable_tests 1.1.2 {
000368    1    "CREATE TABLE sqlit_abc(a, b, c)"         {}
000369    2    "CREATE TABLE temp.sqlitehelloworld(x)"   {}
000370    3    {CREATE TABLE auxa."sqlite"(x, y)}        {}
000371    4    {CREATE TABLE auxb."sqlite-"(z)}          {}
000372    5    {CREATE TABLE "SQLITE-TBL"(z)}            {}
000373  }
000374  
000375  
000376  # EVIDENCE-OF: R-18448-33677 If a schema-name is specified, it must be
000377  # either "main", "temp", or the name of an attached database.
000378  #
000379  # EVIDENCE-OF: R-39822-07822 In this case the new table is created in
000380  # the named database.
000381  #
000382  #   Test cases 1.2.* test the first of the two requirements above. The
000383  #   second is verified by cases 1.3.*.
000384  #
000385  do_createtable_tests 1.2.1 -error {
000386    unknown database %s
000387  } {
000388    1    "CREATE TABLE george.t1(a, b)"            george
000389    2    "CREATE TABLE _.t1(a, b)"                 _
000390  }
000391  do_createtable_tests 1.2.2 {
000392    1    "CREATE TABLE main.abc(a, b, c)"          {}
000393    2    "CREATE TABLE temp.helloworld(x)"         {}
000394    3    {CREATE TABLE auxa."t 1"(x, y)}           {}
000395    4    {CREATE TABLE auxb.xyz(z)}                {}
000396  }
000397  drop_all_tables
000398  do_createtable_tests 1.3 -tclquery {
000399    unset -nocomplain X
000400    array set X [table_list]
000401    list $X(main) $X(temp) $X(auxa) $X(auxb)
000402  } {
000403    1    "CREATE TABLE main.abc(a, b, c)"  {abc {} {} {}}
000404    2    "CREATE TABLE main.t1(a, b, c)"   {{abc t1} {} {} {}}
000405    3    "CREATE TABLE temp.tmp(a, b, c)"  {{abc t1} tmp {} {}}
000406    4    "CREATE TABLE auxb.tbl(x, y)"     {{abc t1} tmp {} tbl}
000407    5    "CREATE TABLE auxb.t1(k, v)"      {{abc t1} tmp {} {t1 tbl}}
000408    6    "CREATE TABLE auxa.next(c, d)"    {{abc t1} tmp next {t1 tbl}}
000409  }
000410  
000411  # EVIDENCE-OF: R-18895-27365 If the "TEMP" or "TEMPORARY" keyword occurs
000412  # between the "CREATE" and "TABLE" then the new table is created in the
000413  # temp database.
000414  #
000415  drop_all_tables
000416  do_createtable_tests 1.4 -tclquery {
000417    unset -nocomplain X
000418    array set X [table_list]
000419    list $X(main) $X(temp) $X(auxa) $X(auxb)
000420  } {
000421    1    "CREATE TEMP TABLE t1(a, b)"      {{} t1 {} {}}
000422    2    "CREATE TEMPORARY TABLE t2(a, b)" {{} {t1 t2} {} {}}
000423  }
000424  
000425  # EVIDENCE-OF: R-23976-43329 It is an error to specify both a
000426  # schema-name and the TEMP or TEMPORARY keyword, unless the schema-name
000427  # is "temp".
000428  #
000429  drop_all_tables
000430  do_createtable_tests 1.5.1 -error {
000431    temporary table name must be unqualified
000432  } {
000433    1    "CREATE TEMP TABLE main.t1(a, b)"        {}
000434    2    "CREATE TEMPORARY TABLE auxa.t2(a, b)"   {}
000435    3    "CREATE TEMP TABLE auxb.t3(a, b)"        {}
000436    4    "CREATE TEMPORARY TABLE main.xxx(x)"     {}
000437  }
000438  drop_all_tables
000439  do_createtable_tests 1.5.2 -tclquery {
000440    unset -nocomplain X
000441    array set X [table_list]
000442    list $X(main) $X(temp) $X(auxa) $X(auxb)
000443  } {
000444    1    "CREATE TEMP TABLE temp.t1(a, b)"        {{} t1 {} {}}
000445    2    "CREATE TEMPORARY TABLE temp.t2(a, b)"   {{} {t1 t2} {} {}}
000446    3    "CREATE TEMP TABLE TEMP.t3(a, b)"        {{} {t1 t2 t3} {} {}}
000447    4    "CREATE TEMPORARY TABLE TEMP.xxx(x)"     {{} {t1 t2 t3 xxx} {} {}}
000448  }
000449  
000450  # EVIDENCE-OF: R-31997-24564 If no schema name is specified and the TEMP
000451  # keyword is not present then the table is created in the main database.
000452  #
000453  drop_all_tables
000454  do_createtable_tests 1.6 -tclquery {
000455    unset -nocomplain X
000456    array set X [table_list]
000457    list $X(main) $X(temp) $X(auxa) $X(auxb)
000458  } {
000459    1    "CREATE TABLE t1(a, b)"   {t1 {} {} {}}
000460    2    "CREATE TABLE t2(a, b)"   {{t1 t2} {} {} {}}
000461    3    "CREATE TABLE t3(a, b)"   {{t1 t2 t3} {} {} {}}
000462    4    "CREATE TABLE xxx(x)"     {{t1 t2 t3 xxx} {} {} {}}
000463  }
000464  
000465  drop_all_tables
000466  do_execsql_test e_createtable-1.7.0 {
000467    CREATE TABLE t1(x, y);
000468    CREATE INDEX i1 ON t1(x);
000469    CREATE VIEW  v1 AS SELECT * FROM t1;
000470  
000471    CREATE TABLE auxa.tbl1(x, y);
000472    CREATE INDEX auxa.idx1 ON tbl1(x);
000473    CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
000474  } {}
000475  
000476  # EVIDENCE-OF: R-01232-54838 It is usually an error to attempt to create
000477  # a new table in a database that already contains a table, index or view
000478  # of the same name.
000479  #
000480  #   Test cases 1.7.1.* verify that creating a table in a database with a
000481  #   table/index/view of the same name does fail. 1.7.2.* tests that creating
000482  #   a table with the same name as a table/index/view in a different database
000483  #   is Ok.
000484  #
000485  do_createtable_tests 1.7.1 -error { %s } {
000486    1    "CREATE TABLE t1(a, b)"   {{table t1 already exists}}
000487    2    "CREATE TABLE i1(a, b)"   {{there is already an index named i1}}
000488    3    "CREATE TABLE v1(a, b)"   {{table v1 already exists}}
000489    4    "CREATE TABLE auxa.tbl1(a, b)"   {{table tbl1 already exists}}
000490    5    "CREATE TABLE auxa.idx1(a, b)"   {{there is already an index named idx1}}
000491    6    "CREATE TABLE auxa.view1(a, b)"  {{table view1 already exists}}
000492  }
000493  do_createtable_tests 1.7.2 {
000494    1    "CREATE TABLE auxa.t1(a, b)"   {}
000495    2    "CREATE TABLE auxa.i1(a, b)"   {}
000496    3    "CREATE TABLE auxa.v1(a, b)"   {}
000497    4    "CREATE TABLE tbl1(a, b)"      {}
000498    5    "CREATE TABLE idx1(a, b)"      {}
000499    6    "CREATE TABLE view1(a, b)"     {}
000500  }
000501  
000502  # EVIDENCE-OF: R-33917-24086 However, if the "IF NOT EXISTS" clause is
000503  # specified as part of the CREATE TABLE statement and a table or view of
000504  # the same name already exists, the CREATE TABLE command simply has no
000505  # effect (and no error message is returned).
000506  #
000507  drop_all_tables
000508  do_execsql_test e_createtable-1.8.0 {
000509    CREATE TABLE t1(x, y);
000510    CREATE INDEX i1 ON t1(x);
000511    CREATE VIEW  v1 AS SELECT * FROM t1;
000512    CREATE TABLE auxa.tbl1(x, y);
000513    CREATE INDEX auxa.idx1 ON tbl1(x);
000514    CREATE VIEW auxa.view1 AS SELECT * FROM tbl1;
000515  } {}
000516  do_createtable_tests 1.8 {
000517    1    "CREATE TABLE IF NOT EXISTS t1(a, b)"          {}
000518    2    "CREATE TABLE IF NOT EXISTS auxa.tbl1(a, b)"   {}
000519    3    "CREATE TABLE IF NOT EXISTS v1(a, b)"          {}
000520    4    "CREATE TABLE IF NOT EXISTS auxa.view1(a, b)"  {}
000521  }
000522  
000523  # EVIDENCE-OF: R-16465-40078 An error is still returned if the table
000524  # cannot be created because of an existing index, even if the "IF NOT
000525  # EXISTS" clause is specified.
000526  #
000527  do_createtable_tests 1.9 -error { %s } {
000528    1    "CREATE TABLE IF NOT EXISTS i1(a, b)"   
000529         {{there is already an index named i1}}
000530    2    "CREATE TABLE IF NOT EXISTS auxa.idx1(a, b)"   
000531         {{there is already an index named idx1}}
000532  }
000533  
000534  # EVIDENCE-OF: R-05513-33819 It is not an error to create a table that
000535  # has the same name as an existing trigger.
000536  #
000537  drop_all_tables
000538  do_execsql_test e_createtable-1.10.0 {
000539    CREATE TABLE t1(x, y);
000540    CREATE TABLE auxb.t2(x, y);
000541  
000542    CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
000543      SELECT 1;
000544    END;
000545    CREATE TRIGGER auxb.tr2 AFTER INSERT ON t2 BEGIN
000546      SELECT 1;
000547    END;
000548  } {}
000549  do_createtable_tests 1.10 {
000550    1    "CREATE TABLE tr1(a, b)"          {}
000551    2    "CREATE TABLE tr2(a, b)"          {}
000552    3    "CREATE TABLE auxb.tr1(a, b)"     {}
000553    4    "CREATE TABLE auxb.tr2(a, b)"     {}
000554  }
000555  
000556  # EVIDENCE-OF: R-22283-14179 Tables are removed using the DROP TABLE
000557  # statement.
000558  #
000559  drop_all_tables
000560  do_execsql_test e_createtable-1.11.0 {
000561    CREATE TABLE t1(a, b);
000562    CREATE TABLE t2(a, b);
000563    CREATE TABLE auxa.t3(a, b);
000564    CREATE TABLE auxa.t4(a, b);
000565  } {}
000566  
000567  do_execsql_test e_createtable-1.11.1.1 {
000568    SELECT * FROM t1;
000569    SELECT * FROM t2;
000570    SELECT * FROM t3;
000571    SELECT * FROM t4;
000572  } {}
000573  do_execsql_test  e_createtable-1.11.1.2 { DROP TABLE t1 } {}
000574  do_catchsql_test e_createtable-1.11.1.3 { 
000575    SELECT * FROM t1 
000576  } {1 {no such table: t1}}
000577  do_execsql_test  e_createtable-1.11.1.4 { DROP TABLE t3 } {}
000578  do_catchsql_test e_createtable-1.11.1.5 { 
000579    SELECT * FROM t3 
000580  } {1 {no such table: t3}}
000581  
000582  do_execsql_test e_createtable-1.11.2.1 {
000583    SELECT name FROM sqlite_master;
000584    SELECT name FROM auxa.sqlite_master;
000585  } {t2 t4}
000586  do_execsql_test  e_createtable-1.11.2.2 { DROP TABLE t2 } {}
000587  do_execsql_test  e_createtable-1.11.2.3 { DROP TABLE t4 } {}
000588  do_execsql_test e_createtable-1.11.2.4 {
000589    SELECT name FROM sqlite_master;
000590    SELECT name FROM auxa.sqlite_master;
000591  } {}
000592  
000593  #-------------------------------------------------------------------------
000594  # Test cases e_createtable-2.* - test statements related to the CREATE
000595  # TABLE AS ... SELECT statement.
000596  #
000597  
000598  # Three Tcl commands:
000599  #
000600  #   select_column_names SQL
000601  #     The argument must be a SELECT statement. Return a list of the names
000602  #     of the columns of the result-set that would be returned by executing
000603  #     the SELECT.
000604  #
000605  #   table_column_names TBL
000606  #     The argument must be a table name. Return a list of column names, from
000607  #     left to right, for the table.
000608  #
000609  #   table_column_decltypes TBL
000610  #     The argument must be a table name. Return a list of column declared
000611  #     types, from left to right, for the table.
000612  #
000613  proc sci {select cmd} {
000614    set res [list]
000615    set STMT [sqlite3_prepare_v2 db $select -1 dummy]
000616    for {set i 0} {$i < [sqlite3_column_count $STMT]} {incr i} {
000617      lappend res [$cmd $STMT $i]
000618    }
000619    sqlite3_finalize $STMT
000620    set res
000621  }
000622  proc tci {tbl cmd} { sci "SELECT * FROM $tbl" $cmd }
000623  proc select_column_names    {sql} { sci $sql sqlite3_column_name }
000624  proc table_column_names     {tbl} { tci $tbl sqlite3_column_name }
000625  proc table_column_decltypes {tbl} { tci $tbl sqlite3_column_decltype }
000626  
000627  # Create a database schema. This schema is used by tests 2.1.* through 2.3.*.
000628  #
000629  drop_all_tables
000630  do_execsql_test e_createtable-2.0 {
000631    CREATE TABLE t1(a, b, c);
000632    CREATE TABLE t2(d, e, f);
000633    CREATE TABLE t3(g BIGINT, h VARCHAR(10));
000634    CREATE TABLE t4(i BLOB, j ANYOLDATA);
000635    CREATE TABLE t5(k FLOAT, l INTEGER);
000636    CREATE TABLE t6(m DEFAULT 10, n DEFAULT 5, PRIMARY KEY(m, n));
000637    CREATE TABLE t7(x INTEGER PRIMARY KEY);
000638    CREATE TABLE t8(o COLLATE nocase DEFAULT 'abc');
000639    CREATE TABLE t9(p NOT NULL, q DOUBLE CHECK (q!=0), r STRING UNIQUE);
000640  } {}
000641  
000642  # EVIDENCE-OF: R-64828-59568 The table has the same number of columns as
000643  # the rows returned by the SELECT statement. The name of each column is
000644  # the same as the name of the corresponding column in the result set of
000645  # the SELECT statement.
000646  #
000647  do_createtable_tests 2.1 -tclquery {
000648    table_column_names x1
000649  } -repair {
000650    catchsql { DROP TABLE x1 }
000651  } {
000652    1    "CREATE TABLE x1 AS SELECT * FROM t1"                     {a b c}
000653    2    "CREATE TABLE x1 AS SELECT c, b, a FROM t1"               {c b a}
000654    3    "CREATE TABLE x1 AS SELECT * FROM t1, t2"                 {a b c d e f}
000655    4    "CREATE TABLE x1 AS SELECT count(*) FROM t1"              {count(*)}
000656    5    "CREATE TABLE x1 AS SELECT count(a) AS a, max(b) FROM t1" {a max(b)}
000657  }
000658  
000659  # EVIDENCE-OF: R-55407-45319 The declared type of each column is
000660  # determined by the expression affinity of the corresponding expression
000661  # in the result set of the SELECT statement, as follows: Expression
000662  # Affinity Column Declared Type TEXT "TEXT" NUMERIC "NUM" INTEGER "INT"
000663  # REAL "REAL" BLOB (a.k.a "NONE") "" (empty string)
000664  #
000665  do_createtable_tests 2.2 -tclquery {
000666    table_column_decltypes x1
000667  } -repair {
000668    catchsql { DROP TABLE x1 }
000669  } {
000670    1    "CREATE TABLE x1 AS SELECT a FROM t1"     {""}
000671    2    "CREATE TABLE x1 AS SELECT * FROM t3"     {INT TEXT}
000672    3    "CREATE TABLE x1 AS SELECT * FROM t4"     {"" NUM}
000673    4    "CREATE TABLE x1 AS SELECT * FROM t5"     {REAL INT}
000674  }
000675  
000676  # EVIDENCE-OF: R-16667-09772 A table created using CREATE TABLE AS has
000677  # no PRIMARY KEY and no constraints of any kind. The default value of
000678  # each column is NULL. The default collation sequence for each column of
000679  # the new table is BINARY.
000680  #
000681  #   The following tests create tables based on SELECT statements that read
000682  #   from tables that have primary keys, constraints and explicit default 
000683  #   collation sequences. None of this is transfered to the definition of
000684  #   the new table as stored in the sqlite_master table.
000685  #
000686  #   Tests 2.3.2.* show that the default value of each column is NULL.
000687  #
000688  do_createtable_tests 2.3.1 -query {
000689    SELECT sql FROM sqlite_master ORDER BY rowid DESC LIMIT 1
000690  } {
000691    1    "CREATE TABLE x1 AS SELECT * FROM t6" {{CREATE TABLE x1(m,n)}}
000692    2    "CREATE TABLE x2 AS SELECT * FROM t7" {{CREATE TABLE x2(x INT)}}
000693    3    "CREATE TABLE x3 AS SELECT * FROM t8" {{CREATE TABLE x3(o)}}
000694    4    "CREATE TABLE x4 AS SELECT * FROM t9" {{CREATE TABLE x4(p,q REAL,r NUM)}}
000695  }
000696  do_execsql_test e_createtable-2.3.2.1 {
000697    INSERT INTO x1 DEFAULT VALUES;
000698    INSERT INTO x2 DEFAULT VALUES;
000699    INSERT INTO x3 DEFAULT VALUES;
000700    INSERT INTO x4 DEFAULT VALUES;
000701  } {}
000702  db nullvalue null
000703  do_execsql_test e_createtable-2.3.2.2 { SELECT * FROM x1 } {null null}
000704  do_execsql_test e_createtable-2.3.2.3 { SELECT * FROM x2 } {null}
000705  do_execsql_test e_createtable-2.3.2.4 { SELECT * FROM x3 } {null}
000706  do_execsql_test e_createtable-2.3.2.5 { SELECT * FROM x4 } {null null null}
000707  db nullvalue {}
000708  
000709  drop_all_tables
000710  do_execsql_test e_createtable-2.4.0 {
000711    CREATE TABLE t1(x, y);
000712    INSERT INTO t1 VALUES('i',   'one');
000713    INSERT INTO t1 VALUES('ii',  'two');
000714    INSERT INTO t1 VALUES('iii', 'three');
000715  } {}
000716  
000717  # EVIDENCE-OF: R-24153-28352 Tables created using CREATE TABLE AS are
000718  # initially populated with the rows of data returned by the SELECT
000719  # statement.
000720  #
000721  # EVIDENCE-OF: R-08224-30249 Rows are assigned contiguously ascending
000722  # rowid values, starting with 1, in the order that they are returned by
000723  # the SELECT statement.
000724  #
000725  #   Each test case below is specified as the name of a table to create
000726  #   using "CREATE TABLE ... AS SELECT ..." and a SELECT statement to use in
000727  #   creating it. The table is created. 
000728  #
000729  #   Test cases 2.4.*.1 check that after it has been created, the data in the
000730  #   table is the same as the data returned by the SELECT statement executed as
000731  #   a standalone command, verifying the first testable statement above.
000732  #
000733  #   Test cases 2.4.*.2 check that the rowids were allocated contiguously
000734  #   as required by the second testable statement above. That the rowids
000735  #   from the contiguous block were allocated to rows in the order rows are
000736  #   returned by the SELECT statement is verified by 2.4.*.1.
000737  #
000738  # EVIDENCE-OF: R-32365-09043 A "CREATE TABLE ... AS SELECT" statement
000739  # creates and populates a database table based on the results of a
000740  # SELECT statement.
000741  #
000742  #   The above is also considered to be tested by the following. It is
000743  #   clear that tables are being created and populated by the command in
000744  #   question.
000745  #
000746  foreach {tn tbl select} {
000747    1   x1   "SELECT * FROM t1"
000748    2   x2   "SELECT * FROM t1 ORDER BY x DESC"
000749    3   x3   "SELECT * FROM t1 ORDER BY x ASC"
000750  } {
000751    # Create the table using a "CREATE TABLE ... AS SELECT ..." command.
000752    execsql [subst {CREATE TABLE $tbl AS $select}]
000753  
000754    # Check that the rows inserted into the table, sorted in ascending rowid
000755    # order, match those returned by executing the SELECT statement as a
000756    # standalone command.
000757    do_execsql_test e_createtable-2.4.$tn.1 [subst {
000758      SELECT * FROM $tbl ORDER BY rowid;
000759    }] [execsql $select]
000760  
000761    # Check that the rowids in the new table are a contiguous block starting
000762    # with rowid 1. Note that this will fail if SELECT statement $select 
000763    # returns 0 rows (as max(rowid) will be NULL).
000764    do_execsql_test e_createtable-2.4.$tn.2 [subst {
000765      SELECT min(rowid), count(rowid)==max(rowid) FROM $tbl
000766    }] {1 1}
000767  }
000768  
000769  #--------------------------------------------------------------------------
000770  # Test cases for column defintions in CREATE TABLE statements that do not
000771  # use a SELECT statement. Not including data constraints. In other words,
000772  # tests for the specification of:
000773  #
000774  #   * declared types,
000775  #   * default values, and
000776  #   * default collation sequences.
000777  #
000778  
000779  # EVIDENCE-OF: R-27219-49057 Unlike most SQL databases, SQLite does not
000780  # restrict the type of data that may be inserted into a column based on
000781  # the columns declared type.
000782  #
000783  #   Test this by creating a few tables with varied declared types, then
000784  #   inserting various different types of values into them.
000785  #
000786  drop_all_tables
000787  do_execsql_test e_createtable-3.1.0 {
000788    CREATE TABLE t1(x VARCHAR(10), y INTEGER, z DOUBLE);
000789    CREATE TABLE t2(a DATETIME, b STRING, c REAL);
000790    CREATE TABLE t3(o, t);
000791  } {}
000792  
000793  # value type -> declared column type
000794  # ----------------------------------
000795  # integer    -> VARCHAR(10)
000796  # string     -> INTEGER
000797  # blob       -> DOUBLE
000798  #
000799  do_execsql_test e_createtable-3.1.1 {
000800    INSERT INTO t1 VALUES(14, 'quite a lengthy string', X'555655');
000801    SELECT * FROM t1;
000802  } {14 {quite a lengthy string} UVU}
000803  
000804  # string     -> DATETIME
000805  # integer    -> STRING
000806  # time       -> REAL
000807  #
000808  do_execsql_test e_createtable-3.1.2 {
000809    INSERT INTO t2 VALUES('not a datetime', 13, '12:41:59');
000810    SELECT * FROM t2;
000811  } {{not a datetime} 13 12:41:59}
000812  
000813  # EVIDENCE-OF: R-10565-09557 The declared type of a column is used to
000814  # determine the affinity of the column only.
000815  #
000816  #     Affinities are tested in more detail elsewhere (see document
000817  #     datatype3.html). Here, just test that affinity transformations
000818  #     consistent with the expected affinity of each column (based on
000819  #     the declared type) appear to take place.
000820  #
000821  # Affinities of t1 (test cases 3.2.1.*): TEXT, INTEGER, REAL
000822  # Affinities of t2 (test cases 3.2.2.*): NUMERIC, NUMERIC, REAL
000823  # Affinities of t3 (test cases 3.2.3.*): NONE, NONE
000824  #
000825  do_execsql_test e_createtable-3.2.0 { DELETE FROM t1; DELETE FROM t2; } {}
000826  
000827  do_createtable_tests 3.2.1 -query {
000828    SELECT quote(x), quote(y), quote(z) FROM t1 ORDER BY rowid DESC LIMIT 1;
000829  } {
000830    1   "INSERT INTO t1 VALUES(15,   '22.0', '14')"   {'15' 22 14.0}
000831    2   "INSERT INTO t1 VALUES(22.0, 22.0, 22.0)"     {'22.0' 22 22.0}
000832  }
000833  do_createtable_tests 3.2.2 -query {
000834    SELECT quote(a), quote(b), quote(c) FROM t2 ORDER BY rowid DESC LIMIT 1;
000835  } {
000836    1   "INSERT INTO t2 VALUES(15,   '22.0', '14')"   {15   22  14.0}
000837    2   "INSERT INTO t2 VALUES(22.0, 22.0, 22.0)"     {22   22  22.0}
000838  }
000839  do_createtable_tests 3.2.3 -query {
000840    SELECT quote(o), quote(t) FROM t3 ORDER BY rowid DESC LIMIT 1;
000841  } {
000842    1   "INSERT INTO t3 VALUES('15', '22.0')"         {'15' '22.0'}
000843    2   "INSERT INTO t3 VALUES(15, 22.0)"             {15 22.0}
000844  }
000845  
000846  # EVIDENCE-OF: R-42316-09582 If there is no explicit DEFAULT clause
000847  # attached to a column definition, then the default value of the column
000848  # is NULL.
000849  #
000850  #     None of the columns in table t1 have an explicit DEFAULT clause.
000851  #     So testing that the default value of all columns in table t1 is
000852  #     NULL serves to verify the above.
000853  #     
000854  do_createtable_tests 3.2.3 -query {
000855    SELECT quote(x), quote(y), quote(z) FROM t1
000856  } -repair {
000857    execsql { DELETE FROM t1 }
000858  } {
000859    1   "INSERT INTO t1(x, y) VALUES('abc', 'xyz')"   {'abc' 'xyz' NULL}
000860    2   "INSERT INTO t1(x, z) VALUES('abc', 'xyz')"   {'abc' NULL 'xyz'}
000861    3   "INSERT INTO t1 DEFAULT VALUES"               {NULL NULL NULL}
000862  }
000863  
000864  # EVIDENCE-OF: R-07343-35026 An explicit DEFAULT clause may specify that
000865  # the default value is NULL, a string constant, a blob constant, a
000866  # signed-number, or any constant expression enclosed in parentheses. A
000867  # default value may also be one of the special case-independent keywords
000868  # CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
000869  #
000870  do_execsql_test e_createtable-3.3.1 {
000871    CREATE TABLE t4(
000872      a DEFAULT NULL,
000873      b DEFAULT 'string constant',
000874      c DEFAULT X'424C4F42',
000875      d DEFAULT 1,
000876      e DEFAULT -1,
000877      f DEFAULT 3.14,
000878      g DEFAULT -3.14,
000879      h DEFAULT ( substr('abcd', 0, 2) || 'cd' ),
000880      i DEFAULT CURRENT_TIME,
000881      j DEFAULT CURRENT_DATE,
000882      k DEFAULT CURRENT_TIMESTAMP
000883    );
000884  } {}
000885  
000886  # EVIDENCE-OF: R-18415-27776 For the purposes of the DEFAULT clause, an
000887  # expression is considered constant if it does contains no sub-queries,
000888  # column or table references, bound parameters, or string literals
000889  # enclosed in double-quotes instead of single-quotes.
000890  #
000891  do_createtable_tests 3.4.1 -error {
000892    default value of column [x] is not constant
000893  } {
000894    1   {CREATE TABLE t5(x DEFAULT ( (SELECT 1) ))}  {}
000895    2   {CREATE TABLE t5(x DEFAULT ( "abc" ))}  {}
000896    3   {CREATE TABLE t5(x DEFAULT ( 1 IN (SELECT 1) ))}  {}
000897    4   {CREATE TABLE t5(x DEFAULT ( EXISTS (SELECT 1) ))}  {}
000898    5   {CREATE TABLE t5(x DEFAULT ( x!=?1 ))}  {}
000899  }
000900  do_createtable_tests 3.4.2 -repair {
000901    catchsql { DROP TABLE t5 }
000902  } {
000903    1   {CREATE TABLE t5(x DEFAULT ( 'abc' ))}  {}
000904    2   {CREATE TABLE t5(x DEFAULT ( 1 IN (1, 2, 3) ))}  {}
000905  }
000906  
000907  # EVIDENCE-OF: R-18814-23501 Each time a row is inserted into the table
000908  # by an INSERT statement that does not provide explicit values for all
000909  # table columns the values stored in the new row are determined by their
000910  # default values
000911  #
000912  #     Verify this with some assert statements for which all, some and no
000913  #     columns lack explicit values.
000914  #
000915  set sqlite_current_time 1000000000
000916  do_createtable_tests 3.5 -query {
000917    SELECT quote(a), quote(b), quote(c), quote(d), quote(e), quote(f), 
000918           quote(g), quote(h), quote(i), quote(j), quote(k)
000919    FROM t4 ORDER BY rowid DESC LIMIT 1;
000920  } {
000921    1 "INSERT INTO t4 DEFAULT VALUES" {
000922      NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 
000923      'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
000924    }
000925  
000926    2 "INSERT INTO t4(a, b, c) VALUES(1, 2, 3)" {
000927      1 2 3 1 -1 3.14 -3.14 'acd' '01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}
000928    }
000929  
000930    3 "INSERT INTO t4(k, j, i) VALUES(1, 2, 3)" {
000931      NULL {'string constant'} X'424C4F42' 1 -1 3.14 -3.14 'acd' 3 2 1
000932    }
000933  
000934    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)" {
000935      1 2 3 4 5 6 7 8 9 10 11
000936    }
000937  }
000938  
000939  # EVIDENCE-OF: R-12572-62501 If the default value of the column is a
000940  # constant NULL, text, blob or signed-number value, then that value is
000941  # used directly in the new row.
000942  #
000943  do_execsql_test e_createtable-3.6.1 {
000944    CREATE TABLE t5(
000945      a DEFAULT NULL,  
000946      b DEFAULT 'text value',  
000947      c DEFAULT X'424C4F42',
000948      d DEFAULT -45678.6,
000949      e DEFAULT 394507
000950    );
000951  } {}
000952  do_execsql_test e_createtable-3.6.2 {
000953    INSERT INTO t5 DEFAULT VALUES;
000954    SELECT quote(a), quote(b), quote(c), quote(d), quote(e) FROM t5;
000955  } {NULL {'text value'} X'424C4F42' -45678.6 394507}
000956  
000957  # EVIDENCE-OF: R-60616-50251 If the default value of a column is an
000958  # expression in parentheses, then the expression is evaluated once for
000959  # each row inserted and the results used in the new row.
000960  #
000961  #   Test case 3.6.4 demonstrates that the expression is evaluated 
000962  #   separately for each row if the INSERT is an "INSERT INTO ... SELECT ..."
000963  #   command.
000964  #
000965  set ::nextint 0
000966  proc nextint {} { incr ::nextint }
000967  db func nextint nextint
000968  
000969  do_execsql_test e_createtable-3.7.1 {
000970    CREATE TABLE t6(a DEFAULT ( nextint() ), b DEFAULT ( nextint() ));
000971  } {}
000972  do_execsql_test e_createtable-3.7.2 {
000973    INSERT INTO t6 DEFAULT VALUES;
000974    SELECT quote(a), quote(b) FROM t6;
000975  } {1 2}
000976  do_execsql_test e_createtable-3.7.3 {
000977    INSERT INTO t6(a) VALUES('X');
000978    SELECT quote(a), quote(b) FROM t6;
000979  } {1 2 'X' 3}
000980  do_execsql_test e_createtable-3.7.4 {
000981    INSERT INTO t6(a) SELECT a FROM t6;
000982    SELECT quote(a), quote(b) FROM t6;
000983  } {1 2 'X' 3 1 4 'X' 5}
000984  
000985  # EVIDENCE-OF: R-15363-55230 If the default value of a column is
000986  # CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the value used
000987  # in the new row is a text representation of the current UTC date and/or
000988  # time.
000989  #
000990  #     This is difficult to test literally without knowing what time the 
000991  #     user will run the tests. Instead, we test that the three cases
000992  #     above set the value to the current date and/or time according to
000993  #     the xCurrentTime() method of the VFS. Which is usually the same
000994  #     as UTC. In this case, however, we instrument it to always return
000995  #     a time equivalent to "2001-09-09 01:46:40 UTC".
000996  #
000997  set sqlite_current_time 1000000000
000998  do_execsql_test e_createtable-3.8.1 {
000999    CREATE TABLE t7(
001000      a DEFAULT CURRENT_TIME, 
001001      b DEFAULT CURRENT_DATE, 
001002      c DEFAULT CURRENT_TIMESTAMP
001003    );
001004  } {}
001005  do_execsql_test e_createtable-3.8.2 {
001006    INSERT INTO t7 DEFAULT VALUES;
001007    SELECT quote(a), quote(b), quote(c) FROM t7;
001008  } {'01:46:40' '2001-09-09' {'2001-09-09 01:46:40'}}
001009  
001010  
001011  # EVIDENCE-OF: R-62327-53843 For CURRENT_TIME, the format of the value
001012  # is "HH:MM:SS".
001013  #
001014  # EVIDENCE-OF: R-03775-43471 For CURRENT_DATE, "YYYY-MM-DD".
001015  #
001016  # EVIDENCE-OF: R-07677-44926 The format for CURRENT_TIMESTAMP is
001017  # "YYYY-MM-DD HH:MM:SS".
001018  #
001019  #     The three above are demonstrated by tests 1, 2 and 3 below. 
001020  #     Respectively.
001021  #
001022  do_createtable_tests 3.8.3 -query {
001023    SELECT a, b, c FROM t7 ORDER BY rowid DESC LIMIT 1;
001024  } {
001025    1 "INSERT INTO t7(b, c) VALUES('x', 'y')" {01:46:40 x y}
001026    2 "INSERT INTO t7(c, a) VALUES('x', 'y')" {y 2001-09-09 x}
001027    3 "INSERT INTO t7(a, b) VALUES('x', 'y')" {x y {2001-09-09 01:46:40}}
001028  }
001029  
001030  # EVIDENCE-OF: R-55061-47754 The COLLATE clause specifies the name of a
001031  # collating sequence to use as the default collation sequence for the
001032  # column.
001033  #
001034  # EVIDENCE-OF: R-40275-54363 If no COLLATE clause is specified, the
001035  # default collation sequence is BINARY.
001036  #
001037  do_execsql_test e_createtable-3-9.1 {
001038    CREATE TABLE t8(a COLLATE nocase, b COLLATE rtrim, c COLLATE binary, d);
001039    INSERT INTO t8 VALUES('abc',   'abc',   'abc',   'abc');
001040    INSERT INTO t8 VALUES('abc  ', 'abc  ', 'abc  ', 'abc  ');
001041    INSERT INTO t8 VALUES('ABC  ', 'ABC  ', 'ABC  ', 'ABC  ');
001042    INSERT INTO t8 VALUES('ABC',   'ABC',   'ABC',   'ABC');
001043  } {}
001044  do_createtable_tests 3.9 {
001045    2    "SELECT a FROM t8 ORDER BY a, rowid"    {abc ABC {abc  } {ABC  }}
001046    3    "SELECT b FROM t8 ORDER BY b, rowid"    {{ABC  } ABC abc {abc  }}
001047    4    "SELECT c FROM t8 ORDER BY c, rowid"    {ABC {ABC  } abc {abc  }}
001048    5    "SELECT d FROM t8 ORDER BY d, rowid"    {ABC {ABC  } abc {abc  }}
001049  }
001050  
001051  # EVIDENCE-OF: R-25473-20557 The number of columns in a table is limited
001052  # by the SQLITE_MAX_COLUMN compile-time parameter.
001053  #
001054  proc columns {n} {
001055    set res [list]
001056    for {set i 0} {$i < $n} {incr i} { lappend res "c$i" }
001057    join $res ", "
001058  }
001059  do_execsql_test e_createtable-3.10.1 [subst {
001060    CREATE TABLE t9([columns $::SQLITE_MAX_COLUMN]);
001061  }] {}
001062  do_catchsql_test e_createtable-3.10.2 [subst {
001063    CREATE TABLE t10([columns [expr $::SQLITE_MAX_COLUMN+1]]);
001064  }] {1 {too many columns on t10}}
001065  
001066  # EVIDENCE-OF: R-27775-64721 Both of these limits can be lowered at
001067  # runtime using the sqlite3_limit() C/C++ interface.
001068  #
001069  #   A 30,000 byte blob consumes 30,003 bytes of record space. A record 
001070  #   that contains 3 such blobs consumes (30,000*3)+1 bytes of space. Tests
001071  #   3.11.4 and 3.11.5, which verify that SQLITE_MAX_LENGTH may be lowered
001072  #   at runtime, are based on this calculation.
001073  #
001074  sqlite3_limit db SQLITE_LIMIT_COLUMN 500
001075  do_execsql_test e_createtable-3.11.1 [subst {
001076    CREATE TABLE t10([columns 500]);
001077  }] {}
001078  do_catchsql_test e_createtable-3.11.2 [subst {
001079    CREATE TABLE t11([columns 501]);
001080  }] {1 {too many columns on t11}}
001081  
001082  # Check that it is not possible to raise the column limit above its 
001083  # default compile time value.
001084  #
001085  sqlite3_limit db SQLITE_LIMIT_COLUMN [expr $::SQLITE_MAX_COLUMN+2]
001086  do_catchsql_test e_createtable-3.11.3 [subst {
001087    CREATE TABLE t11([columns [expr $::SQLITE_MAX_COLUMN+1]]);
001088  }] {1 {too many columns on t11}}
001089  
001090  sqlite3_limit db SQLITE_LIMIT_LENGTH 90010
001091  do_execsql_test e_createtable-3.11.4 {
001092    CREATE TABLE t12(a, b, c);
001093    INSERT INTO t12 VALUES(randomblob(30000),randomblob(30000),randomblob(30000));
001094  } {}
001095  do_catchsql_test e_createtable-3.11.5 {
001096    INSERT INTO t12 VALUES(randomblob(30001),randomblob(30000),randomblob(30000));
001097  } {1 {string or blob too big}}
001098  
001099  #-------------------------------------------------------------------------
001100  # Tests for statements regarding constraints (PRIMARY KEY, UNIQUE, NOT 
001101  # NULL and CHECK constraints).
001102  #
001103  
001104  # EVIDENCE-OF: R-52382-54248 Each table in SQLite may have at most one
001105  # PRIMARY KEY.
001106  # 
001107  # EVIDENCE-OF: R-31826-01813 An error is raised if more than one PRIMARY
001108  # KEY clause appears in a CREATE TABLE statement.
001109  #
001110  #     To test the two above, show that zero primary keys is Ok, one primary
001111  #     key is Ok, and two or more primary keys is an error.
001112  #
001113  drop_all_tables
001114  do_createtable_tests 4.1.1 {
001115    1    "CREATE TABLE t1(a, b, c)"                                        {}
001116    2    "CREATE TABLE t2(a PRIMARY KEY, b, c)"                            {}
001117    3    "CREATE TABLE t3(a, b, c, PRIMARY KEY(a))"                        {}
001118    4    "CREATE TABLE t4(a, b, c, PRIMARY KEY(c,b,a))"                    {}
001119  }
001120  do_createtable_tests 4.1.2 -error {
001121    table "t5" has more than one primary key
001122  } {
001123    1    "CREATE TABLE t5(a PRIMARY KEY, b PRIMARY KEY, c)"                {}
001124    2    "CREATE TABLE t5(a, b PRIMARY KEY, c, PRIMARY KEY(a))"            {}
001125    3    "CREATE TABLE t5(a INTEGER PRIMARY KEY, b PRIMARY KEY, c)"        {}
001126    4    "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(b, c))" {}
001127    5    "CREATE TABLE t5(a PRIMARY KEY, b, c, PRIMARY KEY(a))"            {}
001128    6    "CREATE TABLE t5(a INTEGER PRIMARY KEY, b, c, PRIMARY KEY(a))"    {}
001129  }
001130  
001131  # EVIDENCE-OF: R-54755-39291 The PRIMARY KEY is optional for ordinary
001132  # tables but is required for WITHOUT ROWID tables.
001133  #
001134  do_catchsql_test 4.1.3 {
001135    CREATE TABLE t6(a, b); --ok
001136  } {0 {}}
001137  do_catchsql_test 4.1.4 {
001138    CREATE TABLE t7(a, b) WITHOUT ROWID; --Error, no PRIMARY KEY
001139  } {1 {PRIMARY KEY missing on table t7}}
001140  
001141  
001142  proc table_pk {tbl} { 
001143    set pk [list]
001144    db eval "pragma table_info($tbl)" a {
001145      if {$a(pk)} { lappend pk $a(name) }
001146    }
001147    set pk
001148  }
001149  
001150  # EVIDENCE-OF: R-41411-18837 If the keywords PRIMARY KEY are added to a
001151  # column definition, then the primary key for the table consists of that
001152  # single column.
001153  #
001154  #     The above is tested by 4.2.1.*
001155  #
001156  # EVIDENCE-OF: R-31775-48204 Or, if a PRIMARY KEY clause is specified as
001157  # a table-constraint, then the primary key of the table consists of the
001158  # list of columns specified as part of the PRIMARY KEY clause.
001159  #
001160  #     The above is tested by 4.2.2.*
001161  #
001162  do_createtable_tests 4.2 -repair {
001163    catchsql { DROP TABLE t5 }
001164  } -tclquery {
001165    table_pk t5
001166  } {
001167    1.1    "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)"       {b}
001168    1.2    "CREATE TABLE t5(a PRIMARY KEY, b, c)"               {a}
001169  
001170    2.1    "CREATE TABLE t5(a, b, c, PRIMARY KEY(a))"           {a}
001171    2.2    "CREATE TABLE t5(a, b, c, PRIMARY KEY(c,b,a))"       {a b c}
001172    2.3    "CREATE TABLE t5(a, b INTEGER PRIMARY KEY, c)"       {b}
001173  }
001174  
001175  # EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must
001176  # have a unique combination of values in its primary key columns.
001177  #
001178  # EVIDENCE-OF: R-06471-16287 If an INSERT or UPDATE statement attempts
001179  # to modify the table content so that two or more rows have identical
001180  # primary key values, that is a constraint violation.
001181  #
001182  drop_all_tables
001183  do_execsql_test 4.3.0 {
001184    CREATE TABLE t1(x PRIMARY KEY, y);
001185    INSERT INTO t1 VALUES(0,          'zero');
001186    INSERT INTO t1 VALUES(45.5,       'one');
001187    INSERT INTO t1 VALUES('brambles', 'two');
001188    INSERT INTO t1 VALUES(X'ABCDEF',  'three');
001189  
001190    CREATE TABLE t2(x, y, PRIMARY KEY(x, y));
001191    INSERT INTO t2 VALUES(0,          'zero');
001192    INSERT INTO t2 VALUES(45.5,       'one');
001193    INSERT INTO t2 VALUES('brambles', 'two');
001194    INSERT INTO t2 VALUES(X'ABCDEF',  'three');
001195  } {}
001196  
001197  do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t1.x} {
001198    1    "INSERT INTO t1 VALUES(0, 0)"                 {"column x is"}
001199    2    "INSERT INTO t1 VALUES(45.5, 'abc')"          {"column x is"}
001200    3    "INSERT INTO t1 VALUES(0.0, 'abc')"           {"column x is"}
001201    4    "INSERT INTO t1 VALUES('brambles', 'abc')"    {"column x is"}
001202    5    "INSERT INTO t1 VALUES(X'ABCDEF', 'abc')"     {"column x is"}
001203  }
001204  do_createtable_tests 4.3.1 -error {UNIQUE constraint failed: t2.x, t2.y} {
001205    6    "INSERT INTO t2 VALUES(0, 'zero')"            {"columns x, y are"}
001206    7    "INSERT INTO t2 VALUES(45.5, 'one')"          {"columns x, y are"}
001207    8    "INSERT INTO t2 VALUES(0.0, 'zero')"          {"columns x, y are"}
001208    9    "INSERT INTO t2 VALUES('brambles', 'two')"    {"columns x, y are"}
001209    10   "INSERT INTO t2 VALUES(X'ABCDEF', 'three')"   {"columns x, y are"}
001210  }
001211  do_createtable_tests 4.3.2 {
001212    1    "INSERT INTO t1 VALUES(-1, 0)"                {}
001213    2    "INSERT INTO t1 VALUES(45.2, 'abc')"          {}
001214    3    "INSERT INTO t1 VALUES(0.01, 'abc')"          {}
001215    4    "INSERT INTO t1 VALUES('bramble', 'abc')"     {}
001216    5    "INSERT INTO t1 VALUES(X'ABCDEE', 'abc')"     {}
001217  
001218    6    "INSERT INTO t2 VALUES(0, 0)"                 {}
001219    7    "INSERT INTO t2 VALUES(45.5, 'abc')"          {}
001220    8    "INSERT INTO t2 VALUES(0.0, 'abc')"           {}
001221    9    "INSERT INTO t2 VALUES('brambles', 'abc')"    {}
001222    10   "INSERT INTO t2 VALUES(X'ABCDEF', 'abc')"     {}
001223  }
001224  do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t1.x} {
001225    1    "UPDATE t1 SET x=0           WHERE y='two'"    {"column x is"}
001226    2    "UPDATE t1 SET x='brambles'  WHERE y='three'"  {"column x is"}
001227    3    "UPDATE t1 SET x=45.5        WHERE y='zero'"   {"column x is"}
001228    4    "UPDATE t1 SET x=X'ABCDEF'   WHERE y='one'"    {"column x is"}
001229    5    "UPDATE t1 SET x=0.0         WHERE y='three'"  {"column x is"}
001230  }
001231  do_createtable_tests 4.3.3 -error {UNIQUE constraint failed: t2.x, t2.y} {
001232    6    "UPDATE t2 SET x=0, y='zero' WHERE y='two'"    {"columns x, y are"}
001233    7    "UPDATE t2 SET x='brambles', y='two' WHERE y='three'"  
001234         {"columns x, y are"}
001235    8    "UPDATE t2 SET x=45.5, y='one' WHERE y='zero'" {"columns x, y are"}
001236    9    "UPDATE t2 SET x=X'ABCDEF', y='three' WHERE y='one'" 
001237         {"columns x, y are"}
001238    10   "UPDATE t2 SET x=0.0, y='zero'        WHERE y='three'"  
001239         {"columns x, y are"}
001240  }
001241  
001242  
001243  # EVIDENCE-OF: R-52572-02078 For the purposes of determining the
001244  # uniqueness of primary key values, NULL values are considered distinct
001245  # from all other values, including other NULLs.
001246  #
001247  do_createtable_tests 4.4 {
001248    1    "INSERT INTO t1 VALUES(NULL, 0)"              {}
001249    2    "INSERT INTO t1 VALUES(NULL, 0)"              {}
001250    3    "INSERT INTO t1 VALUES(NULL, 0)"              {}
001251  
001252    4    "INSERT INTO t2 VALUES(NULL, 'zero')"         {}
001253    5    "INSERT INTO t2 VALUES(NULL, 'one')"          {}
001254    6    "INSERT INTO t2 VALUES(NULL, 'two')"          {}
001255    7    "INSERT INTO t2 VALUES(NULL, 'three')"        {}
001256  
001257    8    "INSERT INTO t2 VALUES(0, NULL)"              {}
001258    9    "INSERT INTO t2 VALUES(45.5, NULL)"           {}
001259    10   "INSERT INTO t2 VALUES(0.0, NULL)"            {}
001260    11   "INSERT INTO t2 VALUES('brambles', NULL)"     {}
001261    12   "INSERT INTO t2 VALUES(X'ABCDEF', NULL)"      {}
001262  
001263    13   "INSERT INTO t2 VALUES(NULL, NULL)"           {}
001264    14   "INSERT INTO t2 VALUES(NULL, NULL)"           {}
001265  }
001266  
001267  # EVIDENCE-OF: R-35113-43214 Unless the column is an INTEGER PRIMARY KEY
001268  # or the table is a WITHOUT ROWID table or the column is declared NOT
001269  # NULL, SQLite allows NULL values in a PRIMARY KEY column.
001270  #
001271  #     If the column is an integer primary key, attempting to insert a NULL
001272  #     into the column triggers the auto-increment behavior. Attempting
001273  #     to use UPDATE to set an ipk column to a NULL value is an error.
001274  #
001275  do_createtable_tests 4.5.1 {
001276    1    "SELECT count(*) FROM t1 WHERE x IS NULL"                   3
001277    2    "SELECT count(*) FROM t2 WHERE x IS NULL"                   6
001278    3    "SELECT count(*) FROM t2 WHERE y IS NULL"                   7
001279    4    "SELECT count(*) FROM t2 WHERE x IS NULL AND y IS NULL"     2
001280  }
001281  do_execsql_test 4.5.2 {
001282    CREATE TABLE t3(s, u INTEGER PRIMARY KEY, v);
001283    INSERT INTO t3 VALUES(1, NULL, 2);
001284    INSERT INTO t3 VALUES('x', NULL, 'y');
001285    SELECT u FROM t3;
001286  } {1 2}
001287  do_catchsql_test 4.5.3 {
001288    INSERT INTO t3 VALUES(2, 5, 3);
001289    UPDATE t3 SET u = NULL WHERE s = 2;
001290  } {1 {datatype mismatch}}
001291  do_catchsql_test 4.5.4 {
001292    CREATE TABLE t4(s, u INT PRIMARY KEY, v) WITHOUT ROWID;
001293    INSERT INTO t4 VALUES(1, NULL, 2);
001294  } {1 {NOT NULL constraint failed: t4.u}}
001295  do_catchsql_test 4.5.5 {
001296    CREATE TABLE t5(s, u INT PRIMARY KEY NOT NULL, v);
001297    INSERT INTO t5 VALUES(1, NULL, 2);
001298  } {1 {NOT NULL constraint failed: t5.u}}
001299  
001300  # EVIDENCE-OF: R-00227-21080 A UNIQUE constraint is similar to a PRIMARY
001301  # KEY constraint, except that a single table may have any number of
001302  # UNIQUE constraints.
001303  #
001304  drop_all_tables
001305  do_createtable_tests 4.6 {
001306    1    "CREATE TABLE t1(a UNIQUE, b UNIQUE)"                       {}
001307    2    "CREATE TABLE t2(a UNIQUE, b, c, UNIQUE(c, b))"             {}
001308    3    "CREATE TABLE t3(a, b, c, UNIQUE(a), UNIQUE(b), UNIQUE(c))" {}
001309    4    "CREATE TABLE t4(a, b, c, UNIQUE(a, b, c))"                 {}
001310  }
001311  
001312  # EVIDENCE-OF: R-30981-64168 For each UNIQUE constraint on the table,
001313  # each row must contain a unique combination of values in the columns
001314  # identified by the UNIQUE constraint.
001315  #
001316  # EVIDENCE-OF: R-59124-61339 Each row in a table with a primary key must
001317  # have a unique combination of values in its primary key columns.
001318  #
001319  do_execsql_test 4.7.0 {
001320    INSERT INTO t1 VALUES(1, 2);
001321    INSERT INTO t1 VALUES(4.3, 5.5);
001322    INSERT INTO t1 VALUES('reveal', 'variableness');
001323    INSERT INTO t1 VALUES(X'123456', X'654321');
001324  
001325    INSERT INTO t4 VALUES('xyx', 1, 1);
001326    INSERT INTO t4 VALUES('xyx', 2, 1);
001327    INSERT INTO t4 VALUES('uvw', 1, 1);
001328  }
001329  do_createtable_tests 4.7.1 -error {UNIQUE constraint failed: %s} {
001330    1    "INSERT INTO t1 VALUES(1, 'one')"             {{t1.a}}
001331    2    "INSERT INTO t1 VALUES(4.3, 'two')"           {{t1.a}}
001332    3    "INSERT INTO t1 VALUES('reveal', 'three')"    {{t1.a}}
001333    4    "INSERT INTO t1 VALUES(X'123456', 'four')"    {{t1.a}}
001334  
001335    5    "UPDATE t1 SET a = 1 WHERE rowid=2"           {{t1.a}}
001336    6    "UPDATE t1 SET a = 4.3 WHERE rowid=3"         {{t1.a}}
001337    7    "UPDATE t1 SET a = 'reveal' WHERE rowid=4"    {{t1.a}}
001338    8    "UPDATE t1 SET a = X'123456' WHERE rowid=1"   {{t1.a}}
001339  
001340    9    "INSERT INTO t4 VALUES('xyx', 1, 1)"          {{t4.a, t4.b, t4.c}}
001341    10   "INSERT INTO t4 VALUES('xyx', 2, 1)"          {{t4.a, t4.b, t4.c}}
001342    11   "INSERT INTO t4 VALUES('uvw', 1, 1)"          {{t4.a, t4.b, t4.c}}
001343  
001344    12   "UPDATE t4 SET a='xyx' WHERE rowid=3"         {{t4.a, t4.b, t4.c}}
001345    13   "UPDATE t4 SET b=1 WHERE rowid=2"             {{t4.a, t4.b, t4.c}}
001346    14   "UPDATE t4 SET a=0, b=0, c=0"                 {{t4.a, t4.b, t4.c}}
001347  }
001348  
001349  # EVIDENCE-OF: R-00404-17670 For the purposes of UNIQUE constraints,
001350  # NULL values are considered distinct from all other values, including
001351  # other NULLs.
001352  #
001353  do_createtable_tests 4.8 {
001354    1    "INSERT INTO t1 VALUES(NULL, NULL)"           {}
001355    2    "INSERT INTO t1 VALUES(NULL, NULL)"           {}
001356    3    "UPDATE t1 SET a = NULL"                      {}
001357    4    "UPDATE t1 SET b = NULL"                      {}
001358  
001359    5    "INSERT INTO t4 VALUES(NULL, NULL, NULL)"     {}
001360    6    "INSERT INTO t4 VALUES(NULL, NULL, NULL)"     {}
001361    7    "UPDATE t4 SET a = NULL"                      {}
001362    8    "UPDATE t4 SET b = NULL"                      {}
001363    9    "UPDATE t4 SET c = NULL"                      {}
001364  }
001365  
001366  # EVIDENCE-OF: R-55820-29984 In most cases, UNIQUE and PRIMARY KEY
001367  # constraints are implemented by creating a unique index in the
001368  # database.
001369  do_createtable_tests 4.9 -repair drop_all_tables -query {
001370    SELECT count(*) FROM sqlite_master WHERE type='index'
001371  } {
001372    1    "CREATE TABLE t1(a TEXT PRIMARY KEY, b)"              1
001373    2    "CREATE TABLE t1(a INTEGER PRIMARY KEY, b)"           0
001374    3    "CREATE TABLE t1(a TEXT UNIQUE, b)"                   1
001375    4    "CREATE TABLE t1(a PRIMARY KEY, b TEXT UNIQUE)"       2
001376    5    "CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(c, b))"  2
001377  }
001378  
001379  # Obsolete: R-02252-33116 Such an index is used like any other index
001380  # in the database to optimize queries.
001381  #
001382  do_execsql_test 4.10.0 {
001383    CREATE TABLE t1(a, b PRIMARY KEY);
001384    CREATE TABLE t2(a, b, c, UNIQUE(b, c));
001385  }
001386  do_createtable_tests 4.10 {
001387    1    "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" 
001388         {/*SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?)*/}
001389  
001390    2    "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
001391         {/*SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1*/}
001392  
001393    3    "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
001394         {/*SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?)*/}
001395  }
001396  
001397  # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a
001398  # column definition or specified as a table constraint. In practice it
001399  # makes no difference.
001400  #
001401  #   All the tests that deal with CHECK constraints below (4.11.* and 
001402  #   4.12.*) are run once for a table with the check constraint attached
001403  #   to a column definition, and once with a table where the check 
001404  #   condition is specified as a table constraint.
001405  #
001406  # EVIDENCE-OF: R-55435-14303 Each time a new row is inserted into the
001407  # table or an existing row is updated, the expression associated with
001408  # each CHECK constraint is evaluated and cast to a NUMERIC value in the
001409  # same way as a CAST expression. If the result is zero (integer value 0
001410  # or real value 0.0), then a constraint violation has occurred.
001411  #
001412  drop_all_tables
001413  do_execsql_test 4.11 {
001414    CREATE TABLE x1(a TEXT, b INTEGER CHECK( b>0 ));
001415    CREATE TABLE t1(a TEXT, b INTEGER, CHECK( b>0 ));
001416    INSERT INTO x1 VALUES('x', 'xx');
001417    INSERT INTO x1 VALUES('y', 'yy');
001418    INSERT INTO t1 SELECT * FROM x1;
001419  
001420    CREATE TABLE x2(a CHECK( a||b ), b);
001421    CREATE TABLE t2(a, b, CHECK( a||b ));
001422    INSERT INTO x2 VALUES(1, 'xx');
001423    INSERT INTO x2 VALUES(1, 'yy');
001424    INSERT INTO t2 SELECT * FROM x2;
001425  }
001426  
001427  do_createtable_tests 4.11 -error {CHECK constraint failed: %s} {
001428    1a    "INSERT INTO x1 VALUES('one', 0)"       {x1}
001429    1b    "INSERT INTO t1 VALUES('one', -4.0)"    {t1}
001430  
001431    2a    "INSERT INTO x2 VALUES('abc', 1)"       {x2}
001432    2b    "INSERT INTO t2 VALUES('abc', 1)"       {t2}
001433  
001434    3a    "INSERT INTO x2 VALUES(0, 'abc')"       {x2}
001435    3b    "INSERT INTO t2 VALUES(0, 'abc')"       {t2}
001436  
001437    4a    "UPDATE t1 SET b=-1 WHERE rowid=1"      {t1}
001438    4b    "UPDATE x1 SET b=-1 WHERE rowid=1"      {x1}
001439  
001440    4a    "UPDATE x2 SET a='' WHERE rowid=1"      {x2}
001441    4b    "UPDATE t2 SET a='' WHERE rowid=1"      {t2}
001442  }
001443  
001444  # EVIDENCE-OF: R-34109-39108 If the CHECK expression evaluates to NULL,
001445  # or any other non-zero value, it is not a constraint violation.
001446  #
001447  do_createtable_tests 4.12 {
001448    1a    "INSERT INTO x1 VALUES('one', NULL)"    {}
001449    1b    "INSERT INTO t1 VALUES('one', NULL)"    {}
001450  
001451    2a    "INSERT INTO x1 VALUES('one', 2)"    {}
001452    2b    "INSERT INTO t1 VALUES('one', 2)"    {}
001453  
001454    3a    "INSERT INTO x2 VALUES(1, 'abc')"       {}
001455    3b    "INSERT INTO t2 VALUES(1, 'abc')"       {}
001456  }
001457  
001458  # EVIDENCE-OF: R-02060-64547 A NOT NULL constraint may only be attached
001459  # to a column definition, not specified as a table constraint.
001460  #
001461  drop_all_tables
001462  do_createtable_tests 4.13.1 {
001463    1     "CREATE TABLE t1(a NOT NULL, b)"                               {}
001464    2     "CREATE TABLE t2(a PRIMARY KEY NOT NULL, b)"                   {}
001465    3     "CREATE TABLE t3(a NOT NULL, b NOT NULL, c NOT NULL UNIQUE)"   {}
001466  }
001467  do_createtable_tests 4.13.2 -error {
001468    near "NOT": syntax error
001469  } {
001470    1     "CREATE TABLE t4(a, b, NOT NULL(a))"                   {}
001471    2     "CREATE TABLE t4(a PRIMARY KEY, b, NOT NULL(a))"       {}
001472    3     "CREATE TABLE t4(a, b, c UNIQUE, NOT NULL(a, b, c))"   {}
001473  }
001474  
001475  # EVIDENCE-OF: R-31795-57643 a NOT NULL constraint dictates that the
001476  # associated column may not contain a NULL value. Attempting to set the
001477  # column value to NULL when inserting a new row or updating an existing
001478  # one causes a constraint violation.
001479  #
001480  #     These tests use the tables created by 4.13.
001481  #
001482  do_execsql_test 4.14.0 {
001483    INSERT INTO t1 VALUES('x', 'y');
001484    INSERT INTO t1 VALUES('z', NULL);
001485  
001486    INSERT INTO t2 VALUES('x', 'y');
001487    INSERT INTO t2 VALUES('z', NULL);
001488  
001489    INSERT INTO t3 VALUES('x', 'y', 'z');
001490    INSERT INTO t3 VALUES(1, 2, 3);
001491  }
001492  do_createtable_tests 4.14 -error {NOT NULL constraint failed: %s} {
001493    1    "INSERT INTO t1 VALUES(NULL, 'a')"         {t1.a}
001494    2    "INSERT INTO t2 VALUES(NULL, 'b')"         {t2.a}
001495    3    "INSERT INTO t3 VALUES('c', 'd', NULL)"    {t3.c}
001496    4    "INSERT INTO t3 VALUES('e', NULL, 'f')"    {t3.b}
001497    5    "INSERT INTO t3 VALUES(NULL, 'g', 'h')"    {t3.a}
001498  }
001499  
001500  # EVIDENCE-OF: R-42511-39459 PRIMARY KEY, UNIQUE and NOT NULL
001501  # constraints may be explicitly assigned a default conflict resolution
001502  # algorithm by including a conflict-clause in their definitions.
001503  #
001504  #     Conflict clauses: ABORT, ROLLBACK, IGNORE, FAIL, REPLACE
001505  #
001506  #     Test cases 4.15.*, 4.16.* and 4.17.* focus on PRIMARY KEY, NOT NULL
001507  #     and UNIQUE constraints, respectively.
001508  #
001509  drop_all_tables
001510  do_execsql_test 4.15.0 {
001511    CREATE TABLE t1_ab(a PRIMARY KEY ON CONFLICT ABORT, b);
001512    CREATE TABLE t1_ro(a PRIMARY KEY ON CONFLICT ROLLBACK, b);
001513    CREATE TABLE t1_ig(a PRIMARY KEY ON CONFLICT IGNORE, b);
001514    CREATE TABLE t1_fa(a PRIMARY KEY ON CONFLICT FAIL, b);
001515    CREATE TABLE t1_re(a PRIMARY KEY ON CONFLICT REPLACE, b);
001516    CREATE TABLE t1_xx(a PRIMARY KEY, b);
001517  
001518    INSERT INTO t1_ab VALUES(1, 'one');
001519    INSERT INTO t1_ab VALUES(2, 'two');
001520    INSERT INTO t1_ro SELECT * FROM t1_ab;
001521    INSERT INTO t1_ig SELECT * FROM t1_ab;
001522    INSERT INTO t1_fa SELECT * FROM t1_ab;
001523    INSERT INTO t1_re SELECT * FROM t1_ab;
001524    INSERT INTO t1_xx SELECT * FROM t1_ab;
001525  
001526    CREATE TABLE t2_ab(a, b NOT NULL ON CONFLICT ABORT);
001527    CREATE TABLE t2_ro(a, b NOT NULL ON CONFLICT ROLLBACK);
001528    CREATE TABLE t2_ig(a, b NOT NULL ON CONFLICT IGNORE);
001529    CREATE TABLE t2_fa(a, b NOT NULL ON CONFLICT FAIL);
001530    CREATE TABLE t2_re(a, b NOT NULL ON CONFLICT REPLACE);
001531    CREATE TABLE t2_xx(a, b NOT NULL);
001532  
001533    INSERT INTO t2_ab VALUES(1, 'one');
001534    INSERT INTO t2_ab VALUES(2, 'two');
001535    INSERT INTO t2_ro SELECT * FROM t2_ab;
001536    INSERT INTO t2_ig SELECT * FROM t2_ab;
001537    INSERT INTO t2_fa SELECT * FROM t2_ab;
001538    INSERT INTO t2_re SELECT * FROM t2_ab;
001539    INSERT INTO t2_xx SELECT * FROM t2_ab;
001540  
001541    CREATE TABLE t3_ab(a, b, UNIQUE(a, b) ON CONFLICT ABORT);
001542    CREATE TABLE t3_ro(a, b, UNIQUE(a, b) ON CONFLICT ROLLBACK);
001543    CREATE TABLE t3_ig(a, b, UNIQUE(a, b) ON CONFLICT IGNORE);
001544    CREATE TABLE t3_fa(a, b, UNIQUE(a, b) ON CONFLICT FAIL);
001545    CREATE TABLE t3_re(a, b, UNIQUE(a, b) ON CONFLICT REPLACE);
001546    CREATE TABLE t3_xx(a, b, UNIQUE(a, b));
001547  
001548    INSERT INTO t3_ab VALUES(1, 'one');
001549    INSERT INTO t3_ab VALUES(2, 'two');
001550    INSERT INTO t3_ro SELECT * FROM t3_ab;
001551    INSERT INTO t3_ig SELECT * FROM t3_ab;
001552    INSERT INTO t3_fa SELECT * FROM t3_ab;
001553    INSERT INTO t3_re SELECT * FROM t3_ab;
001554    INSERT INTO t3_xx SELECT * FROM t3_ab;
001555  }
001556  
001557  foreach {tn tbl res ac data} {
001558    1   t1_ab    {1 {UNIQUE constraint failed: t1_ab.a}} 0 {1 one 2 two 3 three}
001559    2   t1_ro    {1 {UNIQUE constraint failed: t1_ro.a}} 1 {1 one 2 two}
001560    3   t1_fa    {1 {UNIQUE constraint failed: t1_fa.a}} 0 {1 one 2 two 3 three 4 string}
001561    4   t1_ig    {0 {}} 0 {1 one 2 two 3 three 4 string 6 string}
001562    5   t1_re    {0 {}} 0 {1 one 2 two 4 string 3 string 6 string}
001563    6   t1_xx    {1 {UNIQUE constraint failed: t1_xx.a}} 0 {1 one 2 two 3 three}
001564  } {
001565    catchsql COMMIT
001566    do_execsql_test  4.15.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
001567  
001568    do_catchsql_test 4.15.$tn.2 " 
001569      INSERT INTO $tbl SELECT ((a%2)*a+3), 'string' FROM $tbl;
001570    " $res
001571  
001572    do_test e_createtable-4.15.$tn.3 { sqlite3_get_autocommit db } $ac
001573    do_execsql_test 4.15.$tn.4 "SELECT * FROM $tbl" $data
001574  }
001575  foreach {tn tbl res ac data} {
001576    1   t2_ab    {1 {NOT NULL constraint failed: t2_ab.b}} 0 {1 one 2 two 3 three}
001577    2   t2_ro    {1 {NOT NULL constraint failed: t2_ro.b}} 1 {1 one 2 two}
001578    3   t2_fa    {1 {NOT NULL constraint failed: t2_fa.b}} 0 {1 one 2 two 3 three 4 xx}
001579    4   t2_ig    {0 {}} 0 {1 one 2 two 3 three 4 xx 6 xx}
001580    5   t2_re    {1 {NOT NULL constraint failed: t2_re.b}} 0 {1 one 2 two 3 three}
001581    6   t2_xx    {1 {NOT NULL constraint failed: t2_xx.b}} 0 {1 one 2 two 3 three}
001582  } {
001583    catchsql COMMIT
001584    do_execsql_test  4.16.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
001585  
001586    do_catchsql_test 4.16.$tn.2 " 
001587      INSERT INTO $tbl SELECT a+3, CASE a WHEN 2 THEN NULL ELSE 'xx' END FROM $tbl
001588    " $res
001589  
001590    do_test e_createtable-4.16.$tn.3 { sqlite3_get_autocommit db } $ac
001591    do_execsql_test 4.16.$tn.4 "SELECT * FROM $tbl" $data
001592  }
001593  foreach {tn tbl res ac data} {
001594    1   t3_ab    {1 {UNIQUE constraint failed: t3_ab.a, t3_ab.b}}
001595                 0 {1 one 2 two 3 three}
001596    2   t3_ro    {1 {UNIQUE constraint failed: t3_ro.a, t3_ro.b}}
001597                 1 {1 one 2 two}
001598    3   t3_fa    {1 {UNIQUE constraint failed: t3_fa.a, t3_fa.b}}
001599                 0 {1 one 2 two 3 three 4 three}
001600    4   t3_ig    {0 {}} 0 {1 one 2 two 3 three 4 three 6 three}
001601    5   t3_re    {0 {}} 0 {1 one 2 two 4 three 3 three 6 three}
001602    6   t3_xx    {1 {UNIQUE constraint failed: t3_xx.a, t3_xx.b}}
001603                 0 {1 one 2 two 3 three}
001604  } {
001605    catchsql COMMIT
001606    do_execsql_test  4.17.$tn.1 "BEGIN; INSERT INTO $tbl VALUES(3, 'three')"
001607  
001608    do_catchsql_test 4.17.$tn.2 " 
001609      INSERT INTO $tbl SELECT ((a%2)*a+3), 'three' FROM $tbl
001610    " $res
001611  
001612    do_test e_createtable-4.17.$tn.3 { sqlite3_get_autocommit db } $ac
001613    do_execsql_test 4.17.$tn.4 "SELECT * FROM $tbl ORDER BY rowid" $data
001614  }
001615  catchsql COMMIT
001616  
001617  # EVIDENCE-OF: R-12645-39772 Or, if a constraint definition does not
001618  # include a conflict-clause or it is a CHECK constraint, the default
001619  # conflict resolution algorithm is ABORT.
001620  #
001621  #     The first half of the above is tested along with explicit ON 
001622  #     CONFLICT clauses above (specifically, the tests involving t1_xx, t2_xx
001623  #     and t3_xx). The following just tests that the default conflict
001624  #     handling for CHECK constraints is ABORT.
001625  #
001626  do_execsql_test 4.18.1 {
001627    CREATE TABLE t4(a, b CHECK (b!=10));
001628    INSERT INTO t4 VALUES(1, 2);
001629    INSERT INTO t4 VALUES(3, 4);
001630  }
001631  do_execsql_test  4.18.2 { BEGIN; INSERT INTO t4 VALUES(5, 6) }
001632  do_catchsql_test 4.18.3 { 
001633    INSERT INTO t4 SELECT a+4, b+4 FROM t4
001634  } {1 {CHECK constraint failed: t4}}
001635  do_test e_createtable-4.18.4 { sqlite3_get_autocommit db } 0
001636  do_execsql_test 4.18.5 { SELECT * FROM t4 } {1 2 3 4 5 6}
001637  
001638  # EVIDENCE-OF: R-19114-56113 Different constraints within the same table
001639  # may have different default conflict resolution algorithms.
001640  #
001641  do_execsql_test 4.19.0 {
001642    CREATE TABLE t5(a NOT NULL ON CONFLICT IGNORE, b NOT NULL ON CONFLICT ABORT);
001643  }
001644  do_catchsql_test 4.19.1 { INSERT INTO t5 VALUES(NULL, 'not null') } {0 {}}
001645  do_execsql_test  4.19.2 { SELECT * FROM t5 } {}
001646  do_catchsql_test 4.19.3 { INSERT INTO t5 VALUES('not null', NULL) } \
001647    {1 {NOT NULL constraint failed: t5.b}}
001648  do_execsql_test  4.19.4 { SELECT * FROM t5 } {}
001649  
001650  #------------------------------------------------------------------------
001651  # Tests for INTEGER PRIMARY KEY and rowid related statements.
001652  #
001653  
001654  # EVIDENCE-OF: R-52584-04009 The rowid value can be accessed using one
001655  # of the special case-independent names "rowid", "oid", or "_rowid_" in
001656  # place of a column name.
001657  #
001658  # EVIDENCE-OF: R-06726-07466 A column name can be any of the names
001659  # defined in the CREATE TABLE statement or one of the following special
001660  # identifiers: "ROWID", "OID", or "_ROWID_".
001661  #
001662  drop_all_tables
001663  do_execsql_test 5.1.0 {
001664    CREATE TABLE t1(x, y);
001665    INSERT INTO t1 VALUES('one', 'first');
001666    INSERT INTO t1 VALUES('two', 'second');
001667    INSERT INTO t1 VALUES('three', 'third');
001668  }
001669  do_createtable_tests 5.1 {
001670    1   "SELECT rowid FROM t1"        {1 2 3}
001671    2   "SELECT oid FROM t1"          {1 2 3}
001672    3   "SELECT _rowid_ FROM t1"      {1 2 3}
001673    4   "SELECT ROWID FROM t1"        {1 2 3}
001674    5   "SELECT OID FROM t1"          {1 2 3}
001675    6   "SELECT _ROWID_ FROM t1"      {1 2 3}
001676    7   "SELECT RoWiD FROM t1"        {1 2 3}
001677    8   "SELECT OiD FROM t1"          {1 2 3}
001678    9   "SELECT _RoWiD_ FROM t1"      {1 2 3}
001679  }
001680  
001681  # EVIDENCE-OF: R-26501-17306 If a table contains a user defined column
001682  # named "rowid", "oid" or "_rowid_", then that name always refers the
001683  # explicitly declared column and cannot be used to retrieve the integer
001684  # rowid value.
001685  #
001686  # EVIDENCE-OF: R-44615-33286 The special identifiers only refer to the
001687  # row key if the CREATE TABLE statement does not define a real column
001688  # with the same name.
001689  #
001690  do_execsql_test 5.2.0 {
001691    CREATE TABLE t2(oid, b);
001692    CREATE TABLE t3(a, _rowid_);
001693    CREATE TABLE t4(a, b, rowid);
001694  
001695    INSERT INTO t2 VALUES('one', 'two');
001696    INSERT INTO t2 VALUES('three', 'four');
001697  
001698    INSERT INTO t3 VALUES('five', 'six');
001699    INSERT INTO t3 VALUES('seven', 'eight');
001700  
001701    INSERT INTO t4 VALUES('nine', 'ten', 'eleven');
001702    INSERT INTO t4 VALUES('twelve', 'thirteen', 'fourteen');
001703  }
001704  do_createtable_tests 5.2 {
001705    1   "SELECT oid, rowid, _rowid_ FROM t2"   {one 1 1      three 2 2}
001706    2   "SELECT oid, rowid, _rowid_ FROM t3"   {1 1 six      2 2 eight} 
001707    3   "SELECT oid, rowid, _rowid_ FROM t4"   {1 eleven 1   2 fourteen 2}
001708  }
001709  
001710  
001711  # Argument $tbl is the name of a table in the database. Argument $col is
001712  # the name of one of the tables columns. Return 1 if $col is an alias for
001713  # the rowid, or 0 otherwise.
001714  #
001715  proc is_integer_primary_key {tbl col} {
001716    lindex [db eval [subst {
001717      DELETE FROM $tbl;
001718      INSERT INTO $tbl ($col) VALUES(0);
001719      SELECT (rowid==$col) FROM $tbl;
001720      DELETE FROM $tbl;
001721    }]] 0
001722  }
001723  
001724  # EVIDENCE-OF: R-47901-33947 With one exception noted below, if a rowid
001725  # table has a primary key that consists of a single column and the
001726  # declared type of that column is "INTEGER" in any mixture of upper and
001727  # lower case, then the column becomes an alias for the rowid.
001728  #
001729  # EVIDENCE-OF: R-45951-08347 if the declaration of a column with
001730  # declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does
001731  # not become an alias for the rowid and is not classified as an integer
001732  # primary key.
001733  #
001734  do_createtable_tests 5.3 -tclquery { 
001735    is_integer_primary_key t5 pk
001736  } -repair {
001737    catchsql { DROP TABLE t5 }
001738  } {
001739    1   "CREATE TABLE t5(pk integer primary key)"                         1
001740    2   "CREATE TABLE t5(pk integer, primary key(pk))"                    1
001741    3   "CREATE TABLE t5(pk integer, v integer, primary key(pk))"         1
001742    4   "CREATE TABLE t5(pk integer, v integer, primary key(pk, v))"      0
001743    5   "CREATE TABLE t5(pk int, v integer, primary key(pk, v))"          0
001744    6   "CREATE TABLE t5(pk int, v integer, primary key(pk))"             0
001745    7   "CREATE TABLE t5(pk int primary key, v integer)"                  0
001746    8   "CREATE TABLE t5(pk inTEger primary key)"                         1
001747    9   "CREATE TABLE t5(pk inteGEr, primary key(pk))"                    1
001748    10  "CREATE TABLE t5(pk INTEGER, v integer, primary key(pk))"         1
001749  }
001750  
001751  # EVIDENCE-OF: R-41444-49665 Other integer type names like "INT" or
001752  # "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary
001753  # key column to behave as an ordinary table column with integer affinity
001754  # and a unique index, not as an alias for the rowid.
001755  #
001756  do_execsql_test 5.4.1 {
001757    CREATE TABLE t6(pk INT primary key);
001758    CREATE TABLE t7(pk BIGINT primary key);
001759    CREATE TABLE t8(pk SHORT INTEGER primary key);
001760    CREATE TABLE t9(pk UNSIGNED INTEGER primary key);
001761  } 
001762  do_test e_createtable-5.4.2.1 { is_integer_primary_key t6 pk } 0
001763  do_test e_createtable-5.4.2.2 { is_integer_primary_key t7 pk } 0
001764  do_test e_createtable-5.4.2.3 { is_integer_primary_key t8 pk } 0
001765  do_test e_createtable-5.4.2.4 { is_integer_primary_key t9 pk } 0
001766  
001767  do_execsql_test 5.4.3 {
001768    INSERT INTO t6 VALUES('2.0');
001769    INSERT INTO t7 VALUES('2.0');
001770    INSERT INTO t8 VALUES('2.0');
001771    INSERT INTO t9 VALUES('2.0');
001772    SELECT typeof(pk), pk FROM t6;
001773    SELECT typeof(pk), pk FROM t7;
001774    SELECT typeof(pk), pk FROM t8;
001775    SELECT typeof(pk), pk FROM t9;
001776  } {integer 2 integer 2 integer 2 integer 2}
001777  
001778  do_catchsql_test 5.4.4.1 { 
001779    INSERT INTO t6 VALUES(2) 
001780  } {1 {UNIQUE constraint failed: t6.pk}}
001781  do_catchsql_test 5.4.4.2 { 
001782    INSERT INTO t7 VALUES(2) 
001783  } {1 {UNIQUE constraint failed: t7.pk}}
001784  do_catchsql_test 5.4.4.3 { 
001785    INSERT INTO t8 VALUES(2) 
001786  } {1 {UNIQUE constraint failed: t8.pk}}
001787  do_catchsql_test 5.4.4.4 { 
001788    INSERT INTO t9 VALUES(2) 
001789  } {1 {UNIQUE constraint failed: t9.pk}}
001790  
001791  # EVIDENCE-OF: R-56094-57830 the following three table declarations all
001792  # cause the column "x" to be an alias for the rowid (an integer primary
001793  # key): CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); CREATE TABLE
001794  # t(x INTEGER, y, z, PRIMARY KEY(x ASC)); CREATE TABLE t(x INTEGER, y,
001795  # z, PRIMARY KEY(x DESC));
001796  #
001797  # EVIDENCE-OF: R-20149-25884 the following declaration does not result
001798  # in "x" being an alias for the rowid: CREATE TABLE t(x INTEGER PRIMARY
001799  # KEY DESC, y, z);
001800  #
001801  do_createtable_tests 5 -tclquery { 
001802    is_integer_primary_key t x
001803  } -repair {
001804    catchsql { DROP TABLE t }
001805  } {
001806    5.1    "CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z)"      1
001807    5.2    "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC))"  1
001808    5.3    "CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC))" 1
001809    6.1    "CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z)"     0
001810  }
001811  
001812  # EVIDENCE-OF: R-03733-29734 Rowid values may be modified using an
001813  # UPDATE statement in the same way as any other column value can, either
001814  # using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by
001815  # using an alias created by an integer primary key.
001816  #
001817  do_execsql_test 5.7.0 {
001818    CREATE TABLE t10(a, b);
001819    INSERT INTO t10 VALUES('ten', 10);
001820  
001821    CREATE TABLE t11(a, b INTEGER PRIMARY KEY);
001822    INSERT INTO t11 VALUES('ten', 10);
001823  }
001824  do_createtable_tests 5.7.1 -query { 
001825    SELECT rowid, _rowid_, oid FROM t10;
001826  } {
001827    1    "UPDATE t10 SET rowid = 5"   {5 5 5}
001828    2    "UPDATE t10 SET _rowid_ = 6" {6 6 6}
001829    3    "UPDATE t10 SET oid = 7"     {7 7 7}
001830  }
001831  do_createtable_tests 5.7.2 -query { 
001832    SELECT rowid, _rowid_, oid, b FROM t11;
001833  } {
001834    1    "UPDATE t11 SET rowid = 5"   {5 5 5 5}
001835    2    "UPDATE t11 SET _rowid_ = 6" {6 6 6 6}
001836    3    "UPDATE t11 SET oid = 7"     {7 7 7 7}
001837    4    "UPDATE t11 SET b = 8"       {8 8 8 8}
001838  }
001839  
001840  # EVIDENCE-OF: R-58706-14229 Similarly, an INSERT statement may provide
001841  # a value to use as the rowid for each row inserted.
001842  #
001843  do_createtable_tests 5.8.1 -query { 
001844    SELECT rowid, _rowid_, oid FROM t10;
001845  } -repair { 
001846    execsql { DELETE FROM t10 } 
001847  } {
001848    1    "INSERT INTO t10(oid) VALUES(15)"           {15 15 15}
001849    2    "INSERT INTO t10(rowid) VALUES(16)"         {16 16 16}
001850    3    "INSERT INTO t10(_rowid_) VALUES(17)"       {17 17 17}
001851    4    "INSERT INTO t10(a, b, oid) VALUES(1,2,3)"  {3 3 3}
001852  }
001853  do_createtable_tests 5.8.2 -query { 
001854    SELECT rowid, _rowid_, oid, b FROM t11;
001855  } -repair { 
001856    execsql { DELETE FROM t11 } 
001857  } {
001858    1    "INSERT INTO t11(oid) VALUES(15)"           {15 15 15 15}
001859    2    "INSERT INTO t11(rowid) VALUES(16)"         {16 16 16 16}
001860    3    "INSERT INTO t11(_rowid_) VALUES(17)"       {17 17 17 17}
001861    4    "INSERT INTO t11(a, b) VALUES(1,2)"         {2 2 2 2}
001862  }
001863  
001864  # EVIDENCE-OF: R-32326-44592 Unlike normal SQLite columns, an integer
001865  # primary key or rowid column must contain integer values. Integer
001866  # primary key or rowid columns are not able to hold floating point
001867  # values, strings, BLOBs, or NULLs.
001868  #
001869  #     This is considered by the tests for the following 3 statements,
001870  #     which show that:
001871  #
001872  #       1. Attempts to UPDATE a rowid column to a non-integer value fail,
001873  #       2. Attempts to INSERT a real, string or blob value into a rowid 
001874  #          column fail, and
001875  #       3. Attempting to INSERT a NULL value into a rowid column causes the
001876  #          system to automatically select an integer value to use.
001877  #
001878  
001879  
001880  # EVIDENCE-OF: R-64224-62578 If an UPDATE statement attempts to set an
001881  # integer primary key or rowid column to a NULL or blob value, or to a
001882  # string or real value that cannot be losslessly converted to an
001883  # integer, a "datatype mismatch" error occurs and the statement is
001884  # aborted.
001885  #
001886  drop_all_tables
001887  do_execsql_test 5.9.0 {
001888    CREATE TABLE t12(x INTEGER PRIMARY KEY, y);
001889    INSERT INTO t12 VALUES(5, 'five');
001890  }
001891  do_createtable_tests 5.9.1 -query { SELECT typeof(x), x FROM t12 } {
001892    1   "UPDATE t12 SET x = 4"       {integer 4}
001893    2   "UPDATE t12 SET x = 10.0"    {integer 10}
001894    3   "UPDATE t12 SET x = '12.0'"  {integer 12}
001895    4   "UPDATE t12 SET x = '-15.0'" {integer -15}
001896  }
001897  do_createtable_tests 5.9.2 -error {
001898    datatype mismatch
001899  } {
001900    1   "UPDATE t12 SET x = 4.1"         {}
001901    2   "UPDATE t12 SET x = 'hello'"     {}
001902    3   "UPDATE t12 SET x = NULL"        {}
001903    4   "UPDATE t12 SET x = X'ABCD'"     {}
001904    5   "UPDATE t12 SET x = X'3900'"     {}
001905    6   "UPDATE t12 SET x = X'39'"       {}
001906  }
001907  
001908  # EVIDENCE-OF: R-05734-13629 If an INSERT statement attempts to insert a
001909  # blob value, or a string or real value that cannot be losslessly
001910  # converted to an integer into an integer primary key or rowid column, a
001911  # "datatype mismatch" error occurs and the statement is aborted.
001912  #
001913  do_execsql_test 5.10.0 { DELETE FROM t12 }
001914  do_createtable_tests 5.10.1 -error { 
001915    datatype mismatch
001916  } {
001917    1   "INSERT INTO t12(x) VALUES(4.1)"     {}
001918    2   "INSERT INTO t12(x) VALUES('hello')" {}
001919    3   "INSERT INTO t12(x) VALUES(X'ABCD')" {}
001920    4   "INSERT INTO t12(x) VALUES(X'3900')" {}
001921    5   "INSERT INTO t12(x) VALUES(X'39')"   {}
001922  }
001923  do_createtable_tests 5.10.2 -query { 
001924    SELECT typeof(x), x FROM t12 
001925  } -repair {
001926    execsql { DELETE FROM t12 }
001927  } {
001928    1   "INSERT INTO t12(x) VALUES(4)"       {integer 4}
001929    2   "INSERT INTO t12(x) VALUES(10.0)"    {integer 10}
001930    3   "INSERT INTO t12(x) VALUES('12.0')"  {integer 12}
001931    4   "INSERT INTO t12(x) VALUES('4e3')"   {integer 4000}
001932    5   "INSERT INTO t12(x) VALUES('-14.0')" {integer -14}
001933  }
001934  
001935  # EVIDENCE-OF: R-07986-46024 If an INSERT statement attempts to insert a
001936  # NULL value into a rowid or integer primary key column, the system
001937  # chooses an integer value to use as the rowid automatically.
001938  #
001939  do_execsql_test 5.11.0 { DELETE FROM t12 }
001940  do_createtable_tests 5.11 -query { 
001941    SELECT typeof(x), x FROM t12 WHERE y IS (SELECT max(y) FROM t12)
001942  } {
001943    1   "INSERT INTO t12 DEFAULT VALUES"                {integer 1}
001944    2   "INSERT INTO t12(y)   VALUES(5)"                {integer 2}
001945    3   "INSERT INTO t12(x,y) VALUES(NULL, 10)"         {integer 3}
001946    4   "INSERT INTO t12(x,y) SELECT NULL, 15 FROM t12" 
001947        {integer 4 integer 5 integer 6}
001948    5   "INSERT INTO t12(y) SELECT 20 FROM t12 LIMIT 3"
001949        {integer 7 integer 8 integer 9}
001950  }
001951  
001952  finish_test