|Title:||Unexpected affinity conversion in view|
|Last Modified:||2019-08-05 20:41:11|
|Version Found In:||3.29.0|
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  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.