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.