Constant propagation is taking place in the where clause expression, but not in the select expression.

That is, in the first case

WHERE v1 = 10 and v1 like 10

devolves to

WHERE v1 = 10 and 10 like 10

However, in the select clause the constant 10 is not propagated and the expression is evaluated as written and the stringification of v1 (when a real) is not LIKE the stringification of the integer 10 (an integer).

So either
(a) constant propagation should also occur in the SELECT expressions or
(b) the result is correct.

While this may appear inconsistent, I cannot state whether it is or not. It is a consequence of the "query optimization" not optimizing the select expression.

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 */
SELECT * FROM v0 WHERE v1 LIKE 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.

This is the same thing just spoken differently.

v1 has no affinity. the constant 10 has no affinity.

WHERE v1 LIKE 10 and v1 = 10

results propagates the integer constant 10 so the condition becomes:

WHERE 10 LIKE 10 AND v1 = 10

which is true for the row where v1 has the value 10 (when the real number 10.0 is compared to the integer number 10, the result is TRUE). Similary when the integer 10 is strigified it is like the integer 10 when stringified.

In the second case

WHERE v1 LIKE 10

v1 is a REAL number. When stringified the result is the string '10.0'. When the integer constant 10 is stringified, the result is '10'. '10.0' LIKE '10' is False. Hence the row does not satisfy the condition.

sqlite> select 10. like 10;
┌─────────────┐
│ 10. like 10 │
├─────────────┤
│ 0           │
└─────────────┘
sqlite> select cast(cast(10 as real) as text), cast(10 as text);
┌────────────────────────────────┬──────────────────┐
│ cast(cast(10 as real) as text) │ cast(10 as text) │
├────────────────────────────────┼──────────────────┤
│ 10.0                           │ 10               │
└────────────────────────────────┴──────────────────┘
sqlite> select '10.0' like '10';
┌──────────────────┐
│ '10.0' like '10' │
├──────────────────┤
│ 0                │
└──────────────────┘
sqlite>

Fixed on trunk https://www.sqlite.org/src/info/9be208a6d70582c6

Thank you for the patch, and the detail explanation.