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