SQLite Forum

Inconsistent output possibly due to affinity issue
Thanks for the explanation, Keith. Now I understand the cause of this problem. Whether to propagate constant in the SELECT expression really depends on the design of SQLite. However, I guess the following query, which is slightly modified from the original one, may be useful for us to think about this problem again.

CREATE TABLE v0 ( v2 REAL, v1 );
INSERT INTO v0 VALUES ( 10, 11 );
UPDATE v0 SET v1 = v2;

SELECT * FROM v0 WHERE v1 LIKE 10 AND v1 = 10;
/* 10 | 10 */
/* (EMPTY) */

In this updated query, the condition is only being placed in the WHERE clause, so we don't need to worry about the different propagation rules between different clause. However, the inconsistent behavior still persist with just the addition of AND statement. Here, the propagation rule is overwriting the result of a 'FALSE AND TRUE' condition to TRUE, which is unexpected. 

Looking forward to your reply.