CREATE TABLE test (a INTEGER); DROP VIEW IF EXISTS test;

Expected behavior: DROP VIEW should do just nothing as no view with that name exists.

Actual behavior: Error: use DROP TABLE to delete table

Why do you think a 'DROP VIEW' should drop a table?

Clearly, there could not have been a view of that name. If you want no table or view of that name to exist, you'll need to do 2 'drop if exist' ops. One will succeed, the other maybe not. A small price to pay for uncertainty or confusion as to what might be present already. The "IF EXISTS" option is just a convenience, and so is that error message.

If you are unwilling to do a "DROP VIEW IF EXISTS" and "DROP TABLE IF EXISTS", then settle for one succeeding, you can query the sqlite_master table. If the view or table exists under a suspected name, it will show up there, along with it form as 'table' or 'view' in the "type" column.

Ok, maybe I didn't clearly point this out:

I am using "DROP VIEW IF EXISTS", and I am expecting it to do nothing. But instead, I get an error which tells me to use "DROP TABLE" -- which is not want I want to. I am expecting "test" to be deleted if it is a view, and otherwise skip silently. That's why I'm using "IF EXISTS".

The error may be correct for "DROP VIEW", but imho it is not correct for "DROP VIEW IF EXISTS".

I suspect that your basic misconception is the belief that tables and views occupy separate namespaces. IOW that you can have a view "test", and a table "test", both, at the same time.

This is not the case. Views, as a kind of virtual table are in the same namespace as tables itself.

In your example you create an object named "test", which is of type "table". The IF EXISTS of the DROP VIEW finds that object, and then sqlite notes the type mismatch, view vs. table, and complains.

I hope that this explanation helps.

The problem is that this behavior

a) doesn't match the documentation, and

b) complicates deleting views a lot! For every view I want to delete, I'd have to check whether it actually is a view before running the DROP statement. If I had to do this, the IF EXISTS is merely useless because I could check for the view to exist by myself then.

The documentation could be more clear about this odd case. Whether the additional verbiage would dilute the rest with less detriment that the rare confusion you have suffered is unclear. I suspect the additional "clarity" would consume brain cycles that would be put to better use elsewhere.

Your 2nd point puzzles me. I have used "drop X if exists SomeName" a lot in development, always to ensure recreation of an X entity that might already be present, as an X entity. I hardly ever, perhaps never, have cause to convert tables to views or vice versa, and have never been faced with your difficulty. (This is why I do not see documentation clarity on this as being more useful than pedantic.)

