SQLite

View Ticket
Login
Ticket Hash: d52a29a9e6bc55c55be01321c46c31f193aa9712
Title: Unexpected affinity conversion in view
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-08-05 20:41:11
Version Found In: 3.29.0
User Comments:
mrigger added on 2019-08-05 11:27:47:

Consider the following test case:

CREATE TABLE t0(c0, c1 TEXT);
CREATE VIEW v0(c0) AS SELECT SUM(t0.c1) FROM t0;
INSERT INTO t0(c0, c1) VALUES ('a', 1);
SELECT * FROM v0, t0 WHERE t0.c1 <= v0.c0; -- unexpected: row is not fetched

Unexpectedly, the WHERE clause yields false. t0.c1 has TEXT affinity. v0.c0 refers to SUM(t0.c1) and should have no affinity. This can also be verified using the following PRAGMA:

PRAGMA table_info(v0); -- 0|c0||0||0

Thus, I would expect that v0.c0 is converted to TEXT. However, in this case, the row should have been fetched:

SELECT * FROM v0, t0 WHERE t0.c1 <= CAST(v0.c0 AS TEXT); -- 1|a|1


drh added on 2019-08-05 13:06:14:

Documentation changed [1] to reflect the actual behavior of the code for the past 10 years. Current behavior was established in 2009 by check-in [33cf83591e6e1387] but the documentation was 8 years later in 2017 at [524ba450fb959a75].

Changing the code to match the documentation risk breaking programs that are already in the field. Hence, it is better to change the documentation to match the code.


drh added on 2019-08-05 20:41:11:

Change of plan: We will now go back and change the implementation to match the documentation.