ALTER TABLE x DROP COLUMN y; fails with error message about unrelated view column
(1.1) Originally by Daniel P (daniel) with edits by Richard Hipp (drh) on 2022-10-20 11:54:50 from 1.0 [link] [source]
Hello, I'm trying to find a bug in my application where it is unable to drop a column, but where the shell succeeds with the same sql commands. I've compiled both my application and the shell with the same compile options, but am unable to reproduce the problem in the shell.
The problem seems to be related to views, but there are no views using the column I want to drop. This is a mockup example:
CREATE VIEW aView (id,signal_strength) AS SELECT id, CASE WHEN signal_strength == 0 THEN "No signal" ELSE (signal_strength || "dB") END FROM aTable; CREATE TABLE an_unrelated_table ( id INT NOT NULL, name varchar( 20 ) NOT NULL, icon_id INT NOT NULL, column_to_be_dropped INT NOT NULL, description VARCHAR(400), com_type INTEGER REFERENCES com_types(id) CONSTRAINT "PK_an_unrelated_table " PRIMARY KEY(id), CONSTRAINT "FK_an_unrelated_table " FOREIGN KEY( icon_id ) REFERENCES icons ( id ) ); BEGIN TRANSACTION; UPDATE backend_information SET db_version = 178; ALTER TABLE an_unrelated_table DROP COLUMN column_to_be_dropped; -> error in view aView: no such column "No signal" ROLLBACK;
I have dumped the database schema to a text file to find any unwanted references to the column to be dropped, but there are none. (No references to that column anywhere as far as I understand it).
Any suggestions how I can try to figure out what my application is doing differently from the shell? (Using Sqlite 3.39.4)
(2) By David Raymond (dvdraymond) on 2022-10-20 12:01:20 in reply to 1.1 [link] [source]
Double quotes are for identifiers, single quotes are for text literals. Historically SQLite will let you use double quotes where it really should be single, but there's a compile time option to disallow improper quoting, which might be set on the one that's giving you the error, and not on the one that's making the table in the first place.
(3) By Daniel P (daniel) on 2022-10-20 13:53:42 in reply to 2 [link] [source]
You are absolutely right! I only compared the output from pragma compile_options when comparing the builds, and SQLITE_DQS wasn't visible there so I missed that difference between the application and sqlite shell. After fixing the double quoted strings the alter table command worked perfectly fine also in my application. Thanks!
(4) By Daniel P (daniel) on 2022-10-21 06:02:40 in reply to 3 [source]
It seems like compile option information about SQLITE_DQS is missing? 07:54:50 **** Build of configuration Debug for project Sqlite3 **** make all Building file: ../shell.c Invoking: Cross GCC Compiler gcc -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 -DSQLITE_DQS=1 -DSQLITE_DEFAULT_MEMSTATUS=0 -DSQLITE_LIKE_DOESNT_MATCH_BLOBS -DSQLITE_MAX_EXPR_DEPTH=0 -DSQLITE_OMIT_SHARED_CACHE -O0 -g3 -Wall -c -fmessage-length=0 -MMD -MP -MF"shell.d" -MT"shell.o" -o "shell.o" "../shell.c" Finished building: ../shell.c Building file: ../sqlite3.c Invoking: Cross GCC Compiler gcc -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 -DSQLITE_DQS=1 -DSQLITE_DEFAULT_MEMSTATUS=0 -DSQLITE_LIKE_DOESNT_MATCH_BLOBS -DSQLITE_MAX_EXPR_DEPTH=0 -DSQLITE_OMIT_SHARED_CACHE -O0 -g3 -Wall -c -fmessage-length=0 -MMD -MP -MF"sqlite3.d" -MT"sqlite3.o" -o "sqlite3.o" "../sqlite3.c" Finished building: ../sqlite3.c Building target: Sqlite3 Invoking: Cross GCC Linker gcc -o "Sqlite3" ./shell.o ./sqlite3.o -lpthread -ldl Finished building target: Sqlite3 SQLite version 3.39.4 2022-09-29 15:55:41 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select sqlite_compileoption_used('SQLITE_DEFAULT_WAL_SYNCHRONOUS'); 1 sqlite> select sqlite_compileoption_used('SQLITE_DQS'); 0 sqlite> select sqlite_compileoption_used('SQLITE_DEFAULT_MEMSTATUS'); 1 sqlite> select sqlite_compileoption_used('SQLITE_LIKE_DOESNT_MATCH_BLOBS'); 1 sqlite> select sqlite_compileoption_used('SQLITE_MAX_EXPR_DEPTH'); 1 sqlite> select sqlite_compileoption_used('SQLITE_OMIT_SHARED_CACHE'); 1 sqlite>
(5) By Stephan Beal (stephan) on 2022-10-21 07:05:11 in reply to 4 [link] [source]
It seems like compile option information about SQLITE_DQS is missing?
It's not missing, it's just missing from the compileoption_used list. That setting is still honored when built with it, though. My patch to add that to the compileoption_used list is currently awaiting approval from the project's gurus.