SQLite

CREATE TABLE customer (id INT PRIMARY KEY);
CREATE TABLE apr (id INT PRIMARY KEY, apr REAL);

CREATE VIEW v1 AS
SELECT c.id, i.apr
FROM customer c
LEFT JOIN apr i ON i.id=c.id;

CREATE VIEW v2 AS
SELECT c.id, v1.apr
FROM customer c
LEFT JOIN v1 ON v1.id=c.id;

INSERT INTO customer (id) VALUES (1);
INSERT INTO apr (id, apr) VALUES (1, 12);
INSERT INTO customer (id) VALUES (2);
INSERT INTO apr (id, apr) VALUES (2, 12.01);

--works as expected
SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1;
--wrong type value for "apr"
SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2;

The two queries at the end should give identical results, however the second query returns apr as an integer when it can be expressed as an integer, which is incorrecct.

The problem was originally reported on the mailing list by Mark Brand.

The problem was introduced by automatic indices with check-in [2364313142668b]. The issue is that REAL values are automatically convert into INT if they can be prior to storage in tables an indices. This saves storage space since INT is typically smaller than REAL. (A integer 0 takes up 1 bytes versus 9 bytes for 0.0.) The conversion back to REAL happens automatically for real tables. But if the table is on a manifestation of a view, then the conversion back to REAL is omitted, resulting in an incorrect type on the result.

The problem can be worked around by disabling automatic indices, which will prevent any indices on manifestations of views.