I assume you want a view to be dropped because you are creating another one with the same name. But I cannot square such a desire with uncertainty as to whether a table of that name already exists. If you do not intend to recreate a view, and just intend to remove some grunge from a database, that is very much a one-off kind of problem, unworthy of lament such as "complicates a lot!". These considerations leave me wondering how your conundrum arose, and to question whether it is an ongoing, real difficulty or simply an unpleasant surprise coupled with insistence that your expectation was "correct". (I sympathize with that view, even if I don't agree with it.)

Can you describe some scenario, likely to be repeated, where this in fact creates more work than the following pseudo-code?

drop view if exists SomeViewOrTable;
if failed {
  drop table SomeViewOrTable;
}
if failed {
  yap
} else {
  be happy
}

BTW, I understood your initial complaint. What I did not understand is how you could not already know whether you had a table or a view.

Perhaps another way to phrase the question: Why do these two DROP statements behave differently?

% ./sqlite3
SQLite version 3.33.0 2020-06-11 16:04:10
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t1(x);
sqlite> DROP VIEW IF EXISTS v1;
sqlite> DROP VIEW IF EXISTS t1;
Error: use DROP TABLE to delete table t1
sqlite>

Neither VIEW exists, but the behavior is different. I think the OP expects no error in either case.

Why do you think that?

The <name> that you are trying to DROP does exist, therefore the clause part "IF EXIST <name>" is true. So then the command DROP VIEW is applied to that <name>, to which you get the error that it is not a view and YOU made an error and should use DROP TABLE to drop that <name>.

Why would you think that an error on your part to know what you are doing should not be reported as an error?

The command is "DROP VIEW IF EXISTS <name>"
the command to carry out your thoughts would be "DROP IF EXISTS VIEW <test>"
but this is not the command given, and is not a valid command.

I'm not sure I do think that, I was just trying to (maybe) clarify the question. If it's strictly a name issue, what should this do:

% ./sqlite3
SQLite version 3.33.0 2020-06-11 16:04:10
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t1(x);
sqlite> DROP INDEX IF EXISTS t1;
sqlite>

I'm not sure what it should do, but it doesn't report an error. The docs for DROP INDEX don't explicitly mention the IF EXISTS clause, so I can't say what I expect it to do.

Well, that is interesting. To be consistent it should return an error that you need to use 'drop table' to drop table t1.

This is an inconsistency so one or the other is incorrect.

create table t1(x);
drop trigger if exists t1;

also does not return that you have to use "drop table".

Maybe it is because tables and views both reside in the same structure chain in the internal schema data structure. (That is, a "view" is a table-like object, whereas indexes and triggers are not).

Are there parts of SQL syntax such that a table/view name might cause misinterpretation when it appears where a trigger or index name is expected? I suspect that those roles could allow table/view names to be in a different namespace than trigger/index names (with revised language rules.) This is distinctly different from the situation with table versus view names, where they can appear interchangeably in some of the syntax.

(I grant this is not expressed as clearly as maybe it could be.)

The <name> that you are trying to DROP does exist, therefore the clause part "IF EXIST <name>" is true.

No, it does not exist as a view. And that's what I'm telling SQLite to do: If a view named "test" exists, drop it.

Have I tried DROP TABLE, it would have dropped it without discussion.

Hence, the required type of the object to drop is well deduced from the drop statement.

My keen expectation was the if exists condition to be checked, including the type of the object, and the operation fulfilled only if it fully met.

From the documentation:

If the specified view cannot be found and an IF EXISTS clause is present in the DROP VIEW statement, then the statement is a no-op.

But it is not a no-op if there is a table with the given name.

But it is not a no-op if there is a table with the given name.

It is a no-op. It has no side effects. Reporting an error is not an "op" with side effects.

The full line in the DROP VIEW documentation says:

If the specified view cannot be found and the IF EXISTS clause is not present, it is an error. If the specified view cannot be found and an IF EXISTS clause is present in the DROP VIEW statement, then the statement is a no-op. 

which implies (no guarantee) it doesn't return an error.

On the other side, the DROP TABLE docs say:

The optional IF EXISTS clause suppresses the error that would normally result if the table does not exist.

It says nothing about views, so there may be an issue (documentation or otherwise) if we try with drop table:

% ./sqlite3
SQLite version 3.33.0 2020-06-11 16:04:10
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE t1(x);
sqlite> CREATE VIEW v1 AS SELECT * FROM t1;
sqlite> DROP TABLE IF EXISTS v1;
Error: use DROP VIEW to delete view v1
sqlite>

Perhaps a note in the documentation wouldn't hurt to clarify this edge case?

As a single statement, yes.

As part of a whole bunch of statements, it has the severe side effect of canceling the whole stack and requiring me to find out where the error arose, solve it manually, and to continue somewhere.

How should one check if a view is actually a view with pure SQL only? It'll result in a construct like

CASE WHEN EXISTS SELECT * FROM sqlite_master WHERE ... THEN DROP VIEW ... ELSE END;

Which leads us to the question about the sense of "IF EXISTS".

How should one check if a view is actually a view with pure SQL only?

Without consulting sqlite_master, you can't determine, using only SQL, and without potentially generating an SQL error, the nature of any construct, be it a table, view, index, or a function.

it has the severe side effect of canceling the whole stack and requiring me to find out where the error arose

The alternative which you're implying is that no error be generated and the presumably up-coming create view fails instead because there's a table with that name. Either way, it fails. In general it's preferable for software to fail earlier than later.

As Larry pointed out, this sounds very much like a one-time problem which has simply struck a nerve for you, not a recurring thing which is a frequent/recurring hindrance. You fix your (demonstrably incorrect) "whole stack" a single time and it's done.

As Larry pointed out, this sounds very much like a one-time problem which has simply struck a nerve for you, not a recurring thing which is a frequent/recurring hindrance. You fix your (demonstrably incorrect) "whole stack" a single time and it's done.

I cannot do that. At the point I am trying to "DROP VIEW IF EXISTS" (no one has told me about the sense of "IF EXISTS" if I had to check for existance by myself anyway) I simply don't know whether it is a view or a table. If it's a view, it should be dropped. If it's a table, it should be kept.

The same applies to "CREATE VIEW IF NOT EXISTS" (which I haven't tested yet): It should of course do nothing if the given name doesn't exist as a view. Whether it doesn't exist at all or it is a table, should not matter. The current behavior is completely counterintuitive.

It's perfectly correct for "DROP VIEW / CREATE VIEW" to throw an error in this case. But the sole purpose of using "IF [NOT] EXISTS" is to skip that error.

So @everyone who thinks the behavior is correct: Please explain to me how I should drop a view if, and only if, it exists as a view and without giving an error, and with plain SQL only as it has to be carried out within a batch of SQL statements.

Please explain to me how I should drop a view if, and only if, it exists as a view and without giving an error

Stop using the same name for tables and views. Then there is no problem.

I have been following this discussion as an amicus curiae. I might be able to help solve your problem, but I need some more information about your system as a whole.

Presumably, you are generating the SQL which contains "DROP VIEW IF EXISTS..." semi- or fully-automatic. otherwise, you would know what exists and what doesn't and the issue would be moot.

Presuming you are generating the SQL, is there a way to make that process smarter? For example, do you control of you database such that you could add your own (look-aside) table to track the existence of views and tables? And then use that information to generate the SQL. The generated SQL could update the look-aside table so the next time you generated the SQL, it could be smarter.

For example, here is some sample generated SQL:

CREATE TABLE IF NOT EXISTS lookaside (tname TEXT PRIMARY KEY, ttype TEXT CHECK(ttype in ('TABLE','VIEW')));
CREATE TABLE t1 (a TEXT, b INTEGER);
INSERT INTO lookaside (tname, ttype) VALUES('t1','TABLE');
CREATE VIEW v1 AS SELECT a FROM  t1;
INSERT INTO lookaside (tname, ttype) VALUES('v1','VIEW');
CREATE VIEW v2 AS SELECT b FROM  t1;
INSERT INTO lookaside (tname, ttype) VALUES('v2','VIEW');
DROP VIEW v2;
DELETE FROM lookaside WHERE tname='v2';   -- smart gen knows its a view

Your SQL generator can check "lookaside" to find out what exists and what doesn't and generate the correct SQL for each case.

DROP TABLE IF EXISTS <name>;

also returns an error message if <name> is a view rather than a table, in order to tell YOU that YOU made an error in keeping track of YOUR objects.

CREATE TABLE IF NOT EXISTS <name> ...
CREATE VIEW IF NOT EXISTS <name> ...

does not return an error if a table/view <name> exists, because why would it?

I disagree. Generating an error is an op. no-op means 'this command does nothing', not 'this command does nothing to your data'. If generating an error is a no-op, then so is every print command.

If generating an error is a no-op, then so is every print command

The printed error comes from the shell, the no-op result code indicating an error comes from the library.

If the API generates SQLITE_OK to this situation then it's a no-op.

But given the complaint, and the fact that the shell somehow knows there's a table with that name, that doesn't seem to be what's happening. Or does the shell, for some reason, do its own detective work and go looking for a table with that name ?

The shell does no such detective work. When it sees an error return, it emits whatever error message the library associates with the error occurrence. (So the presence of the message does indicate that something other than SQLITE_OK was returned at some phase of the processing outside of stepping or its completion.)

The shell does not somehow "magically know" that there is a table with the name specified.

When SQLite3 starts it loads (parses) the data dictionary (sqlite_master) into internal structures that represent the schema. This dictionary contains (at present) 3 "branches". There is a branch that links all the indexes. There is a branch that links all triggers. There is a branch that links all the table-like objects (tables, views, virtual tables, and so on and so forth -- all the things which one may use as the target of FROM in SELECT ... FROM).

As you can see, this is quite an efficient way of classifying objects. It does not matter whether a thing is a table or a view or a virtual table. What matters is that is if something that can be used as the target of a SELECT FROM -- that is behaves as if it were a table. (A view is nothing more than an ephemeral table).

When you command "DROP VIEW name" SQLlite3 searches the structure that contains table like objects looking for the name you specified. If the name is a view, then it is dropped. However, if it is not the type of thing you asked for an error is raised.

On the other hand, if you command "DROP INDEX name" then only the structures containing the list of indexes are searched. If the name is not found then an error is raised. It does not go on a hunt looking for what you might have meant.

It is your job to command what you mean.

However, in the case of table-like objects which may be the target of SELECT FROM, you may issue a command naming the wrong object type, in which case you get informed of YOUR error.

It is your job to command what you mean.

I am commanding what I mean. I want a view to be dropped if it exists. That's exactly as the documentation says.

However, in the case of table-like objects which may be the target of SELECT FROM, you may issue a command naming the wrong object type, in which case you get informed of YOUR error.

Between the following two scenarios, there's absolutely no difference. Both are, of course, my error:

a) trying to drop something that doesn't exist at all

b) trying to drop a view where a table with this name exists

