SQLite Forum

"ALTER TABLE ..." looks at other tables
Login

"ALTER TABLE ..." looks at other tables

(1) By Gerry Snyder (GSnyder) on 2020-10-11 21:03:18 [link] [source]

My db has a table that I don't use very often that has a custom collation (in Tcl), which I load only when I am going to be using that table. This has not caused any problems until now.

I have been doing some things with the native ALTER TABLE command and it seems that ADD COLUMN is not a problem, but both RENAME TO and RENAME COLUMN fail with errors because of the missing collation in another table.

Is this expected?

I tried creating a tiny example, but the problem did not arise.

The file the situation occurs in passes the INTEGRITY_CHECK, and I use it all the time, but there could be some problem that the check does not see.

My file is 137,312 KB, and I can send it or a link to it if you (drh) care to look into it.

Gerry Snyder

(2) By Richard Hipp (drh) on 2020-10-11 22:00:45 in reply to 1 [source]

Can you send me just the output of the ".schema" command in the CLI?

(3) By Gerry Snyder (GSnyder) on 2020-10-11 22:30:54 in reply to 2 [link] [source]

sqlite> .schema
CREATE TABLE blobs(filename text, length integer, time text default current_timestamp, content blob);
CREATE TABLE testnocase(a text collate nocase);
CREATE TABLE tc(procname text not null on conflict ignore,tcl text,version text,comments text);
CREATE TABLE gigo(a text,--comment
b int,C real,aa blob,"ggg integer" int, "ggg int" int default 0 not null, abc, xyz, abcxyz, unique(c,aa));
CREATE TABLE norowid(a text, b text primary key) WITHOUT ROWID;
CREATE TABLE norowid2(a text, b text, primary key(a,b)) WITHOUT ROWID;
CREATE TABLE CenturyClub_countries (country text, visited text, region text);
CREATE TABLE CenturyClub_regions (region text);
CREATE TABLE animal(type char (100),sound char(100));
CREATE TABLE ot(RegistrationYear,IntroductionYear,Status,Iris_Name,Hybridizer,Ctry,SeedlingNumber,Class,HeightInches,Season,Introducer,Description,Pedigree_Exporter_No_Period);
CREATE TABLE cmd_history(cmdtype text,tablename text,created default current_timestamp,command text);
CREATE TABLE ancestors_GCS (depth integer, id integer, name_etc text, America_born integer);
CREATE TABLE tclcode(procname text not null on conflict ignore unique on conflict ignore,version text,hidden integer default 0,tcl text,comments text);
CREATE TABLE ancestors1_GCS (depth integer, id integer, name text, birthdate text, birthplace text, America_born integer);
CREATE TABLE OED_index(headword text,position text,length text);
CREATE TABLE OED_index_collated(headword text,position text collate compare64,length text);
CREATE TABLE OED_index_sorted(headword text,position text collate compare64,length text);
CREATE TABLE abc(xyz collation missing);
CREATE TABLE def(ghi, jkl);
CREATE UNIQUE INDEX cmd_history_index ON cmd_history(cmdtype, tablename, command) WHERE cmdtype != 'patch';
CREATE INDEX OED_index_index ON OED_index(headword);
CREATE VIEW procname as select procname from tclcode
/* procname(procname) */;
CREATE VIEW hidden_views as select command from cmd_history where cmdtype = 'view' and tablename = 'temp'
/* hidden_views(command) */;
CREATE VIEW dict_view AS SELECT * from OED_index
/* dict_view(headword,position,length) */;
CREATE VIEW ancestor_depth AS SELECT COUNT(*) FROM ancestors1_GCS GROUP BY depth
/* ancestor_depth("COUNT(*)") */;
CREATE VIEW ancestor_depth_American AS SELECT depth, COUNT(*) FROM ancestors1_GCS WHERE America_born = 1 GROUP BY depth
/* ancestor_depth_American(depth,"COUNT(*)") */;
sqlite> alter table gigo rename to ggiiggoo;
Error: error in table OED_index_collated: no such collation sequence: compare64

(4) By Dan Kennedy (dan) on 2020-10-12 16:15:41 in reply to 3 [link] [source]

Thanks for this. Now fixed here:

https://sqlite.org/src/info/bc4bb9433fed5193

Dan.

(5) By Gerry Snyder (GSnyder) on 2020-10-12 17:41:47 in reply to 4 [link] [source]

Thank you.

It had seemed about a googol times more likely that my s/w was doing something stupid, or that my db had been corrupted (also by my s/w), than that I had found a real problem.

I feel relieved.

Gerry Snyder