gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/altertab3.test (about) 1 # 2019 January 23 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 13 set testdir [file dirname $argv0] 14 source $testdir/tester.tcl 15 set testprefix altertab3 16 17 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 18 ifcapable !altertable { 19 finish_test 20 return 21 } 22 23 ifcapable windowfunc { 24 do_execsql_test 1.0 { 25 CREATE TABLE t1(a, b); 26 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN 27 SELECT sum(b) OVER w FROM t1 WINDOW w AS (ORDER BY a); 28 END; 29 } 30 31 do_execsql_test 1.1 { 32 ALTER TABLE t1 RENAME a TO aaa; 33 } 34 35 do_execsql_test 1.2 { 36 SELECT sql FROM sqlite_master WHERE name='tr1' 37 } {{CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN 38 SELECT sum(b) OVER w FROM t1 WINDOW w AS (ORDER BY aaa); 39 END}} 40 41 do_execsql_test 1.3 { 42 INSERT INTO t1 VALUES(1, 2); 43 } 44 } ;# windowfunc 45 46 #------------------------------------------------------------------------- 47 reset_db 48 do_execsql_test 2.0 { 49 CREATE TABLE t1(a,b,c); 50 CREATE TABLE t2(a,b,c); 51 CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN 52 SELECT a,b, a name FROM t1 53 INTERSECT 54 SELECT a,b,c FROM t1 WHERE b>='d' ORDER BY name; 55 SELECT new.c; 56 END; 57 } 58 59 do_execsql_test 2.1 { 60 ALTER TABLE t1 RENAME TO t1x; 61 SELECT sql FROM sqlite_master WHERE name = 'r1'; 62 } {{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.a NOT NULL BEGIN 63 SELECT a,b, a name FROM "t1x" 64 INTERSECT 65 SELECT a,b,c FROM "t1x" WHERE b>='d' ORDER BY name; 66 SELECT new.c; 67 END}} 68 69 #------------------------------------------------------------------------- 70 reset_db 71 do_execsql_test 3.0 { 72 CREATE TABLE t1(a, b, c, d); 73 CREATE VIEW v1 AS SELECT * FROM t1 WHERE a=1 OR (b IN ()); 74 } 75 76 do_execsql_test 3.1 { 77 ALTER TABLE t1 RENAME b TO bbb; 78 } 79 80 do_execsql_test 3.2 { 81 SELECT sql FROM sqlite_master WHERE name = 'v1' 82 } {{CREATE VIEW v1 AS SELECT * FROM t1 WHERE a=1 OR (b IN ())}} 83 84 #------------------------------------------------------------------------- 85 reset_db 86 do_execsql_test 4.0 { 87 CREATE TABLE t1(a, b); 88 CREATE TABLE t3(e, f); 89 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN 90 INSERT INTO t2 VALUES(new.a, new.b); 91 END; 92 } 93 94 do_catchsql_test 4.1.2 { 95 BEGIN; 96 ALTER TABLE t3 RENAME TO t4; 97 } {1 {error in trigger tr1: no such table: main.t2}} 98 do_execsql_test 4.1.2 { 99 COMMIT; 100 } 101 do_execsql_test 4.1.3 { 102 SELECT type, name, tbl_name, sql 103 FROM sqlite_master WHERE type='table' AND name!='t1'; 104 } {table t3 t3 {CREATE TABLE t3(e, f)}} 105 106 107 do_catchsql_test 4.2.1 { 108 BEGIN; 109 ALTER TABLE t3 RENAME e TO eee; 110 } {1 {error in trigger tr1: no such table: main.t2}} 111 do_execsql_test 4.2.2 { 112 COMMIT; 113 } 114 do_execsql_test 4.2.3 { 115 SELECT type, name, tbl_name, sql 116 FROM sqlite_master WHERE type='table' AND name!='t1'; 117 } {table t3 t3 {CREATE TABLE t3(e, f)}} 118 119 #------------------------------------------------------------------------- 120 reset_db 121 do_execsql_test 5.0 { 122 CREATE TABLE t1 ( 123 c1 integer, c2, PRIMARY KEY(c1 collate rtrim), 124 UNIQUE(c2) 125 ) 126 } 127 do_execsql_test 5.1 { 128 ALTER TABLE t1 RENAME c1 TO c3; 129 } 130 131 #------------------------------------------------------------------------- 132 reset_db 133 do_execsql_test 6.0 { 134 CREATE TEMPORARY TABLE Table0 ( 135 Col0 INTEGER, 136 PRIMARY KEY(Col0 COLLATE RTRIM), 137 FOREIGN KEY (Col0) REFERENCES Table0 138 ); 139 } 140 141 do_execsql_test 6.1 { 142 ALTER TABLE Table0 RENAME Col0 TO Col0; 143 } 144 145 #------------------------------------------------------------------------- 146 reset_db 147 do_execsql_test 7.1.0 { 148 CREATE TABLE t1(a,b,c); 149 CREATE TRIGGER AFTER INSERT ON t1 BEGIN 150 SELECT a, rank() OVER w1 FROM t1 151 WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1); 152 END; 153 } 154 155 do_execsql_test 7.1.2 { 156 ALTER TABLE t1 RENAME TO t1x; 157 SELECT sql FROM sqlite_master; 158 } { 159 {CREATE TABLE "t1x"(a,b,c)} 160 {CREATE TRIGGER AFTER INSERT ON "t1x" BEGIN 161 SELECT a, rank() OVER w1 FROM "t1x" 162 WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1); 163 END} 164 } 165 166 do_execsql_test 7.2.1 { 167 DROP TRIGGER after; 168 CREATE TRIGGER AFTER INSERT ON t1x BEGIN 169 SELECT a, rank() OVER w1 FROM t1x 170 WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1 ORDER BY d); 171 END; 172 } 173 174 do_catchsql_test 7.2.2 { 175 ALTER TABLE t1x RENAME TO t1; 176 } {1 {error in trigger AFTER: no such column: d}} 177 178 #------------------------------------------------------------------------- 179 reset_db 180 do_execsql_test 8.0 { 181 CREATE TABLE t0(c0); 182 CREATE INDEX i0 ON t0('1' IN ()); 183 } 184 do_execsql_test 8.1 { 185 ALTER TABLE t0 RENAME TO t1; 186 SELECT sql FROM sqlite_master; 187 } { 188 {CREATE TABLE "t1"(c0)} 189 {CREATE INDEX i0 ON "t1"('1' IN ())} 190 } 191 do_execsql_test 8.2.1 { 192 CREATE TABLE t2 (c0); 193 CREATE INDEX i2 ON t2((LIKELIHOOD(c0, 100) IN ())); 194 ALTER TABLE t2 RENAME COLUMN c0 TO c1; 195 } 196 do_execsql_test 8.2.2 { 197 SELECT sql FROM sqlite_master WHERE tbl_name = 't2'; 198 } { 199 {CREATE TABLE t2 (c1)} 200 {CREATE INDEX i2 ON t2((LIKELIHOOD(c0, 100) IN ()))} 201 } 202 do_test 8.2.3 { 203 sqlite3 db2 test.db 204 db2 eval { INSERT INTO t2 VALUES (1), (2), (3) } 205 db close 206 } {} 207 db2 close 208 209 #------------------------------------------------------------------------- 210 reset_db 211 do_execsql_test 9.1 { 212 CREATE TABLE t1(a,b,c); 213 CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN 214 SELECT true WHERE (SELECT a, b FROM (t1)) IN (); 215 END; 216 } 217 do_execsql_test 9.2 { 218 ALTER TABLE t1 RENAME TO t1x; 219 } 220 221 #------------------------------------------------------------------------- 222 reset_db 223 do_execsql_test 10.1 { 224 CREATE TABLE t1(a, b, c); 225 CREATE TABLE t2(a, b, c); 226 CREATE VIEW v1 AS SELECT * FROM t1 WHERE ( 227 SELECT t1.a FROM t1, t2 228 ) IN () OR t1.a=5; 229 } 230 231 do_execsql_test 10.2 { 232 ALTER TABLE t2 RENAME TO t3; 233 SELECT sql FROM sqlite_master WHERE name='v1'; 234 } { 235 {CREATE VIEW v1 AS SELECT * FROM t1 WHERE ( 236 SELECT t1.a FROM t1, t2 237 ) IN () OR t1.a=5} 238 } 239 240 #------------------------------------------------------------------------- 241 reset_db 242 do_execsql_test 11.1 { 243 CREATE TABLE t1( 244 a,b,c,d,e,f,g,h,j,jj,jjb,k,aa,bb,cc,dd,ee DEFAULT 3.14, 245 ff DEFAULT('hiccup'),Wg NOD NULL DEFAULT(false) 246 ); 247 248 CREATE TRIGGER b AFTER INSERT ON t1 WHEN new.a BEGIN 249 SELECT a, sum() w3 FROM t1 250 WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM abc)); 251 END; 252 } 253 254 do_catchsql_test 11.2 { 255 ALTER TABLE t1 RENAME TO t1x; 256 } {1 {error in trigger b: no such table: main.abc}} 257 258 do_execsql_test 11.3 { 259 DROP TRIGGER b; 260 CREATE TRIGGER b AFTER INSERT ON t1 WHEN new.a BEGIN 261 SELECT a, sum() w3 FROM t1 262 WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM t1)); 263 END; 264 } {} 265 266 do_execsql_test 11.4 { 267 ALTER TABLE t1 RENAME TO t1x; 268 SELECT sql FROM sqlite_master WHERE name = 'b'; 269 } { 270 {CREATE TRIGGER b AFTER INSERT ON "t1x" WHEN new.a BEGIN 271 SELECT a, sum() w3 FROM "t1x" 272 WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM "t1x")); 273 END} 274 } 275 276 #------------------------------------------------------------------------- 277 reset_db 278 do_execsql_test 12.1 { 279 CREATE TABLE t1(a,b,c,d,e,f,g,h,j,jj,Zjj,k,aQ,bb,cc,dd,ee DEFAULT 3.14, 280 ff DEFAULT('hiccup'),gg NOD NULL DEFAULT(false)); 281 CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN 282 283 SELECT b () OVER , dense_rank() OVER d, d () OVER w1 284 FROM t1 285 WINDOW 286 w1 AS 287 ( w1 ORDER BY d 288 ROWS BETWEEN 2 NOT IN(SELECT a, sum(d) w2,max(d)OVER FROM t1 289 WINDOW 290 w1 AS 291 (PARTITION BY d 292 ROWS BETWEEN '' PRECEDING AND false FOLLOWING), 293 d AS 294 (PARTITION BY b ORDER BY d 295 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 296 ) PRECEDING AND 1 FOLLOWING), 297 w2 AS 298 (PARTITION BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 299 w3 AS 300 (PARTITION BY b ORDER BY d 301 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 302 ; 303 SELECT a, sum(d) w2,max(d)OVER FROM t1 304 WINDOW 305 w1 AS 306 (PARTITION BY d 307 ROWS BETWEEN '' PRECEDING AND false FOLLOWING), 308 d AS 309 (PARTITION BY b ORDER BY d 310 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 311 ; 312 313 END; 314 } 315 316 do_execsql_test 12.2 { 317 ALTER TABLE t1 RENAME TO t1x; 318 } 319 320 #------------------------------------------------------------------------- 321 reset_db 322 do_execsql_test 13.1 { 323 CREATE TABLE t1(a); 324 CREATE TRIGGER r1 INSERT ON t1 BEGIN 325 SELECT a(*) OVER (ORDER BY (SELECT 1)) FROM t1; 326 END; 327 } 328 329 do_execsql_test 13.2 { 330 ALTER TABLE t1 RENAME TO t1x; 331 } 332 333 #------------------------------------------------------------------------- 334 reset_db 335 do_execsql_test 14.1 { 336 CREATE TABLE t1(a); 337 CREATE TABLE t2(b); 338 CREATE TRIGGER AFTER INSERT ON t1 BEGIN 339 SELECT sum() FILTER (WHERE (SELECT sum() FILTER (WHERE 0)) AND a); 340 END; 341 } 342 343 do_catchsql_test 14.2 { 344 ALTER TABLE t1 RENAME TO t1x; 345 } {1 {error in trigger AFTER: no such column: a}} 346 347 #------------------------------------------------------------------------- 348 reset_db 349 350 do_execsql_test 16.1 { 351 CREATE TABLE t1(x); 352 CREATE TRIGGER AFTER INSERT ON t1 BEGIN 353 SELECT (WITH t2 AS (WITH t3 AS (SELECT true) 354 SELECT * FROM t3 ORDER BY true COLLATE nocase) 355 SELECT 11); 356 357 WITH t4 AS (SELECT * FROM t1) SELECT 33; 358 END; 359 } 360 do_execsql_test 16.2 { 361 ALTER TABLE t1 RENAME TO t1x; 362 } 363 364 #------------------------------------------------------------------------- 365 reset_db 366 do_execsql_test 17.1 { 367 CREATE TABLE t1(a,b,c); 368 CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN 369 SELECT a () FILTER (WHERE a>0) FROM t1; 370 END; 371 } 372 373 do_execsql_test 17.2 { 374 ALTER TABLE t1 RENAME TO t1x; 375 ALTER TABLE t1x RENAME a TO aaa; 376 SELECT sql FROM sqlite_master WHERE type='trigger'; 377 } { 378 {CREATE TRIGGER AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN 379 SELECT a () FILTER (WHERE aaa>0) FROM "t1x"; 380 END} 381 } 382 383 #------------------------------------------------------------------------- 384 reset_db 385 do_execsql_test 18.1 { 386 CREATE TABLE t1(a,b); 387 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN 388 SELECT a, b FROM t1 389 INTERSECT SELECT b,a FROM t1 390 ORDER BY b IN ( 391 SELECT a UNION SELECT b 392 FROM t1 393 ORDER BY b COLLATE nocase 394 ) 395 ; 396 END; 397 } 398 399 do_catchsql_test 18.2 { 400 SELECT a, b FROM t1 401 INTERSECT 402 SELECT b,a FROM t1 403 ORDER BY b IN ( 404 SELECT a UNION SELECT b 405 FROM t1 406 ORDER BY b COLLATE nocase 407 ); 408 } {1 {1st ORDER BY term does not match any column in the result set}} 409 410 do_catchsql_test 18.3 { 411 ALTER TABLE t1 RENAME TO t1x; 412 } {1 {error in trigger r1: 1st ORDER BY term does not match any column in the result set}} 413 414 #------------------------------------------------------------------------- 415 reset_db 416 do_execsql_test 19.0 { 417 CREATE TABLE a(a,h CONSTRAINT a UNIQUE ON CONFLICT FAIL,CONSTRAINT a); 418 } 419 420 foreach {tn v res} { 421 1 { 422 CREATE VIEW q AS SELECT 123 423 424 WINDOW x AS ( 425 RANGE BETWEEN UNBOUNDED PRECEDING AND INDEXED() OVER( 426 PARTITION BY ( WITH x AS(VALUES(col1)) VALUES(453) ) 427 ) 428 FOLLOWING 429 ) 430 } {1 {error in view q: no such column: col1}} 431 432 2 { 433 CREATE VIEW q AS SELECT 434 CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(RIGHT 435 AS)AS)AS)AS)AS)AS)AS)AS)AS)AS)AS)WINDOW x AS(RANGE BETWEEN UNBOUNDED 436 PRECEDING AND INDEXED(*)OVER(PARTITION BY 437 CROSS,CROSS,NATURAL,sqlite_master(*)OVER a,(WITH a AS(VALUES(LEFT)UNION 438 VALUES(LEFT)UNION VALUES(LEFT)UNION VALUES(LEFT)UNION VALUES(LEFT)UNION 439 VALUES(LEFT)UNION VALUES(LEFT))VALUES(LEFT))IN 440 STORED,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT)*LEFT FOLLOWING)ORDER BY 441 LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT LIMIT 442 LEFT,INDEXED(*)OVER(PARTITION BY 443 CROSS,CROSS,CROSS,LEFT,INDEXED(*)OVER(PARTITION BY 444 CROSS,CROSS,CROSS),INDEXED(*)OVER(PARTITION BY 445 LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT), 446 LEFT,LEFT,INNER,CROSS,CROSS,CROSS,INNER,NATURAL ORDER BY 447 OUTER,NATURAL,NATURAL,NATURAL,NATURAL,NATURAL,NATURAL,NATURAL,INNER, 448 INNER,INNER NULLS LAST GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED 449 FOLLOWING); 450 } {1 {error in view q: no such column: LEFT}} 451 452 3 { 453 CREATE VIEW q AS SELECT 99 WINDOW x AS (RANGE BETWEEN UNBOUNDED PRECEDING 454 AND count(*)OVER(PARTITION BY (WITH a AS(VALUES(2),(x3))VALUES(0))) 455 FOLLOWING)ORDER BY x2,sum(1)OVER(PARTITION BY avg(5)OVER(PARTITION BY x1)); 456 } {1 {error in view q: no such column: x3}} 457 } { 458 do_execsql_test 19.$tn.1 " 459 DROP VIEW IF EXISTS q; 460 $v 461 " {} 462 463 do_catchsql_test 19.$tn.2 { 464 ALTER TABLE a RENAME TO g; 465 } $res 466 } 467 468 # Verify that the "if( pParse->nErr ) return WRC_Abort" at the top of the 469 # renameUnmapSelectCb() routine in alter.c (2019-12-04) is really required. 470 # 471 sqlite3 db :memory: 472 do_catchsql_test 20.10 { 473 CREATE TABLE s(a, b, c); 474 CREATE INDEX k ON s( (WITH s AS( SELECT * ) VALUES(2) ) IN () ); 475 ALTER TABLE s RENAME a TO a2; 476 } {1 {error in index k: no tables specified}} 477 478 #------------------------------------------------------------------------ 479 # 480 reset_db 481 do_execsql_test 21.1 { 482 CREATE TABLE s(col); 483 CREATE VIEW v AS SELECT ( 484 WITH x(a) AS(SELECT * FROM s) VALUES(RIGHT) 485 ) IN() ; 486 CREATE TABLE a(a); 487 ALTER TABLE a RENAME a TO b; 488 } 489 490 #------------------------------------------------------------------------ 491 # 492 reset_db 493 do_execsql_test 22.1 { 494 CREATE TABLE t1(a); 495 CREATE VIEW v2(b) AS SELECT * FROM v2; 496 } 497 498 do_catchsql_test 22.2 { 499 ALTER TABLE t1 RENAME TO t4; 500 } {1 {error in view v2: view v2 is circularly defined}} 501 502 do_execsql_test 22.3 { 503 DROP VIEW v2; 504 CREATE VIEW v2(b) AS WITH t3 AS (SELECT b FROM v2) SELECT * FROM t3; 505 } 506 507 do_catchsql_test 22.4 { 508 ALTER TABLE t1 RENAME TO t4; 509 } {1 {error in view v2: view v2 is circularly defined}} 510 511 do_execsql_test 22.5 { 512 DROP VIEW v2; 513 CREATE VIEW v2(b) AS WITH t3 AS (SELECT b FROM v2) VALUES(1); 514 } 515 516 do_catchsql_test 22.6 { 517 ALTER TABLE t1 RENAME TO t4; 518 } {0 {}} 519 520 #------------------------------------------------------------------------ 521 # 522 reset_db 523 do_execsql_test 23.1 { 524 CREATE TABLE t1(x); 525 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN 526 UPDATE t1 SET (c,d)=((SELECT 1 FROM t1 JOIN t2 ON b=x),1); 527 END; 528 } 529 530 do_catchsql_test 23.2 { 531 ALTER TABLE t1 RENAME TO t1x; 532 } {1 {error in trigger r1: no such table: main.t2}} 533 534 #------------------------------------------------------------------------ 535 # 536 reset_db 537 do_execsql_test 23.1 { 538 CREATE TABLE v0 (a); 539 CREATE VIEW v2 (v3) AS 540 WITH x1 AS (SELECT * FROM v2) 541 SELECT v3 AS x, v3 AS y FROM v2; 542 } 543 544 do_catchsql_test 23.2 { 545 SELECT * FROM v2 546 } {1 {view v2 is circularly defined}} 547 548 db close 549 sqlite3 db test.db 550 551 do_catchsql_test 23.3 { 552 ALTER TABLE v0 RENAME TO t3 ; 553 } {1 {error in view v2: view v2 is circularly defined}} 554 555 #------------------------------------------------------------------------ 556 # 557 reset_db 558 do_execsql_test 24.1 { 559 CREATE TABLE v0 (v1); 560 CREATE TABLE v2 (v3 INTEGER UNIQUE ON CONFLICT ABORT); 561 CREATE TRIGGER x AFTER INSERT ON v2 WHEN ( 562 ( SELECT v1 AS PROMO_REVENUE FROM v2 JOIN v0 USING ( VALUE ) ) AND 0 ) 563 BEGIN 564 DELETE FROM v2; 565 END; 566 } 567 do_catchsql_test 24.2 { 568 ALTER TABLE v0 RENAME TO x ; 569 } {1 {error in trigger x: cannot join using column VALUE - column not present in both tables}} 570 571 do_execsql_test 24.3 { 572 DROP TRIGGER x; 573 CREATE TRIGGER x AFTER INSERT ON v2 WHEN ( 574 0 AND (SELECT rowid FROM v0) 575 ) BEGIN 576 DELETE FROM v2; 577 END; 578 } 579 580 do_execsql_test 24.4 { 581 ALTER TABLE v0 RENAME TO xyz; 582 SELECT sql FROM sqlite_master WHERE type='trigger' 583 } {{CREATE TRIGGER x AFTER INSERT ON v2 WHEN ( 584 0 AND (SELECT rowid FROM "xyz") 585 ) BEGIN 586 DELETE FROM v2; 587 END}} 588 589 #------------------------------------------------------------------------ 590 # 591 reset_db 592 do_execsql_test 25.1 { 593 CREATE TABLE t1(a, b, c); 594 CREATE TABLE t2(a, b, c); 595 CREATE TRIGGER ttt AFTER INSERT ON t1 BEGIN 596 UPDATE t1 SET a=t2.a FROM t2 WHERE t1.a=t2.a; 597 END; 598 } 599 #do_execsql_test 25.2 { 600 # ALTER TABLE t2 RENAME COLUMN a TO aaa; 601 #} 602 603 #------------------------------------------------------------------------ 604 # 605 reset_db 606 do_execsql_test 26.1 { 607 CREATE TABLE t1(x); 608 609 CREATE TABLE t3(y); 610 CREATE TABLE t4(z); 611 612 CREATE TRIGGER tr1 INSERT ON t3 BEGIN 613 UPDATE t3 SET y=z FROM (SELECT z FROM t4); 614 END; 615 616 CREATE TRIGGER tr2 INSERT ON t3 BEGIN 617 UPDATE t3 SET y=abc FROM (SELECT x AS abc FROM t1); 618 END; 619 } 620 621 do_execsql_test 26.2 { 622 ALTER TABLE t1 RENAME TO t2; 623 } 624 625 do_execsql_test 26.3 { 626 ALTER TABLE t2 RENAME x TO xx; 627 } 628 629 do_execsql_test 26.4 { 630 SELECT sql FROM sqlite_schema WHERE name='tr2' 631 } { 632 {CREATE TRIGGER tr2 INSERT ON t3 BEGIN 633 UPDATE t3 SET y=abc FROM (SELECT xx AS abc FROM "t2"); 634 END} 635 } 636 637 # 2020-11-02 OSSFuzz 638 # 639 reset_db 640 do_execsql_test 26.5 { 641 CREATE TABLE t1(xx); 642 CREATE TRIGGER xx INSERT ON t1 BEGIN 643 UPDATE t1 SET xx=xx FROM(SELECT xx); 644 END; 645 } {} 646 do_catchsql_test 26.6 { 647 ALTER TABLE t1 RENAME TO t2; 648 } {1 {error in trigger xx: no such column: xx}} 649 650 651 #------------------------------------------------------------------------- 652 reset_db 653 654 do_execsql_test 27.1 { 655 CREATE TABLE t1(a, b AS ((WITH w1 (xyz) AS ( SELECT t1.b FROM t1 ) SELECT 123) IN ()), c); 656 } 657 658 do_execsql_test 27.2 { 659 ALTER TABLE t1 DROP COLUMN c; 660 SELECT sql FROM sqlite_schema WHERE name = 't1'; 661 } { 662 {CREATE TABLE t1(a, b AS ((WITH w1 (xyz) AS ( SELECT t1.b FROM t1 ) SELECT 123) IN ()))} 663 } 664 665 do_execsql_test 27.3 { 666 CREATE TABLE t0(c0 , c1 AS (CASE TRUE NOT IN () WHEN NULL THEN CASE + 0xa ISNULL WHEN NOT + 0x9 THEN t0.c1 ELSE CURRENT_TIME LIKE CAST (t0.c1 REGEXP '-([1-9]\d*.\d*|0\.\d*[1-9]\d*)'ESCAPE (c1) COLLATE BINARY BETWEEN c1 AND c1 NOT IN (WITH t4 (c0) AS (WITH t3 (c0) AS NOT MATERIALIZED (WITH RECURSIVE t2 (c0) AS (WITH RECURSIVE t1 AS (VALUES (x'717171ff71717171' ) ) SELECT DISTINCT t0.c0 FROM t0 NOT INDEXED WHERE t0.c0 =t0.c0 GROUP BY 0x9 ) SELECT DISTINCT t0.c0 FROM t0 NOT INDEXED WHERE t0.c0 =t0.c1 ) SELECT DISTINCT t0.c0 FROM t0 NOT INDEXED WHERE t0.c0 =t0.c0 GROUP BY typeof(0x9 ) ) SELECT DISTINCT t0.c0 FROM t0 NOT INDEXED WHERE t0.c0 =t0.c0 GROUP BY typeof(typeof(0x9 ) ) ) IN t0 BETWEEN typeof(typeof(typeof(hex(*) FILTER (WHERE + x'5ccd1e68' ) ) ) ) AND 1 >0xa AS BLOB (+4.4E4 , -0xe ) ) END <> c1 IN () END ) VIRTUAL , c35 PRIMARY KEY , c60 , c64 NUMERIC (-6.8 , -0xE ) ) WITHOUT ROWID ; 667 } {} 668 669 do_execsql_test 27.4 { 670 ALTER TABLE t0 DROP COLUMN c60; 671 } {} 672 673 #------------------------------------------------------------------------- 674 reset_db 675 do_execsql_test 28.1 { 676 CREATE TABLE t1(a,b,c,d); 677 CREATE TRIGGER AFTER INSERT ON t1 BEGIN 678 UPDATE t1 SET (c,d)=(a,b); 679 END; 680 ALTER TABLE t1 RENAME TO t2; 681 } 682 683 do_execsql_test 28.2 { 684 SELECT sql FROM sqlite_schema WHERE type='trigger' 685 } {{CREATE TRIGGER AFTER INSERT ON "t2" BEGIN 686 UPDATE "t2" SET (c,d)=(a,b); 687 END}} 688 689 690 #------------------------------------------------------------------------- 691 reset_db 692 do_execsql_test 29.1 { 693 CREATE TABLE t1(x, y); 694 CREATE TRIGGER Trigger1 DELETE ON t1 695 BEGIN 696 SELECT t1.*, t1.x FROM t1 ORDER BY t1.x; 697 END; 698 } 699 700 701 do_execsql_test 29.2 { 702 ALTER TABLE t1 RENAME x TO z; 703 } 704 705 do_execsql_test 29.3 { 706 ALTER TABLE t1 RENAME TO t2; 707 } 708 709 do_execsql_test 29.4 { 710 CREATE TRIGGER tr2 AFTER DELETE ON t2 BEGIN 711 SELECT z, y FROM ( 712 SELECT t2.* FROM t2 713 ); 714 END; 715 } 716 717 do_execsql_test 29.5 { 718 DELETE FROM t2 719 } 720 721 do_execsql_test 29.6 { 722 ALTER TABLE t2 RENAME TO t3; 723 } 724 725 do_execsql_test 29.7 { 726 SELECT sql FROM sqlite_schema WHERE type='trigger' 727 } { 728 {CREATE TRIGGER Trigger1 DELETE ON "t3" 729 BEGIN 730 SELECT "t3".*, "t3".z FROM "t3" ORDER BY "t3".z; 731 END} 732 {CREATE TRIGGER tr2 AFTER DELETE ON "t3" BEGIN 733 SELECT z, y FROM ( 734 SELECT "t3".* FROM "t3" 735 ); 736 END} 737 } 738 739 finish_test