CLI: .expert fails with "no such column" when query contains generated columns
(1) By Thomas Hess (luziferius) on 2020-12-08 14:08:26 [link] [source]
I think I found an issue with the experimental .expert command in the sqlite3 CLI. This is related to generated columns.
The issue triggers when you directly reference a generated column in a query and when a database view mentions a generated column, even if it is not part of any join or where clause.
Consider this schema:
CREATE TABLE SomeObject (
id_ INTEGER PRIMARY KEY,
x TEXT GENERATED ALWAYS AS(HEX(id_)) VIRTUAL
);
CREATE VIEW ObjectView AS
SELECT id_, A.x AS AX, B.x AS BX
FROM SomeObject AS A
JOIN SomeObject AS B USING (id_)
;
Running this query works and yields no results (expected).
When you turn on .expert
, this happens:
sqlite> SELECT * from ObjectView;
sqlite> .expert
sqlite> SELECT * from ObjectView;
Error: no such column: A.x
Note that the generated column is not used in the JOIN clause defined in the view.
Accessing x
directly:
sqlite> .expert
sqlite> SELECT x FROM SomeObject;
Error: no such column: x
sqlite> SELECT x FROM SomeObject;
sqlite>
Accessing only id_
works:
sqlite> .expert
sqlite> SELECT id_ FROM SomeObject;
(no new indexes)
SCAN TABLE SomeObject
sqlite> SELECT id_ FROM SomeObject;
sqlite>
Used versions:
SQLite 3.35.0 2020-12-07 23:14:25 4b286129138d44e6f8e9b3450289941e01d20fdfb9d0b5d846031425e8ca6b49
zlib version 1.2.11
This also happens in 3.33.0, as shipped on Ubuntu 20.10.