path: root/test/altertab3.test

# 2019 January 23
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix altertab3

# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
ifcapable !altertable {
  finish_test
  return
}

ifcapable windowfunc {
do_execsql_test 1.0 {
  CREATE TABLE t1(a, b);
  CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
    SELECT sum(b) OVER w FROM t1 WINDOW w AS (ORDER BY a);
  END;
}

do_execsql_test 1.1 {
  ALTER TABLE t1 RENAME a TO aaa;
}

do_execsql_test 1.2 {
  SELECT sql FROM sqlite_master WHERE name='tr1'
} {{CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
    SELECT sum(b) OVER w FROM t1 WINDOW w AS (ORDER BY aaa);
  END}}

do_execsql_test 1.3 {
  INSERT INTO t1 VALUES(1, 2);
}
} ;# windowfunc

#-------------------------------------------------------------------------
reset_db
do_execsql_test 2.0 {
  CREATE TABLE t1(a,b,c);
  CREATE TABLE t2(a,b,c);
  CREATE TRIGGER r1 AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN
    SELECT a,b, a name FROM t1 
      INTERSECT 
    SELECT a,b,c FROM t1 WHERE b>='d' ORDER BY name;
    SELECT new.c;
  END;
}

do_execsql_test 2.1 {
  ALTER TABLE t1 RENAME TO t1x;
  SELECT sql FROM sqlite_master WHERE name = 'r1';
} {{CREATE TRIGGER r1 AFTER INSERT ON "t1x" WHEN new.a NOT NULL BEGIN
    SELECT a,b, a name FROM "t1x" 
      INTERSECT 
    SELECT a,b,c FROM "t1x" WHERE b>='d' ORDER BY name;
    SELECT new.c;
  END}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 3.0 {
  CREATE TABLE t1(a, b, c, d);
  CREATE VIEW v1 AS SELECT * FROM t1 WHERE a=1 OR (b IN ());
}

do_execsql_test 3.1 {
  ALTER TABLE t1 RENAME b TO bbb;
}

do_execsql_test 3.2 {
  SELECT sql FROM sqlite_master WHERE name = 'v1'
} {{CREATE VIEW v1 AS SELECT * FROM t1 WHERE a=1 OR (b IN ())}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.0 {
  CREATE TABLE t1(a, b);
  CREATE TABLE t3(e, f);
  CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
    INSERT INTO t2 VALUES(new.a, new.b);
  END;
}

do_catchsql_test 4.1.2 {
  BEGIN;
    ALTER TABLE t3 RENAME TO t4;
} {1 {error in trigger tr1: no such table: main.t2}}
do_execsql_test 4.1.2 {
  COMMIT;
}
do_execsql_test 4.1.3 {
  SELECT type, name, tbl_name, sql 
  FROM sqlite_master WHERE type='table' AND name!='t1';
} {table t3 t3 {CREATE TABLE t3(e, f)}}


do_catchsql_test 4.2.1 {
  BEGIN;
    ALTER TABLE t3 RENAME e TO eee;
} {1 {error in trigger tr1: no such table: main.t2}}
do_execsql_test 4.2.2 {
  COMMIT;
}
do_execsql_test 4.2.3 {
  SELECT type, name, tbl_name, sql 
  FROM sqlite_master WHERE type='table' AND name!='t1';
} {table t3 t3 {CREATE TABLE t3(e, f)}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 5.0 {
  CREATE TABLE t1 (
      c1 integer, c2, PRIMARY KEY(c1 collate rtrim),
      UNIQUE(c2)
  )
}
do_execsql_test 5.1 {
  ALTER TABLE t1 RENAME c1 TO c3;
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.0 {
  CREATE TEMPORARY TABLE Table0 (
    Col0 INTEGER, 
    PRIMARY KEY(Col0 COLLATE RTRIM), 
    FOREIGN KEY (Col0) REFERENCES Table0
  );
}

do_execsql_test 6.1 {
  ALTER TABLE Table0 RENAME Col0 TO Col0;
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 7.1.0 {
  CREATE TABLE t1(a,b,c);
  CREATE TRIGGER AFTER INSERT ON t1 BEGIN
    SELECT a, rank() OVER w1 FROM t1
    WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1);
  END;
}

do_execsql_test 7.1.2 {
  ALTER TABLE t1 RENAME TO t1x;
  SELECT sql FROM sqlite_master;
} {
  {CREATE TABLE "t1x"(a,b,c)}
  {CREATE TRIGGER AFTER INSERT ON "t1x" BEGIN
    SELECT a, rank() OVER w1 FROM "t1x"
    WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1);
  END}
}

do_execsql_test 7.2.1 {
  DROP TRIGGER after;
  CREATE TRIGGER AFTER INSERT ON t1x BEGIN
    SELECT a, rank() OVER w1 FROM t1x
    WINDOW w1 AS (PARTITION BY b, percent_rank() OVER w1 ORDER BY d);
  END;
}

do_catchsql_test 7.2.2 {
  ALTER TABLE t1x RENAME TO t1;
} {1 {error in trigger AFTER: no such column: d}}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 8.0 {
  CREATE TABLE t0(c0);
  CREATE INDEX i0 ON t0('1' IN ());
}
do_execsql_test 8.1 {
  ALTER TABLE t0 RENAME TO t1;
  SELECT sql FROM sqlite_master;
} {
  {CREATE TABLE "t1"(c0)}
  {CREATE INDEX i0 ON "t1"('1' IN ())}
}
do_execsql_test 8.2.1 {
  CREATE TABLE t2 (c0);
  CREATE INDEX i2 ON t2((LIKELIHOOD(c0, 100) IN ()));
  ALTER TABLE t2 RENAME COLUMN c0 TO c1;
}
do_execsql_test 8.2.2 {
  SELECT sql FROM sqlite_master WHERE tbl_name = 't2';
} {
  {CREATE TABLE t2 (c1)} 
  {CREATE INDEX i2 ON t2((LIKELIHOOD(c0, 100) IN ()))}
}
do_test 8.2.3 {
  sqlite3 db2 test.db
  db2 eval { INSERT INTO t2 VALUES (1), (2), (3) }
  db close
} {}
db2 close

#-------------------------------------------------------------------------
reset_db
do_execsql_test 9.1 {
  CREATE TABLE t1(a,b,c);
  CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN
    SELECT true WHERE (SELECT a, b FROM (t1)) IN ();
  END;
}
do_execsql_test 9.2 {
  ALTER TABLE t1 RENAME TO t1x;
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 10.1 {
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(a, b, c);
  CREATE VIEW v1 AS SELECT * FROM t1 WHERE (
    SELECT t1.a FROM t1, t2
  ) IN () OR t1.a=5;
}

do_execsql_test 10.2 {
  ALTER TABLE t2 RENAME TO t3;
  SELECT sql FROM sqlite_master WHERE name='v1';
} {
  {CREATE VIEW v1 AS SELECT * FROM t1 WHERE (
    SELECT t1.a FROM t1, t2
  ) IN () OR t1.a=5}
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 11.1 {
  CREATE TABLE t1(
      a,b,c,d,e,f,g,h,j,jj,jjb,k,aa,bb,cc,dd,ee DEFAULT 3.14,
      ff DEFAULT('hiccup'),Wg NOD NULL DEFAULT(false)
  );

  CREATE TRIGGER b AFTER INSERT ON t1 WHEN new.a BEGIN
    SELECT a, sum() w3 FROM t1 
    WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM abc));
  END;
}

do_catchsql_test 11.2 {
  ALTER TABLE t1 RENAME TO t1x;
} {1 {error in trigger b: no such table: main.abc}}

do_execsql_test 11.3 {
  DROP TRIGGER b;
  CREATE TRIGGER b AFTER INSERT ON t1 WHEN new.a BEGIN
    SELECT a, sum() w3 FROM t1 
    WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM t1));
  END;
} {}

do_execsql_test 11.4 {
  ALTER TABLE t1 RENAME TO t1x;
  SELECT sql FROM sqlite_master WHERE name = 'b';
} {
{CREATE TRIGGER b AFTER INSERT ON "t1x" WHEN new.a BEGIN
    SELECT a, sum() w3 FROM "t1x" 
    WINDOW b AS (ORDER BY NOT EXISTS(SELECT 1 FROM "t1x"));
  END}
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 12.1 {
CREATE TABLE t1(a,b,c,d,e,f,g,h,j,jj,Zjj,k,aQ,bb,cc,dd,ee DEFAULT 3.14,
ff DEFAULT('hiccup'),gg NOD NULL DEFAULT(false));
CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN

SELECT b () OVER , dense_rank() OVER d, d () OVER w1
FROM t1
WINDOW
w1 AS
( w1 ORDER BY d
ROWS BETWEEN 2 NOT IN(SELECT a, sum(d) w2,max(d)OVER FROM t1
WINDOW
w1 AS
(PARTITION BY d
ROWS BETWEEN '' PRECEDING AND false FOLLOWING),
d AS
(PARTITION BY b ORDER BY d
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
) PRECEDING AND 1 FOLLOWING),
w2 AS
(PARTITION BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
w3 AS
(PARTITION BY b ORDER BY d
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
;
SELECT a, sum(d) w2,max(d)OVER FROM t1
WINDOW
w1 AS
(PARTITION BY d
ROWS BETWEEN '' PRECEDING AND false FOLLOWING),
d AS
(PARTITION BY b ORDER BY d
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
;

END;
}

do_execsql_test 12.2 {
  ALTER TABLE t1 RENAME TO t1x;
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 13.1 {
  CREATE TABLE t1(a);
  CREATE TRIGGER r1 INSERT ON t1 BEGIN
    SELECT a(*) OVER (ORDER BY (SELECT 1)) FROM t1;
  END;
}

do_execsql_test 13.2 {
  ALTER TABLE t1 RENAME TO t1x;
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 14.1 {
  CREATE TABLE t1(a);
  CREATE TABLE t2(b);
  CREATE TRIGGER AFTER INSERT ON t1 BEGIN
    SELECT sum() FILTER (WHERE (SELECT sum() FILTER (WHERE 0)) AND a);
  END;
}

do_catchsql_test 14.2 {
  ALTER TABLE t1 RENAME TO t1x;
} {1 {error in trigger AFTER: no such column: a}}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 16.1 {
  CREATE TABLE t1(x);
  CREATE TRIGGER AFTER INSERT ON t1 BEGIN
    SELECT (WITH t2 AS (WITH t3 AS (SELECT true)
          SELECT * FROM t3 ORDER BY true COLLATE nocase)
        SELECT 11);

    WITH t4 AS (SELECT * FROM t1) SELECT 33;
  END;
}
do_execsql_test 16.2 {
  ALTER TABLE t1 RENAME TO t1x;
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 17.1 {
  CREATE TABLE t1(a,b,c);
  CREATE TRIGGER AFTER INSERT ON t1 WHEN new.a NOT NULL BEGIN
    SELECT a () FILTER (WHERE a>0) FROM t1;
  END;
}

do_execsql_test 17.2 {
  ALTER TABLE t1 RENAME TO t1x;
  ALTER TABLE t1x RENAME a TO aaa;
  SELECT sql FROM sqlite_master WHERE type='trigger';
} {
{CREATE TRIGGER AFTER INSERT ON "t1x" WHEN new.aaa NOT NULL BEGIN
    SELECT a () FILTER (WHERE aaa>0) FROM "t1x";
  END}
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 18.1 {
  CREATE TABLE t1(a,b);
  CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
    SELECT a, b FROM t1
    INTERSECT SELECT b,a FROM t1
    ORDER BY b IN (
        SELECT a UNION SELECT b
        FROM t1
        ORDER BY b COLLATE nocase
        )
    ;
  END;
}

do_catchsql_test 18.2 {
    SELECT a, b FROM t1
    INTERSECT 
    SELECT b,a FROM t1
    ORDER BY b IN (
        SELECT a UNION SELECT b
        FROM t1
        ORDER BY b COLLATE nocase
        );
} {1 {1st ORDER BY term does not match any column in the result set}}

do_catchsql_test 18.3 {
  ALTER TABLE t1 RENAME TO t1x;
} {1 {error in trigger r1: 1st ORDER BY term does not match any column in the result set}}

#-------------------------------------------------------------------------
reset_db 
do_execsql_test 19.0 {
  CREATE TABLE a(a,h CONSTRAINT a UNIQUE ON CONFLICT FAIL,CONSTRAINT a);
}

foreach {tn v res} {
  1 {
    CREATE VIEW q AS SELECT 123
    
      WINDOW x AS (
        RANGE BETWEEN UNBOUNDED PRECEDING AND INDEXED() OVER(
          PARTITION BY ( WITH x AS(VALUES(col1)) VALUES(453) )
        )
      FOLLOWING
    ) 
  } {1 {error in view q: no such column: col1}}

  2 {
    CREATE VIEW q AS SELECT
    CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(CAST(RIGHT
    AS)AS)AS)AS)AS)AS)AS)AS)AS)AS)AS)WINDOW x AS(RANGE BETWEEN UNBOUNDED
    PRECEDING AND INDEXED(*)OVER(PARTITION BY
    CROSS,CROSS,NATURAL,sqlite_master(*)OVER a,(WITH a AS(VALUES(LEFT)UNION
    VALUES(LEFT)UNION VALUES(LEFT)UNION VALUES(LEFT)UNION VALUES(LEFT)UNION
    VALUES(LEFT)UNION VALUES(LEFT))VALUES(LEFT))IN
    STORED,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT)*LEFT FOLLOWING)ORDER BY
    LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT LIMIT
    LEFT,INDEXED(*)OVER(PARTITION BY
    CROSS,CROSS,CROSS,LEFT,INDEXED(*)OVER(PARTITION BY
    CROSS,CROSS,CROSS),INDEXED(*)OVER(PARTITION BY
    LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT,LEFT),
    LEFT,LEFT,INNER,CROSS,CROSS,CROSS,INNER,NATURAL ORDER BY
    OUTER,NATURAL,NATURAL,NATURAL,NATURAL,NATURAL,NATURAL,NATURAL,INNER,
    INNER,INNER NULLS LAST GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
    FOLLOWING); 
  } {1 {error in view q: no such column: LEFT}}

  3 {
    CREATE VIEW q AS SELECT 99 WINDOW x AS (RANGE BETWEEN UNBOUNDED PRECEDING
    AND count(*)OVER(PARTITION BY (WITH a AS(VALUES(2),(x3))VALUES(0)))
    FOLLOWING)ORDER BY x2,sum(1)OVER(PARTITION BY avg(5)OVER(PARTITION BY x1));
  } {1 {error in view q: no such column: x3}}
} {
  do_execsql_test 19.$tn.1 "
    DROP VIEW IF EXISTS q;
    $v
  " {}

  do_catchsql_test 19.$tn.2 {
    ALTER TABLE a RENAME TO g;
  } $res
}

# Verify that the "if( pParse->nErr ) return WRC_Abort" at the top of the
# renameUnmapSelectCb() routine in alter.c (2019-12-04) is really required.
#
sqlite3 db :memory:
do_catchsql_test 20.10 {
  CREATE TABLE s(a, b, c);
  CREATE INDEX k ON s( (WITH s AS( SELECT * ) VALUES(2) ) IN () );
  ALTER TABLE s RENAME a TO a2;
} {1 {error in index k: no tables specified}}

#------------------------------------------------------------------------
#
reset_db
do_execsql_test 21.1 {
  CREATE TABLE s(col);
  CREATE VIEW v AS SELECT ( 
    WITH x(a) AS(SELECT * FROM s) VALUES(RIGHT) 
  ) IN() ; 
  CREATE TABLE a(a);
  ALTER TABLE a RENAME a TO b;
}

#------------------------------------------------------------------------
#
reset_db
do_execsql_test 22.1 {
  CREATE TABLE t1(a);
  CREATE VIEW v2(b) AS SELECT * FROM v2;
}

do_catchsql_test 22.2 {
  ALTER TABLE t1 RENAME TO t4;
} {1 {error in view v2: view v2 is circularly defined}}

do_execsql_test 22.3 {
  DROP VIEW v2;
  CREATE VIEW v2(b) AS WITH t3 AS (SELECT b FROM v2) SELECT * FROM t3;
}

do_catchsql_test 22.4 {
  ALTER TABLE t1 RENAME TO t4;
} {1 {error in view v2: view v2 is circularly defined}}

do_execsql_test 22.5 {
  DROP VIEW v2;
  CREATE VIEW v2(b) AS WITH t3 AS (SELECT b FROM v2) VALUES(1);
}

do_catchsql_test 22.6 {
  ALTER TABLE t1 RENAME TO t4;
} {0 {}}

#------------------------------------------------------------------------
#
reset_db
do_execsql_test 23.1 {
  CREATE TABLE t1(x);
  CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN
    UPDATE t1 SET (c,d)=((SELECT 1 FROM t1 JOIN t2 ON b=x),1);
  END;
}

do_catchsql_test 23.2 {
  ALTER TABLE t1 RENAME TO t1x;
} {1 {error in trigger r1: no such table: main.t2}}

#------------------------------------------------------------------------
#
reset_db
do_execsql_test 23.1 {
  CREATE TABLE v0 (a);
  CREATE VIEW v2 (v3) AS 
    WITH x1 AS (SELECT * FROM v2) 
    SELECT v3 AS x, v3 AS y FROM v2; 
}

do_catchsql_test 23.2 {
  SELECT * FROM v2
} {1 {view v2 is circularly defined}}

db close
sqlite3 db test.db

do_catchsql_test 23.3 {
  ALTER TABLE v0 RENAME TO t3 ;
} {1 {error in view v2: view v2 is circularly defined}}

#------------------------------------------------------------------------
#
reset_db
do_execsql_test 24.1 {
  CREATE TABLE v0 (v1); 
  CREATE TABLE v2 (v3 INTEGER UNIQUE ON CONFLICT ABORT); 
  CREATE TRIGGER x AFTER INSERT ON v2 WHEN ( 
      ( SELECT v1 AS PROMO_REVENUE FROM v2 JOIN v0 USING ( VALUE ) ) AND 0 ) 
  BEGIN 
    DELETE FROM v2; 
  END; 
}
do_catchsql_test 24.2 {
  ALTER TABLE v0 RENAME TO x ;
} {1 {error in trigger x: cannot join using column VALUE - column not present in both tables}}

do_execsql_test 24.3 {
  DROP TRIGGER x;
  CREATE TRIGGER x AFTER INSERT ON v2 WHEN (
    0 AND (SELECT rowid FROM v0)
  ) BEGIN
    DELETE FROM v2;
  END;
}

do_execsql_test 24.4 {
  ALTER TABLE v0 RENAME TO xyz;
  SELECT sql FROM sqlite_master WHERE type='trigger'
} {{CREATE TRIGGER x AFTER INSERT ON v2 WHEN (
    0 AND (SELECT rowid FROM "xyz")
  ) BEGIN
    DELETE FROM v2;
  END}}

#------------------------------------------------------------------------
#
reset_db
do_execsql_test 25.1 {
  CREATE TABLE t1(a, b, c);
  CREATE TABLE t2(a, b, c);
  CREATE TRIGGER ttt AFTER INSERT ON t1 BEGIN
    UPDATE t1 SET a=t2.a FROM t2 WHERE t1.a=t2.a; 
  END;
}
#do_execsql_test 25.2 {
#  ALTER TABLE t2 RENAME COLUMN a TO aaa;
#}

#------------------------------------------------------------------------
#
reset_db
do_execsql_test 26.1 {
  CREATE TABLE t1(x);

  CREATE TABLE t3(y);
  CREATE TABLE t4(z);

  CREATE TRIGGER tr1 INSERT ON t3 BEGIN
    UPDATE t3 SET y=z FROM (SELECT z FROM t4);
  END;

  CREATE TRIGGER tr2 INSERT ON t3 BEGIN
    UPDATE t3 SET y=abc FROM (SELECT x AS abc FROM t1);
  END;
}

do_execsql_test 26.2 {
  ALTER TABLE t1 RENAME TO t2;
}

do_execsql_test 26.3 {
  ALTER TABLE t2 RENAME x TO xx;
}

do_execsql_test 26.4 {
  SELECT sql FROM sqlite_schema WHERE name='tr2'
} {
{CREATE TRIGGER tr2 INSERT ON t3 BEGIN
    UPDATE t3 SET y=abc FROM (SELECT xx AS abc FROM "t2");
  END}
}

# 2020-11-02 OSSFuzz 
#
reset_db
do_execsql_test 26.5 {
  CREATE TABLE t1(xx);
  CREATE TRIGGER xx INSERT ON t1 BEGIN
     UPDATE t1 SET xx=xx FROM(SELECT xx);
  END;
} {}
do_catchsql_test 26.6 {
  ALTER TABLE t1 RENAME TO t2;
} {1 {error in trigger xx: no such column: xx}}


#-------------------------------------------------------------------------
reset_db

do_execsql_test 27.1 {
  CREATE TABLE t1(a, b AS ((WITH w1 (xyz) AS  ( SELECT t1.b FROM t1 )  SELECT 123) IN ()), c);
}

do_execsql_test 27.2 {
  ALTER TABLE t1 DROP COLUMN c;
  SELECT sql FROM sqlite_schema WHERE name = 't1';
} {
  {CREATE TABLE t1(a, b AS ((WITH w1 (xyz) AS  ( SELECT t1.b FROM t1 )  SELECT 123) IN ()))}
}

do_execsql_test 27.3 {
  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 ;
} {}

do_execsql_test 27.4 {
  ALTER TABLE t0 DROP COLUMN c60;
} {}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 28.1 {
  CREATE TABLE t1(a,b,c,d);
  CREATE TRIGGER AFTER INSERT ON t1 BEGIN
    UPDATE t1 SET (c,d)=(a,b);
  END;
  ALTER TABLE t1 RENAME TO t2;
}

do_execsql_test 28.2 {
  SELECT sql FROM sqlite_schema WHERE type='trigger'
} {{CREATE TRIGGER AFTER INSERT ON "t2" BEGIN
    UPDATE "t2" SET (c,d)=(a,b);
  END}}


#-------------------------------------------------------------------------
reset_db
do_execsql_test 29.1 {
  CREATE TABLE t1(x, y);
  CREATE TRIGGER Trigger1 DELETE ON t1 
  BEGIN 
    SELECT t1.*, t1.x FROM t1 ORDER BY t1.x;
  END;
}


do_execsql_test 29.2 {
  ALTER TABLE t1 RENAME x TO z;
}

do_execsql_test 29.3 {
  ALTER TABLE t1 RENAME TO t2;
}

do_execsql_test 29.4 {
  CREATE TRIGGER tr2 AFTER DELETE ON t2 BEGIN
    SELECT z, y FROM (
      SELECT t2.* FROM t2
    );
  END;
}

do_execsql_test 29.5 {
  DELETE FROM t2
}

do_execsql_test 29.6 {
  ALTER TABLE t2 RENAME TO t3;
}

do_execsql_test 29.7 {
  SELECT sql FROM sqlite_schema WHERE type='trigger'
} {
  {CREATE TRIGGER Trigger1 DELETE ON "t3" 
  BEGIN 
    SELECT "t3".*, "t3".z FROM "t3" ORDER BY "t3".z;
  END}
  {CREATE TRIGGER tr2 AFTER DELETE ON "t3" BEGIN
    SELECT z, y FROM (
      SELECT "t3".* FROM "t3"
    );
  END}
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 30.0 {
  CREATE TABLE t1(a, b);
  CREATE VIEW v1 AS 
      SELECT ( VALUES(a), (b) ) FROM (
        SELECT a, b FROM t1
      )
  ;
}

do_execsql_test 30.1 {
  SELECT * FROM v1
}

do_execsql_test 30.1 {
  ALTER TABLE t1 RENAME TO t2;
}
do_execsql_test 30.2 {
  SELECT sql FROM sqlite_schema WHERE type='view'
} {
  {CREATE VIEW v1 AS 
      SELECT ( VALUES(a), (b) ) FROM (
        SELECT a, b FROM "t2"
      )}
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 31.0 {
  CREATE TABLE t1(ii INTEGER PRIMARY KEY, tt INTEGER, rr REAL);
  WITH s(i) AS (
    SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000
  )
  INSERT INTO t1 SELECT NULL, i, 5.0 FROM s;
}

do_test 31.1 {
  set pg [db one {PRAGMA page_count}]
  execsql {
    ALTER TABLE t1 DROP COLUMN tt;
  }
  set pg2 [db one {PRAGMA page_count}]
  expr $pg==$pg2
} {1}

do_execsql_test 31.2 {
  SELECT rr FROM t1 LIMIT 1
} {5.0}

finish_test