SQLite Forum

Bug: ALTER TABLE RENAME TO vs CTE VIEWs / multiple references
Login
SQLite seems to get confused when a CTE VIEW references a table multiple times when renaming that table:

```
% sqlite                                                                                                  
-- Loading resources from /home/bjoern/.sqliterc
SQLite version 3.35.4 2021-04-02 15:20:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE VIEW v0 AS
   ...> WITH
   ...> p AS (
   ...>   SELECT 1 FROM t1
   ...> ),
   ...> g AS (
   ...>   SELECT
   ...>     1
   ...>   FROM
   ...>     p, t1
   ...> )
   ...> SELECT
   ...>   1
   ...> FROM
   ...>   g
   ...> ;
Run Time: real 0.000 user 0.000417 sys 0.000139
sqlite> 
sqlite> create table t1(x);
Run Time: real 0.000 user 0.000277 sys 0.000000

sqlite> alter table t1 rename to t2;
(1) statement aborts at 119: [alter table t1 rename to t2;] error in view v0 after rename: no such table: main.t1
Run Time: real 0.001 user 0.001657 sys 0.000000
Error: error in view v0 after rename: no such table: main.t1
```

When I comment out the `FROM g`, the `ALTER TABLE` oddly succeeds, but fails to rename the second reference:


```
% sqlite
-- Loading resources from /home/bjoern/.sqliterc
SQLite version 3.35.4 2021-04-02 15:20:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> 
sqlite> CREATE VIEW v0 AS
   ...> WITH
   ...> p AS (
   ...>   SELECT 1 FROM t1
   ...> ),
   ...> g AS (
   ...>   SELECT
   ...>     1
   ...>   FROM
   ...>     p, t1
   ...> )
   ...> SELECT
   ...>   1
   ...> -- FROM
   ...> --   g
   ...> ;
Run Time: real 0.001 user 0.000000 sys 0.000697
sqlite> 

sqlite> create table t1(x);
Run Time: real 0.000 user 0.000000 sys 0.000231

sqlite> alter table t1 rename to t2;
Run Time: real 0.001 user 0.000018 sys 0.001597

sqlite> .schema v0
(1) invalid arguments to fts4aux constructor in "PRAGMA "main".table_info='fts4aux';"
(21) API called with NULL prepared statement
(21) misuse at line 84383 of [5d4c65779d]
CREATE VIEW v0 AS
WITH
p AS (
  SELECT 1 FROM "t2"
),
g AS (
  SELECT
    1
  FROM
    p, t1
)
SELECT
  1
-- FROM
--   g
/* v0("1
-- FROM
--   g") */;
```