I believe this is a demonstration of the Garbage-In-Garbage-Out principle at work.

What do you expect to happen?

All i can see is that the defective create view does not appear to actually be aborted at the statement level bit it takes rather a bit of investigation to determine this. Is this of which you are complaining?

The statement:

   CREATE VIEW IF NOT EXISTS IF AS SELECT null;

Generates an entry in the sqlite_schema table that contains the following SQL:

   CREATE VIEW IF AS SELECT null;

In other words, the "IF NOT EXISTS" clause gets removed. That is unfortunate, because with the IF NOT EXISTS clause, the statement is successfully parsed and generates a new view named "IF". But without the IF NOT EXISTS clause, it is a syntax error. And this syntax error gets inserted into the sqlite_schema table, where it causes problems whenever the schema is parsed again in the future.

You can create similar problems using a statement like:

   CREATE TABLE IF NOT EXISTS IF(a,b,c);

There are probably other variations on this same issue.

Yes, but this behaviour ONLY exists inside a transaction.

In autocommit it does not exist.

So the underlying cause of the difficulty has to do with the statement backout in an explicit transaction since it functions correctly in an implicit transaction.

SQLite version 3.34.0 2020-10-26 16:56:18
Enter ".help" for usage hints.
sqlite> create view if not exists if as select null;
Error: malformed database schema (if) - near "as": syntax error
sqlite> .schema
sqlite> create table x(x);
sqlite> insert into x values (1),(2);
sqlite> select * from x;
1
2
sqlite> create view if not exists if as select null;
Error: malformed database schema (if) - near "as": syntax error
sqlite> select * from x;
1
2
sqlite> .schema
CREATE TABLE x(x);
sqlite> begin;
sqlite> create view if not exists if as select null;
Error: malformed database schema (if) - near "as": syntax error
sqlite> .schema
Error: malformed database schema (if) - near "as": syntax error
sqlite> select * from x;
Error: malformed database schema (if) - near "as": syntax error
sqlite> rollback;
sqlite> .schema
CREATE TABLE x(x);
sqlite> select * from x;
1
2

Right. So I think the solution will be as simple as making sure a statement journal exists for all CREATE statements. Hence it probably will not be a big change. But I have not verified this.

Any way you slice it, this is a corner case.

There are probably other variations on this same issue.

The issue seems to occur with 'savepoint' too, i.e.:

 savepoint abcdef;
 cReaTE vieW IF nOt eXIsTs  if  aS sElEcT * ;

Using savepoint means that you are inside a transaction -- either it was explicity begun with a "begin" statement or was implicitly created by the savepoint command, so this should be expected.

The solution is the same and fixes the issue for all transactions no matter how that state was commenced.

Makes sense, thanks!

Unable to repro. I always get "no such table: V" after the line "CREatE temp TrIGGeR if nOt eXists V DeleTe on V when upper ( * ) begiN seLect * ; enD ;"

Fixed at check-in 991ca9b26bacd8f6.

Thank you for the bug report. A fix will be forthcoming.

In the meantime, if you create a corrupt schema this way by accident, and want to recovery the database file, you can do so using the following command sequence:

   PRAGMA writable_schema=ON;
   DELETE FROM sqlite_master WHERE name LIKE 'if';
   PRAGMA writable_schema=OFF;