Debian Codegraph @

test/altertab3.test

raw
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, 1.0) 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(c1, 1.0) 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 #-------------------------------------------------------------------------
666 reset_db
667 do_execsql_test 28.1 {
668 CREATE TABLE t1(a,b,c,d);
669 CREATE TRIGGER AFTER INSERT ON t1 BEGIN
670 UPDATE t1 SET (c,d)=(a,b);
671 END;
672 ALTER TABLE t1 RENAME TO t2;
673 }
674
675 do_execsql_test 28.2 {
676 SELECT sql FROM sqlite_schema WHERE type='trigger'
677 } {{CREATE TRIGGER AFTER INSERT ON "t2" BEGIN
678 UPDATE "t2" SET (c,d)=(a,b);
679 END}}
680
681
682 #-------------------------------------------------------------------------
683 reset_db
684 do_execsql_test 29.1 {
685 CREATE TABLE t1(x, y);
686 CREATE TRIGGER Trigger1 DELETE ON t1
687 BEGIN
688 SELECT t1.*, t1.x FROM t1 ORDER BY t1.x;
689 END;
690 }
691
692
693 do_execsql_test 29.2 {
694 ALTER TABLE t1 RENAME x TO z;
695 }
696
697 do_execsql_test 29.3 {
698 ALTER TABLE t1 RENAME TO t2;
699 }
700
701 do_execsql_test 29.4 {
702 CREATE TRIGGER tr2 AFTER DELETE ON t2 BEGIN
703 SELECT z, y FROM (
704 SELECT t2.* FROM t2
705 );
706 END;
707 }
708
709 do_execsql_test 29.5 {
710 DELETE FROM t2
711 }
712
713 do_execsql_test 29.6 {
714 ALTER TABLE t2 RENAME TO t3;
715 }
716
717 do_execsql_test 29.7 {
718 SELECT sql FROM sqlite_schema WHERE type='trigger'
719 } {
720 {CREATE TRIGGER Trigger1 DELETE ON "t3"
721 BEGIN
722 SELECT "t3".*, "t3".z FROM "t3" ORDER BY "t3".z;
723 END}
724 {CREATE TRIGGER tr2 AFTER DELETE ON "t3" BEGIN
725 SELECT z, y FROM (
726 SELECT "t3".* FROM "t3"
727 );
728 END}
729 }
730
731 #-------------------------------------------------------------------------
732 reset_db
733 do_execsql_test 30.0 {
734 CREATE TABLE t1(a, b);
735 CREATE VIEW v1 AS
736 SELECT ( VALUES(a), (b) ) FROM (
737 SELECT a, b FROM t1
738 )
739 ;
740 }
741
742 do_execsql_test 30.1 {
743 SELECT * FROM v1
744 }
745
746 do_execsql_test 30.1 {
747 ALTER TABLE t1 RENAME TO t2;
748 }
749 do_execsql_test 30.2 {
750 SELECT sql FROM sqlite_schema WHERE type='view'
751 } {
752 {CREATE VIEW v1 AS
753 SELECT ( VALUES(a), (b) ) FROM (
754 SELECT a, b FROM "t2"
755 )}
756 }
757
758 #-------------------------------------------------------------------------
759 reset_db
760 do_execsql_test 31.0 {
761 CREATE TABLE t1(ii INTEGER PRIMARY KEY, tt INTEGER, rr REAL);
762 WITH s(i) AS (
763 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000
764 )
765 INSERT INTO t1 SELECT NULL, i, 5.0 FROM s;
766 }
767
768 do_test 31.1 {
769 set pg [db one {PRAGMA page_count}]
770 execsql {
771 ALTER TABLE t1 DROP COLUMN tt;
772 }
773 set pg2 [db one {PRAGMA page_count}]
774 expr $pg==$pg2
775 } {1}
776
777 do_execsql_test 31.2 {
778 SELECT rr FROM t1 LIMIT 1
779 } {5.0}
780
781 #-------------------------------------------------------------------------
782 reset_db
783 do_execsql_test 32.1.0 {
784 CREATE TABLE t1(
785 a INT,
786 b INT,
787 -- comment with comma
788 c INT
789 );
790 }
791 do_execsql_test 32.1.1 {
792 ALTER TABLE t1 DROP COLUMN c;
793 }
794 do_execsql_test 32.1.2 {
795 SELECT sql FROM sqlite_schema
796 } {{CREATE TABLE t1(
797 a INT,
798 b INT)}}
799
800 reset_db
801 do_execsql_test 32.2.0 {
802 CREATE TABLE t1(
803 a INT,
804 b INT,
805 -- comment with, comma
806 c INT
807 );
808 }
809 do_execsql_test 32.2.1 {
810 ALTER TABLE t1 DROP COLUMN c;
811 }
812 do_execsql_test 32.2.2 {
813 SELECT sql FROM sqlite_schema
814 } {{CREATE TABLE t1(
815 a INT,
816 b INT)}}
817
818 #-------------------------------------------------------------------------
819 reset_db
820 do_execsql_test 33.1 {
821 CREATE TABLE x1(a TEXT, b INTEGER, c CHECK(c!=0));
822 }
823
824 do_execsql_test 33.2 {
825 ALTER TABLE x1 DROP COLUMN b;
826 SELECT sql FROM sqlite_schema;
827 } {{CREATE TABLE x1(a TEXT, c CHECK(c!=0))}}
828
829 finish_test