SQLite Forum

Bug: ALTER TABLE RENAME COLUM TO vs CTE VIEWs
Login

Bug: ALTER TABLE RENAME COLUM TO vs CTE VIEWs

(1) By Bjoern Hoehrmann (bjoern) on 2021-06-12 22:49:59 [link] [source]

Similar to https://sqlite.org/forum/forumpost/04f72b274e but with RENAME COLUMN and after the changes there. Test case:

SELECT sqlite_source_id();

CREATE TABLE t0 AS SELECT 1 AS col1;

CREATE VIEW v0 AS
  WITH
  n AS (
    SELECT
      t0.col1
    FROM
      t0 
  ),
  o AS (
    SELECT
      col1
    FROM
      n
  )
  SELECT * FROM o
;

SELECT * FROM v0;

ALTER TABLE t0 RENAME COLUMN col1 TO col2;
% sqlite < sqlite-bug-rename-column
sqlite_source_id()                                                                  
------------------------------------------------------------------------------------
2021-06-11 13:18:56 230fedd923c87741d20caf55f29e8464cc6df344536f9b89331e0a0059a926f7
Run Time: real 0.000 user 0.000211 sys 0.000000
Run Time: real 0.001 user 0.000624 sys 0.000000
Run Time: real 0.000 user 0.000379 sys 0.000000
col1
----
1   
Run Time: real 0.000 user 0.000187 sys 0.000000
(1) statement aborts at 157: [ALTER TABLE t0 RENAME COLUMN col1 TO col2;] error in view v0 after rename: no such column: col1
Run Time: real 0.003 user 0.000000 sys 0.003209
Error: near line 24: error in view v0 after rename: no such column: col1

(2) By Richard Hipp (drh) on 2021-06-13 10:58:47 in reply to 1 [link] [source]

I think this is a logic error in your SQL. I think SQLite is doing the right thing.

When you rename t0.col1 to t0.col2, the view is transformed from this:

CREATE VIEW v0 AS
  WITH n AS (SELECT t0.col1 FROM t0 ),
       o AS (SELECT col1 FROM n)
  SELECT * FROM o
;

Into this:

CREATE VIEW v0 AS
  WITH n AS (SELECT t0.col2 FROM t0 ),
       o AS (SELECT col1 FROM n)
  SELECT * FROM o
;

Because you have not defined the column names of CTE "n", names are selected by SQLite. Formerly, SQLite was choosing "col1" as the name of the only column in "n". After the ALTER TABLE it chooses "col2". SQLite is free to choose any column name it wants for "n", since you haven't specified one. But later, in the "o" CTE, you assume that the column name for "n" is "col1".

(3) By Bjoern Hoehrmann (bjoern) on 2021-06-14 00:08:03 in reply to 2 [source]

In PostgreSQL 9.6 the ALTER TABLE statement succeeds and the VIEW is transformed into:

WITH
n AS ( SELECT t0.col2 AS col1 FROM t0 ),
o AS ( SELECT n.col1 FROM n )
SELECT o.col1 FROM o

For that matter,

CREATE TABLE t0 AS SELECT 1 AS col1;
CREATE VIEW v0 AS SELECT col1 FROM t0;
ALTER TABLE t0 RENAME COLUMN col1 TO col2;
SELECT col1 FROM v0; -- Works in PostgreSQL

This seems an unfortunate difference between PostgreSQL and SQLite. It would be nice if this was made clearer in the documentation and had a better error message.