For query:
-----------------------------------------------------
```SQL
CREATE TABLE v0 ( v2 INT, v1 INT );
INSERT INTO v0 VALUES ( 1, 10 );
INSERT INTO v0 VALUES ( 1, 0 );
INSERT INTO v0 VALUES ( 10, 10 );
INSERT INTO v0 VALUES ( 10, 0 );
/* INDEX STMT */
CREATE INDEX v3 ON v0 ( v2, v1 );
/* STMT 1 */
SELECT * FROM v0 WHERE ( v2 IN ( SELECT v2 FROM v0 ORDER BY max ( nth_value ( v1, 10 ) OVER( ), COUNT () ) ) );
/* Outputs " 1|0 1|10 ". 2 matches.*/
/* STMT 2 */
SELECT (v2 IN ( SELECT v2 FROM v0 ORDER BY max ( nth_value ( v1, 10 ) OVER( ), COUNT ( ) ) )) FROM v0;
/* Output " 1 1 1 1 ". 4 matches. */
/* STMT 3 */
SELECT v2 FROM v0 ORDER BY max ( nth_value ( v1, 10 ) OVER( ), COUNT () );
/* Output 1 */
```
-----------------------------------------------------
STMT 1 has an inconsistent output compared to STMT 2, which is just a rewritten version of the former. Because we can see from STMT 3 that the IN subquery from STMT 1, STMT 2 returns value 1, and column v2 only has 2 rows equal to 1, we suspect that there might be some errors from STMT 2.
Additionally, we notice by removing the INDEX STMT, the inconsistency is gone and STMT 2 outputs 2 matches as expected.
This behavior is reproducible in SQLite version 3.32.3.
This is a further discovery and an extension from post:
<https://sqlite.org/forum/forumpost/5d3d400d49>. We still appreciate any explanation available that could help us better understand the STMT 3 used in this query sets. Thank you.