SQLite Forum

ALTER TABLE fails with what seems irrelevant error
Login

ALTER TABLE fails with what seems irrelevant error

(1) By anonymous on 2021-05-31 15:53:37 [link] [source]

Using version 3.35.5 but it also happens with 3.35.3 that I have as backup.

I attempt to change the name of a column, and I get the error:

Error: near line 47: error in view failure: 2nd ORDER BY term does not match any column in the result set

The [uncut] view failure works correctly. The following schema is now garbage as I have reduced it to the point of still getting the error, so don't try to make much sense of it. It's only meant to show the specific problem. Just feed it to SQLite3.

I have tried with pragma legacy_alter_table either on or off. No difference.

I can remove the error by changing

select name, button, date, time, null from altlog

to

select name, button, date, time as time_in, null from altlog

inside failure view.

But remember, the way it is the [uncut] view works.

I just can't see how attempting to rename a column in another table that does not even participate in the failure view directly or indirectly could give an error.

--- SCRIPT BEGINS HERE ---

CREATE TABLE device(
  id integer primary key,
  name varchar(20) not null collate nocase
);

CREATE TABLE act(
 act_id integer primary key,
 name varchar(5) not null collate nocase
);

CREATE TABLE log (
  device integer not null,
  dt char(10) not null,
  tm char(6) not null,
  act_id integer not null,
  button char(16) not null collate nocase,
  primary key (device,dt,tm,act_id,button)
) without rowid;

CREATE TABLE `users` (
  `company` varchar(100) not null collate nocase,
  `lname` varchar(100) not null collate nocase,
  `fname` varchar(100) not null collate nocase,
  `button` varchar(16) not null,
  primary key (`button`)
) without rowid;

CREATE VIEW failure as
  with tmp(name,button,date,time_in,time_out) as (
    select name, button, date, time, null from altlog
    union
    select name, button, date, time_in, time
      from altlog a join tmp b using(button,name,date)
    order by date,time_in,button
    )
    select * from tmp where time_out is not null;

CREATE VIEW altlog as
  select device,
         trim(fname||' '||lname) name,
         button,
         name action,
         dt date,
         tm time
  from users,log using(button),act using(act_id);

alter table device rename column id to dev_id;

(2.1) By Richard Hipp (drh) on 2021-06-01 12:34:39 edited from 2.0 in reply to 1 [source]

Simplified test case:

CREATE TABLE t2(b,c);
INSERT INTO t2 VALUES(1,2),(1,3),(2,5);
CREATE VIEW v3 AS 
  WITH RECURSIVE t3(x,y,z) AS (
    SELECT b,c,NULL FROM t2
    UNION
    SELECT x,y,c FROM t3, t2 WHERE b=x
    ORDER BY y
  )
  SELECT * FROM t3;
SELECT * FROM v3;  -- This works

CREATE TABLE t1(a);
ALTER TABLE t1 RENAME a TO a2; -- fails in v3