SQLite Forum

Inconsistent output possibly due to affinity issue
Login
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>
```