1# 2004 November 10 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# This file implements regression tests for SQLite library. The 12# focus of this script is testing the ALTER TABLE statement. 13# 14# $Id: alter.test,v 1.27 2007/10/23 15:39:46 drh Exp $ 15# 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 21ifcapable !altertable { 22 finish_test 23 return 24} 25 26#---------------------------------------------------------------------- 27# Test organization: 28# 29# alter-1.1.* - alter-1.7.*: Basic tests of ALTER TABLE, including tables 30# with implicit and explicit indices. These tests came from an earlier 31# fork of SQLite that also supported ALTER TABLE. 32# alter-1.8.*: Tests for ALTER TABLE when the table resides in an 33# attached database. 34# alter-1.9.*: Tests for ALTER TABLE when their is whitespace between the 35# table name and left parenthesis token. i.e: 36# "CREATE TABLE abc (a, b, c);" 37# alter-2.*: Test error conditions and messages. 38# alter-3.*: Test ALTER TABLE on tables that have TRIGGERs attached to them. 39# alter-4.*: Test ALTER TABLE on tables that have AUTOINCREMENT fields. 40# 41 42# Create some tables to rename. Be sure to include some TEMP tables 43# and some tables with odd names. 44# 45do_test alter-1.1 { 46 ifcapable tempdb { 47 set ::temp TEMP 48 } else { 49 set ::temp {} 50 } 51 execsql [subst -nocommands { 52 CREATE TABLE t1(a,b); 53 INSERT INTO t1 VALUES(1,2); 54 CREATE TABLE [t1'x1](c UNIQUE, b PRIMARY KEY); 55 INSERT INTO [t1'x1] VALUES(3,4); 56 CREATE INDEX t1i1 ON T1(B); 57 CREATE INDEX t1i2 ON t1(a,b); 58 CREATE INDEX i3 ON [t1'x1](b,c); 59 CREATE $::temp TABLE "temp table"(e,f,g UNIQUE); 60 CREATE INDEX i2 ON [temp table](f); 61 INSERT INTO [temp table] VALUES(5,6,7); 62 }] 63 execsql { 64 SELECT 't1', * FROM t1; 65 SELECT 't1''x1', * FROM "t1'x1"; 66 SELECT * FROM [temp table]; 67 } 68} {t1 1 2 t1'x1 3 4 5 6 7} 69do_test alter-1.2 { 70 execsql [subst { 71 CREATE $::temp TABLE objlist(type, name, tbl_name); 72 INSERT INTO objlist SELECT type, name, tbl_name 73 FROM sqlite_master WHERE NAME!='objlist'; 74 }] 75 ifcapable tempdb { 76 execsql { 77 INSERT INTO objlist SELECT type, name, tbl_name 78 FROM sqlite_temp_master WHERE NAME!='objlist'; 79 } 80 } 81 82 execsql { 83 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name; 84 } 85} [list \ 86 table t1 t1 \ 87 index t1i1 t1 \ 88 index t1i2 t1 \ 89 table t1'x1 t1'x1 \ 90 index i3 t1'x1 \ 91 index {sqlite_autoindex_t1'x1_1} t1'x1 \ 92 index {sqlite_autoindex_t1'x1_2} t1'x1 \ 93 table {temp table} {temp table} \ 94 index i2 {temp table} \ 95 index {sqlite_autoindex_temp table_1} {temp table} \ 96 ] 97 98# Make some changes 99# 100integrity_check alter-1.3.0 101do_test alter-1.3 { 102 execsql { 103 ALTER TABLE [T1] RENAME to [-t1-]; 104 ALTER TABLE "t1'x1" RENAME TO T2; 105 ALTER TABLE [temp table] RENAME to TempTab; 106 } 107} {} 108integrity_check alter-1.3.1 109do_test alter-1.4 { 110 execsql { 111 SELECT 't1', * FROM [-t1-]; 112 SELECT 't2', * FROM t2; 113 SELECT * FROM temptab; 114 } 115} {t1 1 2 t2 3 4 5 6 7} 116do_test alter-1.5 { 117 execsql { 118 DELETE FROM objlist; 119 INSERT INTO objlist SELECT type, name, tbl_name 120 FROM sqlite_master WHERE NAME!='objlist'; 121 } 122 catchsql { 123 INSERT INTO objlist SELECT type, name, tbl_name 124 FROM sqlite_temp_master WHERE NAME!='objlist'; 125 } 126 execsql { 127 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name; 128 } 129} [list \ 130 table -t1- -t1- \ 131 index t1i1 -t1- \ 132 index t1i2 -t1- \ 133 table T2 T2 \ 134 index i3 T2 \ 135 index {sqlite_autoindex_T2_1} T2 \ 136 index {sqlite_autoindex_T2_2} T2 \ 137 table {TempTab} {TempTab} \ 138 index i2 {TempTab} \ 139 index {sqlite_autoindex_TempTab_1} {TempTab} \ 140 ] 141 142# Make sure the changes persist after restarting the database. 143# (The TEMP table will not persist, of course.) 144# 145ifcapable tempdb { 146 do_test alter-1.6 { 147 db close 148 sqlite3 db test.db 149 set DB [sqlite3_connection_pointer db] 150 execsql { 151 CREATE TEMP TABLE objlist(type, name, tbl_name); 152 INSERT INTO objlist SELECT type, name, tbl_name FROM sqlite_master; 153 INSERT INTO objlist 154 SELECT type, name, tbl_name FROM sqlite_temp_master 155 WHERE NAME!='objlist'; 156 SELECT type, name, tbl_name FROM objlist 157 ORDER BY tbl_name, type desc, name; 158 } 159 } [list \ 160 table -t1- -t1- \ 161 index t1i1 -t1- \ 162 index t1i2 -t1- \ 163 table T2 T2 \ 164 index i3 T2 \ 165 index {sqlite_autoindex_T2_1} T2 \ 166 index {sqlite_autoindex_T2_2} T2 \ 167 ] 168} else { 169 execsql { 170 DROP TABLE TempTab; 171 } 172} 173 174# Make sure the ALTER TABLE statements work with the 175# non-callback API 176# 177do_test alter-1.7 { 178 stepsql $DB { 179 ALTER TABLE [-t1-] RENAME to [*t1*]; 180 ALTER TABLE T2 RENAME TO [<t2>]; 181 } 182 execsql { 183 DELETE FROM objlist; 184 INSERT INTO objlist SELECT type, name, tbl_name 185 FROM sqlite_master WHERE NAME!='objlist'; 186 } 187 catchsql { 188 INSERT INTO objlist SELECT type, name, tbl_name 189 FROM sqlite_temp_master WHERE NAME!='objlist'; 190 } 191 execsql { 192 SELECT type, name, tbl_name FROM objlist ORDER BY tbl_name, type desc, name; 193 } 194} [list \ 195 table *t1* *t1* \ 196 index t1i1 *t1* \ 197 index t1i2 *t1* \ 198 table <t2> <t2> \ 199 index i3 <t2> \ 200 index {sqlite_autoindex_<t2>_1} <t2> \ 201 index {sqlite_autoindex_<t2>_2} <t2> \ 202 ] 203 204# Check that ALTER TABLE works on attached databases. 205# 206ifcapable attach { 207 do_test alter-1.8.1 { 208 file delete -force test2.db 209 file delete -force test2.db-journal 210 execsql { 211 ATTACH 'test2.db' AS aux; 212 } 213 } {} 214 do_test alter-1.8.2 { 215 execsql { 216 CREATE TABLE t4(a PRIMARY KEY, b, c); 217 CREATE TABLE aux.t4(a PRIMARY KEY, b, c); 218 CREATE INDEX i4 ON t4(b); 219 CREATE INDEX aux.i4 ON t4(b); 220 } 221 } {} 222 do_test alter-1.8.3 { 223 execsql { 224 INSERT INTO t4 VALUES('main', 'main', 'main'); 225 INSERT INTO aux.t4 VALUES('aux', 'aux', 'aux'); 226 SELECT * FROM t4 WHERE a = 'main'; 227 } 228 } {main main main} 229 do_test alter-1.8.4 { 230 execsql { 231 ALTER TABLE t4 RENAME TO t5; 232 SELECT * FROM t4 WHERE a = 'aux'; 233 } 234 } {aux aux aux} 235 do_test alter-1.8.5 { 236 execsql { 237 SELECT * FROM t5; 238 } 239 } {main main main} 240 do_test alter-1.8.6 { 241 execsql { 242 SELECT * FROM t5 WHERE b = 'main'; 243 } 244 } {main main main} 245 do_test alter-1.8.7 { 246 execsql { 247 ALTER TABLE aux.t4 RENAME TO t5; 248 SELECT * FROM aux.t5 WHERE b = 'aux'; 249 } 250 } {aux aux aux} 251} 252 253do_test alter-1.9.1 { 254 execsql { 255 CREATE TABLE tbl1 (a, b, c); 256 INSERT INTO tbl1 VALUES(1, 2, 3); 257 } 258} {} 259do_test alter-1.9.2 { 260 execsql { 261 SELECT * FROM tbl1; 262 } 263} {1 2 3} 264do_test alter-1.9.3 { 265 execsql { 266 ALTER TABLE tbl1 RENAME TO tbl2; 267 SELECT * FROM tbl2; 268 } 269} {1 2 3} 270do_test alter-1.9.4 { 271 execsql { 272 DROP TABLE tbl2; 273 } 274} {} 275 276# Test error messages 277# 278do_test alter-2.1 { 279 catchsql { 280 ALTER TABLE none RENAME TO hi; 281 } 282} {1 {no such table: none}} 283do_test alter-2.2 { 284 execsql { 285 CREATE TABLE t3(p,q,r); 286 } 287 catchsql { 288 ALTER TABLE [<t2>] RENAME TO t3; 289 } 290} {1 {there is already another table or index with this name: t3}} 291do_test alter-2.3 { 292 catchsql { 293 ALTER TABLE [<t2>] RENAME TO i3; 294 } 295} {1 {there is already another table or index with this name: i3}} 296do_test alter-2.4 { 297 catchsql { 298 ALTER TABLE SqLiTe_master RENAME TO master; 299 } 300} {1 {table sqlite_master may not be altered}} 301do_test alter-2.5 { 302 catchsql { 303 ALTER TABLE t3 RENAME TO sqlite_t3; 304 } 305} {1 {object name reserved for internal use: sqlite_t3}} 306do_test alter-2.6 { 307 catchsql { 308 ALTER TABLE t3 ADD COLUMN (ALTER TABLE t3 ADD COLUMN); 309 } 310} {1 {near "(": syntax error}} 311 312# If this compilation does not include triggers, omit the alter-3.* tests. 313ifcapable trigger { 314 315#----------------------------------------------------------------------- 316# Tests alter-3.* test ALTER TABLE on tables that have triggers. 317# 318# alter-3.1.*: ALTER TABLE with triggers. 319# alter-3.2.*: Test that the ON keyword cannot be used as a database, 320# table or column name unquoted. This is done because part of the 321# ALTER TABLE code (specifically the implementation of SQL function 322# "sqlite_alter_trigger") will break in this case. 323# alter-3.3.*: ALTER TABLE with TEMP triggers (todo). 324# 325 326# An SQL user-function for triggers to fire, so that we know they 327# are working. 328proc trigfunc {args} { 329 set ::TRIGGER $args 330} 331db func trigfunc trigfunc 332 333do_test alter-3.1.0 { 334 execsql { 335 CREATE TABLE t6(a, b, c); 336 CREATE TRIGGER trig1 AFTER INSERT ON t6 BEGIN 337 SELECT trigfunc('trig1', new.a, new.b, new.c); 338 END; 339 } 340} {} 341do_test alter-3.1.1 { 342 execsql { 343 INSERT INTO t6 VALUES(1, 2, 3); 344 } 345 set ::TRIGGER 346} {trig1 1 2 3} 347do_test alter-3.1.2 { 348 execsql { 349 ALTER TABLE t6 RENAME TO t7; 350 INSERT INTO t7 VALUES(4, 5, 6); 351 } 352 set ::TRIGGER 353} {trig1 4 5 6} 354do_test alter-3.1.3 { 355 execsql { 356 DROP TRIGGER trig1; 357 } 358} {} 359do_test alter-3.1.4 { 360 execsql { 361 CREATE TRIGGER trig2 AFTER INSERT ON main.t7 BEGIN 362 SELECT trigfunc('trig2', new.a, new.b, new.c); 363 END; 364 INSERT INTO t7 VALUES(1, 2, 3); 365 } 366 set ::TRIGGER 367} {trig2 1 2 3} 368do_test alter-3.1.5 { 369 execsql { 370 ALTER TABLE t7 RENAME TO t8; 371 INSERT INTO t8 VALUES(4, 5, 6); 372 } 373 set ::TRIGGER 374} {trig2 4 5 6} 375do_test alter-3.1.6 { 376 execsql { 377 DROP TRIGGER trig2; 378 } 379} {} 380do_test alter-3.1.7 { 381 execsql { 382 CREATE TRIGGER trig3 AFTER INSERT ON main.'t8'BEGIN 383 SELECT trigfunc('trig3', new.a, new.b, new.c); 384 END; 385 INSERT INTO t8 VALUES(1, 2, 3); 386 } 387 set ::TRIGGER 388} {trig3 1 2 3} 389do_test alter-3.1.8 { 390 execsql { 391 ALTER TABLE t8 RENAME TO t9; 392 INSERT INTO t9 VALUES(4, 5, 6); 393 } 394 set ::TRIGGER 395} {trig3 4 5 6} 396 397# Make sure "ON" cannot be used as a database, table or column name without 398# quoting. Otherwise the sqlite_alter_trigger() function might not work. 399file delete -force test3.db 400file delete -force test3.db-journal 401ifcapable attach { 402 do_test alter-3.2.1 { 403 catchsql { 404 ATTACH 'test3.db' AS ON; 405 } 406 } {1 {near "ON": syntax error}} 407 do_test alter-3.2.2 { 408 catchsql { 409 ATTACH 'test3.db' AS 'ON'; 410 } 411 } {0 {}} 412 do_test alter-3.2.3 { 413 catchsql { 414 CREATE TABLE ON.t1(a, b, c); 415 } 416 } {1 {near "ON": syntax error}} 417 do_test alter-3.2.4 { 418 catchsql { 419 CREATE TABLE 'ON'.t1(a, b, c); 420 } 421 } {0 {}} 422 do_test alter-3.2.4 { 423 catchsql { 424 CREATE TABLE 'ON'.ON(a, b, c); 425 } 426 } {1 {near "ON": syntax error}} 427 do_test alter-3.2.5 { 428 catchsql { 429 CREATE TABLE 'ON'.'ON'(a, b, c); 430 } 431 } {0 {}} 432} 433do_test alter-3.2.6 { 434 catchsql { 435 CREATE TABLE t10(a, ON, c); 436 } 437} {1 {near "ON": syntax error}} 438do_test alter-3.2.7 { 439 catchsql { 440 CREATE TABLE t10(a, 'ON', c); 441 } 442} {0 {}} 443do_test alter-3.2.8 { 444 catchsql { 445 CREATE TRIGGER trig4 AFTER INSERT ON ON BEGIN SELECT 1; END; 446 } 447} {1 {near "ON": syntax error}} 448ifcapable attach { 449 do_test alter-3.2.9 { 450 catchsql { 451 CREATE TRIGGER 'on'.trig4 AFTER INSERT ON 'ON' BEGIN SELECT 1; END; 452 } 453 } {0 {}} 454} 455do_test alter-3.2.10 { 456 execsql { 457 DROP TABLE t10; 458 } 459} {} 460 461do_test alter-3.3.1 { 462 execsql [subst { 463 CREATE TABLE tbl1(a, b, c); 464 CREATE $::temp TRIGGER trig1 AFTER INSERT ON tbl1 BEGIN 465 SELECT trigfunc('trig1', new.a, new.b, new.c); 466 END; 467 }] 468} {} 469do_test alter-3.3.2 { 470 execsql { 471 INSERT INTO tbl1 VALUES('a', 'b', 'c'); 472 } 473 set ::TRIGGER 474} {trig1 a b c} 475do_test alter-3.3.3 { 476 execsql { 477 ALTER TABLE tbl1 RENAME TO tbl2; 478 INSERT INTO tbl2 VALUES('d', 'e', 'f'); 479 } 480 set ::TRIGGER 481} {trig1 d e f} 482do_test alter-3.3.4 { 483 execsql [subst { 484 CREATE $::temp TRIGGER trig2 AFTER UPDATE ON tbl2 BEGIN 485 SELECT trigfunc('trig2', new.a, new.b, new.c); 486 END; 487 }] 488} {} 489do_test alter-3.3.5 { 490 execsql { 491 ALTER TABLE tbl2 RENAME TO tbl3; 492 INSERT INTO tbl3 VALUES('g', 'h', 'i'); 493 } 494 set ::TRIGGER 495} {trig1 g h i} 496do_test alter-3.3.6 { 497 execsql { 498 UPDATE tbl3 SET a = 'G' where a = 'g'; 499 } 500 set ::TRIGGER 501} {trig2 G h i} 502do_test alter-3.3.7 { 503 execsql { 504 DROP TABLE tbl3; 505 } 506} {} 507ifcapable tempdb { 508 do_test alter-3.3.8 { 509 execsql { 510 SELECT * FROM sqlite_temp_master WHERE type = 'trigger'; 511 } 512 } {} 513} 514 515} ;# ifcapable trigger 516 517# If the build does not include AUTOINCREMENT fields, omit alter-4.*. 518ifcapable autoinc { 519 520do_test alter-4.1 { 521 execsql { 522 CREATE TABLE tbl1(a INTEGER PRIMARY KEY AUTOINCREMENT); 523 INSERT INTO tbl1 VALUES(10); 524 } 525} {} 526do_test alter-4.2 { 527 execsql { 528 INSERT INTO tbl1 VALUES(NULL); 529 SELECT a FROM tbl1; 530 } 531} {10 11} 532do_test alter-4.3 { 533 execsql { 534 ALTER TABLE tbl1 RENAME TO tbl2; 535 DELETE FROM tbl2; 536 INSERT INTO tbl2 VALUES(NULL); 537 SELECT a FROM tbl2; 538 } 539} {12} 540do_test alter-4.4 { 541 execsql { 542 DROP TABLE tbl2; 543 } 544} {} 545 546} ;# ifcapable autoinc 547 548# Test that it is Ok to execute an ALTER TABLE immediately after 549# opening a database. 550do_test alter-5.1 { 551 execsql { 552 CREATE TABLE tbl1(a, b, c); 553 INSERT INTO tbl1 VALUES('x', 'y', 'z'); 554 } 555} {} 556do_test alter-5.2 { 557 sqlite3 db2 test.db 558 execsql { 559 ALTER TABLE tbl1 RENAME TO tbl2; 560 SELECT * FROM tbl2; 561 } db2 562} {x y z} 563do_test alter-5.3 { 564 db2 close 565} {} 566 567foreach tblname [execsql { 568 SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite%' 569}] { 570 execsql "DROP TABLE \"$tblname\"" 571} 572 573set ::tbl_name "abc\uABCDdef" 574do_test alter-6.1 { 575 string length $::tbl_name 576} {7} 577do_test alter-6.2 { 578 execsql " 579 CREATE TABLE ${tbl_name}(a, b, c); 580 " 581 set ::oid [execsql {SELECT max(oid) FROM sqlite_master}] 582 execsql " 583 SELECT sql FROM sqlite_master WHERE oid = $::oid; 584 " 585} "{CREATE TABLE ${::tbl_name}(a, b, c)}" 586execsql " 587 SELECT * FROM ${::tbl_name} 588" 589set ::tbl_name2 "abcXdef" 590do_test alter-6.3 { 591 execsql " 592 ALTER TABLE $::tbl_name RENAME TO $::tbl_name2 593 " 594 execsql " 595 SELECT sql FROM sqlite_master WHERE oid = $::oid 596 " 597} "{CREATE TABLE '${::tbl_name2}'(a, b, c)}" 598do_test alter-6.4 { 599 execsql " 600 ALTER TABLE $::tbl_name2 RENAME TO $::tbl_name 601 " 602 execsql " 603 SELECT sql FROM sqlite_master WHERE oid = $::oid 604 " 605} "{CREATE TABLE '${::tbl_name}'(a, b, c)}" 606set ::col_name ghi\1234\jkl 607do_test alter-6.5 { 608 execsql " 609 ALTER TABLE $::tbl_name ADD COLUMN $::col_name VARCHAR 610 " 611 execsql " 612 SELECT sql FROM sqlite_master WHERE oid = $::oid 613 " 614} "{CREATE TABLE '${::tbl_name}'(a, b, c, $::col_name VARCHAR)}" 615set ::col_name2 B\3421\A 616do_test alter-6.6 { 617 db close 618 sqlite3 db test.db 619 execsql " 620 ALTER TABLE $::tbl_name ADD COLUMN $::col_name2 621 " 622 execsql " 623 SELECT sql FROM sqlite_master WHERE oid = $::oid 624 " 625} "{CREATE TABLE '${::tbl_name}'(a, b, c, $::col_name VARCHAR, $::col_name2)}" 626do_test alter-6.7 { 627 execsql " 628 INSERT INTO ${::tbl_name} VALUES(1, 2, 3, 4, 5); 629 SELECT $::col_name, $::col_name2 FROM $::tbl_name; 630 " 631} {4 5} 632 633# Ticket #1665: Make sure ALTER TABLE ADD COLUMN works on a table 634# that includes a COLLATE clause. 635# 636do_test alter-7.1 { 637 execsql { 638 CREATE TABLE t1(a TEXT COLLATE BINARY); 639 ALTER TABLE t1 ADD COLUMN b INTEGER COLLATE NOCASE; 640 INSERT INTO t1 VALUES(1,'-2'); 641 INSERT INTO t1 VALUES(5.4e-08,'5.4e-08'); 642 SELECT typeof(a), a, typeof(b), b FROM t1; 643 } 644} {text 1 integer -2 text 5.4e-08 real 5.4e-08} 645 646# Make sure that when a column is added by ALTER TABLE ADD COLUMN and has 647# a default value that the default value is used by aggregate functions. 648# 649do_test alter-8.1 { 650 execsql { 651 CREATE TABLE t2(a INTEGER); 652 INSERT INTO t2 VALUES(1); 653 INSERT INTO t2 VALUES(1); 654 INSERT INTO t2 VALUES(2); 655 ALTER TABLE t2 ADD COLUMN b INTEGER DEFAULT 9; 656 SELECT sum(b) FROM t2; 657 } 658} {27} 659do_test alter-8.2 { 660 execsql { 661 SELECT a, sum(b) FROM t2 GROUP BY a; 662 } 663} {1 18 2 9} 664 665#-------------------------------------------------------------------------- 666# alter-9.X - Special test: Make sure the sqlite_rename_trigger() and 667# rename_table() functions do not crash when handed bad input. 668# 669ifcapable trigger { 670 do_test alter-9.1 { 671 execsql {SELECT SQLITE_RENAME_TRIGGER(0,0)} 672 } {{}} 673} 674do_test alter-9.2 { 675 execsql { 676 SELECT SQLITE_RENAME_TABLE(0,0); 677 SELECT SQLITE_RENAME_TABLE(10,20); 678 SELECT SQLITE_RENAME_TABLE("foo", "foo"); 679 } 680} {{} {} {}} 681 682#------------------------------------------------------------------------ 683# alter-10.X - Make sure ALTER TABLE works with multi-byte UTF-8 characters 684# in the names. 685# 686do_test alter-10.1 { 687 execsql "CREATE TABLE xyz(x UNIQUE)" 688 execsql "ALTER TABLE xyz RENAME TO xyz\u1234abc" 689 execsql {SELECT name FROM sqlite_master WHERE name LIKE 'xyz%'} 690} [list xyz\u1234abc] 691do_test alter-10.2 { 692 execsql {SELECT name FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'} 693} [list sqlite_autoindex_xyz\u1234abc_1] 694do_test alter-10.3 { 695 execsql "ALTER TABLE xyz\u1234abc RENAME TO xyzabc" 696 execsql {SELECT name FROM sqlite_master WHERE name LIKE 'xyz%'} 697} [list xyzabc] 698do_test alter-10.4 { 699 execsql {SELECT name FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'} 700} [list sqlite_autoindex_xyzabc_1] 701 702do_test alter-11.1 { 703 sqlite3_exec db {CREATE TABLE t11(%c6%c6)} 704 execsql { 705 ALTER TABLE t11 ADD COLUMN abc; 706 } 707 catchsql { 708 ALTER TABLE t11 ADD COLUMN abc; 709 } 710} {1 {duplicate column name: abc}} 711set isutf16 [regexp 16 [db one {PRAGMA encoding}]] 712if {!$isutf16} { 713 do_test alter-11.2 { 714 execsql {INSERT INTO t11 VALUES(1,2)} 715 sqlite3_exec db {SELECT %c6%c6 AS xyz, abc FROM t11} 716 } {0 {xyz abc 1 2}} 717} 718do_test alter-11.3 { 719 sqlite3_exec db {CREATE TABLE t11b("%81%82%83" text)} 720 execsql { 721 ALTER TABLE t11b ADD COLUMN abc; 722 } 723 catchsql { 724 ALTER TABLE t11b ADD COLUMN abc; 725 } 726} {1 {duplicate column name: abc}} 727if {!$isutf16} { 728 do_test alter-11.4 { 729 execsql {INSERT INTO t11b VALUES(3,4)} 730 sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11b} 731 } {0 {xyz abc 3 4}} 732 do_test alter-11.5 { 733 sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11b} 734 } {0 {xyz abc 3 4}} 735 do_test alter-11.6 { 736 sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11b} 737 } {0 {xyz abc 3 4}} 738} 739do_test alter-11.7 { 740 sqlite3_exec db {CREATE TABLE t11c(%81%82%83 text)} 741 execsql { 742 ALTER TABLE t11c ADD COLUMN abc; 743 } 744 catchsql { 745 ALTER TABLE t11c ADD COLUMN abc; 746 } 747} {1 {duplicate column name: abc}} 748if {!$isutf16} { 749 do_test alter-11.8 { 750 execsql {INSERT INTO t11c VALUES(5,6)} 751 sqlite3_exec db {SELECT %81%82%83 AS xyz, abc FROM t11c} 752 } {0 {xyz abc 5 6}} 753 do_test alter-11.9 { 754 sqlite3_exec db {SELECT [%81%82%83] AS xyz, abc FROM t11c} 755 } {0 {xyz abc 5 6}} 756 do_test alter-11.10 { 757 sqlite3_exec db {SELECT "%81%82%83" AS xyz, abc FROM t11c} 758 } {0 {xyz abc 5 6}} 759} 760 761 762finish_test 763