Following your argument, situation a) would have to raise an error as well.

Your explanation is in error.

DROP TABLE <name>

means find the table-like object called <name>. If it cannot be found, report an error; if it is of type TABLE, then drop it; otherwise report that you are using the wrong command because the table-like object with name <name> is not a TABLE but rather is of type VIEW (or whatever type that table-like object that was found happens to have).

Similarly the command:

DROP VIEW <name>

means find the table-like object called <name>. If it cannot be found, report an error; if it is of type VIEW, then drop it; otherwise report that you are using the wrong command because the table-like object with name <name> is not a VIEW but rather is of type TABLE (or whatever type that table-like object that was found happens to have).

When you insert the conditional IF EXISTS you are modifying the "finding" of the in the "base class" of the inheritance tree and saying that instead of reporting that the object <name> was not found, just do nothing. However if a table-like object with name <name> is found, then report an error if the object is not of the specific instance type you wanted to drop.

So the translation of DROP <type> IF EXISTS <name> is not, as you posit

IF WITHIN <type> EXISTS <name>
   DROP <type> <name>
FI

but rather

IF WITHIN classof(<type>) EXISTS <name>
   DROP <type> <name>
FI

The same applies to the commands DROP INDEX which works on index-like objects, and DROP TRIGGER which works with trigger-like objects.

