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