/ View Ticket
Login
2019-08-06
15:32
Ensure that columns of views and sub-queries that are expressions with no affinity are comparied without any type conversions, as required in the documentation. Tickets [61c853857f40da49] and [d52a29a9e6bc55c5]. (check-in: 9c8c1092 user: drh tags: trunk)
2019-08-05
20:41 Ticket [d52a29a9] Unexpected affinity conversion in view status still Fixed with 4 other changes (artifact: 7c070101 user: drh)
13:06 Fixed ticket [d52a29a9]. (artifact: ac513e2e user: drh)
11:27 New ticket [d52a29a9]. (artifact: c86e9f56 user: mrigger)

Ticket UUID: 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.