Presently the set of table-like objects include TABLE's (including VIRTUAL tables) and VIEW's.

The set of index-like objects presently only includes INDEX's.

The set of trigger-like objects presently only includes TRIGGER's.

If a new object called a FUDGYBANGER that was an index-like object so that the set of index-like objects now included FUDGYBANGER's as well and INDEX's, then you would get exactly the same behaviour if you tried to DROP FUDGYBANGER <name> but it was an INDEX rather than a FUDGYBANGER.

It is like if your programming language had a delete command where you had to specify the type, and you created a variable called a of type mudslinger. If you then said "delete guacamole a" you would expect to to get an error saying that "a is not a guacamole, it is a mudslinger". If would not matter whether you said "don't do this if there is no variable named a because there is, in fact, a variable called a -- you are simply being told that you need to keep better track of the objects you have created.

You're correct... I did have the logic clear in my mind before I started to post, but something got confused during entry (lesson: don't post before breakfast!). Your explanation is correct, so I've deleted mine.

I did some checking. Your "expected behavior" is what MySQL does. The "actual behavior" is what PostgreSQL does. I think SQLite should continue to do what PostgreSQL does.

But in PostgreSQL, too, the documentation says:

IF EXISTS -- Do not throw an error if the view does not exist. A notice is issued in this case.

