Hi,
I've run into a phenomenon I don't understand where view nesting affects
types.
Seen in sqlite 3.7.6.3.
The following is my attempt at a minimal case that produces this
behavior. Here a real is unexpectedly converted to int.
CREATE TABLE customer (
id INT,
PRIMARY KEY (id)
);
-- annual percentage points
CREATE TABLE apr (
id INT,
apr REAL,
PRIMARY KEY (id)
);
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;
/*
1|0.12|real
2|0.1201|real
*/
--fails expectation to agree with v1
SELECT id, (apr / 100), typeof(apr) apr_type FROM v2;
/*
1|0|integer
2|0.1201|real
*/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
