[sqlite] unexpected effect of view nesting on type conversion

[Mark Brand]

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