So imho it's a bug there as well (Documented behavior <> Actual behavior).

What would be the problem if "DROP VIEW IF EXISTS" behaved as printed in the documentation? There wouldn't be any adverse effects, would there? It cannot lead to any compatibility issue because the statement obviously hasn't followed the documentation.

Advantages of "DROP VIEW IF EXISTS" raising an error: ?? (I would say: none; those who want errors simply leave out the "IF EXISTS" clause)

Advantages of not raising an error: View can properly be dropped even if a table with same name exists. Why should this behavior be unwanted?? It doesn't make any sense to me at all. The "IF EXISTS" clause is just useless then.

It is not possible, in SQLite or any other DBMS that is going to implement SQL, to have a view with the same name as a table in the same schema. This is because there is no way specified to disambiguate the names in queries or DML. Hence, that last advantage should be dropped from your list.

That it was there makes me wonder (more than before) what you are doing, and what your use case is. Did you anticipate having table/view name collisions, and just want to clear them? If so, it is an illusory problem; it cannot exist.

Do you have quite a number of databases, with a mix of actual populated tables in some and mere views in others, known to share a name? (It's at least conceivably useful; sometimes views are "materialized" into tables for efficiency reasons.)

Setting aside any dispute as to what "ought" to happen per your 1st post, you could make a partial copy of the database(s), plucking out what you want to keep rather than trying to drop what may or may not be there in unknown or forgotten form.

MySQL has been notoriously forgiving + hand-holdy in this regard (and others - like chopping inserted varchars to size and other horrible non-errors). It plays fast-and-loose with the standard, and it works for them, it works for DB beginners or simple implementations, and one could argue that it definitely has merit. (PS - I love MySQL too)

What one shouldn't do, is hold it up as the standard by which others are measured.

You get that (and advocate for) DROPping a DB object by name should be excused if that name does not exist at all in the namespace and the programmer added "IF EXISTS". Here we agree.

That is however different from trying to DROP a DB object by name that DOES exist, but is not of the type you are trying to DROP (or CREATE, for that matter). The needed action in this case is to immediately notify the programmer of that mistake - and we all want to know about that mistake.

The assumption that it has Zero value is simply not true.

If the namespace was unique per object, i.e. a table and a view both named "jerry" could exist side-by-side, then you'd have a point. Since this is not possible, in any way for all Engines (or at least, the mainstream ones) your assertion that:

Advantages of not raising an error: View can properly be dropped even if a table with same name exists.../

Is demonstrably false.

What if you said DROP TABLE IF EXISTS jerry; while a View with the name "jerry" does exist? Would you like the DB to simply do nothing... maybe hold its hands behind its back and whistle softly while staring into the distance like nothing happened, or would you like it to say: "Oi mate, jerry is a view, not a table."? I have made that mistake before and am not so infallible as to be sure to never make it again, so I most certainly would like to be informed.

I get that it would be easier in the specific case you mention, but I certainly don't want the Engine to become forgiving of such a transgression when I'm the culprit doing the bad thing.

[Edit - removed silly mistaken NOT's from the text]

Aside: Inb4 I incur the wrath of every MySQL hacker...

One reading of the top part of the previous message can seem to say that MySQL is for beginners or simple implementations, this is of course not true.

The intended meaning is that the hand-holding proclivities of MySQL is useful/good for beginners and simple systems.

Most seasoned users have STRICT mode enabled by default.

That it was there makes me wonder (more than before) what you are doing, and what your use case is. Did you anticipate having table/view name collisions, and just want to clear them? If so, it is an illusory problem; it cannot exist.

It's not an illusory problem. And of course I do not want (and never wanted and will never want) to have both a view and a table with the same name.

[tl;dr] The use case (which many of you seem to be wondering about) is actually very simple: There are databases created by a software in which the database schema has changed every now and then. In former times, there existed a table "metadata" which has later been replaced by a table "meta" with an entirely different structure. For compatiblity purposes, a view "metadata" has been introduced at the same time which is defined to provide exactly the same layout as the original table with the same name. So any program version can work with database version. (Triggers ensure the correct mapping to "meta" when inserting or updating anything in "metadata"). We are working with these databases and want to change a different table now. And because SQLite doesn't simply provide "ALTER TABLE", but instead requires a 12-step update algorithm, we have to drop any views that refer to the particular table. Unfortunately, "metadata" is one of them. The problem is, I obviously have no way do drop the view only if it exists :(

Advantages of not raising an error: View can properly be dropped even if a table with same name exists.../

This has been a typo of mine. It should, of course, read: "View-dropping can properly be skipped even if a table with the same name exists". Sorry for that.

What if you said DROP TABLE IF EXISTS jerry; while a View with the name "jerry" does exist? Would you like the DB to simply do nothing...

Sure. What else? From the documentation: The optional IF EXISTS clause suppresses the error that would normally result if the *table* does not exist.

I get that it would be easier in the specific case you mention, but I certainly don't want the Engine to become forgiving of such a transgression when I'm the culprit doing the bad thing.

That's what you have "DROP VIEW" (without "IF EXISTS") for. And you'd get a notice or warning anyway even with "IF EXISTS".

The documentations of all RDBMSs that I found and that support the "IF EXISTS" extensions agree:

SQLite documentation:

If the specified view cannot be found and the IF EXISTS clause is not present, it is an error. If the specified view cannot be found and an IF EXISTS clause is present in the DROP VIEW statement, then the statement is a no-op.

PostgreSQL documentation:

Do not throw an error if the view does not exist. A notice is issued in this case.

MariaDB documentation:

The IF EXISTS clause prevents an error from occurring for views that don't exist. When this clause is given, a NOTE is generated for each non-existent view.

SQL Server documentation:

Conditionally drops the view only if it already exists.

That is however different from trying to DROP a DB object by name that DOES exist, but is not of the type you are trying to DROP (or CREATE, for that matter). The needed action in this case is to immediately notify the programmer of that mistake - and we all want to know about that mistake.

Then, as has been pointed out before, a DROP TRIGGER, DROP INDEX, etc. had to raise an error as well. Because the user could have wanted to delete the table instead of the index. Someone recently mentioned the SQL is a declarative language: "you say what you want accomplished, not how it must be accomplished". Here I have the point that I tell the engine exactly what I want - and get a different response.

And, by the way, for an RDBMS that is the supreme example of being forgiving on errors (some keywords: data types, using identifiers as names without quoting, double quoted strings, etc.), this sight is a bit narrow-minded.

Finally, a suggestion for a compromise:

How about adding some clause like "ON ERROR DO NOTHING"? Or a "PRAGMA halt_on_error=FALSE"? Or a "DROP ALL VIEWS"? Any of these would help :)

There is a way to separate the drop statements construction from running them. To wit:

create table ta (x);
create view v as select x as x from ta;
create index i on ta(x);
create trigger tr instead of insert on v begin insert into ta(x) values(new.x); end;

.load eval

create view if not exists ZapAll as select group_concat(zap,';')
 from ( select 'drop '||sm.type||' '||sm.name as zap from sqlite_master sm
   where sm.type in ('table','view','trigger','index') 
    and sm.name in ('ta','v','i','tr','giblets')
   order by sm.type='table',sm.type='view',sm.type='index',sm.type='trigger'
 );

.parameter set $zaps 'select * from ZapAll'
select eval(eval($zaps));

The named objects that are created first are gone after running all of that in the SQLite shell.

Many thanks for your efforts. I'll try that.

I have several comments here:-

First, is there any documentation of this "eval" function? I can't find anything by searching on the sqlite web site. I wrote a user-defined function which can be used in a script to evaluate a possibly dynamically constructed SQL statement, and return any error message as a text string, which solves the OP's problem for me: I don't know if "eval" would have helped me with that.

Second, if tables and views share a namespace, the documentation doesn't appear to mention this.

Third, if tables and views share a namesapce, IMHO it ought to be possible to delete an object in that namespace without needing to know whether it is a table or a view. The current implementation does not allow this.

Fourth, I described what I believe is a perfectly reasonable use case in another post at https://sqlite.org/forum/forumpost/53a2dec184. I maintain a program which has a UI which allows a user to do various useful things on more or less any sqlite database: it examines the schema to find out which objects exist. I have a bunch of test scripts (the program can load and run a script of SQL statements), which of course I run on a test database. A failed test can leave a view or a table still existing. I don't want to have to clean up by hand after errors, so I want my test scripts to DROP any views or tables that they will create. So I want to start off with

DROP VIEW IF EXISTS xxx ; DROP TABLE IF EXISTS xxx ;

for each table or view xxx that the script is subsequently going to create.

I fell over the same issue as the OP in this post.

The solution that works for me is

SELECT exec(NULL, 'DROP VIEW IF EXISTS xxx') ; SELECT exec(NULL, 'DROP TABLE IF EXISTS xxx') ;

where "exec" is the user-defined function that I mentioned above. If the first argument is NULL, it just ignores any error.

Incidentally the program I mentioned above is a valid example of a database using program which does not know what objects exist in the database, except of course by examining the schema.

Well, if you need to know whether "metadata" is a table or a view, your upgrade application should be using a query like:

select type from sqlite_master where name == 'metadata';

and your application will then be told whether it is a table or a view, and can do the proper thing, or have a controlled explosion if the type returned is something other than what you are prepared to cope with.

Or are you just using "hope and pray" scripting?

It would seem that in your uses case, you will need to know before actually starting the procedure if the view exists, and take different paths based on this, if only that if the view existed, you will need to recreate it, but if it didn't, you don't want to create it. (And you may even need to pull the definition so you can recreate it back to its original state, maybe modified as needed), or are you going to do a blind CREATE VIEW IF NOT EXIST (and NOW you want the 'NOT EXIST' to detect that the table does exist), and trust that you apriori know the definition of the view in full detail?

This sort of operation is almost certainly going to be in a program-driven operation, so the code can do the tests and determine the path to take. This is a much different case than this option was built for, to allow the creation of a simple script that can be run through a command-line tool to create/re-create a given environment.

Advantages of "DROP VIEW IF EXISTS" raising an error: ?? (I would say: none; those who want errors simply leave out the "IF EXISTS" clause)

I think it would be more counter-intuitive for CREATE VIEW x to fail after DROP VIEW IF EXISTS x returned no error than for DROP VIEW IF EXISTS x to fail even though x is a table and not a view.

As a compromise, wouldn't it be preferable to change the ERROR to a simple HINT instead? The wording of the message resembles a hint, anyway.

Then it's not an error (which I also concur) while also informing the user in case of unintentional choice of command.

Regarding the view (NPI) expressed that because views and tables are similar objects and share a common namespace, the error makes sense when trying to DROP an existing object of the other type.

If we accept this, we can also accept that either command (DROP VIEW or DROP TABLE) should drop the object, regardless of type.

I think the OP's complain is valid. With DROP VIEW/TABLE IF EXISTS there should be no errors. And that MySQL does the same gives it extra validity.

There have been a lot of comments on this topic, and I want to add a bit of a voice to make you think about what you are trying to do. A Database program should generally understand that state and purpose of the database. It should generally know what sort of things the names represent, at least when it matters.

If we look at what the purpose of statements like DROP VIEW/TABLE IF EXISTS name, it is generally something to run just before creating a new copy of that table or view to reset back to a know state for testing/development or restoring a backup. Such purposes will generally KNOW if a name should have been a table or a view, and if it doesn't match, the erroring out is generally a good thing.

Yes, there are cases where perhaps a view was at some point converted to a table, and to roll back we need to know that to delete to the table to make a view instead, but we should know that and use an appropriate script to roll back.

Your need to delete something, not knowing if it is a table or a view is a bit unusual (especially in the middle of a longish transaction where throwing the error would be a problem, seems to point to a very unusual case.

It can be argued that the current behavior does match the meaning of the statement, in your example above, test DOES exist, it just isn't a view, so if EXISTS is satisfied, and DROP VIEW fails on the wrong type of object. Yes, you could argue that the meaning could be the DROP VIEW IF EXISTS test; could be interpreted as IF VIEW EXISTS, with the VIEW being assumed, but the current implementation doesn't do it that way.

Note, that the statement isn't DROP VIEW test ON ERROR IGNORE,(which doesn't exist as a statement) so the error of test being a table that is the target of a DROP VIEW could be reasonably argued can be raised.

It should generally know what sort of things the names represent, at least when it matters.

With this argument, the IF [NOT] EXISTS clause would be needless anywhere.

Again, I do not want to eliminate the error at all. But that's what "DROP VIEW" (without "IF EXISTS") is for. The only purpose of the "IF EXISTS" clause is to suppress the error. And none of the documentations mention that this is only the case when "some table-like object doesn't exist".

Your need to delete something, not knowing if it is a table or a view is a bit unusual (especially in the middle of a longish transaction where throwing the error would be a problem, seems to point to a very unusual case.

The error is an ABORT, not a ROLLBACK. It does not affect transactions in progress, only the statement that is in error is aborted.

sqlite> begin immediate;
sqlite> create table x(x);
sqlite> drop view if exists x;
Error: use DROP TABLE to delete table x
sqlite> commit;
sqlite> .schema
CREATE TABLE x(x);

The problem is, most people obviously think about using SQLite within an application. There it is, of course, possible to query the list of tables and views and to decide whether or not to drop one or the other. But if I have to do so, the whole "IF EXISTS" construct is dispensable.

But there's also the situation where databases have to be changed outside an application with an SQL-diff. And there I cannot do conditionals to check whether I need to drop a view or a table. And again, if I have to do so, "IF EXISTS" is dispensable at all.

Since views do not have their own b-trees, you could use writable schema mode to drop views (either individually or all at once), although you would then need to force it to reload the schema (what is the best way to do that?).

First: It is a real use case that one may not know if a given object is a view or a table.

We do have a complex process that starts with a table for some values. In some cases this approach has to be dropped and a view replaces the original table. Later running scripts don't know what 'objectname' is.

I realy would appreciate to have DROP VIEW/TABLE xx IF EXISTS running (or a ' ON CONFLICT IGNORE instead). The suggested "writable schema mode" way comes with a smell:

-- create test objects
CREATE VIEW TESTVIEW   AS select msg from (SELECT 1 line, '------------------' msg UNION SELECT 2,'-- here is the testview.')  order by line ;
CREATE TABLE TESTTABLE AS select msg from (SELECT 1 line, '------------------' msg UNION SELECT 2,'-- here is the testtable.') order by line ;
SELECT * FROM TESTVIEW;SELECT * FROM TESTTABLE;


-- this is working:
PRAGMA writable_schema = ON;
DELETE FROM sqlite_master WHERE type = 'view' AND name = 'TESTVIEW';
DELETE FROM sqlite_master WHERE type = 'view' AND name = 'TESTTABLE';
PRAGMA writable_schema = RESET;
DROP TABLE IF EXISTS TESTVIEW;
DROP TABLE IF EXISTS TESTTABLE;

instead of

-- this is not working but would be my favorite:
DROP VIEW  IF EXISTS TESTVIEW;
DROP VIEW  IF EXISTS TESTTABLE;
DROP TABLE IF EXISTS TESTVIEW;
DROP TABLE IF EXISTS TESTTABLE;

Thank you very much Horst for your workaround. This helped me indeed, however I second by all means that drop view if not exists should not raise that error.