SQLite Forum

ALTER TABLE fails with what seems irrelevant error
Login
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;
```