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
select name, button, date, time as time_in, null from altlog
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