Inconsistent behavior when using nth_value and COUNT
(1.1) Originally by Yu Liang (LY1598773890) with edits by Richard Hipp (drh) on 2021-05-07 14:48:59 from 1.0 [source]
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.
(2.1) By Yu Liang (LY1598773890) on 2021-05-07 21:43:30 edited from 2.0 in reply to 1.1 [link] [source]
It seems that an update of code is being proposed https://sqlite.org/src/info/0d11d777c8d368f0, that block the aggregation function such as COUNT in the ORDER BY clause. The latest code clear some of the confusions we asked in the original thread https://sqlite.org/forum/forumpost/5d3d400d49. However, when testing the latest commit with the query being questioned in this thread, we still see the inconsistency that 4 matches being returned from STMT 2. We wonder whether this output is an expected behavior.
(3) By Richard Hipp (drh) on 2021-05-07 22:43:41 in reply to 2.1 [link] [source]
STMT 2 should raise an error in the ORDER BY clause. However, it recognizes that the ORDER BY clause is superfluous and deletes it prior to getting to the point where it sees that the ORDER BY clause contains an illegal aggregation function. Hence, the error never gets raised.
We are not sure what, if anything, should be done about this. In any event, it is a low-priority issue.
(4) By Yu Liang (LY1598773890) on 2021-05-08 18:41:03 in reply to 3 [link] [source]
Thank you for the information.