Trigger conditions: the only row in rt2 also supplies the only candidate value for the subquery. Replacing (SELECT c1 FROM rt2) with a copied version of the same values should not change the membership test, so the filter should still keep that row and AVG(CAST(c0 AS REAL)) should stay 1.0. SQLite instead treats the copied subquery value as non-matching and returns NULL.

CREATE VIRTUAL TABLE rt2 USING rtree_i32(c0, c1, c2, c3, c4, +c5 INT);
INSERT INTO rt2(c1) VALUES (0.16872286295631456);

CREATE TABLE TEMP_DATA (rt2_c1);
INSERT INTO TEMP_DATA
SELECT c1
FROM rt2;
CREATE TEMP VIEW VIEW_TEMP AS
SELECT +rt2_c1 AS rt2_c1
FROM TEMP_DATA;

SELECT AVG(CAST(c0 AS REAL))
FROM rt2
WHERE json_quote(0) IN (SELECT rt2_c1 FROM VIEW_TEMP); -- Expected correct result: 1.0  -- actual Wrong result: NULL

Dear yxchen,

please use the bug report forum at https://sqlite.org/bugs/forum to report what you consider to be bugs.

IMHO, in this and your other recent posts, you are using implicit type conversions where the behavior isn't defined strict or not as you seem to assume it is. But the detailed discussion is really what the other forum is for.

Thank you for your attention.

Regards, ralf