Inconsistent output possibly due to affinity issue
(1) By Yu Liang (LY1598773890) on 2021-05-25 19:26:28 [link] [source]
CREATE TABLE v0 ( v2 REAL, v1 ); INSERT INTO v0 VALUES ( 10, 11 ); UPDATE v0 SET v1 = v2; SELECT * FROM v0 WHERE v1 = 10 and v1 LIKE 10; /* 10.0 | 10.0 */ SELECT v1 = 10 and v1 LIKE 10 FROM v0; /* 0 */
The two SELECT statements return inconsistent results. We do not know whether this is a potential bug or an expected behavior due to affinity issue.
The behavior is reproducible with Fossil: 708ce7ad8acee702d08d1987aa253b0bfc3fd97255d6e4153122b03eba337570. But it does not appear on stable release SQLite 3.31.1.
Looking forward to your reply.
(2) By Keith Medcalf (kmedcalf) on 2021-05-25 20:46:05 in reply to 1 [source]
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
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).
(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.
(3) By Yu Liang (LY1598773890) on 2021-05-25 22:07:33 in reply to 2 [link] [source]
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.
(4.1) By Keith Medcalf (kmedcalf) on 2021-05-25 23:17:39 edited from 4.0 in reply to 3 [link] [source]
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>
(5) By Keith Medcalf (kmedcalf) on 2021-05-27 10:28:25 in reply to 1 [link] [source]
Fixed on trunk https://www.sqlite.org/src/info/9be208a6d70582c6
(6) By Yu Liang (LY1598773890) on 2021-05-27 18:02:35 in reply to 5 [link] [source]
Thank you for the patch, and the detail explanation.