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