SQLite User Forum

Incorrect result for COUNT on view with window function
Login

Incorrect result for COUNT on view with window function

(1) By ChiZhang on 2023-03-03 02:19:28 [source]

Consider the following test case:

CREATE TABLE t0(c0);
INSERT INTO t0(c0) VALUES (0);
CREATE VIEW v0(c0) AS SELECT TOTAL(0) OVER (PARTITION BY t0.c0) FROM t0;

SELECT COUNT(*) FROM v0 WHERE ('1' IS NOT('1' NOTNULL))-(0); 

This query produces 0, which I believe, is unexpected. Since ('1' IS NOT('1' NOTNULL)) will return 1, so this condition is true and I expect the result to be 1. Note that this reproduces also on the latest trunk version (8fe13f7a).

(2) By Richard Hipp (drh) on 2023-03-03 21:01:42 in reply to 1 [link] [source]

Should now be fixed on trunk.