Creating view malforms database

[mohmad-null]

3.4.4, 3.3.3, even 2.1.5

Create a database:

CREATE TABLE urls (
	url_id			INTEGER		PRIMARY KEY		AUTOINCREMENT,
	url				TEXT	 	UNIQUE						NOT NULL
);

CREATE TABLE lookups (
	lookup_id		  	INTEGER  	PRIMARY KEY AUTOINCREMENT,
	url_id			 	INTEGER 	REFERENCES urls (url_id) ON DELETE CASCADE		NOT NULL,
	content_hash	   	TEXT	 	COLLATE NOCASE,
	retrieval_datetime 	DATETIME	DEFAULT (datetime('now'))
);

CREATE VIEW v_most_recent_lookup_per_url AS
	SELECT url_id,
		   MAX(retrieval_datetime) AS retrieval_datetime
	  FROM lookups
	 GROUP BY url_id;
	 

All runs fine.

Now run this, replacing "db2" with whatever the schema alias is:

CREATE VIEW db2.v_latest_content_hash_for_url AS
	SELECT
		url,
		content_hash
	FROM
		db2.lookups l
	JOIN
		(SELECT
			*
		FROM
			db2.v_most_recent_lookup_per_url
		) sub
		ON l.url_id = sub.url_id and l.retrieval_datetime = sub.url_id
	JOIN
		db2.urls USING (url_id)

SQLite studio will now drop the entire database and be unable to re-open it, as it's now "malformed".

The problem is the schema part of CREATE VIEW [schema].v_latest_content_hash_for_url - if you leave it empty SQLite will refuse to run it. If you add a schema, it'll break.

The same create-view query runs just fine via Python, but then SQLiteStudio can't open the file ("malformed").