SQLite

View Ticket
Login
Ticket Hash: 0a5e2c1dcbefeae5ffdae7ca317132c38e184b28
Title: Unexpected affinity conversion for view column in IN operator
Status: Fixed Type: Code_Defect
Severity: Important Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2019-08-06 23:28:25
Version Found In: 3.29.0
User Comments:
mrigger added on 2019-08-06 20:43:05:

Consider the following test case:

CREATE TABLE t0(c0 TEXT);
CREATE VIEW v0(c0) AS SELECT t0.c0 FROM t0;
INSERT INTO t0(c0) VALUES ('0');
SELECT 0 IN (c0) FROM v0; -- expected: 0, actual: 1

The expression 0 in (c0) unexpectedly yields TRUE, although c0 is a string and 0 an integer; according to the documentation, values in the right list should have no affinity, so I would not expect an affinity conversion to be performed. When fetching from the table, rather than the view, the result is as expected:

SELECT 0 IN (c0) FROM t0; -- 0