Bug: inconsistent result when an optimization is on and off
For this case:
-- Version: latest(hash: check-in: 832ac4c1) CREATE TABLE t0(c0 INT,c1 INT); INSERT INTO t0 VALUES(10,10); SELECT * FROM t0 JOIN (SELECT CAST(c0 AS TEXT) AS c2 FROM t0 UNION ALL SELECT c1 FROM t0) WHERE 10=c2; -- 2 lines .testctrl optimizations 0x00000001; SELECT '------'; SELECT * FROM t0 JOIN (SELECT CAST(c0 AS TEXT) AS c2 FROM t0 UNION ALL SELECT c1 FROM t0) WHERE 10=c2; -- 1 lines
When the optimization option
SQLITE_QueryFlattener is off, we get incorrect result, which indicates that something could be going wrong here.
Hope it will be solved properly if there is indeed a problem here, and if there is no problem, your kind explanation would be appreciated.
Looking forward to your reply.
I believe that the documentation at https://sqlite.org/datatype3.html#affcompoundview applies in this case. In other words, this is the same (non-)issue that you reported previously at forum post 02d7be94d7.
You have a compound sub-query in which the output column sometimes has TEXT affinity and other times has INTEGER affinity. So the affinity of the C2 column in the overall sub-query is indeterminate. According to the documentation:
Different constituent SELECT statements might be used to determine affinity at different times during query evaluation. The choice might vary across different versions of SQLite. The choice might change between one query and the next in the same version of SQLite. The choice might be different at different times within the same query. Hence, you can never be sure what affinity will be used for columns of a compound SELECT that have different affinities in the constituent subqueries.
Best practice is to avoid mixing affinities in a compound SELECT if you care about the datatype of the result. Mixing affinities in a compound SELECT can lead to surprising and unintuitive results.
Long time no see, Richard.
I didn't realize that it's a compound query suiting that rule because I only remember it is a rule applicable to view.
After all, thanks for your patient explanation, and sorry for the false positive report.
I will read the documentation more carefully before reporting any issues.